SQL ウィンドウ関数 — LAG/LEAD・移動平均・月次推移の基礎

QUESTION 6

LAG() / LEAD() — 前後の行の値を取得し「前日比」を出す

LAGLEAD前日比比較

前提知識 ▼

LAG()（前の行）と LEAD()（次の行）を使うと、同一テーブル内で「他の行の値」を横に持ってきて簡単に引き算（差分）などを計算できます。これもウィンドウ関数の真骨頂です。

SELECT
  sort_col, num_col,
  LAG(num_col) OVER(ORDER BY sort_col ASC) AS prev_val
FROM table_name;

LAG(列名, ずらす行数, デフォルト値)：引数で「何行前か」を指定できます（省略時は1行前）。一番最初の行には前の行がないため NULL が返ります。

問題 ▼

以下の daily_sales テーブルから、日付ごとの「売上(amount)」「前日の売上」「前日からの差分(diff)」を計算してください。

使用テーブル

▸ daily_sales

date	amount
04-01	100
04-02	120
04-03	90
04-04	150

期待出力 ▼

date	amount	prev_amount	diff
04-01	100	NULL	NULL
04-02	120	100	20
04-03	90	120	-30
04-04	150	90	60

模範解答コード ▼

SELECT
  date,
  amount,
  LAG(amount) OVER(ORDER BY date) AS prev_amount,  -- 日付順の1つ前の amount
  amount - LAG(amount) OVER(ORDER BY date) AS diff
FROM daily_sales                                    -- 今の売上から前の売上を引くことで「差分」を計算
ORDER BY date;

/*
  実行順序:
  1. FROM daily_sales
  2. Window関数が date 順に行を評価
  3. SELECT 出力
  */

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

SELECT
  date, amount,
  LAG(amount) OVER(ORDER BY date) AS prev_amount,
  amount - LAG(amount) OVER(ORDER BY date) AS diff
FROM daily_sales
ORDER BY date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM daily_salesテーブルを読み込みます。

1 / 3

date	amount
04-01	100
04-02	120
04-03	90
04-04	150

全 4行読込

学習ポイント

自己結合（SELF JOIN）の撲滅：LAG関数がない時代は、前日比を出すために「日付を1日ずらした自分自身のテーブルとJOINする」という非常に重くて複雑なクエリが必要でした。LAGの登場により、分析SQLの書きやすさが革命的に向上しました。

PARTITION BYとの併用：LAG(amount) OVER(PARTITION BY store_id ORDER BY date) とすれば、店舗が切り替わったタイミングで適切にLAGがリセット（NULL）されるため、別店舗の前日売上を引いてしまう事故を防げます。

▤ LAG() の動作 — 各行が「1行前の値」を参照する

date	amount	LAG(amount) — 参照先	prev_amount	diff（引き算）
04-01	100	前の行がない → NULL	NULL	NULL
04-02	120	↑ 04-01 の amount	100	+20
04-03	90	↑ 04-02 の amount	120	−30
04-04	150	↑ 04-03 の amount	90	+60

↑ 矢印 = LAG が参照する行。ORDER BY date で並んだ状態で、各行は必ず「直上の行」の値を借りる。先頭行 (04-01) は借りる先がないため NULL となる。

アンチパターン

NULLの考慮漏れ：最初の行は必ず LAG の結果が NULL になります。前日差分(diff)を後続の算術演算や、非NULL行数が分母に影響する集計へ渡すと、意図した結果が変わることがあります。業務定義上必要であれば、COALESCE(LAG(amount) OVER(...), 0) のように NULL を保護しましょう。

QUESTION 7

ROWS BETWEEN — フレーム指定で「移動平均（Moving Avg）」を出す

フレーム指定ROWS BETWEEN移動平均

前提知識 ▼

ウィンドウ関数は、集計対象となる「行の範囲（フレーム）」を細かく指定できます。これを活用して株価グラフ等でおなじみの移動平均を計算します。

AVG(amount) OVER(
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

フレーム指定の読み方：2 PRECEDING(2行前) から CURRENT ROW(現在の行) まで、つまり「直近最大3行（過去2行＋現在行）」を対象にAVGを計算するという意味です。このテーブルは1日1行なので直近3日間に相当します。

問題 ▼

daily_sales テーブルを使用し、日付ごとの売上と、「直近3行（2行前〜現在の行）の移動平均」を求めてください。

使用テーブル

▸ daily_sales

date	amount
04-01	100
04-02	140
04-03	120
04-04	190
04-05	170

期待出力 ▼

date	amount	moving_avg_3d
04-01	100	100
04-02	140	120
04-03	120	120
04-04	190	150
04-05	170	160

模範解答コード ▼

SELECT
  date,
  amount,
  AVG(amount) OVER(  -- 移動平均（行は集約しない）
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 直近3日（自分＋前2行）の枠
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY date;

/*
  計算過程:
  04-01: 前がないので [04-01] の平均 → 100
  04-02: 1つ前まであるので [04-01, 04-02] の平均 → (100+140)/2 = 120
  04-03: 2つ前まで揃う [04-01, 04-02, 04-03] の平均 → 360/3 = 120
  04-04: [04-02, 04-03, 04-04] の平均 → (140+120+190)/3 = 150
*/

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

SELECT
  date, amount,
  AVG(amount) OVER(
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM daily_salesテーブルを読み込みます。

1 / 3

date	amount
04-01	100
04-02	140
04-03	120
04-04	190
04-05	170

全 5行読込

学習ポイント

フレーム指定のキーワード早見表：PRECEDING（前の行）/ FOLLOWING（後の行）/ UNBOUNDED PRECEDING（最初の行）/ UNBOUNDED FOLLOWING（最後の行）/ CURRENT ROW（現在の行）の5つが基本セットです。

実務での移動平均の価値：日々の売上やアクティブユーザー数は曜日によって大きく変動します（土日だけ上がるなど）。これを「7行移動平均（6 PRECEDING AND CURRENT ROW）」にすることでノイズが消え、本当のトレンド（上昇傾向か下降傾向か）が見えるようになります。1日1行なら7日移動平均に相当します。分析の基本テクニックです。

▤ ROWS BETWEEN ... — フレームがどう動くか

date	amount	04-01 100	04-02 140	04-03 120	04-04 190	04-05 170	moving_avg
04-01	100	★					100
04-02	140	○	★				120
04-03	120	○	○	★			120
04-04	190	—	○	○	★		150
04-05	170	—	—	○	○	★	160

★ CURRENT ROW（現在行） ○ PRECEDING（集計対象の過去行） — フレーム外（集計対象外）

04-04行の場合: フレーム = 「2行前(04-02)〜現在(04-04)」→ (140+120+190)÷3 = 150。04-01 はフレーム外なので対象外。

アンチパターン

フレームのデフォルト挙動を誤解する：ORDER BY だけを指定し ROWS BETWEEN を省略した場合、暗黙的に RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（先頭行から現在の行まで）が適用され、「累計」になってしまいます。移動平均を出したいときは必ず明示的にフレームを指定してください。

QUESTION 8

サブクエリで絞り込む — WHERE でウィンドウ関数の結果を使う

サブクエリROW_NUMBER最新行抽出超頻出

前提知識 ▼

ウィンドウ関数は SQL の実行順序の都合上、WHERE 句に直接書くことができません。計算結果で行を絞り込みたい場合は、サブクエリ（またはCTE）内でウィンドウ関数を実行し、外側のクエリのWHEREで絞るという手順を踏む必要があります。

-- 最新履歴の取得など、実務で1日10回書く最強の型
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC, log_id DESC) AS rn
  FROM logs
) AS tmp
WHERE rn = 1;

なぜ WHERE に直接書けないか：WHERE はウィンドウ関数より先に評価されるため、同じ SELECT の中でウィンドウ関数の結果を条件に使うことはできません。サブクエリや CTE でいったん列にしてから、外側で絞り込みます。並び順が同値になる行があるときは、ORDER BY にタイブレーカーを足さないと選ばれる行が定まりません。

問題 ▼

以下の orders テーブルから、各ユーザーの最新の注文履歴（最も新しい order_date の行）のみを全て（全カラム）抽出してください。同日なら order_id が最大の行を選びます。

使用テーブル

▸ orders

order_id	user_id	order_date	item
1	U1	2024-04-01	マウス
2	U2	2024-04-02	デスク
3	U1	2024-04-05	ノートPC
4	U3	2024-04-06	椅子
5	U2	2024-04-08	モニター

期待出力 ▼

order_id	user_id	order_date	item
3	U1	2024-04-05	ノートPC
5	U2	2024-04-08	モニター
4	U3	2024-04-06	椅子

模範解答コード ▼

SELECT
  order_id,
  user_id,
  order_date,
  item
FROM (
  -- サブクエリ: ユーザーごとに最新順で連番を振る
  SELECT
    order_id,
    user_id,
    order_date,
    item,
    ROW_NUMBER() OVER(
      PARTITION BY user_id
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
) AS tmp    -- サブクエリにはエイリアス(一時名)が必須
WHERE rn = 1 -- 外側のクエリで「最新の1件」のみに絞り込む
ORDER BY user_id;

/*
  実行順序:
  1. FROM orders（内側）
  2. Window関数（内側）: ROW_NUMBER が付与された一時的な表(tmp)が作られる
     U1: ノートPC(rn=1), マウス(rn=2)
     U2: モニター(rn=1), デスク(rn=2)
     U3: 椅子(rn=1)
  3. FROM tmp（外側）
  4. WHERE rn = 1: 外側のクエリで条件に合致する行だけを取り出す
  5. SELECT 出力
*/

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

SELECT
  order_id, user_id, order_date, item
FROM (
  SELECT
    order_id, user_id, order_date, item,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders
) AS tmp
WHERE rn = 1
ORDER BY user_id;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① サブクエリの実行 ▼

サブクエリ内で ROW_NUMBER を付与まずは内側のクエリが実行され、ユーザーごとの最新順（order_date DESC、同日なら order_id DESC）で連番 rn が振られた仮想テーブル（tmp）が作られます。

1 / 3

order_id	user_id	order_date	item	rn
3	U1	04-05	ノートPC	1
1	U1	04-01	マウス	2
5	U2	04-08	モニター	1
2	U2	04-02	デスク	2
4	U3	04-06	椅子	1

5行（連番付与状態）

学習ポイント

GROUP BY で代替できない理由：よくある間違いとして SELECT user_id, MAX(order_date), item FROM orders GROUP BY user_id と書いてしまう人がいますが、MySQLなどでは item がどの行のものか保証されず、でたらめなデータが返るかエラーになります。「最新の行の全ての列」を取りたいときは、必ず ROW_NUMBER + サブクエリのパターンを使います。

▤ 2層構造の実行フロー — サブクエリ → 外側WHERE という2ステップ

① 内側サブクエリ実行 — ROW_NUMBER で全行に連番付与

order_id	user_id	order_date	item	rn（ユーザー別最新順）
3	U1	04-05	ノートPC	1 ← 最新
1	U1	04-01	マウス	2
5	U2	04-08	モニター	1 ← 最新
2	U2	04-02	デスク	2
4	U3	04-06	椅子	1 ← 最新

↓ WHERE rn = 1

② 外側クエリで rn=1 の行だけを抽出 — 各ユーザーの最新1件のみ残る

order_id	user_id	order_date	item
3	U1	04-05	ノートPC
5	U2	04-08	モニター
4	U3	04-06	椅子

5行 → 3行（ユーザー数 = ユーザーごと最新1件）。rn列は外側SELECTで除外される。

アンチパターン

RANK() を使う、または同率時の順序を決めない：「最新」を取得する際、もし同じ日時に2回の注文があった場合、RANK() では rn=1 が2件でき、行数がユーザー数より多くなります。ROW_NUMBER() でも同日時のタイブレーカーがなければ、どちらを選ぶかは不定です。order_id DESC など一意になる列まで ORDER BY に加えましょう。

QUESTION 9

Top N 抽出 — 各カテゴリごとの「上位2順位」を取り出す

Top NRANK()カテゴリ別順位

前提知識 ▼

「最新1件抽出」の応用で、rn <= 3 のように条件を変えることで、各グループの上位N件を抽出できます。同率を同順位にする場合は RANK を使い、上位N順位を絞ります。これも GROUP BY では不可能な、ウィンドウ関数ならではの強力な処理です。

WITH ranked AS (                   -- ① 内側で順位を付ける
  SELECT t.*,
         RANK() OVER (PARTITION BY group_col ORDER BY num_col DESC) AS rnk
  FROM   table_name t
)
SELECT *
FROM   ranked
WHERE  rnk <= 3;                     -- ② 外側の WHERE で上位N順位に絞る

ROW_NUMBER と RANK の使い分け：各グループから「ちょうどN行」が欲しいときは ROW_NUMBER()、「上位N順位（同率はすべて残す）」が欲しいときは RANK() を使います。同率を残すかどうかで、取り出される行数が変わります。

問題 ▼

以下の products テーブルから、各カテゴリ（category）ごとに、価格（price）が高い上位2順位の商品を抽出してください。

※価格が同じ場合は同順位として抽出し、順位(rnk)も一緒に出力すること。

使用テーブル

▸ products

product_id	category	name	price
P1	家電	TV	80000
P2	家電	冷蔵庫	120000
P3	家電	洗濯機	90000
P4	家具	ベッド	50000
P5	家具	ソファ	70000
P6	家具	テーブル	30000

期待出力 ▼

category	name	price	rnk
家電	冷蔵庫	120000	1
家電	洗濯機	90000	2
家具	ソファ	70000	1
家具	ベッド	50000	2

模範解答コード ▼

SELECT
  category,
  name,
  price,
  rnk
FROM (
  SELECT
    category,
    name,
    price,
    RANK() OVER(  -- カテゴリ内で価格の高い順に順位
      PARTITION BY category
      ORDER BY price DESC
    ) AS rnk
  FROM products
) AS tmp
WHERE rnk <= 2  -- 各カテゴリ上位2順位を残す（同率なら2件を超える）
ORDER BY CASE category
  WHEN '家電' THEN 1
  WHEN '家具' THEN 2
  END, rnk, price DESC, name;

/*
  実行順序:
  1. サブクエリ内で、カテゴリごとに価格降順の RANK を付与する
  2. 外側のクエリで rnk <= 2 （上位2順位）の条件で絞る
  */

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

SELECT
  category, name, price, rnk
FROM (
  SELECT
    category, name, price,
    RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rnk
  FROM products
) AS tmp
WHERE rnk <= 2
ORDER BY CASE category WHEN '家電' THEN 1 WHEN '家具' THEN 2 END, rnk, price DESC, name;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① サブクエリで RANK() 付与 ▼

PARTITION BY category ORDER BY price DESCカテゴリごとに独立して順位（RANK）を付与します。

1 / 3

category	name	price	rnk
家電	冷蔵庫	120000	1
家電	洗濯機	90000	2
家電	TV	80000	3
家具	ソファ	70000	1
家具	ベッド	50000	2
家具	テーブル	30000	3

順位付け完了

学習ポイント

WITH句（CTE）を使った書き換え：サブクエリがネストして読みづらい場合、WITH ranked AS ( SELECT ... RANK() ... ) SELECT * FROM ranked WHERE rnk <= 2; と書くと上から下へ自然に読めるモダンなSQLになります。

▤ カテゴリ別 Top N — パーティション × RANK の組み合わせ効果

category	name	price	RANK() PARTITION BY category ORDER BY price DESC	WHERE rnk <= 2
家電	冷蔵庫	120,000	1	✓ 抽出
家電	洗濯機	90,000	2	✓ 抽出
家電	TV	80,000	3	✗ 除外
家具	ソファ	70,000	1 ← パーティションでリセット	✓ 抽出
家具	ベッド	50,000	2	✓ 抽出
家具	テーブル	30,000	3	✗ 除外

家電パーティション内で 1〜3 位を付与し、次に家具パーティションで再び 1 から付与。
「カテゴリをまたがった総合ランク」ではなく「カテゴリ内ランク」が付与されるのが PARTITION BY の効果。

アンチパターン

ウィンドウ関数非対応環境での無理な代替：古いMySQL（5.7以前）ではウィンドウ関数が使えず、これを相関サブクエリ等で代替しようとするとクエリが非常に複雑かつ重くなります。モダンなRDBMS環境ではウィンドウ関数を積極的に利用しましょう。

QUESTION 10

実務総まとめ — ウィンドウ関数をフル活用した月次推移レポート

累計前月比実務レポート総まとめ

前提知識 ▼

実務のダッシュボード作成では、1つのクエリで様々な集計値を横並びにします。
今までの知識を総動員して、1回の SELECT で「当月売上」「累計売上」「前月売上」「前月差分」を一気に算出しましょう。

SELECT month_col,
       SUM(num_col)                                AS monthly,     -- 当月の集計
       SUM(SUM(num_col)) OVER (ORDER BY month_col) AS cumulative,  -- 集計値の累計
       LAG(SUM(num_col)) OVER (ORDER BY month_col) AS prev_month   -- 1つ前の集計値
FROM   table_name
GROUP BY month_col
ORDER BY month_col;

集計とウィンドウの評価順：ウィンドウ関数は GROUP BY による集約のあとに評価されます。そのため SUM(SUM(...)) OVER (...) のように、集計結果をさらにウィンドウ関数へ渡せます。OVER 句の ORDER BY に書けるのも、集約後に存在する列です。

問題 ▼

以下の monthly_sales テーブルから、月（month）ごとに以下の4つの値を出力してください。
1. amount （当月売上）
2. running_total （1月からの累計売上）
3. prev_amount （前月の売上。1月はNULLでよい）
4. diff_from_prev （前月からの増減額）

使用テーブル

▸ monthly_sales

month	amount
01月	100
02月	120
03月	150
04月	130
05月	180

期待出力 ▼

month	amount	running_total	prev_amount	diff_from_prev
01月	100	100	NULL	NULL
02月	120	220	100	20
03月	150	370	120	30
04月	130	500	150	-20
05月	180	680	130	50

模範解答コード ▼

SELECT
  month,
  amount,
  SUM(amount) OVER(ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END) AS running_total,
  LAG(amount) OVER(ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END) AS prev_amount,
  amount - LAG(amount) OVER(ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END) AS diff_from_prev

FROM monthly_sales
ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END;

/*
  実行順序:
  1. FROM monthly_sales
  2. SELECT句内の複数のWindow関数が同時に評価される
  3. ORDER BY 出力
  */

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

SELECT
  month, amount,
  SUM(amount) OVER(ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END) AS running_total,
  LAG(amount) OVER(ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END) AS prev_amount,
  amount - LAG(amount) OVER(ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END) AS diff_from_prev
FROM monthly_sales
ORDER BY CASE month WHEN '01月' THEN 1 WHEN '02月' THEN 2 WHEN '03月' THEN 3 WHEN '04月' THEN 4 WHEN '05月' THEN 5 END;

LEGEND ▼

データ取得・読込対象

① ベースデータ ▼

FROM monthly_salesテーブルを読み込みます。

1 / 3

month	amount
01月	100
02月	120
03月	150
04月	130
05月	180

全 5行読込

学習ポイント

ウィンドウ関数は最強の武器：「行を潰さずに全体の文脈（前後の行やグループ全体）を参照できる」という性質は、SQLの表現力を飛躍的に高めます。実務でダッシュボード向けのデータマートを作成する際、ウィンドウ関数を使わずに書かれたSQLはほぼ存在しないと言っても過言ではありません。この感覚をマスターすることが、中級者から上級者への最大の壁になります。

分析エンジニアの実務：「前月比120%達成！でも累計目標には届かないかも…」といった多角的な視点を提供するために、様々な角度からの指標（単月、累計、移動平均など）を1つのテーブルにまとめることがデータエンジニアの重要なミッションです。

▤ 3つのウィンドウ関数が各行で同時評価される — 行ごとの計算内訳

month	amount	SUM OVER(logical month order) = running_total	LAG(amount) OVER(...) = prev_amount	amount − LAG() = diff_from_prev
01月	100	100 [100]	NULL（先頭行）	NULL
02月	120	220 [100+120]	100 ↑01月の値	+20 120−100
03月	150	370 [100+120+150]	120 ↑02月の値	+30 150−120
04月	130	500 [100+120+150+130]	150 ↑03月の値	−20 130−150
05月	180	680 [100+120+150+130+180]	130 ↑04月の値	+50 180−130

3つのウィンドウ式は、各行に対してそれぞれのウィンドウ上で論理的に評価される。物理的な実行方法はデータベースのオプティマイザが決定する。

LAG() / LEAD() — 前後の行の値を取得し「前日比」を出す

ROWS BETWEEN — フレーム指定で「移動平均（Moving Avg）」を出す

サブクエリで絞り込む — WHERE で ウィンドウ関数の結果を使う

Top N 抽出 — 各カテゴリごとの「上位2順位」を取り出す

実務総まとめ — ウィンドウ関数をフル活用した月次推移レポート

サブクエリで絞り込む — WHERE でウィンドウ関数の結果を使う