Menu

PostgreSQL generate_series:連番・日付・欠けた日の補完

generate_series は開始値から終了値までの行の集合を返します。数値、日付、タイムスタンプに使え、レポートの欠けた期間の補完、テストデータの生成、カレンダー作りに役立ちます。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

generate_series(start, stop, step) は start から stop まで (stop を含む) step ずつ進み、値ごとに1行を返します (step を省くと 1)。整数、numeric、そして interval の刻みでタイムスタンプにも使えます。FROM の中でテーブルのように使います。

結果は 1、4、7、10 とその2乗です。負の刻みなら逆向きに数えます: generate_series(5, 1, -2) は 5、3、1 です。generate_series(5, 1) のように終了値に届かない連番は行を返しません。generate_series(0, 1, 0.25) は numeric で刻みます。

日付の連番を生成する

タイムスタンプか日付を、interval の刻みと一緒に渡します。date 版はないので値は timestamptz で返ります。::date でキャストします。

2月26日から3月2日まで、月末をまたいで5日分が返ります。刻みにはどんな interval でも使えます: interval '1 hour'、interval '15 minutes'、interval '1 month'、interval '1 week'。

timestamptz の値に interval '1 day' の刻みを使うと、セッションの TimeZone のカレンダーに従います。夏時間の切り替えをまたぐと、1回の刻みが23時間や25時間になることがあります。PostgreSQL 16 以降は、4つ目の引数でセッションの設定に関係なく刻みに使うタイムゾーンを指定できます。

SELECT generate_series(
    timestamptz '2026-03-28 00:00 Europe/Berlin',
    timestamptz '2026-03-31 00:00 Europe/Berlin',
    interval '1 day',
    'Europe/Berlin');

レポートの欠けた日を埋める

データに対する GROUP BY は、行がある期間しか返しません。注文のない日も 0 として毎日を表示するには、日付を生成してデータを LEFT JOIN します。

11月1日は2件、4日は1件で、2日、3日、5日は 0 です。count(*) ではなく結合したテーブルの列を数えます (count(o.id))。そうしないと空の日がすべて 1 と数えられます。

created_at::date = d ではなく範囲で created_at を比較すると、created_at のインデックスが使えます。interval '1 month' と date_trunc で同じパターンを使えば、空の月も埋められます (日付関数 を参照)。

テストデータを生成する

連番からの INSERT ... SELECT なら、1つの文で必要なだけ行を作れます。

テーブルには、30日間の登録日に散らばった 1,000 人のユーザーが入ります。値は i から計算するので、何度実行しても結果は同じです。値を変えたいときは random() を使います。同じ方法で100万行を生成すれば、インデックスを試して EXPLAIN ANALYZE で実行計画を比べられます。

行に番号を付け、結合で連番を使う

WITH ORDINALITY は、集合を返すどの関数にも位置の列を加えます。

09:00 から 10:00 までの15分刻みの枠が5つ、1 から 5 の番号付きで並びます。連番は CROSS JOIN にも使えます。たとえば、すべてのユーザーを1か月の毎日と組み合わせるときです。

SELECT のリストに書いた generate_series も複数の行を返しますが、長さの違う2つの呼び出しがあると、短いほうが NULL で埋められます。集合を返す関数は FROM に置きましょう。木構造をたどるなど、1つの関数では表せない連番には再帰 CTE を使います。

よくある質問

generate_series は終了値を含みますか?

刻みがちょうど終了値に届けば含みます。generate_series(1, 5) は 1 から 5 を返します。届かない場合は終了値を超えない最後の値で止まるので、generate_series(1, 10, 4) は 1、5、9 を返します。

MySQL に generate_series はありますか?

ありません。MySQL では再帰 CTE (WITH RECURSIVE) で連番を作ります。この方法は PostgreSQL でも使えますが、書くのが長くなります。

日付で generate_series を使うとタイムスタンプが返るのはなぜですか?

この関数には date 版がないので、PostgreSQL は timestamptz 版を使います。各値を ::date でキャストし直します。

行ごとに乱数を生成するには?

SELECT のリストで random() を呼びます。SELECT i, random() FROM generate_series(1, 5) AS i は行ごとに 0 から 1 の値を、floor(random() * 100)::int は 0 から 99 の整数を返します。PostgreSQL 17 以降は random(1, 100) でその範囲の整数を直接得られます。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める