SQL ウィンドウ関数 — MAX/MIN OVER・FIRST

QUESTION 1

MAX() / MIN() OVER() — グループ内の最大値・最小値との比較

MAX/MINPARTITION BY基準値比較基本

前提知識 ▼

「行を潰さずに全体の集計値を各行に付与する」という OVER() の特性は、MAX() や MIN() と組み合わせることで真価を発揮します。これを利用すると、「部署ごとの最高給与」などを各行の横に付与し、自身の値との差額を1つのクエリで簡単に計算できます。

SELECT
  key_col, group_col, num_col,
  MAX(num_col) OVER(PARTITION BY group_col) AS group_max,
  MIN(num_col) OVER(PARTITION BY group_col) AS group_min
FROM table_name;

集計範囲の決まり方：OVER() なら結果セット全体、OVER(PARTITION BY group_col) ならその列の値ごとに最大値・最小値を計算します。どちらも元の行を集約せず、計算結果を各行の横に付与します。

問題 ▼

以下の employee_salaries テーブルから、各従業員の「名前」「部署」「給与」と、「同じ部署の最高給与（dept_max）」、および「最高給与との差額（diff_from_max）」を取得してください。

※ 差額は 最高給与 - 自身の給与 で計算すること。

使用テーブル

▸ employee_salaries

emp_name	dept	salary
Aさん	営業	300000
Bさん	営業	250000
Cさん	開発	400000
Dさん	開発	380000

期待出力 ▼

emp_name	dept	salary	dept_max	diff_from_max
Aさん	営業	300000	300000	0
Bさん	営業	250000	300000	50000
Cさん	開発	400000	400000	0
Dさん	開発	380000	400000	20000

模範解答コード ▼

SELECT
  emp_name,
  dept,
  salary,
  MAX(salary) OVER(PARTITION BY dept) AS dept_max,  -- 部署ごとの最高給与
  MAX(salary) OVER(PARTITION BY dept) - salary AS diff_from_max
FROM employee_salaries                                           -- 取得した最高給与から自身の給与を引き、差額を計算
ORDER BY dept, diff_from_max;

/*
  実行順序:
  1. FROM employee_salaries  → 4行取得
  2. PARTITION BY dept       → 部署で2グループに分割
  3. MAX(salary)             → 各グループの最大を全行に付与
  4. SELECT 出力               → 差額を計算
  */

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

SELECT
  emp_name, dept, salary,
  MAX(salary) OVER(PARTITION BY dept) AS dept_max,
  MAX(salary) OVER(PARTITION BY dept) - salary AS diff_from_max
FROM employee_salaries
ORDER BY dept, diff_from_max;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM employee_salariesテーブル全体を読み込みます。4名のデータがあり、部署は「営業」と「開発」の2種類です。

1 / 4

emp_name	dept	salary
Aさん	営業	300000
Bさん	営業	250000
Cさん	開発	400000
Dさん	開発	380000

全 4行読込

学習ポイント

相対的な比較が容易に：「一番高い人と比べて自分はどれくらい差があるか」「全体の平均からどれくらい離れているか」といった相対評価は、ウィンドウ関数の得意領域です。GROUP BY と自己結合を組み合わせる従来の方法に比べ、クエリを簡潔に書けます。実行性能はデータ量・索引・実行計画に依存するため、実環境で確認します。

従来の書き方と比較：ウィンドウ関数なしで同じ結果を得るには、サブクエリで部署ごとの最大値を求め JOIN する必要がありました。ウィンドウ関数を使うことでこれが1クエリに収まります。

⇄ 従来の書き方 vs ウィンドウ関数

✖ 従来：サブクエリ + JOIN（2段階で記述）

SELECT e.emp_name, e.dept, e.salary,
       m.dept_max,
       m.dept_max - e.salary AS diff_from_max
FROM employee_salaries e
JOIN (
  SELECT dept, MAX(salary) AS dept_max
  FROM employee_salaries
  GROUP BY dept
) m ON e.dept = m.dept
ORDER BY dept, diff_from_max;

部署別集計のサブクエリと JOIN が必要

✓ ウィンドウ関数：1つの SELECT で記述

SELECT
  emp_name, dept, salary,
  MAX(salary) OVER(PARTITION BY dept)
    AS dept_max,
  MAX(salary) OVER(PARTITION BY dept)
    - salary AS diff_from_max
FROM employee_salaries
ORDER BY dept, diff_from_max;

集計値の付与と差額計算を簡潔に表現できる（実際の走査・ソートは実行計画に依存）

QUESTION 2

COUNT() OVER() — 条件付き集計とデータ件数の付与

COUNTPARTITION BY件数付与

前提知識 ▼

COUNT() もウィンドウ関数として頻繁に使用されます。「このユーザーの全注文回数」や「全注文金額」といったグループ内のデータ件数・合計を、行を潰さずに各明細行に持たせることができます。

SELECT
  key_col, group_col,
  COUNT(*) OVER(PARTITION BY group_col) AS group_row_count
FROM table_name;

COUNT(*) と COUNT(列) の違い：COUNT(*) はパーティション内の行数をそのまま数え、COUNT(列) はその列が NULL でない行だけを数えます。どちらも行を潰さず、同じパーティションに属する全行へ同じ値が付きます。

問題 ▼

以下の orders テーブルから、「注文ID」「ユーザーID」「金額」と、「そのユーザーの合計注文回数(order_count)」、「そのユーザーの合計注文金額(total_amount)」を出力してください。

使用テーブル

▸ orders

order_id	user_id	amount
1	U1	1000
2	U1	2000
3	U2	1500
4	U3	3000
5	U1	500

期待出力 ▼

order_id	user_id	amount	order_count	total_amount
1	U1	1000	3	3500
2	U1	2000	3	3500
5	U1	500	3	3500
3	U2	1500	1	1500
4	U3	3000	1	3000

模範解答コード ▼

SELECT
  order_id,
  user_id,
  amount,
  COUNT(*) OVER(PARTITION BY user_id) AS order_count,  -- ユーザーごとの注文回数
  SUM(amount) OVER(PARTITION BY user_id) AS total_amount
FROM orders                                               -- ユーザーごとの金額の合計を算出
ORDER BY user_id, order_id;

/*
  実行順序:
  1. FROM orders             → 5行取得
  2. PARTITION BY user_id    → ユーザーで分割
  3. COUNT(*) と SUM(amount)  → 同時に集計し付与
  4. SELECT 出力               → user_id, order_id でソート
  */

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

SELECT
  order_id, user_id, amount,
  COUNT(*) OVER(PARTITION BY user_id) AS order_count,
  SUM(amount) OVER(PARTITION BY user_id) AS total_amount
FROM orders
ORDER BY user_id, order_id;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM ordersテーブルを読み込みます。5行が格納されており、U1の注文が3件、U2が1件、U3が1件あります。この時点ではまだグループ分けされていません。

1 / 4

order_id	user_id	amount
1	U1	1000
2	U1	2000
3	U2	1500
4	U3	3000
5	U1	500

全 5行読込（挿入順）

学習ポイント

フラグ立て・異常検知への応用：「注文回数が2回以上のユーザーだけを抽出したい」といった場合、このクエリをサブクエリ（またはCTE）で囲み WHERE order_count >= 2 とすることで、詳細な注文履歴を残したまま対象ユーザーだけを抽出できます。

COUNT(列名) と COUNT(*) の違い：ウィンドウ関数でも通常の集約関数と同様に、COUNT(列名) はその列が NULL でない行だけをカウントし、COUNT(*) は NULL を含めた全ての行数をカウントします。

❖ ウィンドウ関数の本質：「行を潰す」か「行を保つ」か

✖ GROUP BY（行が3行に集約される）

user_id	order_count	total_amount
U1	3	3500
U2	1	1500
U3	1	3000

order_id・amount の明細情報が失われる

✓ ウィンドウ関数（全5行を保持したまま集計値を付与）

order_id	user_id	amount	order_count	total_amount
1	U1	1000	3	3500
2	U1	2000	3	3500
5	U1	500	3	3500
3	U2	1500	1	1500
4	U3	3000	1	3000

明細を残したまま集計値を各行に横付けできる

QUESTION 3

FIRST_VALUE() — グループ内の「最初の値」を取得する

FIRST_VALUEORDER BY初回アクション抽出

前提知識 ▼

FIRST_VALUE(列名) は、指定した順序で並べたときの「一番最初の行の値」を取得する関数です。顧客の「初回購入日」や「最初に閲覧したページ」などを全履歴に付与したい場合に非常に便利です。

SELECT
  user_id, event_date,
  FIRST_VALUE(event_name) OVER(
    PARTITION BY user_id
    ORDER BY event_date ASC
  ) AS first_event
FROM events;

並び順が返る値を決める：FIRST_VALUE が返すのはフレームの先頭行の値なので、ORDER BY の向きを変えると返る値も変わります。ORDER BY を省略するとパーティション内の順序が保証されず、結果が不定になります。

問題 ▼

以下の user_events テーブルから、各イベントの履歴に対して、「そのユーザーが一番最初に実行したイベント（first_event）」を付与してください。

使用テーブル

▸ user_events

user_id	event_date	event_name
U1	04-01	登録
U1	04-02	閲覧
U1	04-05	購入
U2	04-03	登録

期待出力 ▼

user_id	event_date	event_name	first_event
U1	04-01	登録	登録
U1	04-02	閲覧	登録
U1	04-05	購入	登録
U2	04-03	登録	登録

模範解答コード ▼

SELECT
  user_id,
  event_date,
  event_name,
  FIRST_VALUE(event_name) OVER(
    PARTITION BY user_id
    ORDER BY event_date ASC
  ) AS first_event
FROM user_events                 -- ユーザーごとに日付順に並べ、そのグループの一番最初の event_name を取得する
ORDER BY user_id, event_date;

/*
  実行順序:
  1. FROM user_events
  2. PARTITION BY user_id でグループ分割
  3. ORDER BY event_date ASC で各グループ内を日付昇順にソート
  4. FIRST_VALUE() により、U1グループの先頭行（04-01）の「登録」が U1 の全行に付与
  5. SELECT 出力
*/

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

SELECT
  user_id, event_date, event_name,
  FIRST_VALUE(event_name) OVER(
    PARTITION BY user_id
    ORDER BY event_date ASC
  ) AS first_event
FROM user_events
ORDER BY user_id, event_date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM user_eventsテーブルを読み込みます。U1 のイベントが3件、U2 が1件あります。

1 / 4

user_id	event_date	event_name
U1	04-01	登録
U1	04-02	閲覧
U1	04-05	購入
U2	04-03	登録

全 4行読込

学習ポイント

MIN() との違い：「最初のイベント名」を取る際、MIN(event_name) が返すのは照合順序で最小の文字列であり、日付順の「最初」とは限りません。時間軸に沿った最初の値を取るには FIRST_VALUE() と明示的な ORDER BY を使います。同日時の行があり得る場合は、一意になるタイブレーク列も追加します。

対となる LAST_VALUE()：最後の値を取得する LAST_VALUE() もあります。ただし ORDER BY を指定したウィンドウの既定フレームは一般に先頭から現在行（同順位行を含む）までなので、そのままではパーティション全体の最終値になりません。ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を明示するか、ORDER BY ... DESC と FIRST_VALUE() を組み合わせます。

MIN() は「時系列の最初」ではなく「照合順序の最小」を返す

✖ MIN(event_name) — 照合順序の最小を返す

event_date	event_name	MIN の結果（例）
04-01	登録	購入
04-02	閲覧	購入
04-05	購入	購入

この例では照合順序が「購入」を最小とする場合を表示。結果は照合順序に依存し、日付順とは無関係

✓ FIRST_VALUE() ORDER BY event_date — 日付順の先頭を返す

event_date	event_name	FIRST_VALUE の結果
04-01	登録	登録
04-02	閲覧	登録
04-05	購入	登録

ORDER BY event_date ASC で「時系列的に最初のイベント」を正確に取得

QUESTION 4

LEAD() — 「次回のアクセス」を横に並べる

LEADPARTITION BY次回予測・間隔

前提知識 ▼

前の行を取得する LAG() に対し、LEAD() は指定した並び順における「次の行」の値を取得します。ユーザーの次回訪問日などを現在の行に持ってくることで、再訪間隔の計算に利用できます。

LEAD(visit_date) OVER(
  PARTITION BY user_id
  ORDER BY visit_date ASC
) AS next_visit

参照範囲と順序：PARTITION BY user_id により別ユーザーの行は参照しません。どの行が「次」になるかは ORDER BY が決め、パーティションの最後の行では既定で NULL を返します。

問題 ▼

以下の website_visits テーブルから、各ユーザーの訪問日（visit_date）と、「そのユーザーの次回の訪問日（next_visit）」を取得してください。

※ 次回の訪問がない（最後の行）場合は NULL で構いません。

使用テーブル

▸ website_visits

user_id	visit_date
U1	04-01
U1	04-05
U1	04-10
U2	04-02

期待出力 ▼

user_id	visit_date	next_visit
U1	04-01	04-05
U1	04-05	04-10
U1	04-10	NULL
U2	04-02	NULL

模範解答コード ▼

SELECT
  user_id,
  visit_date,
  LEAD(visit_date) OVER(
    PARTITION BY user_id
    ORDER BY visit_date ASC
  ) AS next_visit
FROM website_visits            -- ユーザーごとに日付順に並べ、「1つ次の行の visit_date」を取得する
ORDER BY user_id, visit_date;

/*
  実行順序:
  1. FROM website_visits
  2. PARTITION BY user_id でグループ分割
  3. ORDER BY visit_date ASC で各グループ内を日付昇順にソート
  4. LEAD() が各行から「1つ下の行の visit_date」を取得
  5. SELECT 出力
  */

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

SELECT
  user_id, visit_date,
  LEAD(visit_date) OVER(
    PARTITION BY user_id
    ORDER BY visit_date ASC
  ) AS next_visit
FROM website_visits
ORDER BY user_id, visit_date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM website_visitsテーブルを読み込みます。U1 の訪問が3件、U2 が1件あります。

1 / 4

user_id	visit_date
U1	04-01
U1	04-05
U1	04-10
U2	04-02

全 4行読込

学習ポイント

実務での活用例（日付差）：次回日と現在日の差を取れば再訪間隔を分析できます。式はDBごとに異なり、MySQL は DATEDIFF(next_visit, visit_date)、PostgreSQL は日付同士の減算、BigQuery は DATE_DIFF(next_visit, visit_date, DAY) を使います。まずサブクエリやCTEで next_visit を計算すると移植しやすくなります。

NULL が意味を持つ：next_visit が NULL の行は「観測期間内で、そのユーザーの次の訪問がない最終行」を意味します。離脱候補の抽出には、この結果をサブクエリまたはCTEで囲み、外側で WHERE next_visit IS NULL と絞り込みます。ただし全ユーザーの最終観測行が該当するため、離脱確定には観測終了日からの経過日数など追加条件が必要です。

❖ 日付差 + LEAD で「次回訪問までの日数」を導出する

user_id	visit_date	next_visit (LEAD)	days_to_next DB固有の日付差式
U1	04-01	04-05	4日後に再訪
U1	04-05	04-10	5日後に再訪
U1	04-10	NULL	NULL（観測期間内の最終訪問）
U2	04-02	NULL	NULL（観測期間内の最終訪問）

外側のクエリで WHERE days_to_next IS NULL と絞り込むと、各ユーザーの最終観測行を抽出できます。NULL は「観測期間内に次の訪問がない」という意味を持ちますが、それだけで離脱とは断定しません。

QUESTION 5

LAG() の引数 — NULLを防ぐ「デフォルト値」の指定

LAGデフォルト値NULL回避

前提知識 ▼

LAG() や LEAD() は、対象の行が存在しない場合デフォルトで NULL を返します。しかし、計算（引き算など）を行う際に NULL が混じると結果も NULL になってしまいます。
これを防ぐため、関数に第3引数（デフォルト値）を指定することができます。

-- LAG(列名, ずらす行数, デフォルト値)
LAG(amount, 1, 0) OVER(ORDER BY date)

第2・第3引数の役割：第2引数は「何行ずらすか」で、省略時は1行です。第3引数のデフォルト値は、対象列と比較できる型でなければなりません。省略すると NULL が返り、NULL を含む計算の結果も NULL になります。

問題 ▼

以下の daily_metrics テーブルから、日付ごとの「ユーザー数(users)」と、「日付順で1行前のユーザー数(prev_users)」（この例では前日）を取得してください。
ただし、前日のデータが存在しない最初の行については、NULLではなく 0 を返すように指定してください。

使用テーブル

▸ daily_metrics

date	users
04-01	100
04-02	120
04-03	150

期待出力 ▼

date	users	prev_users
04-01	100	0
04-02	120	100
04-03	150	120

模範解答コード ▼

SELECT
  date,
  users,
  LAG(users, 1, 0) OVER(ORDER BY date) AS prev_users  -- 1行前の users（無ければ 0）
FROM daily_metrics
ORDER BY date;

/*
  実行順序:
  1. FROM daily_metrics  → 行取得
  2. ORDER BY date       → 日付昇順で行順を確定
  3. LAG(users, 1, 0)    → 前行の値を付与（先頭は0）
  4. SELECT 出力           → 列を出力
  */

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

SELECT
  date, users,
  LAG(users, 1, 0) OVER(ORDER BY date) AS prev_users
FROM daily_metrics
ORDER BY date;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM daily_metricsテーブルを読み込みます。3日分のデータがあります。LAG() が「前の行」を参照するには行の順序が重要です。次のステップで ORDER BY によって順序を確定させます。

1 / 4

date	users
04-01	100
04-02	120
04-03	150

全 3行読込

学習ポイント

COALESCE() との使い分け：LAGの第3引数は、指定したオフセット先の行が存在しないときだけ使われます。前行は存在するものの users 自体が NULL なら、その NULL を返します。一方 COALESCE(LAG(users) OVER(...), 0) は両方の NULL を 0 にするため、欠損値と行不存在を区別したい場合は同じ意味ではありません。

複数行ずらす：第2引数を変更して LAG(users, 7) とすれば「7行前のデータ」を取得できます。1日1行が欠損なく並ぶことが保証される場合に限り、7行前を1週間前として扱えます。日付欠損や複数行があり得るデータでは、日付条件による結合などを使います。

⇄ デフォルト値なし (NULL) vs デフォルト値あり (0) の結果比較

✖ LAG(users) — デフォルト値なし

date	users	prev_users	差分 (users - prev)
04-01	100	NULL	NULL ← 計算不能
04-02	120	100	+20
04-03	150	120	+30

先頭行の差分が NULL になり、集計から除外されてしまう

✓ LAG(users, 1, 0) — デフォルト値 0

date	users	prev_users	差分 (users - prev)
04-01	100	0	+100 ← 計算できる
04-02	120	100	+20
04-03	150	120	+30

初日も 0 比較として扱え、全行で差分計算が正常に機能する