PostgreSQL generate_seriesとは？連続データの生成方法 の詳細な説明

はじめに

データベース、特にリレーショナルデータベースを操作する際、連続した数値や日付、タイムスタンプのリストが必要になる場面は少なくありません。例えば、特定期間の全日付に対してレポートを作成したい、数値の範囲全体にわたるテストデータを用意したい、時間ごとのイベント発生数を分析したい、といったケースです。

このような連続データが必要なとき、もしデータベースに関数の機能がなければ、アプリケーション側でループを回してリストを作成し、それをデータベースに渡すか、一時的なテーブルにインサートするといった手間がかかります。これは非効率的であり、アプリケーションコードの複雑さを増し、パフォーマンス上のボトルネックになる可能性もあります。

PostgreSQLは、このような連続データの生成をデータベース内部で効率的に行うための強力な関数を提供しています。それが generate_series です。generate_series関数は、指定された開始値から終了値まで、一定の間隔で連続する値の集合（実際にはテーブルの単一列として扱われる結果セット）を生成します。これにより、アプリケーション側のロジックをシンプルにし、データベースの能力を最大限に活用したデータ操作が可能になります。

この記事では、PostgreSQLのgenerate_series関数について、その基本的な使い方から、整数やタイムスタンプ（日付）の連続生成方法、ステップの指定、さらには様々な応用例やパフォーマンスに関する考慮事項まで、詳細かつ網羅的に解説します。この記事を読むことで、generate_seriesをあなたのPostgreSQL活用において強力なツールとして使いこなせるようになるでしょう。

generate_series の基本

generate_seriesはPostgreSQLの組み込み関数であり、連続する値のセットを生成する際に使用します。これは、値を1つずつ返すのではなく、複数の行として結果セットを返す「集合を返す関数（Set-Returning Function, SRF）」の一種です。generate_seriesが返す結果は、あたかも1つの列を持つテーブルのように扱うことができます。

generate_seriesには、生成する値の型によって主に2つのバージョンがあります。

1. 整数系列を生成するバージョン: 整数型の値を生成します。
2. タイムスタンプ/日付系列を生成するバージョン: timestamp, timestamptz, date 型の値を生成します。

どちらのバージョンも、生成を開始する値 (start)、生成を終了する値 (stop)、そして任意で値を増加または減少させる間隔 (step) を引数として取ります。

基本構文

generate_seriesの構文は以下のようになります。

整数系列:

sql
generate_series(start integer, stop integer)
generate_series(start integer, stop integer, step integer)

タイムスタンプ/日付系列:

sql
generate_series(start timestamp, stop timestamp, step interval)
generate_series(start timestamptz, stop timestamptz, step interval)
generate_series(start date, stop date, step interval)

または、より汎用的な形式（引数の型が自動的に推論される）：

sql
generate_series(start anyelement, stop anyelement)
generate_series(start anyelement, stop anyelement, step anyelement)

ここで anyelement は、数値型、日付/タイムスタンプ型、または間隔型（interval）など、特定のデータ型に限定されないことを示します。しかし、実際には整数、実数、日付/タイムスタンプ、間隔型での使用が一般的です。特に日付/タイムスタンプ系列の場合、stepは必ずinterval型である必要があります。

簡単な使用例

最も基本的な使い方を見てみましょう。

例1: 1から10までの整数を生成する

sql
SELECT * FROM generate_series(1, 10);

結果:

“`
generate_series

---

           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

(10 rows)
“`

このクエリは、generate_series(1, 10)によって生成された連続する整数の列を、単にSELECT文で取得しています。結果は1から10までの整数が1行ずつ表示されます。

例2: 2023年10月1日から2023年10月10日までの日付を生成する

sql
SELECT * FROM generate_series('2023-10-01'::date, '2023-10-10'::date, '1 day'::interval);

結果:

“`
generate_series

---

2023-10-01
2023-10-02
2023-10-03
2023-10-04
2023-10-05
2023-10-06
2023-10-07
2023-10-08
2023-10-09
2023-10-10
(10 rows)
“`

この例では、開始日 ('2023-10-01'::date) から終了日 ('2023-10-10'::date) まで、'1 day'::interval の間隔（1日ごと）で日付を生成しています。::date や ::interval は、文字列リテラルをそれぞれ date 型や interval 型にキャストしていることを示します。多くの場合、PostgreSQLは文脈から型を推論できますが、明示的なキャストは誤解を防ぎ、コードの意図を明確にします。

このように、generate_seriesは指定された範囲と間隔で容易に連続データを生成できる非常に便利な関数です。

generate_series(start, stop) (整数系列)

まずは、最もシンプルな整数系列の生成方法である、開始値と終了値だけを指定するバージョンについて詳しく見ていきましょう。

基本的な使い方

generate_series(start integer, stop integer) は、startから始まり、stop以下のすべての整数値を昇順で生成します。このバージョンは、ステップが暗黙的に 1 であると考えることができます。

sql
SELECT * FROM generate_series(5, 10);

結果:

“`
generate_series

---

           5
           6
           7
           8
           9
          10

(6 rows)
“`

startとstopの関係（昇順、降順）

通常、startはstopより小さいか等しい値を指定します。この場合、startからstopまで1ずつ増加する系列が生成されます。

もし start が stop より大きい場合、generate_series(start, stop) は何も行を生成しません。これは、暗黙のステップが 1 であるため、startから始めて1を加えていってもstopに到達できないからです。

sql
SELECT * FROM generate_series(10, 5); -- start > stop

結果:

“`
generate_series

---

(0 rows)
“`

start = stop の場合

startとstopが同じ値の場合、generate_seriesはstart（またはstop）の値を持つ行を1つだけ生成します。

sql
SELECT * FROM generate_series(7, 7);

結果:

“`
generate_series

---

           7

(1 row)
“`

大きな数値を扱う場合の注意点

generate_seriesはinteger型の引数を取りますが、これは符号付き32ビット整数であり、およそ -20億から +20億までの範囲を扱えます。もしこれより広い範囲の整数系列を生成したい場合は、bigint型を使用する必要があります。generate_seriesはanyelementバージョンを通じてbigint型の引数を受け付けます。

sql
SELECT * FROM generate_series(1::bigint, 5::bigint); -- bigint を明示的に使用

結果:

“`
generate_series

---

           1
           2
           3
           4
           5

(5 rows)
“`

startとstopの差が非常に大きい場合、生成される行数も膨大になります。大量の行を生成すると、クエリの実行時間が増加し、データベースのメモリを大量に消費する可能性があります。無制限に大きな範囲を指定しないように注意が必要です。

この2引数バージョンのgenerate_seriesは、単純な連番リストが必要な場合に便利です。しかし、特定のステップで値を生成したい場合や、降順で値を生成したい場合は、次に説明する3引数バージョンを使用します。

generate_series(start, stop, step) (整数系列)

整数系列を生成するgenerate_seriesのより強力なバージョンは、開始値、終了値、そしてステップ値の3つの引数を取ります。

sql
generate_series(start integer, stop integer, step integer)

step引数の説明

3番目の引数stepは、系列の各値が前の値からどれだけ増加（または減少）するかを指定します。stepの値によって、生成される系列の性質が大きく変わります。

• 正のstep: 系列は昇順で生成されます。
• 負のstep: 系列は降順で生成されます。
• stepが0: これはエラーになります。無限ループを防ぐためです。

正のstep

stepが正の場合、startから始まり、stepずつ値を増やしながらstop以下の値まで生成します。startがstopより小さいか等しい場合に有効です。

sql
SELECT * FROM generate_series(1, 10, 2); -- 1から10まで、2ずつ増加

結果:

“`
generate_series

---

           1
           3
           5
           7
           9

(5 rows)
“`

startがstopより大きい場合、正のstepでは値がstopに到達できないため、結果は空になります。

sql
SELECT * FROM generate_series(10, 1, 2); -- start > stop, 正のstep

結果:

“`
generate_series

---

(0 rows)
“`

負のstep

stepが負の場合、startから始まり、step（絶対値）ずつ値を減らしながらstop以上の値まで生成します。startがstopより大きいか等しい場合に有効です。

sql
SELECT * FROM generate_series(10, 1, -2); -- 10から1まで、2ずつ減少

結果:

“`
generate_series

---

          10
           8
           6
           4
           2

(5 rows)
“`

startがstopより小さい場合、負のstepでは値がstopに到達できないため、結果は空になります。

sql
SELECT * FROM generate_series(1, 10, -2); -- start < stop, 負のstep

結果:

“`
generate_series

---

(0 rows)
“`

stepが0の場合

stepに0を指定するとエラーになります。

sql
SELECT * FROM generate_series(1, 10, 0);

結果:

ERROR: step must not be zero

stepとstart/stopの関係性（境界値の扱い）

generate_seriesは、生成された値がstartとstopで定義される範囲内に収まるまで値を生成します。

• 正のstep: 生成される値 v は start <= v <= stop を満たします。
• 負のstep: 生成される値 v は stop <= v <= start を満たします。（これはv >= stop と同じです）

つまり、終了値(stop)は生成される系列に含まれる可能性があります。

sql
SELECT * FROM generate_series(1, 10, 3); -- 1, 4, 7, 10

結果:

“`
generate_series

---

           1
           4
           7
          10

(4 rows)
“`

この例では、10は系列に含まれています。

sql
SELECT * FROM generate_series(1, 11, 3); -- 1, 4, 7, 10. 11は含まれない

結果:

“`
generate_series

---

           1
           4
           7
          10

(4 rows)
“`

この例では、次に生成される値は13（10 + 3）であり、これは終了値11を超えるため、11は系列に含まれません。

同様に、負のステップの場合も終了値(stop)は系列に含まれる可能性があります。

sql
SELECT * FROM generate_series(10, 1, -3); -- 10, 7, 4, 1

結果:

“`
generate_series

---

          10
           7
           4
           1

(4 rows)
“`

浮動小数点数のgenerate_series

generate_seriesは、整数だけでなく、numericやdouble precisionといった浮動小数点数型にも対応しています。この場合、引数は対応する浮動小数点数型になります。

sql
SELECT * FROM generate_series(1.0, 5.0, 0.5);

結果:

“`
generate_series

---

         1.0
         1.5
         2.0
         2.5
         3.0
         3.5
         4.0
         4.5
         5.0

(9 rows)
“`

浮動小数点数を使用する場合、丸め誤差に注意が必要です。また、終了値に正確に到達するかどうかは、開始値、終了値、ステップ値の関係によります。通常は、終了値を超えない（または下回らない）最後の値までが生成されます。

整数系列版のgenerate_seriesは、ID番号のリスト、特定の範囲内のテスト値、繰り返し処理の回数など、数値的な連番が必要な場面で非常に役立ちます。

generate_series(start, stop, step) (タイムスタンプ/日付系列)

generate_seriesのもう一つの主要な用途は、日付やタイムスタンプといった時間軸上の連続した値を生成することです。これは、時系列データの分析、カレンダーテーブルの作成、特定の時間範囲のレポート作成などに不可欠な機能です。

start, stop引数の型

タイムスタンプ/日付系列を生成する場合、startとstopの引数には以下の型を使用できます。

• timestamp without time zone (timestamp)
• timestamp with time zone (timestamptz)
• date (date)

startとstopは同じ型である必要があります。

step引数の型（interval型）

時間系列のstep引数は、必ずinterval型である必要があります。interval型は、時間的な間隔（例: 1日、30分、1ヶ月）を表すPostgreSQLのデータ型です。

interval型のリテラルは、様々な形式で指定できます。一般的な形式は以下の通りです。

• 単一の時間単位: '1 day', '2 hours', '30 minutes', '5 seconds', '1 month', '1 year'
• 複数の時間単位の組み合わせ: '1 day 2 hours', '3 months 15 days', '1 year 6 months', 'P1DT2H'::interval (ISO 8601形式)
• 数値と単位: interval '1 day', interval '2 hours'

stepとして使用するintervalの値が正の場合、系列は時間的に未来へ進みます。負の場合、系列は時間的に過去へ戻ります。intervalの値が0の場合はエラーになります。

基本的なタイムスタンプ/日付範囲の生成例

開始タイムスタンプから終了タイムスタンプまで、指定された間隔で系列を生成します。

sql
SELECT * FROM generate_series('2023-10-26 10:00:00'::timestamp, '2023-10-26 12:00:00'::timestamp, '30 minutes'::interval);

結果:

“`
generate_series

---

2023-10-26 10:00:00
2023-10-26 10:30:00
2023-10-26 11:00:00
2023-10-26 11:30:00
2023-10-26 12:00:00
(5 rows)
“`

日付型の場合も同様です。

sql
SELECT * FROM generate_series('2023-11-01'::date, '2023-11-07'::date, '1 day'::interval);

結果:

“`
generate_series

---

2023-11-01
2023-11-02
2023-11-03
2023-11-04
2023-11-05
2023-11-06
2023-11-07
(7 rows)
“`

特定の時間間隔の例

様々なintervalを使用して、異なる間隔で系列を生成できます。

• 1時間ごと: '1 hour'
sql
SELECT * FROM generate_series('2023-10-26 09:00:00'::timestamp, '2023-10-26 18:00:00'::timestamp, '1 hour');
• 15分ごと: '15 minutes'
sql
SELECT * FROM generate_series('2023-10-26 08:00:00'::timestamp, '2023-10-26 09:00:00'::timestamp, '15 minutes');
• 1ヶ月ごと: '1 month'
sql
SELECT * FROM generate_series('2023-01-01'::date, '2023-12-01'::date, '1 month');
• 3ヶ月ごと: '3 months'
sql
SELECT * FROM generate_series('2023-01-01'::date, '2024-01-01'::date, '3 months');
• 1年ごと: '1 year'
sql
SELECT * FROM generate_series('2020-01-01'::date, '2025-01-01'::date, '1 year');

タイムゾーンに関する考慮事項 (timestamptz)

generate_seriesをtimestamptz型で使用する場合、タイムゾーンが考慮されます。startとstopはタイムゾーン情報を含む値として扱われ、生成される各値もタイムゾーン情報を含みます。

“`sql
— システムのタイムゾーンが ‘UTC’ の場合
SET TIMEZONE TO ‘UTC’;
SELECT * FROM generate_series(‘2023-10-26 10:00:00+00’::timestamptz, ‘2023-10-26 12:00:00+00′::timestamptz, ’30 minutes’);

— システムのタイムゾーンが ‘Asia/Tokyo’ の場合 (UTC+9)
SET TIMEZONE TO ‘Asia/Tokyo’;
SELECT * FROM generate_series(‘2023-10-26 10:00:00+00’::timestamptz, ‘2023-10-26 12:00:00+00′::timestamptz, ’30 minutes’);
“`

どちらの場合も、生成されるタイムスタンプ値自体はUTCでの時間（10:00, 10:30, …, 12:00 UTC）を保持しますが、表示は現在のセッションのタイムゾーン（’UTC’ または ‘Asia/Tokyo’）に従います。生成プロセス自体はタイムゾーン対応で行われます。

例えば、夏時間から冬時間への移行（またはその逆）がある時間帯で細かいステップ（例: '1 hour'）を使用する場合、実際に経過する壁時計上の時間はステップと異なる場合があります。しかし、timestamptzの加算・減算は内部的にUTCで行われるため、発生するタイムスタンプ値の連続性は論理的な時間間隔に基づいています。

start > stop かつ 負のstepの場合 (時間系列)

整数系列と同様に、時間系列でもstartがstopより未来の時刻であり、stepが負のinterval（時間を巻き戻す）である場合に降順の系列が生成されます。

sql
SELECT * FROM generate_series('2023-10-26 12:00:00'::timestamp, '2023-10-26 10:00:00'::timestamp, '-30 minutes'::interval);

結果:

“`
generate_series

---

2023-10-26 12:00:00
2023-10-26 11:30:00
2023-10-26 11:00:00
2023-10-26 10:30:00
2023-10-26 10:00:00
(5 rows)
“`

start < stop かつ 正のstepの場合 (時間系列)

これも整数系列と同様、startがstopより過去の時刻であり、stepが正のinterval（時間を進める）である場合に昇順の系列が生成されます。これは最も一般的な使用パターンです。

月末やうるう年など、カレンダーの日付の特殊なケース

interval型の '1 month' や '1 year' といった単位は、日数を一定にせず、カレンダー上の月に従います。これには、月末の日付やうるう年の扱いで注意が必要です。

startの日が月の最終日である場合、'1 month' を加算すると、結果の日付もその月の最終日になるように調整されます（ただし、元の月より日数が少ない月の場合）。

例: 月末の挙動

sql
-- 1月31日から開始
SELECT * FROM generate_series('2023-01-31'::date, '2023-04-01'::date, '1 month');

結果:

“`
generate_series

---

2023-01-31
2023-02-28 — 2023年はうるう年ではないため2月は28日まで
2023-03-31
2023-04-30 — 4月は30日まで
(4 rows)
“`

この例では、1月31日から始めて1ヶ月ずつ加算しています。
* 1月31日 + 1ヶ月 = 2月28日 (2月は28日までしかないので調整される)
* 2月28日 + 1ヶ月 = 3月28日 (2月の日数を考慮した加算結果)
* 3月28日 + 1ヶ月 = 4月28日
もし開始日が月の最終日でなければ、通常はその日数を維持したまま月が変わります。

例: 月末でない日付からの開始

sql
-- 1月15日から開始
SELECT * FROM generate_series('2023-01-15'::date, '2023-04-01'::date, '1 month');

結果:

“`
generate_series

---

2023-01-15
2023-02-15
2023-03-15
2023-04-15
(4 rows)
“`

この場合、日付の部分は15日のまま月が進んでいます。

うるう年の挙動

うるう年の2月29日から開始し、'1 year' を加算した場合、うるう年でない年には2月28日に調整されます。

sql
-- 2024年2月29日（うるう年）から開始
SELECT * FROM generate_series('2024-02-29'::date, '2027-02-29'::date, '1 year');

結果:

“`
generate_series

---

2024-02-29
2025-02-28 — 2025年はうるう年ではない
2026-02-28 — 2026年はうるう年ではない
2027-02-28 — 2027年はうるう年ではない
(4 rows)
“`

これらの挙動は、interval型がカレンダー上の意味を理解していることに由来します。日単位や時間単位のステップではこのような特殊な調整は発生せず、単純な加算・減算になります。

日付やタイムスタンプの系列生成は、時間ベースの分析やレポート作成において非常に強力な機能です。これらの特別なケースを理解しておくことで、期待通りの結果を得ることができます。

generate_seriesの応用例

generate_series関数は、単に連番を生成するだけでなく、他のSQLクエリやデータベース機能と組み合わせることで、非常に多岐にわたる用途に活用できます。ここでは、いくつかの代表的な応用例を紹介します。

応用例1: カレンダーテーブルの生成

分析やレポート作成において、日付や曜日の情報を持つ「カレンダーテーブル」は非常に役立ちます。generate_seriesを使えば、特定の期間の全日付を含むカレンダーテーブルを簡単に生成できます。

sql
-- 2023年のカレンダーテーブルを生成する例
SELECT
generated_date::date,
EXTRACT(YEAR FROM generated_date) AS year,
EXTRACT(MONTH FROM generated_date) AS month,
EXTRACT(DAY FROM generated_date) AS day,
EXTRACT(DOW FROM generated_date) AS day_of_week_num, -- 0=Sunday, 6=Saturday
to_char(generated_date, 'Day') AS day_of_week_name,
EXTRACT(DOY FROM generated_date) AS day_of_year,
EXTRACT(WEEK FROM generated_date) AS week_of_year,
to_char(generated_date, 'YYYY-MM') AS year_month,
to_char(generated_date, 'Month') AS month_name,
(EXTRACT(MONTH FROM generated_date) - 1) / 3 + 1 AS quarter,
-- 追加の列（例: 祝日フラグなど）は別途結合やCASE文で追加
CASE WHEN EXTRACT(DOW FROM generated_date) IN (0, 6) THEN TRUE ELSE FALSE END AS is_weekend
FROM generate_series('2023-01-01'::date, '2023-12-31'::date, '1 day'::interval) AS s(generated_date);

このクエリは、2023年1月1日から2023年12月31日までのすべての日付を1日ごとに生成し、それぞれの行に対して年、月、日、曜日（数値および名称）、年内の日数、年内の週番号、年月、月名称、四半期、週末フラグといった情報を含む列を追加しています。結果を新しいテーブルにCREATE TABLE ... AS SELECTで保存すれば、再利用可能なカレンダーテーブルの完成です。

応用例2: 欠落データの検出

時系列データにおいて、特定の期間内に本来存在するはずのデータが欠落していることを検出したい場合があります。generate_seriesで期待される連続する値を生成し、既存のデータとLEFT JOINすることで、欠落している部分を容易に特定できます。

例えば、毎日の売上データがあるとして、データが記録されていない日付（売上0またはデータ欠落）を見つけたい場合。

“`sql
— 仮の売上データテーブル
CREATE TEMP TABLE daily_sales (
sale_date date PRIMARY KEY,
amount numeric
);

INSERT INTO daily_sales (sale_date, amount) VALUES
(‘2023-10-01’, 1000),
(‘2023-10-03’, 1500), — 10月2日が欠落
(‘2023-10-04’, 2000),
(‘2023-10-06’, 1200); — 10月5日が欠落

— 指定期間内の全日付を生成し、売上データとLEFT JOIN
SELECT
gs.generated_date
FROM generate_series(‘2023-10-01’::date, ‘2023-10-07’::date, ‘1 day’::interval) AS gs(generated_date)
LEFT JOIN daily_sales ds ON gs.generated_date = ds.sale_date
WHERE ds.sale_date IS NULL; — sales_dateがNULLの行が欠落データ
“`

結果:

“`
generated_date

---

2023-10-02
2023-10-05
(2 rows)
“`

このクエリは、2023年10月1日から10月7日までの全日付を生成し、daily_salesテーブルと結合しています。LEFT JOINを使用し、結合条件に一致しない（つまり、daily_salesに存在しない）generate_seriesからの日付 (ds.sale_date IS NULL で判定) を抽出することで、欠落している日付を発見できます。

応用例3: 時間バケット/集計

データを一定の時間間隔（例: 1時間ごと、1日ごと）で集計したい場合、generate_seriesを使って時間バケットを生成し、それに基づいてデータをグループ化できます。

例えば、ログデータのタイムスタンプがあり、1時間ごとのログ件数を集計したい場合。

“`sql
— 仮のログデータテーブル
CREATE TEMP TABLE server_logs (
log_timestamp timestamp
);

INSERT INTO server_logs (log_timestamp) VALUES
(‘2023-10-26 09:05:10’),
(‘2023-10-26 09:30:00’),
(‘2023-10-26 10:01:00’),
(‘2023-10-26 10:45:00’),
(‘2023-10-26 10:59:59’),
(‘2023-10-26 11:15:00’);

— 1時間ごとのバケットを生成し、ログ件数を集計
SELECT
hour_bucket,
COUNT(l.log_timestamp) AS log_count
FROM generate_series(date_trunc(‘hour’, ‘2023-10-26 09:00:00’::timestamp), — 開始時間（09:00）
date_trunc(‘hour’, ‘2023-10-26 11:00:00’::timestamp), — 終了時間（11:00）
‘1 hour’::interval) AS gs(hour_bucket)
LEFT JOIN server_logs l ON l.log_timestamp >= gs.hour_bucket AND l.log_timestamp < gs.hour_bucket + ‘1 hour’
GROUP BY hour_bucket
ORDER BY hour_bucket;
“`

結果:

hour_bucket | log_count
---------------------+-----------
2023-10-26 09:00:00 | 2
2023-10-26 10:00:00 | 3
2023-10-26 11:00:00 | 1
(3 rows)

このクエリでは、まず date_trunc('hour', ...) を使用して開始時間と終了時間をそれぞれ時間の始まりに切り捨てています。そして、generate_seriesで1時間ごとのバケット（例: 09:00:00, 10:00:00, 11:00:00）を生成します。これらのバケットをログデータとLEFT JOINし、各ログエントリがどのバケットに属するかを >= gs.hour_bucket AND < gs.hour_bucket + '1 hour' の条件で判断します。最後に、GROUP BY hour_bucketで集計することで、各時間バケット内のログ件数を得ています。LEFT JOINを使うことで、ログがない時間バケット（例: 0件）も結果に含まれるようになります。

応用例4: テストデータの生成

アプリケーション開発やデータベースのパフォーマンス検証のために、大量のテストデータが必要になることがあります。generate_seriesは、連番や日付を生成する基本的な能力と、他の関数（例: random(), md5(), generate_series自体）を組み合わせることで、様々な種類のテストデータを簡単に作成できます。

sql
-- 1000件のダミーユーザーデータを生成
SELECT
gs.id,
'user_' || gs.id AS username,
'user' || gs.id || '@example.com' AS email,
now() - (gs.id || ' days')::interval AS registration_date,
(random() * 1000)::int AS score
FROM generate_series(1, 1000) AS gs(id);

このクエリは、IDが1から1000までのダミーユーザーデータを生成しています。gs.id を利用して、username や email をユニークに生成し、now() - (gs.id || ' days')::interval で登録日を過去の日付として生成しています。random() * 1000)::int で0から999までのランダムな整数を生成し、スコアとしています。

応用例5: 配列操作

generate_seriesは配列操作とも組み合わせられます。例えば、配列の各要素にアクセスする際に添え字を生成するのに使用できます。PostgreSQLにはgenerate_subscriptsという配列添え字専用の関数もありますが、単純な連番が必要な場合はgenerate_seriesも使えます。

sql
-- 配列の各要素と添え字を生成
SELECT
gs.idx,
my_array[gs.idx] AS element
FROM (SELECT '{apple,banana,cherry,date}'::text[] AS my_array) AS arr,
generate_series(1, array_length(arr.my_array, 1)) AS gs(idx);

結果:

idx | element
-----+---------
1 | apple
2 | banana
3 | cherry
4 | date
(4 rows)

この例では、サブクエリで定義された配列my_arrayの長さを取得し、generate_seriesで1から配列の長さまでの連番を生成しています。その連番を添え字として使用し、配列の各要素を取り出しています。

応用例6: 期間の分割

長い期間（例: プロジェクト期間、契約期間）を、より短い期間（例: 月ごと、四半期ごと）に分割して分析したい場合があります。generate_seriesで短い期間の開始日を生成し、ウィンドウ関数などと組み合わせることで、このような分割が可能です。

例：レンタル期間（開始日、終了日）を月ごとに分割する

“`sql
— 仮のレンタルテーブル
CREATE TEMP TABLE rentals (
rental_id int PRIMARY KEY,
start_date date,
end_date date
);

INSERT INTO rentals (rental_id, start_date, end_date) VALUES
(1, ‘2023-01-15’, ‘2023-04-10’),
(2, ‘2023-03-01’, ‘2023-03-31’),
(3, ‘2023-05-20’, ‘2023-07-05’);

— 各レンタル期間を月ごとの期間に分割
SELECT
r.rental_id,
gs.month_start_date AS period_start_date,
— 次の月の開始日の前日、またはレンタル終了日の早い方
LEAST(gs.month_start_date + ‘1 month’::interval – ‘1 day’::interval, r.end_date) AS period_end_date
FROM rentals r,
generate_series(date_trunc(‘month’, r.start_date), — レンタル開始日の月の初めから
r.end_date, — レンタル終了日まで
‘1 month’::interval) AS gs(month_start_date)
WHERE gs.month_start_date <= r.end_date; — generate_seriesの終了値がレンタル終了日を超える可能性があるためフィルタリング
“`

結果例（データとgenerate_seriesの実装により変動）：

rental_id | period_start_date | period_end_date
-----------+-------------------+-----------------
1 | 2023-01-01 | 2023-01-31
1 | 2023-02-01 | 2023-02-28
1 | 2023-03-01 | 2023-03-31
1 | 2023-04-01 | 2023-04-10 -- レンタル終了日で期間終了
2 | 2023-03-01 | 2023-03-31
3 | 2023-05-01 | 2023-05-31
3 | 2023-06-01 | 2023-06-30
3 | 2023-07-01 | 2023-07-05 -- レンタル終了日で期間終了
(8 rows)

このクエリでは、各レンタル期間に対してgenerate_seriesを実行しています。start_dateの月の初めからend_dateまでを月ごとに生成し、それぞれの生成された月の初めをperiod_start_dateとしています。period_end_dateは、生成された月の最終日とレンタル終了日の早い方として計算しています。これにより、元のレンタル期間が月ごとのサブ期間に分割されます。

これらの応用例は、generate_seriesが様々なデータ処理タスクにおいて、柔軟かつ強力なツールとして活用できることを示しています。

パフォーマンスと注意点

generate_seriesは非常に便利な関数ですが、特に大量のデータを生成する場合や、複雑なクエリの一部として使用する場合には、パフォーマンスに関する考慮が必要です。

大量のデータを生成する場合

generate_seriesは指定された範囲内のすべての値を生成します。startとstopの差やstepの値によっては、膨大な数の行が生成される可能性があります。

例: generate_series(1, 1000000000) は10億行を生成しようとします。

このような大量の行を生成すると、以下の問題が発生する可能性があります。

• メモリ消費: 生成された結果セットは、PostgreSQLの内部で処理されるため、大量のメモリを消費する可能性があります。システムメモリが不足すると、ディスクへのスワップが発生し、パフォーマンスが著しく低下したり、クエリが失敗したりする可能性があります。
• 処理時間: 膨大な数の行を生成し、さらにそれを後続の操作（結合、集計など）で使用する場合、クエリ全体の実行時間が非常に長くなります。
• ネットワーク負荷: クライアントアプリケーションに結果を返す場合、ネットワーク帯域幅を大量に消費する可能性があります。

対策としては、以下の点が挙げられます。

• 必要な範囲のみを生成する: 無駄に広い範囲を指定せず、実際に必要な最小限の範囲にとどめます。
• 生成後にすぐにフィルタリングする: WHERE句を使って、生成された直後に不要な行を絞り込みます。
• LIMIT句を使用する: 生成される行数に上限を設けます。
• 一時テーブルやCTEを活用する: 生成した系列を一時テーブルに格納したり、共通テーブル式 (CTE) を使用したりすることで、クエリの可読性やオプティマイザの判断に影響を与える場合があります。特に、結果を複数回参照する場合やインデックスを作成したい場合は、一時テーブルが有効です。

インデックスとの関連性

generate_series関数自体はテーブルをスキャンするわけではありません。したがって、generate_seriesの実行計画には、シーケンシャルスキャンやインデックススキャンといったオペレーションは直接現れません（ただし、生成処理自体のコストは計上されます）。

しかし、generate_seriesで生成した結果を他のテーブルと結合する場合、結合操作のパフォーマンスは、結合される側のテーブルに適切なインデックスが存在するかどうかに大きく依存します。generate_seriesの結果セットは、オプティマイザからは一時的な「仮想テーブル」のように見えます。結合条件にgenerate_seriesで生成した列を使用する場合、相手方のテーブルの該当列にインデックスがあれば、効率的なインデックススキャンやネステッドループ結合などが選択される可能性が高まります。

generate_seriesの結果を一時テーブルやCTEとして使用する

大量の行を生成し、それを複雑なクエリで使用する場合、generate_seriesの結果を一時テーブルに格納したり、CTEを使用したりすることが有効な場合があります。

一時テーブルの使用:

“`sql
— 大量の連続日付を一時テーブルに格納
CREATE TEMP TABLE all_dates AS
SELECT generated_date
FROM generate_series(‘2020-01-01’::date, ‘2030-12-31’::date, ‘1 day’::interval) AS s(generated_date);

— 一時テーブルにインデックスを作成（必要に応じて）
CREATE INDEX ON all_dates (generated_date);

— 他のテーブルと結合して利用
SELECT ad.generated_date, COALESCE(COUNT(t.id), 0)
FROM all_dates ad
LEFT JOIN your_table t ON ad.generated_date = t.some_date
GROUP BY ad.generated_date
ORDER BY ad.generated_date;

DROP TABLE all_dates;
“`

一時テーブルを使用すると、生成されたデータを物理的にディスク（またはwork_memの設定によってはメモリ）に書き出すため、メモリ消費の問題を緩和できる場合があります。また、一時テーブルにインデックスを作成すれば、後続の結合操作を高速化できます。ただし、一時テーブルへの書き込み自体にコストがかかります。

CTE (共通テーブル式) の使用:

sql
WITH generated_dates AS (
SELECT generated_date
FROM generate_series('2023-01-01'::date, '2023-12-31'::date, '1 day'::interval) AS s(generated_date)
)
SELECT gd.generated_date, COALESCE(COUNT(t.id), 0)
FROM generated_dates gd
LEFT JOIN your_table t ON gd.generated_date = t.some_date
GROUP BY gd.generated_date
ORDER BY gd.generated_date;

CTEは、クエリ内で一時的な結果セットを定義するために使用されます。PostgreSQLのオプティマイザは、CTEを「マテリアライズしない」（つまり、物理的な中間結果を生成しない）ように扱うことが多いです。この場合、generate_seriesはCTEを参照する部分で直接実行されます。これにより、一時テーブルのような書き込みコストは発生しませんが、generate_seriesが繰り返し実行される可能性や、メモリ消費が増加する可能性もあります。SET enable_cte_inlining = off; と設定することでCTEをマテリアライズさせることも可能ですが、通常はオプティマイザの判断に任せるのが良いでしょう。

どちらの方法を選択するかは、生成されるデータ量、後続の操作、利用可能なメモリ量などによって最適な選択が変わります。

その他の注意点

• 型の一貫性: 整数系列の場合は引数をすべて整数型、時間系列の場合はstart/stopを同じ日付/タイムスタンプ型、stepをinterval型に合わせる必要があります。型が混在するとエラーになったり、意図しない型で系列が生成されたりする可能性があります。
• 終了値の包含: generate_seriesは、生成された値がstartとstopで定義される範囲内にある限り値を生成します。したがって、stopの値自体が範囲に含まれる場合、それは結果セットに含まれます。この挙動を理解しておくことが重要です。
• interval型の挙動: 特に '1 month' や '1 year' といった単位を含むintervalを使用する場合、月末やうるう年の自動調整に注意が必要です。期待通りの日付が得られるか、事前に短い範囲でテストすることをお勧めします。

generate_seriesは非常に強力で柔軟な関数ですが、その能力を最大限に引き出しつつ、潜在的なパフォーマンス問題を回避するためには、これらの注意点を理解し、適切に使用することが重要です。

関連する関数

generate_seriesはPostgreSQLが提供する集合を返す関数（SRF）の一つです。これ以外にも、連続データや集合を扱う上で関連性の高い関数がいくつかあります。

generate_subscripts

これは配列専用のSRFで、配列の添え字（インデックス）の集合を生成します。generate_seriesと似ていますが、配列の次元に応じて使用できます。

sql
SELECT * FROM generate_subscripts(ARRAY['a', 'b', 'c', 'd'], 1); -- 1次元配列の添え字

結果:

“`
generate_subscripts

---

               1
               2
               3
               4

(4 rows)
“`

多次元配列の場合、第2引数で次元を指定できます。また、オプションで第3引数（bool）で逆順にするか、第4引数（bool）でnull値を無視するかを指定できます。単純な1次元配列の添え字を生成するだけなら、generate_series(1, array_length(arr, 1))でも多くの場合代用可能ですが、generate_subscriptsの方が配列添え字生成の意図が明確になります。

unnest

これも非常に便利なSRFで、配列を受け取り、配列の各要素を個別の行として返します。複数の配列を並行してunnestすることも可能です。

sql
SELECT * FROM unnest(ARRAY[10, 20, 30]);

結果:

“`
unnest

---

 10
 20
 30

(3 rows)
“`

generate_seriesで生成した連番とunnestを組み合わせて、複雑なデータの分解や整形を行うこともあります。

width_bucket

この関数は連続する数値の範囲を等しい幅の「バケット」に分割し、与えられた値がどのバケットに属するかを返します。generate_seriesでバケットの境界値を生成し、width_bucketでデータを分類するといった使い方が考えられます。

sql
-- 0から100の範囲を10個のバケットに分割し、値95がどのバケットか調べる
SELECT width_bucket(95, 0, 100, 10);

結果:

“`
width_bucket

---

       10

(1 row)
“`

これはバケット分析を行う際に役立ちます。

ウィンドウ関数 (LEAD, LAG)

generate_series自体は直接ウィンドウ関数ではありませんが、生成した連続データ（特に時系列データ）に対してウィンドウ関数を適用することはよくあります。LEADやLAGといったウィンドウ関数は、現在の行から「先行する」または「後続する」行の値にアクセスするために使用され、時系列データの差分計算や比較に役立ちます。

例えば、generate_seriesで日次データを生成し、それにLEFT JOINで売上データを結合した後、LAG関数を使って前日の売上との差分を計算するといった分析が可能です。

sql
-- 日付系列を生成し、前日の日付を計算
SELECT
generated_date,
LAG(generated_date, 1) OVER (ORDER BY generated_date) AS previous_date
FROM generate_series('2023-11-01'::date, '2023-11-10'::date, '1 day'::interval) AS gs(generated_date);

結果:

generated_date | previous_date
-----------------+---------------
2023-11-01 |
2023-11-02 | 2023-11-01
2023-11-03 | 2023-11-02
2023-11-04 | 2023-11-03
2023-11-05 | 2023-11-04
2023-11-06 | 2023-11-05
2023-11-07 | 2023-11-06
2023-11-08 | 2023-11-07
2023-11-09 | 2023-11-08
2023-11-10 | 2023-11-09
(10 rows)

これらの関数はgenerate_seriesと直接置き換えるものではありませんが、連続データや集合データを扱う様々なシナリオでgenerate_seriesと組み合わせて使用されることが多いです。

まとめ

PostgreSQLのgenerate_series関数は、指定された開始値から終了値まで、一定の間隔で連続する数値や日付、タイムスタンプの系列を生成するための非常に強力で便利なツールです。この関数は「集合を返す関数（SRF）」として機能し、あたかもテーブルの単一列のように結果セットを提供するため、他のテーブルとの結合や、サブクエリ、CTEなど、様々なSQLクエリ内で柔軟に使用できます。

この記事では、generate_seriesの基本的な構文から始め、整数系列とタイムスタンプ/日付系列のそれぞれの使い方、ステップ引数の役割、そして特に時間系列における月末やうるう年の特殊な挙動について詳細に解説しました。

さらに、generate_seriesの多様な応用例を紹介しました。カレンダーテーブルの生成、時系列データの欠落検出、時間バケットでの集計、テストデータの効率的な生成、配列操作との連携、そして期間の分割といった、具体的な問題解決にどのようにgenerate_seriesが役立つかを示しました。これらの例を通じて、generate_seriesが単なる連番生成に留まらない、データ分析やアプリケーション開発における基盤機能となり得ることを理解していただけたかと思います。

最後に、大量のデータを生成する際のパフォーマンス上の考慮事項や、一時テーブル、CTEの活用、関連する関数についても触れました。generate_seriesは非常に効率的ですが、生成されるデータ量が増えるにつれてシステムリソースを消費するため、使用する際には必要な範囲に限定し、適切にフィルタリングや集計を行うことが重要です。

generate_seriesを使いこなすことで、アプリケーション側で複雑なループ処理を行う必要がなくなり、SQLだけでデータ準備や分析タスクを効率的に実行できるようになります。PostgreSQLを日常的に使用する開発者やデータアナリストにとって、generate_seriesはぜひマスターしておきたい必須の関数と言えるでしょう。この記事が、あなたのgenerate_series活用の助けとなれば幸いです。