SQL ウィンドウ関数 — LEAD・FIRST

QUESTION 6

LEAD() — 次の行の値を先読みして成長率・差分を計算する

LEADORDER BY成長率分析LAGの逆方向版

前提知識 ▼

LEAD(expr, offset, default) は、現在の行から offset 行だけ先（未来）の値を返す関数です。デフォルトは1行先。パーティションの最終行など「次の行が存在しない」場合は default の値（省略時は NULL）を返します。

LEAD(revenue)         -- 1行先の revenue（デフォルト）
LEAD(revenue, 2)      -- 2行先の revenue
LEAD(revenue, 1, 0)  -- 1行先、存在しない場合は 0 を返す

LAG との違い：LAG() が「過去（前の行）」を参照するのに対し、LEAD() は「未来（次の行）」を参照します。どちらも「次／前」がどの行かは ORDER BY が決め、月次・週次の前期比や成長率の計算に頻出します。

問題 ▼

以下の monthly_revenue テーブルから、各月の売上と「翌月の売上（next_revenue）」、および「翌月との差分（diff = next_revenue − revenue）」を出力してください。翌月が存在しない場合は NULL としてください。

使用テーブル

▸ monthly_revenue

month	revenue
2026-01	100
2026-02	120
2026-03	90
2026-04	150

期待出力 ▼

month	revenue	next_revenue	diff
2026-01	100	120	+20
2026-02	120	90	-30
2026-03	90	150	+60
2026-04	150	NULL	NULL

模範解答コード ▼

SELECT
  month,
  revenue,
  LEAD(revenue) OVER(ORDER BY month) AS next_revenue,   -- 1行先の revenue を先読み（2行先なら LEAD(revenue, 2)）
  LEAD(revenue) OVER(ORDER BY month) - revenue AS diff  -- 翌月との差分：NULL を含む演算はすべて NULL になる

FROM monthly_revenue
ORDER BY month;

/*
  実行順序:
  1. FROM monthly_revenue (全4行を読み込む)
  2. OVER(ORDER BY month) で月昇順に並べ、行の順序を確定する
  3. LEAD(revenue) が現在行の「1行後」の revenue を取得する
  4. diff = LEAD(revenue) - revenue を計算
  5. ORDER BY month で月昇順に出力
  */

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

SELECT
  month, revenue,
  LEAD(revenue) OVER(ORDER BY month) AS next_revenue,
  LEAD(revenue) OVER(ORDER BY month) - revenue AS diff
FROM monthly_revenue
ORDER BY month;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM monthly_revenueテーブルを読み込みます。4ヶ月分の売上データがあります。この時点では順序が確定していないため、LEAD() はまだ評価されません。

1 / 4

month	revenue
2026-01	100
2026-02	120
2026-03	90
2026-04	150

全 4行読込

学習ポイント

LAG vs LEAD の使い分け：「前の行を参照したい（前月実績との比較）」→ LAG()、「次の行を参照したい（翌月予測との比較）」→ LEAD()。LAG と LEAD は ORDER BY の方向を逆にすることで互換性があります（LAG(x) OVER(ORDER BY date DESC) は LEAD(x) OVER(ORDER BY date ASC) と同じ結果）。

デフォルト値で NULL を回避：最終行の NULL が後続の計算を壊す場合は LEAD(revenue, 1, 0) のようにデフォルト値を指定します。ただし「0円成長」とみなされる可能性があるため、ビジネス文脈に合わせて COALESCE や NULL のまま扱うかを設計段階で決めておくことが重要です。

⊞ LAG vs LEAD — 参照方向の対比（同じデータ・異なる視点）

month	revenue	LAG(revenue) 前月の売上	LEAD(revenue) 翌月の売上
2026-01	100	NULL (前月なし)	120
2026-02	120	100	90
2026-03	90	120	150
2026-04	150	90	NULL (翌月なし)

LAG と LEAD を同時に出力することで、「前後の値との比較」が一行で可能になります。時系列データの異常検知（前後との乖離が大きい行の検出）にも活用できます。

アンチパターン

ORDER BY なしの LEAD() は非決定的：LEAD(revenue) OVER() のように ORDER BY を省略すると、DBエンジンが好きな順序で行を並べるため、「どの行が次の行か」が実行ごとに変わる可能性があります。常に OVER(ORDER BY date) のように順序を明示しましょう。

PARTITION BY を忘れると複数ユーザーのデータが混在：ユーザーや部門ごとに別々に「次の行」を参照したい場合は、LEAD(revenue) OVER(PARTITION BY user_id ORDER BY month) のように PARTITION BY を必ず指定します。忘れると異なるユーザーの行をまたいで参照してしまいます。

QUESTION 7

FIRST_VALUE() — カテゴリ内トップとの差分を一発で計算する

FIRST_VALUEPARTITION BYカテゴリ比較分析LAST_VALUEの罠あり

前提知識 ▼

FIRST_VALUE(expr) は、ウィンドウフレームの最初の行の値を返す関数です。PARTITION BY category ORDER BY score DESC と組み合わせると、「そのカテゴリでスコアが最も高い値（トップ）」を常に取得できます。

FIRST_VALUE(score) OVER(
  PARTITION BY category
  ORDER BY score DESC
) AS top_score  -- カテゴリ内 最高スコア

既定フレームと同点行：ORDER BY を指定した場合のデフォルトフレーム（通常は RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）には先頭行が常に含まれるため、どの行を処理中でも先頭行の値が返されます。同点行があると同じ並び順の値を持つ行がピアとして同じフレームに入りますが、FIRST_VALUE が返す値自体は変わりません。

問題 ▼

以下の product_scores テーブルから、各商品のスコアと、「同じカテゴリ内で最高のスコア（top_score）」および「トップとの差（gap = top_score − score）」を出力してください。カテゴリ内でスコアが高い順に並べてください。

使用テーブル

▸ product_scores

product	category	score
P1	A	85
P2	A	92
P3	A	78
P4	B	70
P5	B	88

期待出力 ▼

product	category	score	top_score	gap
P2	A	92	92	0
P1	A	85	92	7
P3	A	78	92	14
P5	B	88	88	0
P4	B	70	88	18

模範解答コード ▼

SELECT
  product,
  category,
  score,
  FIRST_VALUE(score) OVER(  -- カテゴリ内でスコア降順にソートした先頭行 = 最高スコア
    PARTITION BY category
    ORDER BY score DESC
  ) AS top_score,
  FIRST_VALUE(score) OVER(  -- トップとの差分（top_score - 自分のスコア）
    PARTITION BY category
    ORDER BY score DESC
  ) - score AS gap

FROM product_scores
ORDER BY category, score DESC;

/*
  実行順序:
  1. FROM product_scores (全5行)
  2. PARTITION BY category でカテゴリ A (3行) と B (2行) に分割
  3. ORDER BY score DESC で各カテゴリ内をスコア降順にソート
  4. FIRST_VALUE(score) が各パーティションの先頭行のスコアを全行に付与
  5. gap = FIRST_VALUE(score) - score で差分を計算
  6. ORDER BY category, score DESC で最終出力
  */

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

SELECT
  product, category, score,
  FIRST_VALUE(score) OVER(
    PARTITION BY category
    ORDER BY score DESC
  ) AS top_score,
  FIRST_VALUE(score) OVER(
    PARTITION BY category ORDER BY score DESC
  ) - score AS gap
FROM product_scores
ORDER BY category, score DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM product_scores5件の商品データを読み込みます。カテゴリ A（3件）と B（2件）が混在しています。この時点では順序もグループも未確定です。

1 / 4

product	category	score
P1	A	85
P2	A	92
P3	A	78
P4	B	70
P5	B	88

全 5行読込

学習ポイント

FIRST_VALUE vs MAX()：MAX(score) OVER(PARTITION BY category) でも「カテゴリ最高スコア」は取れますが、FIRST_VALUE の真価は数値以外を取得できる点です。例えば「カテゴリ最高スコアの商品名」は FIRST_VALUE(product) OVER(PARTITION BY category ORDER BY score DESC) で取得できますが、MAX(product) では取れません。

LAST_VALUE() の罠：LAST_VALUE(score) OVER(PARTITION BY category ORDER BY score DESC) は直感に反して「パーティション全体の最後の行の値」を返しません。デフォルトフレームが「先頭〜現在行（同点のピアを含む）」なので、各行では現在のフレーム末尾の値が返り、最終行のフレームで初めて最小値になります。本当の最後の値を全行で得るには ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を明示する必要があります（下表参照）。

LAST_VALUE() が直感通りに動かない理由（フレームの罠）

✗ デフォルトフレームの LAST_VALUE（カテゴリ A）

product	score	LAST_VALUE （デフォルト = 先頭〜現在行）
P2	92	92 ← 現在行自身
P1	85	85 ← 現在行自身
P3	78	78 ← 現在行自身（= 最小値）

フレームが「先頭〜現在行」なので、最後の行に来るまで最後尾の値が取れない

✓ フレーム明示の LAST_VALUE（カテゴリ A）

product	score	LAST_VALUE （UNBOUNDED FOLLOWING まで）
P2	92	78 ← 常に最小値
P1	85	78 ← 常に最小値
P3	78	78 ← 常に最小値

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を追加して初めて期待通りに動く

このためカテゴリ最小値の取得には LAST_VALUE より MIN(score) OVER(PARTITION BY category) の方が安全でシンプルです。LAST_VALUE はフレームの明示が必要な「上級者向き」の関数と覚えておきましょう。

QUESTION 8

移動平均 — 直近3日間のスライディング・ウィンドウを実装する

ROWS BETWEENAVG + PRECEDING移動平均スライディングウィンドウ

前提知識 ▼

移動平均（Moving Average）は、直近N期分のデータの平均を計算する手法で、株価・売上・アクセス数などのトレンド把握に頻用されます。ウィンドウ関数では ROWS BETWEEN でフレームを指定することで実現します。

AVG(amount) OVER(
  ORDER BY sale_date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)  -- 直近3日（2行前〜現在行）の平均

移動平均と累計平均の違い：フレームが「スライド（滑走）」するため、データが増えても常に直近N件だけを参照します。先頭行から現在行までを平均する累計平均とは対象範囲が根本的に異なります。フレームの先頭が足りない序盤の行では、存在する行だけが対象です。

問題 ▼

以下の daily_sales テーブルから、各日の売上と「直近3日間（当日を含む）の売上移動平均（moving_avg_3）」を計算してください。この表は日付の欠損がない1日1行のデータです。日付昇順で出力し、3日未満のデータがある初期の行については、存在する行のみで平均を計算してください。

使用テーブル

▸ daily_sales

sale_date	amount
04-01	30
04-02	50
04-03	40
04-04	60
04-05	80

期待出力 ▼

sale_date	amount	moving_avg_3
04-01	30	30.00
04-02	50	40.00
04-03	40	40.00
04-04	60	50.00
04-05	80	60.00

模範解答コード ▼

SELECT
  sale_date,
  amount,
  AVG(amount) OVER(                           -- 2行前〜現在行（最大3行）の平均 = 3日移動平均
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3

FROM daily_sales
ORDER BY sale_date;

/*
  フレームの動き（スライディング）:
  04-01: フレーム = [04-01]             → AVG(30) = 30.00
  04-02: フレーム = [04-01, 04-02]      → AVG(30, 50) = 40.00
  04-03: フレーム = [04-01〜04-03]      → AVG(30, 50, 40) = 40.00
  04-04: フレーム = [04-02〜04-04]      → AVG(50, 40, 60) = 50.00  ← 04-01が外れる
  04-05: フレーム = [04-03〜04-05]      → AVG(40, 60, 80) = 60.00  ← 04-02が外れる

  ★ 「2 PRECEDING」は「2行前まで含める」という意味。
    常に最大3行（2行前 + 1行前 + 現在行）でフレームが構成される。
    先頭付近は存在する行のみで計算（自動で件数調整）。

  ★ N日移動平均 = ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW
*/

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

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

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM daily_sales5日間の売上データを読み込みます。これから「直近3日分を動的に参照するフレーム」を定義して移動平均を計算します。

1 / 4

sale_date	amount
04-01	30
04-02	50
04-03	40
04-04	60
04-05	80

全 5行読込

学習ポイント

N日移動平均の公式：ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW。7日移動平均なら 6 PRECEDING、30日なら 29 PRECEDING です。ユーザーアクティビティのダッシュボードで「直近7日間のDAU（日次アクティブユーザー）の平均」を計算する際の定番パターンです。

ROWS が数えるのは暦日ではなく行：この公式がN日移動平均になるのは、今回のように日付欠損のない1日1行のデータだからです。同日に複数行ある、または日付が欠けるデータでは、先に日次集計・日付補完するか、DB方言に応じた時間範囲フレームを使ってください。

移動平均 vs 累計平均：移動平均（ROWS BETWEEN 2 PRECEDING AND CURRENT ROW）は「直近3件のみ」を参照するため、古いデータの影響を排除してトレンドを追えます。一方、累計平均（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）は全期間を平均するため長期的な傾向を示しますが、直近の変動を反映しにくいです。

⊞ 累計平均 vs 移動平均（同じデータでの比較）

sale_date	amount	累計平均 (UNBOUNDED〜CURRENT)	3日移動平均 (2 PRECEDING〜CURRENT)
04-01	30	30.00	30.00
04-02	50	40.00	40.00
04-03	40	40.00	40.00
04-04	60	45.00 ← 全期間の影響で鈍い	50.00 ← 直近3日のみ反映
04-05	80	52.00 ← まだ初期値が足を引っ張る	60.00 ← 上昇トレンドを素早く反映

04-05 のような急上昇があった場合、移動平均 (60.00) は累計平均 (52.00) より素早く変化を捉えます。トレンド把握・異常検知では移動平均が有効です。

アンチパターン

移動平均に RANGE（デフォルト）を使うと同日データで誤った結果に：フレームを省略すると暗黙的に RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になります。同日のデータが複数行あると、その日の全行を「ピアグループ」としてまとめてしまい、移動平均ではなく累計平均になってしまいます。移動平均には必ず ROWS BETWEEN を明示しましょう。

QUESTION 9

WINDOW句 — 共通ウィンドウ定義でDRY（重複排除）なSQLを書く

WINDOW句DRY原則コード保守性PostgreSQL/MySQL8+/BigQuery

前提知識 ▼

同じ PARTITION BY ... ORDER BY ... を複数のウィンドウ関数で繰り返す場合、WINDOW句を使うと名前付きウィンドウを定義して使い回せます（DRY: Don't Repeat Yourself 原則）。

SELECT
  ROW_NUMBER() OVER(w) AS rank,   -- 同じウィンドウ w を参照
  SUM(sales)   OVER(w) AS running,
  LAG(sales)   OVER(w) AS prev
FROM t
WINDOW w AS (PARTITION BY emp_id ORDER BY month); -- 1箇所で定義

WINDOW句の利点：WINDOW句なしで同じクエリを書くと、同一のウィンドウ定義を関数の数だけコピー貼り付けすることになります。名前付きウィンドウなら定義を変更するときも1箇所を直すだけで済みます。WINDOW句は FROM・WHERE・GROUP BY の後、ORDER BY の前に書きます。

問題 ▼

以下の employee_sales テーブルから、各従業員・月ごとに以下の3指標を付与してください。WINDOW句を使って PARTITION BY emp_id, dept ORDER BY month の定義を1箇所にまとめてください。

rank：その社員のその月が「何ヶ月目か」（ROW_NUMBER）
running：その社員のその時点までの累計売上（SUM）
prev_sales：その社員の前月の売上（LAG）。初月は NULL

使用テーブル

▸ employee_sales

emp_id	dept	month	sales
E1	Sales	01	100
E1	Sales	02	120
E2	Sales	01	80
E2	Sales	02	90
E3	Tech	01	150

期待出力 ▼

emp_id	dept	month	sales	rank	running	prev_sales
E1	Sales	01	100	1	100	NULL
E1	Sales	02	120	2	220	100
E2	Sales	01	80	1	80	NULL
E2	Sales	02	90	2	170	80
E3	Tech	01	150	1	150	NULL

模範解答コード ▼

SELECT
  emp_id,
  dept,
  month,
  sales,
  ROW_NUMBER() OVER w AS rank,       -- WINDOW句で定義した w を OVER w で参照する
  SUM(sales)   OVER w AS running,
  LAG(sales)   OVER w AS prev_sales

FROM employee_sales
WINDOW w AS (PARTITION BY emp_id, dept ORDER BY month)  -- 共通ウィンドウ w を1箇所で定義（変更時もここだけ修正）

ORDER BY dept, emp_id, month;

/*
  実行順序:
  1. FROM employee_sales (全5行)
  2. WINDOW句で名前付きウィンドウ w を定義
  3. SELECT の各関数が OVER w を参照して同じウィンドウで評価される
  4. ORDER BY dept, emp_id, month で最終ソート出力
  */

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

SELECT
  emp_id, dept, month, sales,
  ROW_NUMBER() OVER w AS rank,
  SUM(sales) OVER w AS running,
  LAG(sales) OVER w AS prev_sales
FROM employee_sales
WINDOW w AS (PARTITION BY emp_id, dept ORDER BY month)
ORDER BY dept, emp_id, month;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM employee_sales5行のデータを読み込みます。E1・E2は Sales 部門、E3は Tech 部門です。この後 WINDOW句の定義に従いパーティションが作成されます。

1 / 4

emp_id	dept	month	sales
E1	Sales	01	100
E1	Sales	02	120
E2	Sales	01	80
E2	Sales	02	90
E3	Tech	01	150

全 5行読込

学習ポイント

保守コストの削減：例えば「PARTITION BY emp_id, dept」を「PARTITION BY emp_id」に変更する場合、WINDOW句なしだと3箇所（またはそれ以上）を修正する必要があります。WINDOW句があれば1箇所の変更で完了し、修正漏れによるバグを防ぎます。実務のデータマートクエリでは10個以上のウィンドウ関数が並ぶこともあり、この差は非常に大きいです。

WINDOW句の拡張：名前付きウィンドウを基底として、追加の ORDER BY や FRAME を加えることができます。例：WINDOW base AS (PARTITION BY emp_id) と定義しておき、OVER (base ORDER BY month) のように拡張して使えます（PostgreSQL など一部のDB対応）。

◆ WINDOW句あり・なしのコード比較（同じ結果、異なる保守性）

✗ WINDOW句なし（3回コピー）

ROW_NUMBER() OVER(
  PARTITION BY emp_id, dept
  ORDER BY month) AS rank,
SUM(sales) OVER(
  PARTITION BY emp_id, dept  -- ← 重複
  ORDER BY month) AS running,
LAG(sales) OVER(
  PARTITION BY emp_id, dept  -- ← 重複
  ORDER BY month) AS prev_sales

✓ WINDOW句あり（1箇所定義）

ROW_NUMBER() OVER w AS rank,
SUM(sales)   OVER w AS running,
LAG(sales)   OVER w AS prev_sales
...
WINDOW w AS (
  PARTITION BY emp_id, dept  -- ← 1箇所のみ
  ORDER BY month
)

アンチパターン

DB依存に注意：WINDOW句は PostgreSQL、MySQL 8.0+、BigQuery、DuckDB で対応していますが、SQL Server や Oracle では標準的な WINDOW句が使えません（それぞれ独自の構文があります）。チームのDB環境を確認してから使用してください。移植性が必要なコードでは、WINDOW句なしで書く方が安全な場合もあります。

QUESTION 10

応用総まとめ — CTE + LAG + SUM でセッション分析を実装する

CTELAG + CASE WHENセッション分析実務最頻出パターン

前提知識 ▼

セッション分析は、ユーザーの行動ログから「1回のセッション（連続したアクション）」を識別するプロダクト分析の基本技術です。「前のイベントから30分を超えて経過したら新しいセッション開始」というルールを LAG + CASE WHEN で検出し、SUM の累積でセッション番号を振ります。差がちょうど30分なら同じセッションです。

WITH flagged AS (
  SELECT ...,
    CASE WHEN LAG(ts_col) OVER(...) IS NULL
            OR (ts_col - LAG(ts_col) OVER(...)) > gap_limit
         THEN 1 ELSE 0 END AS is_new_session
  FROM table_name
)
SELECT ...,
  SUM(is_new_session) OVER(PARTITION BY key_col ORDER BY ts_col) AS session_id
FROM flagged;

event_time の単位：event_time は「深夜0時からの経過分数」を表す整数です（例：600 = 10:00、660 = 11:00）。したがって時間差は単純な引き算で求まります。実際のDBでは DATEDIFF や EXTRACT(EPOCH FROM ...) 等で時間差を計算します。

問題 ▼

以下の user_events テーブルから、ユーザーごとに「前のイベントから30分（event_time の差が30）を超えたら新しいセッション」というルールでセッション番号（session_id）を付与してください。各ユーザーの最初のイベントはセッション1から始めます。

使用テーブル（event_time は深夜0時からの経過分：600=10:00、605=10:05 など）

▸ user_events

user_id	event_time	event_type
U1	600	login
U1	605	click
U1	645	click
U1	650	logout
U2	660	login
U2	670	click

期待出力 ▼

user_id	event_time	event_type	session_id
U1	600	login	1
U1	605	click	1
U1	645	click	2
U1	650	logout	2
U2	660	login	1
U2	670	click	1

模範解答コード ▼

WITH gap_flagged AS (
  SELECT
    user_id,
    event_time,
    event_type,
    CASE                                                                                       -- 前イベントとの差が30分超、または先頭行(NULL) → 新セッション開始 = 1
      WHEN LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) IS NULL
        OR (event_time - LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time)) > 30
      THEN 1                                                                                   -- セッション開始フラグ
      ELSE 0                                                                                   -- 同一セッション内
    END AS is_new_session

  FROM user_events
)

SELECT
  user_id,
  event_time,
  event_type,
  SUM(is_new_session) OVER(  -- フラグの累積＝セッション番号（1,0,1,0…→1,1,2,2…）
    PARTITION BY user_id
    ORDER BY event_time
  ) AS session_id

FROM gap_flagged
ORDER BY user_id, event_time;

/*
  実行順序:
  1. CTE gap_flagged
  2. メインクエリ
  3. ORDER BY user_id, event_time で出力
  */

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

▼ ◇ STEP 1: CTE gap_flagged — LAGでギャップを検出し、セッション開始フラグを付与

WITH gap_flagged AS (
  SELECT
    user_id, event_time, event_type,
    CASE
      WHEN LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) IS NULL
      OR (event_time - LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time)) > 30
      THEN 1 ELSE 0 END AS is_new_session
  FROM user_events
)

LEGEND ▼

データ取得・読込対象

① FROM user_events ▼

FROM user_events（U1・U2 のイベントが混在）6行のイベントログを読み込みます。U1（4行）とU2（2行）が混在しています。U1の 600→605 は5分差（同一セッション）、605→645 は40分差（新セッション）というルールを次のステップで識別します。

1 / 3

user_id	event_time	event_type
U1	600	login
U1	605	click
U1	645	click
U1	650	logout
U2	660	login
U2	670	click

全 6行読込（U1: 4行、U2: 2行）

▼ ◇ STEP 2: メインクエリ — is_new_session の累積合計でセッションIDを確定

SELECT
  user_id, event_time, event_type,
  SUM(is_new_session) OVER(
    PARTITION BY user_id
    ORDER BY event_time
  ) AS session_id
FROM gap_flagged
ORDER BY user_id, event_time;

LEGEND ▼

データ取得・読込対象

① FROM gap_flagged（CTEの結果） ▼

CTE の結果（is_new_session フラグ付き）を読み込むCTE gap_flagged の結果を使います。is_new_session フラグ [1,0,1,0] が U1の行に付与されています。これを累積することでセッション番号が生まれます。

1 / 3

user_id	event_time	event_type	is_new_session
U1	600	login	1
U1	605	click	0
U1	645	click	1
U1	650	logout	0
U2	660	login	1
U2	670	click	0

全 6行読込（is_new_session フラグ付き）

学習ポイント

「セッション化」パターンの汎用性：このパターンは Web のページビュー解析、アプリのセッション分析、コールセンターの通話ログ、工場の機械稼働ログなど、「時系列イベントを意味のある塊に分割する」あらゆる場面で使えます。セッション判定のしきい値（30分）をパラメータ化することで、ビジネス要件に合わせて柔軟に調整できます。

「フラグを立てて累積」というパターンを覚える：このクエリの本質は「①フラグ（0/1）を付与して ②そのフラグを SUM で累積する」という2段構えです。セッション以外にも「イベントの種類が変わった箇所でグループ番号を変えたい」「特定の条件が現れるたびにカウンタを増やしたい」など、応用が広い万能パターンです。

セッション分析の先の分析：session_id が付与されたデータから、さらに GROUP BY user_id, session_id するだけで「1セッションあたりの平均イベント数」「セッション継続時間（MAX - MIN(event_time)）」「セッションごとのコンバージョン率」といった高度な指標が簡単に計算できます。

⊞ セッション分析後に可能になる次の集計（GROUP BY のみで実現）

分析テーマ	必要な追加クエリ（session_id が付与済みの状態から）
1セッションあたりの平均イベント数	`COUNT(*) / COUNT(DISTINCT session_id) GROUP BY user_id`
セッションの継続時間（分）	`MAX(event_time) - MIN(event_time) GROUP BY user_id, session_id`
セッション数が多いユーザーTop10	`COUNT(DISTINCT session_id) GROUP BY user_id ORDER BY COUNT DESC LIMIT 10`
ログアウトで終わったセッション率	`ROW_NUMBER() OVER(PARTITION BY user_id, session_id ORDER BY event_time DESC)` で各セッションの最後の行を特定し、`event_type = 'logout'` を判定

アンチパターン

素朴な自己結合で前イベントを探さない：CTEとウィンドウ関数を使わず、各イベントをそれ以前の全イベントと突き合わせる自己結合は、比較が O(N²) まで増える可能性があります。LAG は行同士の総当たりを避けられます。なお、実際の全体コストには PARTITION BY user_id ORDER BY event_time のソートも含まれ、索引・クラスタリングやDBの実行計画に左右されます。

PARTITION BY の指定を忘れると全ユーザーのイベントが混在：SUM(is_new_session) OVER(ORDER BY event_time) と書くと、U1のセッション境界がU2のイベントにも影響してしまいます。必ず PARTITION BY user_id を指定して、ユーザーごとに独立したカウンタを維持しましょう。