PostgreSQLで連番を作るGENERATE_SERIES関数を使ってみた

#SQL

記事をシェア：

コピー

こんにちは。関口です。

SQLでデータを抽出する際に、取得元のデータは歯抜けになっているけれど、固定件数分取得したい場面って結構ありますよね。今回はそんな時に便利なGENERATE_SERIES関数の使い方を紹介していきます！

検証環境

検証で使用した環境は以下の通りです。

PostgreSQL: 12.3

GENERATE_SERIES関数とは

GENERATE_SERIES関数とは、集合を返す関数です。連続した値を生成することができます。まずは文法を紹介します。

GENERATE_SERIES(start, stop, step)

startには生成する連続値の最初の値を設定します。stopには生成する連続値の最後の値を設定します。stepには生成する連続値の刻み幅を設定します。stepは省略可能で、省略した場合は刻み１で連続値を生成します。いずれもint型または、bigint型です。

連続した数値を生成する

では早速使ってみましょう！まずは基本の形として、１から１００の連続値を生成してみます。

// 実行SQL
SELECT GENERATE_SERIES(1, 100);

// 実行結果
 generate_series
-----------------
               1
               2
               3
               4
               5
　～～～中略～～～
              97
              98
              99
             100
(100 行)

今回はSELECT句だけで記述してみましたが、FROM句に記述しても同じ結果になります。連番で生成した値の形を変えたい場合はFROM句に記述しましょう。

連続した数値を生成する（stepの利用）

今度はstep（刻み幅）を設定して、１から１００の中から奇数だけを取得してみましょう。

// 実行SQL
SELECT *
FROM GENERATE_SERIES(1, 100, 2);

// 実行結果
 generate_series
-----------------
               1
               3
               5
               7
               9
　～～～中略～～～
              93
              95
              97
              99
(50 行)

今回はFROM句に記述してみました。最後の値で設定した「１」から２ずつ刻んだ値の取得となるので、奇数だけが取得できるわけですね～。連続値の最後の値で「１００」を設定したからといって、取得されるわけではありません。

刻み幅にマイナス値を設定することも可能です。その際は「start ＞ stop」となるように値を設定してくださいね。

連続した日付を生成する

さて、同じ要領で今度は連続した日付を生成してみましょう！今日から１０日後までの日付を取得します。

// 実行SQL
SELECT *
FROM GENERATE_SERIES(NOW(), NOW() + CAST('10 DAYS' AS INTERVAL), '1 DAY');

// 実行結果
        generate_series
-------------------------------
 2021-12-27 13:18:52.742578+09
 2021-12-28 13:18:52.742578+09
 2021-12-29 13:18:52.742578+09
 2021-12-30 13:18:52.742578+09
 2021-12-31 13:18:52.742578+09
 2022-01-01 13:18:52.742578+09
 2022-01-02 13:18:52.742578+09
 2022-01-03 13:18:52.742578+09
 2022-01-04 13:18:52.742578+09
 2022-01-05 13:18:52.742578+09
 2022-01-06 13:18:52.742578+09
(11 行)

年の瀬にこのブログを書いていることがバレてしまいましたが、年や月の切り替わりもバッチリですね！笑

連続した日付を生成する場合は、引数がtimestamp型または、timestamp with time zone型である必要があります。また、step（刻み幅）は必須です。結果をDATE型にしたい場合は、SELECT句で型変換（CAST）しましょう。

既存テーブルと結合する

いままでの応用として、既存テーブルと結合して歯抜けデータを固定件数分取得してみましょう！今回使用するテストテーブルはこちら。

SELECT * FROM test_table;

 id | target_date | type_code |   feeling
----+-------------+-----------+--------------
  1 | 2021-12-28  |         1 | 愉快
  2 | 2021-12-28  |         2 | 集中
  3 | 2021-12-29  |         2 | 忙しい
  4 | 2021-12-30  |         1 | 穏やか
  5 | 2021-12-30  |         2 | ゆっくりする
  6 | 2021-12-31  |         1 | 嬉しい
  7 | 2022-01-01  |         1 | お祭り気分
  8 | 2022-01-01  |         2 | 幸せ
  9 | 2022-01-03  |         1 | 希望に満ちた
 10 | 2022-01-04  |         2 | 気楽
(10 行)

idとtype_codeはINTEGER型。target_dateはDATE型。feelingはTEXT型です。target_dateの日付毎にtype_codeの1と2が存在します。GENERATE_SERIES関数を使って、歯抜けになっているtarget_dateとtype_code毎に、今日から１０日後までの固定件数分、feeling項目を取得してみます。

// 実行SQL
SELECT
  generate.g_date
  , generate.g_type_code
  , tt.feeling
FROM (
  SELECT
    CAST(g_date AS DATE)
    , g_type_code
  FROM
    GENERATE_SERIES(NOW(), NOW() + CAST('10 DAYS' AS INTERVAL), '1 DAY') AS g_date
    CROSS JOIN GENERATE_SERIES(1,2) AS g_type_code
) generate
LEFT JOIN test_table AS tt
  ON tt.target_date = generate.g_date
  AND tt.type_code = generate.g_type_code
ORDER BY
  generate.g_date
  , generate.g_type_code
;

// 実行結果
   g_date   | g_type_code |   feeling
------------+-------------+--------------
 2021-12-27 |           1 |
 2021-12-27 |           2 |
 2021-12-28 |           1 | 愉快
 2021-12-28 |           2 | 集中
 2021-12-29 |           1 |
 2021-12-29 |           2 | 忙しい
 2021-12-30 |           1 | 穏やか
 2021-12-30 |           2 | ゆっくりする
 2021-12-31 |           1 | 嬉しい
 2021-12-31 |           2 |
 2022-01-01 |           1 | お祭り気分
 2022-01-01 |           2 | 幸せ
 2022-01-02 |           1 |
 2022-01-02 |           2 |
 2022-01-03 |           1 | 希望に満ちた
 2022-01-03 |           2 |
 2022-01-04 |           1 |
 2022-01-04 |           2 | 気楽
 2022-01-05 |           1 |
 2022-01-05 |           2 |
 2022-01-06 |           1 |
 2022-01-06 |           2 |
(22 行)

期待通り、固定件数分取得することができました！テストテーブルに存在しないデータのfeelingは、NULLとなります。