PostgreSQLのgenerate_seriesとは?連番・日付の並びの作り方と実績のない日を0で埋める方法
generate_seriesは、開始値から終了値まで一定の刻みで並ぶ行を作り出すPostgreSQLの関数です。表にない連番や日付を作れるため、売上のない日が抜けてしまう集計を、0件の日つきで出し直すときに使います。
例で使う表
salesは1行が売上1件です。sold_onは売上日(date)、amountは金額(円)です。1月は1日(2件)、3日、5日に売上があり、2日と4日は売上がありません。2月は2日に1件あります。
| id | sold_on | amount |
|---|---|---|
| 1 | 2026-01-01 | 1000 |
| 2 | 2026-01-01 | 500 |
| 3 | 2026-01-03 | 2000 |
| 4 | 2026-01-05 | 800 |
| 5 | 2026-02-02 | 1200 |
generate_seriesの基本構文
SELECT *
FROM generate_series(1, 5) AS n;generate_seriesで連番を作る
generate_series(開始, 終了)は、開始から終了までを1ずつ増やした行を返します。FROM句に書き、AS nのように別名を付けると、列名もnになります。終了の値も結果に含まれ、元になる表は必要ありません。
SELECT *
FROM generate_series(1, 5) AS n;| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
第3引数で刻みを指定する
第3引数は刻みです。負の値を指定すると降順になり、このとき開始が終了より小さいと0行になります。10から0へ5ずつ減らすと10、5、0です。刻みが0だとエラーになります。
SELECT *
FROM generate_series(10, 0, -5) AS n;| n |
|---|
| 10 |
| 5 |
| 0 |
日付の並びをinterval刻みで作る
日付や日時では刻みにintervalを指定します。1日刻みの例では、結果の型はtimestampです。日付だけを扱いたいので::dateに変換します。開始と終了にTIMESTAMPを明示すると、タイムゾーンの影響を受けません。
SELECT d::date AS day
FROM generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-01-05', INTERVAL '1 day') AS d;| day |
|---|
| 2026-01-01 |
| 2026-01-02 |
| 2026-01-03 |
| 2026-01-04 |
| 2026-01-05 |
INTERVAL '1 month'で月初の並びを作る
刻みを1か月にすると、毎月1日の並びが作れます。月の長さが違っても、日付が月初のまま進みます。月ごとの集計表の行を先に作るときに使います。
SELECT d::date AS month_start
FROM generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-04-01', INTERVAL '1 month') AS d;| month_start |
|---|
| 2026-01-01 |
| 2026-02-01 |
| 2026-03-01 |
| 2026-04-01 |
LEFT JOINとCOALESCEで売上のない日を0にする
売上表だけをGROUP BYすると、2日と4日の行は現れません。日付の並びを左側にしてLEFT JOINし、SUMの結果をCOALESCEで0にすると、5日分すべてが出ます。2日と4日は売上が1件もなくSUMがNULLになるので、0に置き換わります。
SELECT d::date AS day, COALESCE(SUM(s.amount), 0) AS total
FROM generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-01-05', INTERVAL '1 day') AS d
LEFT JOIN sales AS s ON s.sold_on = d::date
GROUP BY d
ORDER BY d;| day | total |
|---|---|
| 2026-01-01 | 1500 |
| 2026-01-02 | 0 |
| 2026-01-03 | 2000 |
| 2026-01-04 | 0 |
| 2026-01-05 | 800 |
月ごとの件数も0件の月を含めて出す
月の並びを左にして、期間の条件をONに書くと、売上のない月も出せます。COUNT(s.id)は売上がない月に0を返しますが、COUNT(*)にすると、LEFT JOINで作られたNULLの行も数えて1になります。1月は4件、2月は1件、3月は0件です。
SELECT m::date AS month, COUNT(s.id) AS cnt
FROM generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-03-01', INTERVAL '1 month') AS m
LEFT JOIN sales AS s
ON s.sold_on >= m::date AND s.sold_on < (m + INTERVAL '1 month')::date
GROUP BY m
ORDER BY m;| month | cnt |
|---|---|
| 2026-01-01 | 4 |
| 2026-02-01 | 1 |
| 2026-03-01 | 0 |
generate_seriesでよくある間違い
期間の絞り込みをWHEREに書くと0件の行が消える
LEFT JOINの後ろにWHERE s.sold_on >= ...のように右側の表の条件を書くと、NULLの行が条件を満たさず消えてしまい、売上のない日や月が結果から外れます。右側の表の条件はON句に書きます。
SELECT d::date AS day, COALESCE(SUM(s.amount), 0) AS total
FROM generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-01-05', INTERVAL '1 day') AS d
LEFT JOIN sales AS s ON s.sold_on = d::date
WHERE s.amount >= 1000
GROUP BY d
ORDER BY d;実際に1問解いてみる
generate_series
1から5までの連番を、generate_seriesで5行として取得してください。別名は付けません。
関連構文
generate_seriesについてよくある質問
- 終了の値は結果に含まれますか?
- 刻みの途中で終了の値にちょうど届くときは含まれます。1から5なら5を含み、3刻みで1から10なら10を含みますが、3刻みで1から9なら7までです。
- FROM句ではなくSELECT句に書いてもよいですか?
- SELECT generate_series(1, 3)と書くこともできますが、複数の集合を返す関数を並べると行数の扱いが分かりにくくなります。FROM句に書く形をおすすめします。
- 開始が終了より大きいとどうなりますか?
- 正の刻みで開始が終了より大きいと、エラーではなく0行になります。降順にするには、負の刻みを指定します。
- 日付の列を月ごとに集計するときはDATE_TRUNCではだめですか?
- DATE_TRUNCで月ごとのグループは作れますが、売上のない月は行になりません。月の並びをgenerate_seriesで先に作り、LEFT JOINすると0件の月も出せます。
参考文献
内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:集合を返す関数 — generate_seriesの引数、型、終了値と刻みの扱い
- PostgreSQL公式:WITHクエリ(再帰) — WITH RECURSIVEによる並びの生成