SQL バッチ処理 — CTE・ウィンドウ関数・ピボットの応用

QUESTION 1

CTE + GROUP BY — 日次売上サマリを集計してAPIに返す

WITHCTEGROUP BY日次バッチ

前提知識 ▼

CTE（Common Table Expression）は WITH 名前 AS (...) の形式で定義する「一時的な名前付きクエリ」です。バッチ処理では「集計 → フィルタ → 整形」の3工程をCTEで分割するのが王道パターンです。

WITH 集計名 AS (
  -- ① ここで集計（GROUP BY）を定義する
  SELECT col1, SUM(col2) AS total
  FROM   テーブル名
  GROUP BY col1         -- col1ごとにまとめて集計する
)
-- ② 集計結果を使ってフィルタ・並び替え
SELECT *
FROM   集計名
WHERE  total > 10000  -- 集計値（total）をWHEREで使えるのがCTEの強み
ORDER BY col1;          -- 並び替え（ASCが省略されたデフォルト）

なぜCTEを使うか：通常 WHERE SUM(...) は書けないため HAVING が必要ですが、CTEに切り出すと集計後の値を通常の列として扱えます。バッチSQLが長くなるほど「処理ステップを名前付けして分割」するメリットが増します。

問題 ▼

以下の orders テーブルから、日付（order_date）ごとの売上合計・注文件数・平均注文額 を集計してください。

さらに 合計売上が 50,000円以上の日のみ を返し、合計売上の降順で並べること。

使用テーブル

▸ orders

order_id	order_date	amount
1	2024-04-01	20000
2	2024-04-01	35000
3	2024-04-02	60000
4	2024-04-02	15000
5	2024-04-03	80000
6	2024-04-03	40000
7	2024-04-04	12000
8	2024-04-04	18000

期待出力 ▼

order_date	total_sales	order_count	avg_order
2024-04-03	120000	2	60000
2024-04-02	75000	2	37500
2024-04-01	55000	2	27500

模範解答コード ▼

-- WITH: CTEで処理を分割
WITH daily_stats AS (
  -- ① ordersテーブルを日付ごとに集計
  SELECT
    order_date,
    SUM(amount)  AS total_sales,
    COUNT(*)     AS order_count,
    AVG(amount)  AS avg_order
  FROM orders
  GROUP BY order_date
)
-- ② CTEの結果にフィルタと並び替えを適用
SELECT
  order_date,
  total_sales,
  order_count,
  avg_order
FROM daily_stats
WHERE total_sales >= 50000
ORDER BY total_sales DESC;

/*
  実行順序:
  1. CTE daily_stats            → 日付ごとに集計（SUM/COUNT/AVG）
  2. 本体 WHERE                   → total_sales のしきい値で絞る
  3. ORDER BY total_sales DESC  → 降順で出力
*/

解説（テーブル変化・ポイント） ▼

WITH daily_stats AS (
  SELECT
    order_date,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order
  FROM orders
  GROUP BY order_date
)
SELECT
  order_date, total_sales,
  order_count, avg_order
FROM daily_stats
WHERE total_sales >= 50000
ORDER BY total_sales DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM ordersorders テーブル全体（8行）を読み込みます。次のステップで GROUP BY が日付ごとにデータをまとめます。

1 / 5

order_id	order_date	amount
1	2024-04-01	20,000
2	2024-04-01	35,000
3	2024-04-02	60,000
4	2024-04-02	15,000
5	2024-04-03	80,000
6	2024-04-03	40,000
7	2024-04-04	12,000
8	2024-04-04	18,000

全 8行読込

学習ポイント

実行順序：FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY の順で評価されます。CTEで処理を2層に分けることで、この順序が視覚的に整理されます。

COUNT(*) vs COUNT(col)：COUNT(*) はNULLを含む全行、COUNT(col) はNULLを除外して数えます。「行の存在」自体を数える場合は前者を使用します。

アンチパターン

WHEREに集計関数を直書き：WHERE SUM(...) は評価順序の都合上エラーになります。HAVING か CTE外側の WHERE を使用します。

CTE内のORDER BY：CTE内でのソートは実質無意味です。並び替えは外側の本体 SELECT で確実に行います。

実務コラム

CTEを「1.生データ処理」「2.マスタ結合」「3.集計」「4.整形」とパイプライン化すると、各ステップを単体実行でき運用保守性が劇的に向上します。

QUESTION 2

LAG() ウィンドウ関数 — 前月売上との差分・増減率を計算する

LAGウィンドウ関数前月比バッチレポート

前提知識 ▼

LAG() は「1行前（または n行前）の値」を同じ行に引っ張ってくるウィンドウ関数です。前月比・前日比の計算に不可欠です。

LAG(値の列, 1, 0) OVER (
  PARTITION BY グループ列   -- グループごとに独立して「前の行」を参照
  ORDER BY     順序列       -- この順序で「前の行」を決める
) AS 前月値

引数は LAG(列, オフセット, デフォルト値) です。オフセット省略時は1（1行前）、デフォルト値は前の行がない場合（先頭行）に使われます。

OVER句とPARTITION BY：OVER()がウィンドウ関数を「行を消さずに計算する」のに必要です。PARTITION BYを省略すると全行を1グループとして扱います。

問題 ▼

以下の monthly_sales テーブルから、各月の 前月売上・前月比の差額・前月比の増減率（%） を計算してください。

使用テーブル

▸ monthly_sales

month	sales
2024-01	100000
2024-02	130000
2024-03	120000
2024-04	160000
2024-05	145000

期待出力 ▼

month	sales	prev_sales	diff	growth_rate
2024-01	100000	0	NULL	NULL
2024-02	130000	100000	+30000	30.00
2024-03	120000	130000	-10000	-7.69
2024-04	160000	120000	+40000	33.33
2024-05	145000	160000	-15000	-9.38

模範解答コード ▼

-- WITH: CTEで処理を分割
WITH sales_with_prev AS (
  -- ① LAGで前月売上を同じ行に取得
  SELECT
    month,
    sales,
    LAG(sales, 1, 0) OVER (
      ORDER BY month
    ) AS prev_sales
  FROM monthly_sales
)
-- ② 差額・増減率を計算
SELECT
  month,
  sales,
  prev_sales,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE sales - prev_sales
  END AS diff,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE ROUND((sales - prev_sales)::numeric / prev_sales * 100, 2)
  END AS growth_rate
FROM sales_with_prev
ORDER BY month;

/*
  実行順序:
  1. CTE sales_with_prev  → LAG で前月売上を付与
  2. 本体 CASE              → 差額・増減率を計算
  3. ORDER BY month       → 月昇順で出力
*/

解説（テーブル変化・ポイント） ▼

WITH sales_with_prev AS (
  SELECT
    month, sales,
    LAG(sales, 1, 0) OVER (
      ORDER BY month
    ) AS prev_sales
  FROM monthly_sales
)
SELECT
  month, sales, prev_sales,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE sales - prev_sales
  END AS diff,
  CASE
    WHEN prev_sales = 0 THEN NULL
    ELSE ROUND((sales - prev_sales)::numeric / prev_sales * 100, 2)
  END AS growth_rate
FROM sales_with_prev
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM monthly_salesmonthly_sales テーブル全体（5行）を読み込みます。次のステップで LAG が各行に「1行前の sales」を付加します。

1 / 3

month	sales
2024-01	100,000
2024-02	130,000
2024-03	120,000
2024-04	160,000
2024-05	145,000

全 5行読込

学習ポイント

LAGのデフォルト値：LAG(sales, 1, 0) の第3引数で、先頭行のNULLを防ぎます。APIレスポンスのNULL回避に有用です。

整数同士の除算：多くのDBで整数除算は切り捨てられます。::numeric 等で明示的に型変換を行ってから計算します。

アンチパターン

ORDER BY なしの LAG：順序が不定になります。必ず ORDER BY を明示します。

分母ゼロでの除算：ゼロ除算エラーを防ぐため、CASE WHEN prev_sales = 0 THEN NULL でガードをかけます。

実務コラム

前月比や前週比はダッシュボードの最重要指標です。曜日ノイズを消すための LAG(sales, 7) も頻出パターンのひとつです。

QUESTION 3

SUM() OVER(ROWS BETWEEN) — 累計売上と移動平均を計算する

SUM OVERROWS BETWEEN累計移動平均

前提知識 ▼

ウィンドウ関数の ROWS BETWEEN 句を使うと、各行に対して「どの範囲の行を集計するか」を細かく指定できます。

SUM(sales) OVER (
  ORDER BY month
  ROWS BETWEEN UNBOUNDED PRECEDING    -- 先頭行から
           AND CURRENT ROW            -- 現在行まで → 累計
) AS running_total

AVG(sales) OVER (
  ORDER BY month
  ROWS BETWEEN 2 PRECEDING           -- 2行前から
           AND CURRENT ROW            -- 現在行まで → 3ヶ月移動平均
) AS moving_avg_3m

バッチ処理での使いどころ：「累計売上が目標値を超えた月を特定する」など時系列の集計バッチで頻出します。行を残したまま集計するウィンドウ関数が必須です。

問題 ▼

以下の monthly_sales テーブルから、各月の 累計売上（running_total） と 直近3ヶ月の移動平均（moving_avg_3m） を計算してください。

使用テーブル

▸ monthly_sales

month	sales
2024-01	80000
2024-02	120000
2024-03	100000
2024-04	150000
2024-05	130000
2024-06	170000

期待出力 ▼

month	sales	running_total	moving_avg_3m
2024-01	80000	80000	80000.0
2024-02	120000	200000	100000.0
2024-03	100000	300000	100000.0
2024-04	150000	450000	123333.3
2024-05	130000	580000	126666.7
2024-06	170000	750000	150000.0

模範解答コード ▼

SELECT
  month,
  sales,
  SUM(sales) OVER (                                   -- ① 累計売上: 先頭行から現在行までの合計
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROUND(                                              -- ② 3ヶ月移動平均: 2行前〜現在行の平均
    AVG(sales) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric, 1
  ) AS moving_avg_3m
FROM monthly_sales
ORDER BY month;

/*
  実行順序:
  1. FROM monthly_sales  → 行を読み込む
  2. SUM() OVER (...)    → 累計売上を計算
  3. AVG() OVER (...)    → 3ヶ月移動平均を計算
  4. ORDER BY month      → 月昇順で出力
*/

解説（テーブル変化・ポイント） ▼

SELECT
  month, sales,
  SUM(sales) OVER (
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  ROUND(
    AVG(sales) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric, 1
  ) AS moving_avg_3m
FROM monthly_sales
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM monthly_salesmonthly_sales テーブル全体（6行）を読み込みます。次のステップで SUM OVER が各行に「先頭から現在行までの累計」を付加します。

1 / 3

month	sales
2024-01	80,000
2024-02	120,000
2024-03	100,000
2024-04	150,000
2024-05	130,000
2024-06	170,000

全 6行読込

学習ポイント

ROWS vs RANGE：ROWS BETWEEN は物理行数、RANGE BETWEEN は値の範囲でウィンドウを決めます。意図が明確で高速な ROWS の使用を推奨します。

移動平均の実務利用：短期トレンドの平滑化および異常検知の基礎として利用されます。

アンチパターン

ORDER BY なしの累計：SUM() OVER() は全行合計になります。累計には ORDER BY と ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW が必要です。

実務コラム

移動平均は曜日やキャンペーンなどのノイズを平滑化し、トレンドを捉えます。直近7日(7MA)や28日(28MA)の計算は王道です。

QUESTION 4

ROW_NUMBER() + CTE — カテゴリ別トップN商品を抽出するAPI

ROW_NUMBERPARTITION BYトップNランキングAPI

前提知識 ▼

「カテゴリ別に上位N件だけ取得する」はAPIで頻出です。ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) で各行に「グループ内の順位」を付け、CTEで切り出してから WHERE rn <= N でフィルタするのが王道パターンです。

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY category        -- カテゴリごとに独立して番号を振る
      ORDER BY     sales DESC, product_id  -- 同額はproduct_id昇順で一意化
    ) AS rn
  FROM products
)
SELECT *
FROM   ranked
WHERE  rn <= 2;   -- 各カテゴリの上位2件だけ取る

ROW_NUMBER / RANK / DENSE_RANK の違い：ROW_NUMBERは同率でも必ずユニークな連番。RANKは同率で同順位・次の番号をスキップ（1,1,3）。DENSE_RANKは同率で同順位・次の番号はスキップしない（1,1,2）。トップN抽出には ROW_NUMBER が最も扱いやすいです。

問題 ▼

以下の product_sales テーブルから、カテゴリ（category）ごとに売上（sales）の上位2商品を抽出してください。

使用テーブル

▸ product_sales

product_id	category	product_name	sales
P01	食品	りんご	85000
P02	食品	バナナ	62000
P03	食品	みかん	62000
P04	食品	ぶどう	41000
P05	飲料	緑茶	95000
P06	飲料	コーヒー	78000
P07	飲料	ジュース	78000
P08	飲料	水	55000

期待出力 ▼

category	product_name	sales	rn
食品	りんご	85000	1
食品	バナナ	62000	2
飲料	緑茶	95000	1
飲料	コーヒー	78000	2

模範解答コード ▼

-- WITH: CTEで処理を分割
WITH ranked_products AS (
  -- ① カテゴリ内の売上順位を付ける
  SELECT
    category,
    product_name,
    sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY sales DESC, product_id
    ) AS rn
  FROM product_sales
)
-- ② 各カテゴリの上位2件を抽出
SELECT
  category,
  product_name,
  sales,
  rn
FROM ranked_products
WHERE rn <= 2
ORDER BY category, rn;

/*
  実行順序:
  1. CTE ranked_products    → カテゴリ内で売上順位を付与
  2. 本体 WHERE rn <= 2       → 各カテゴリ上位2件を抽出
  3. ORDER BY category, rn  → 整列して出力
*/

解説（テーブル変化・ポイント） ▼

WITH ranked_products AS (
  SELECT
    category, product_name, sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY sales DESC, product_id
    ) AS rn
  FROM product_sales
)
SELECT
  category, product_name, sales, rn
FROM ranked_products
WHERE rn <= 2
ORDER BY category, rn;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM product_salesproduct_sales テーブル全体（8行）を読み込みます。次のステップで PARTITION BY がカテゴリごとにグループを形成します。

1 / 5

product_id	category	product_name	sales
P01	食品	りんご	85,000
P02	食品	バナナ	62,000
P03	食品	みかん	62,000
P04	食品	ぶどう	41,000
P05	飲料	緑茶	95,000
P06	飲料	コーヒー	78,000
P07	飲料	ジュース	78,000
P08	飲料	水	55,000

全 8行読込

学習ポイント

CTEの必要性：ウィンドウ関数はWHEREより後で評価されます。CTEで評価を完了させてから外側でフィルタします。

PARTITION BY：省略すると全体順位になります。「カテゴリ別」などのグループ内順位には必須です。

アンチパターン

同階層でのWHERE評価：ウィンドウ関数の結果を同じSELECT内のWHEREで直接使うとエラーになります。

実務コラム

「カテゴリ別トップN」や、ORDER BY created_at DESC で最新履歴のみ取得するパターンはマート作成時の必須テクニックです。

QUESTION 5

CASE WHEN + SUM — ステータス別件数をピボット集計する

CASE WHENピボットSUM FILTERクロス集計

前提知識 ▼

行方向のデータを列方向に展開する「ピボット集計」は、ダッシュボードAPIでの頻出パターンです。SQLには専用のPIVOT構文がない場合が多いため、CASE WHEN + SUM で実現します。

SELECT
  category,
  SUM(CASE WHEN status = '完了' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = '未完了' THEN 1 ELSE 0 END) AS pending
FROM   tasks
GROUP BY category;

PostgreSQL では FILTER 句も使えます：COUNT(*) FILTER (WHERE status = '完了') と書くと同じ結果になります。ただし、BigQueryなど未対応のDBもあるため CASE WHEN の方が汎用性が高いです。

問題 ▼

以下の tasks テーブルから、担当者（assignee）ごとにステータス別の件数（完了・進行中・未着手）と合計件数を1行にまとめて出力してください。

使用テーブル

▸ tasks

task_id	assignee	status
T01	田中	完了
T02	田中	完了
T03	田中	進行中
T04	田中	未着手
T05	鈴木	完了
T06	鈴木	進行中
T07	鈴木	進行中
T08	佐藤	未着手
T09	佐藤	未着手
T10	佐藤	完了

期待出力 ▼

assignee	done	in_progress	not_started	total
佐藤	1	0	2	3
田中	2	1	1	4
鈴木	1	2	0	3

模範解答コード ▼

SELECT
  assignee,
  SUM(CASE WHEN status = '完了' THEN 1 ELSE 0 END) AS done,          -- ① 完了タスク件数
  SUM(CASE WHEN status = '進行中' THEN 1 ELSE 0 END) AS in_progress,  -- ② 進行中タスク件数
  SUM(CASE WHEN status = '未着手' THEN 1 ELSE 0 END) AS not_started,  -- ③ 未着手タスク件数
  COUNT(*) AS total                                                -- ④ 合計タスク件数
FROM tasks
GROUP BY assignee
ORDER BY assignee;

/*
  実行順序:
  1. FROM tasks         → 行を読み込む
  2. GROUP BY assignee  → 担当者でグループ化
  3. SUM(CASE...)       → 状態別件数を集計（ピボット）
  4. ORDER BY assignee  → 昇順で出力
*/

解説（テーブル変化・ポイント） ▼

SELECT
  assignee,
  SUM(CASE WHEN status = '完了' THEN 1 ELSE 0 END) AS done,
  SUM(CASE WHEN status = '進行中' THEN 1 ELSE 0 END) AS in_progress,
  SUM(CASE WHEN status = '未着手' THEN 1 ELSE 0 END) AS not_started,
  COUNT(*) AS total
FROM tasks
GROUP BY assignee
ORDER BY assignee;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM taskstasks テーブル全体（10行）を読み込みます。次のステップで GROUP BY が担当者ごとにデータをグループ化します。

1 / 5

task_id	assignee	status
T01	田中	完了
T02	田中	完了
T03	田中	進行中
T04	田中	未着手
T05	鈴木	完了
T06	鈴木	進行中
T07	鈴木	進行中
T08	佐藤	未着手
T09	佐藤	未着手
T10	佐藤	完了

全 10行読込

学習ポイント

CASE WHENによるフラグ化：SUM(CASE WHEN ... THEN 1 ELSE 0 END) は条件を満たす行をカウントするイディオムです。

ピボットによる圧縮：行方向のデータを列方向へ展開し、1クエリでAPIレスポンス用の集計表を作成します。

アンチパターン

ELSEの省略：条件不一致でNULLが返ります。意図しない挙動を防ぐため ELSE 0 を明示します。

実務コラム

DB側でピボットすることで、アプリ側への転送量とメモリ消費を大幅に削減できます。大規模ログのダッシュボード集計で有効です。