PostgreSQL で連番を生成する
PostgreSQL で連番を生成するには generate_series 関数が大変便利です。 特に数値の連番値や日付や時刻の連続した値は至る場面で必要になります。数値の連番を生成する
まず数値の連番を生成します。ここでは１から１０までの数値を生成してみます。=> SELECT gen FROM generate_series(1, 10, 1) AS gen; gen ----- 1 2 3 4 5 6 7 8 9 10 (10 rows)generate_series 関数の最初の引数は開始値、次が上限値、その次が増分値となります。 ですので上記例では１が開始値、１０が上限値、そして１づつ増分する、という意味になります。
今度は増分値を２にしてみます。１０が上限値なので９までで表示が終了しているのがわかります。
=> SELECT gen FROM generate_series(1, 10, 2) AS gen; gen ----- 1 3 5 7 9 (5 rows)
さらに今度は１０から１までの増分値ー２という条件で実行すると次のようになります。
=> SELECT gen FROM generate_series(10, 1, -2) AS gen; gen ----- 10 8 6 4 2 (5 rows)
連続した日付や時刻を生成する
今度は generate_series 関数で日時(timestamp)の連続値を生成してみます。ここでは 2017-09-01 00:00:00 から 2017-09-30 23:59:59 まで、増分値１日 という条件で実行してみます。
=>
SELECT
gen
FROM generate_series('2017-09-01 00:00:00'::timestamp with time zone,
'2017-09-30 23:59:59'::timestamp with time zone,
'1 day'::interval) AS gen;
gen
------------------------
2017-09-01 00:00:00+09
2017-09-02 00:00:00+09
2017-09-03 00:00:00+09
2017-09-04 00:00:00+09
2017-09-05 00:00:00+09
2017-09-06 00:00:00+09
2017-09-07 00:00:00+09
2017-09-08 00:00:00+09
2017-09-09 00:00:00+09
2017-09-10 00:00:00+09
2017-09-11 00:00:00+09
2017-09-12 00:00:00+09
2017-09-13 00:00:00+09
2017-09-14 00:00:00+09
2017-09-15 00:00:00+09
2017-09-16 00:00:00+09
2017-09-17 00:00:00+09
2017-09-18 00:00:00+09
2017-09-19 00:00:00+09
2017-09-20 00:00:00+09
2017-09-21 00:00:00+09
2017-09-22 00:00:00+09
2017-09-23 00:00:00+09
2017-09-24 00:00:00+09
2017-09-25 00:00:00+09
2017-09-26 00:00:00+09
2017-09-27 00:00:00+09
2017-09-28 00:00:00+09
2017-09-29 00:00:00+09
2017-09-30 00:00:00+09
(30 rows)
与えた条件通りに１日毎の日時が得られました。
もちろん１時間毎のデータも簡単に生成できます。連続値生成の対象期間は 2017-09-01 00:00:00 から 2017-09-02 23:59:59 までとしています。
=>
SELECT
gen
FROM generate_series('2017-09-01 00:00:00'::timestamp with time zone,
'2017-09-02 23:59:59'::timestamp with time zone,
'1 hour'::interval) AS gen;
gen
------------------------
2017-09-01 00:00:00+09
2017-09-01 01:00:00+09
2017-09-01 02:00:00+09
2017-09-01 03:00:00+09
2017-09-01 04:00:00+09
2017-09-01 05:00:00+09
2017-09-01 06:00:00+09
2017-09-01 07:00:00+09
2017-09-01 08:00:00+09
2017-09-01 09:00:00+09
2017-09-01 10:00:00+09
2017-09-01 11:00:00+09
2017-09-01 12:00:00+09
2017-09-01 13:00:00+09
2017-09-01 14:00:00+09
2017-09-01 15:00:00+09
2017-09-01 16:00:00+09
2017-09-01 17:00:00+09
2017-09-01 18:00:00+09
2017-09-01 19:00:00+09
2017-09-01 20:00:00+09
2017-09-01 21:00:00+09
2017-09-01 22:00:00+09
2017-09-01 23:00:00+09
2017-09-02 00:00:00+09
2017-09-02 01:00:00+09
2017-09-02 02:00:00+09
2017-09-02 03:00:00+09
2017-09-02 04:00:00+09
2017-09-02 05:00:00+09
2017-09-02 06:00:00+09
2017-09-02 07:00:00+09
2017-09-02 08:00:00+09
2017-09-02 09:00:00+09
2017-09-02 10:00:00+09
2017-09-02 11:00:00+09
2017-09-02 12:00:00+09
2017-09-02 13:00:00+09
2017-09-02 14:00:00+09
2017-09-02 15:00:00+09
2017-09-02 16:00:00+09
2017-09-02 17:00:00+09
2017-09-02 18:00:00+09
2017-09-02 19:00:00+09
2017-09-02 20:00:00+09
2017-09-02 21:00:00+09
2017-09-02 22:00:00+09
2017-09-02 23:00:00+09
(48 rows)
また別の解法として、次のように generate_series 関数で連続した数値を生成し、それを SELECT の 中で日付データ(ここでは current_date 関数で今日の日付を取得しています)と足し算を行うことで 連続した日付を得られます。
=> SELECT current_date + gen FROM generate_series(1, 5, 1) AS gen; ?column? ------------ 2017-09-23 2017-09-24 2017-09-25 2017-09-26 2017-09-27 (5 rows)
なお PostgreSQL-9.6.x バージョンで確認した限りでは、小数点以下の増分値が指定できますが、 PostgreSQL-9.1.x バージョンでは未対応でした。
=> SELECT * FROM generate_series(1, 2, 0.5);
generate_series
-----------------
1
1.5
2.0
(3 rows)
