SQL Academy · レッスン

系列とシーケンスを生成する

Q: 「系列とシーケンスを生成する」レッスンは無料ですか？

はい。「系列とシーケンスを生成する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

Q: 「系列とシーケンスを生成する」で何を学びますか？

再帰を使って行を生成します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

Q: SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。

Q: 「系列とシーケンスを生成する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

Q: このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

再帰を使って行を生成します。

「系列とシーケンスを生成する」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

生成される系列とは

どのテーブルにも存在しない数値、日付、その他の連続した値の一覧が必要になることがあります。SQLでは、再帰や組み込み関数を使って、こうした値をその場で作成できます。

このレッスンでは、クエリ自身の出力を参照できる強力な機能、WITH RECURSIVEを使って系列を生成する方法を学びます。

初めての再帰CTE

再帰CTE（共通テーブル式）は、UNION ALLで結合された2つの部分で構成されます。最初の行を生成する基底ケースと、CTE自身を参照して次の行を生成する再帰ステップです。

再帰ステップが行を返さなくなると、再帰は停止します。

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

再帰が展開される仕組み

各ステップを個別に考えると理解しやすくなります。基底ケースでテーブルの初期行を作り、その後、WHERE条件が成立しなくなるまで、再帰処理のたびに行を1つずつ追加します。

WHERE n < 5というクエリでは、エンジンは1、2、3、4、5の行を生成し、5 < 5が偽になるため停止します。

WITH RECURSIVE steps(n, note) AS (
  SELECT 1, 'base case'
  UNION ALL
  SELECT n + 1, 'recursive step'
  FROM steps
  WHERE n < 4
)
SELECT n, note FROM steps;

偶数を生成する

再帰部で1より大きい値を加えるだけで、ステップ幅を変更できます。これにより、2から10までのすべての偶数が生成されます。

パターンは常に次のとおりです：next_value = current_value + step。

WITH RECURSIVE evens(n) AS (
  SELECT 2
  UNION ALL
  SELECT n + 2 FROM evens WHERE n < 10
)
SELECT n FROM evens;

カウントダウンする

再帰は数を増やす処理に限りません。加算の代わりに減算すれば、降順の系列を取得できます。無限ループを避けるため、停止条件では>を<の代わりに使用してください。

WITH RECURSIVE countdown(n) AS (
  SELECT 10
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;

日付範囲を生成する

再帰CTEの最も実用的な用途の1つが、日付系列の作成です。特定の日付から開始し、終了日に達するまで1日ずつ加算します。

これはレポートの欠損を埋める場合に特に便利です。該当日のデータがなくても、すべての日付が表示されます。

WITH RECURSIVE dates(d) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT d + INTERVAL '1 day'
  FROM dates
  WHERE d < DATE '2024-01-07'
)
SELECT d FROM dates;

日付系列でレポートの欠損を埋める

売上が発生した日にしか行が存在しないsalesテーブルを考えてみましょう。生成した日付系列をLEFT JOINでsalesテーブルに結合すると、範囲内のすべての日が取得され、売上がない日にはNULLが入ります。

WITH RECURSIVE cal(d) AS (
  SELECT DATE '2024-03-01'
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM cal WHERE d < DATE '2024-03-05'
),
sales(sale_date, amount) AS (
  VALUES
    (DATE '2024-03-01', 100),
    (DATE '2024-03-03', 250),
    (DATE '2024-03-05', 180)
)
SELECT cal.d, COALESCE(sales.amount, 0) AS amount
FROM cal
LEFT JOIN sales ON cal.d = sales.sale_date
ORDER BY cal.d;

掛け算表を生成する

再帰CTEでは複数の列を扱えるため、より豊富な出力を作成できます。ここでは、行番号と計算された値の両方を同時に追跡しています。

WITH RECURSIVE mult(n, result) AS (
  SELECT 1, 1 * 7
  UNION ALL
  SELECT n + 1, (n + 1) * 7
  FROM mult
  WHERE n < 10
)
SELECT n, result AS seven_times_n FROM mult;

フィボナッチ数

フィボナッチ数列は、各数が直前の2つの数の和になる古典的な数列です：0、1、1、2、3、5、8 …

再帰CTEでは、現在の値aと次の値bの両方を追跡し、各ステップで入れ替えます。

WITH RECURSIVE fib(a, b) AS (
  SELECT 0, 1
  UNION ALL
  SELECT b, a + b FROM fib WHERE a < 100
)
SELECT a AS fibonacci FROM fib;

PostgreSQLでgenerate_seriesを使う

PostgreSQLには、再帰CTEを記述せずに系列を生成できるgenerate_series()という組み込みの簡易機能があります。開始値、終了値、そして任意のステップ幅を指定できます。

PostgreSQLで数値や日付の範囲を生成する最も簡潔な方法です。

SELECT gs AS num
FROM generate_series(1, 10, 2) AS gs;

月単位の間隔を生成する

'1 month'という間隔をgenerate_series()に渡すと、月単位のカレンダーを作成できます。月次レポートのヘッダーを作成したり、データがない月を確認したりするのに最適です。

SELECT gs::DATE AS month_start
FROM generate_series(
  '2024-01-01'::DATE,
  '2024-06-01'::DATE,
  INTERVAL '1 month'
) AS gs;

理解度チェック

再帰CTEによる系列生成の理解度を確認しましょう。

まとめ：系列と連続値の生成

このレッスンでは、元になるテーブルがなくても数値や日付の系列を生成する方法を学びました。

WITH RECURSIVE — 基底ケースと再帰ステップをUNION ALLで結合します。再帰ステップが行を返さなくなると停止します。
柔軟なステップ幅 — 任意の値を加算または減算して、数を増減したり、値を飛ばしたりできます。
日付範囲 — 間隔を加算して、日次、月次、または任意のカレンダー系列を生成します。
複数列のCTE — 反復をまたいで追加の状態を保持し、フィボナッチ数列のような、より豊富な出力を作成します。
generate_series() — 数値や日付の系列を最も簡潔に生成できるPostgreSQLの組み込み機能です。

生成系列は、レポートの欠損補完、テストデータの作成、データの内容にかかわらず値の完全な範囲が必要なあらゆる場面で不可欠です。

よくある質問