SQL練習室
← SQLガイド

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件あります。

sales:売上5件
idsold_onamount
12026-01-011000
22026-01-01500
32026-01-032000
42026-01-05800
52026-02-021200

generate_seriesの基本構文

sql
SELECT *
FROM generate_series(1, 5) AS n;

generate_seriesで連番を作る

generate_series(開始, 終了)は、開始から終了までを1ずつ増やした行を返します。FROM句に書き、AS nのように別名を付けると、列名もnになります。終了の値も結果に含まれ、元になる表は必要ありません。

sql
SELECT *
FROM generate_series(1, 5) AS n;
実行結果
n
1
2
3
4
5

第3引数で刻みを指定する

第3引数は刻みです。負の値を指定すると降順になり、このとき開始が終了より小さいと0行になります。10から0へ5ずつ減らすと10、5、0です。刻みが0だとエラーになります。

sql
SELECT *
FROM generate_series(10, 0, -5) AS n;
実行結果
n
10
5
0

日付の並びをinterval刻みで作る

日付や日時では刻みにintervalを指定します。1日刻みの例では、結果の型はtimestampです。日付だけを扱いたいので::dateに変換します。開始と終了にTIMESTAMPを明示すると、タイムゾーンの影響を受けません。

sql
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日の並びが作れます。月の長さが違っても、日付が月初のまま進みます。月ごとの集計表の行を先に作るときに使います。

sql
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に置き換わります。

sql
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;
実行結果
daytotal
2026-01-011500
2026-01-020
2026-01-032000
2026-01-040
2026-01-05800

月ごとの件数も0件の月を含めて出す

月の並びを左にして、期間の条件をONに書くと、売上のない月も出せます。COUNT(s.id)は売上がない月に0を返しますが、COUNT(*)にすると、LEFT JOINで作られたNULLの行も数えて1になります。1月は4件、2月は1件、3月は0件です。

sql
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;
実行結果
monthcnt
2026-01-014
2026-02-011
2026-03-010

generate_seriesでよくある間違い

期間の絞り込みをWHEREに書くと0件の行が消える

LEFT JOINの後ろにWHERE s.sold_on >= ...のように右側の表の条件を書くと、NULLの行が条件を満たさず消えてしまい、売上のない日や月が結果から外れます。右側の表の条件はON句に書きます。

sql
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。