ウィンドウ関数

Q: 質問：ウィンドウ関数はWHERE句で使用できますか？

回答： いいえ。WHERE はウィンドウ関数より前に実行されるため、ウィンドウ関数はまだ計算されていません。まずサブクエリやCTEでウィンドウ関数を計算し、外側のクエリで WHERE を使ってフィルタリングする必要があります。

Q: 質問：ROW_NUMBERとRANKはいつ使うべきですか？

回答： 「一意の番号付け、同順位なし」が必要な場合（例：ページネーション、重複除去）は ROW_NUMBER を、「値によるランキング、同順位あり」が必要な場合（例：成績ランク、売上ランキング）は RANK を使用してください。

Q: 質問：PARTITION BYとGROUP BYの違いは何ですか？

回答： GROUP BY は複数の行を1行にまとめ（行数を減らし）、PARTITION BY はウィンドウの範囲を定義するだけで行は減りません（各行が保持されます）。両方を組み合わせて使用することもできます：まず GROUP BY で集計し、次にウィンドウ関数でランキングを追加します。

Q: 質問：ウィンドウ関数のパフォーマンスはどのくらいですか？

回答： ウィンドウ関数は通常、自己結合サブクエリより優れたパフォーマンスを発揮します。大規模データセットでは、PARTITION BY や ORDER BY で使用するカラムにインデックスを作成してください。

🌍 実世界のアナロジー

クラスの成績表を想像してください：

GROUP BY — クラス全体を性別で分け、男子と女子の平均点を計算し、結果は2行だけ
ウィンドウ関数 — 各生徒の成績表に「男子の中での順位」と「男子平均より何点高いか」を書き加える。各人の情報はそのまま残る

ウィンドウ関数は「拡大鏡」のようなものです。行数を減らすことなく、各行に集計統計を表示できます。

🎯 コア概念

ウィンドウ関数とは

ウィンドウ関数は、データの「ウィンドウ」に対して集計やランキングの計算を行います。行数を減らさないのが特徴です。各行は自分の詳細情報だけでなく、グループ内のサマリー情報も確認できます。

OVER句

OVER() はウィンドウ関数の核心です。「ウィンドウ」の範囲を定義します：

SQL

function() OVER (
    [PARTITION BY column]    -- どのフィールドでパーティション（グループ）分割するか
    [ORDER BY column]        -- パーティション内でどうソートするか
    [ROWS/RANGE frame]       -- オプション：行範囲をさらに制限
)

ROW_NUMBER / RANK / DENSE_RANK — ランキング関数

SQL

-- ROW_NUMBER：厳密に連続する番号、同順位なし（1, 2, 3, 4）
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- RANK：同順位を許可、飛び番あり（1, 2, 2, 4）
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- DENSE_RANK：同順位を許可、飛び番なし（1, 2, 2, 3）
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

関数	同順位	飛び番	結果例
ROW_NUMBER	なし	—	1, 2, 3, 4
RANK	あり	あり	1, 2, 2, 4
DENSE_RANK	あり	なし	1, 2, 2, 3

NTILE — バケット分割

データをN個のグループに均等に分割します：

SQL

-- 給与で従業員を4段階に分割（四分位）
NTILE(4) OVER (ORDER BY salary DESC)

LAG / LEAD — 隣接行へのアクセス

SQL

-- LAG：前の行にアクセス
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

-- LEAD：次の行にアクセス
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

関数	方向	ユースケース
LAG(col, n)	n行前を参照	前年比・前期比の計算、前レコードとの比較
LEAD(col, n)	n行後を参照	次のレコードの予測

SUM / AVG / COUNT OVER — 集計ウィンドウ関数

SQL

-- 各行に部門の給与合計を表示
SUM(salary) OVER (PARTITION BY department_id)

-- 各行に部門の平均給与を表示
AVG(salary) OVER (PARTITION BY department_id)

-- 各行に部門の人数を表示
COUNT(*) OVER (PARTITION BY department_id)

ウィンドウのPARTITION BYとORDER BY

SQL

-- PARTITION BY：ウィンドウの範囲を定義（GROUP BYに似ているが、行をまとめない）
-- ORDER BY：ウィンドウ内でソート、ランキングや累積計算に影響

-- 例：部門ごとにグループ化し、各部門内で給与降順でランク付け
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- 例：累積合計（入社日でソートし、給与を1行ずつ累積）
SUM(salary) OVER (ORDER BY hire_date)

ウィンドウ関数とGROUP BYの比較

特徴	GROUP BY	ウィンドウ関数
行数	減少（1行にまとまる）	変更なし（全行が保持される）
詳細データ	失われる	保持される
ユースケース	サマリー統計	ランキング、比較、累積計算
詳細とサマリーを同時に表示	❌ いいえ	✅ はい

💡 ヒント： 集計後に詳細を残したい場合はウィンドウ関数を使用し、サマリー結果だけが必要な場合は GROUP BY を使用してください。

📝 基本構文

SQL

-- ウィンドウ関数の一般的構文
function() OVER (
    [PARTITION BY col1, col2]
    [ORDER BY col3 [ASC|DESC]]
    [ROWS BETWEEN ... AND ...]
)

-- ランキング
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)
RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC)
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- 隣接行
LAG(column, offset, default)  OVER (ORDER BY column)
LEAD(column, offset, default) OVER (ORDER BY column)

-- 集計
SUM(column) OVER (PARTITION BY col)
AVG(column) OVER (PARTITION BY col)
COUNT(*)    OVER (PARTITION BY col)

-- バケット分割
NTILE(n) OVER (ORDER BY column)

💡 ヒント：

PARTITION BY は省略可能で、省略した場合ウィンドウは結果セット全体に及ぶ

ORDER BY はランキング関数では必須、集計関数ではオプション

ウィンドウ関数は WHERE や HAVING では使用できない。サブクエリやCTEでラップする必要がある

📌 例

例：部門内給与ランキング（難易度⭐）

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dense_rank
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

▶ 試してみよう

結果：

TEXT

name   | department | salary   | row_num | rank | dense_rank
-------+------------+----------+---------+------+-----------
Zhang Wei | Tech    | 12000.00 |       1 |    1 |         1
Li Na     | Tech    |  9500.00 |       2 |    2 |         2
Wang Qiang| Sales   |  8000.00 |       1 |    1 |         1
Zhao Min  | Sales   | 11000.00 |       2 |    2 |         2
Liu Yang  | Marketing| 7500.00 |       1 |    1 |         1
Chen Jing | NULL    |  9000.00 |       1 |    1 |         1

説明：3つのランキング関数の違いは、同値がある場合に最も顕著に現れます。ROW_NUMBER は厳密な連続番号を割り当て、RANK は同順位後に飛び番を許可し、DENSE_RANK は飛び番なしです。

例：部門平均給与との差を計算（難易度⭐）

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS dept_avg,
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS diff_from_avg
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

▶ 試してみよう

結果：

TEXT

name   | department | salary   | dept_avg | diff_from_avg
-------+------------+----------+----------+--------------
Zhang Wei | Tech    | 12000.00 | 10750.00 |      1250.00
Li Na     | Tech    |  9500.00 | 10750.00 |     -1250.00
Zhao Min  | Sales   | 11000.00 |  9500.00 |      1500.00
Wang Qiang| Sales   |  8000.00 |  9500.00 |     -1500.00
Liu Yang  | Marketing|  7500.00|  7500.00 |         0.00
Chen Jing | NULL    |  9000.00 |  9000.00 |         0.00

説明：AVG() OVER() は行をまとめずに各行に部門平均を計算するため、各従業員は自分の給与と平均の差を直接確認できます。

例：LAGによる注文の前年比成長率（難易度⭐⭐）

SQL

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) AS prev_order_amount,
    ROUND(
        (o.total_amount - LAG(o.total_amount, 1) OVER (ORDER BY o.order_date)) 
        / LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) * 100, 
    2) AS growth_pct
FROM orders o
WHERE o.status != 'cancelled'
ORDER BY o.order_date;

▶ 試してみよう

結果：

TEXT

order_id | order_date | total_amount | prev_order_amount | growth_pct
---------+------------+--------------+-------------------+-----------
    1001 | 2026-01-10 |      2500.00 |              NULL |       NULL
    1002 | 2026-02-15 |      1800.00 |           2500.00 |     -28.00
    1003 | 2026-02-20 |      3200.00 |           1800.00 |      77.78
    1004 | 2026-03-05 |       950.00 |           3200.00 |     -70.31
    1005 | 2026-03-10 |      4100.00 |            950.00 |     331.58
    ...

説明：LAG() は「前の行」の値にアクセスするため、前期比や前年比などのトレンド分析に最適です。最初の行には前の行がないため、NULL が返されます。

🎬 シナリオ演習

シナリオ1：各部門のトップ営業担当者を見つける

各部門で売上最高的な従業員を見つけます。

SQL

WITH emp_sales AS (
    SELECT 
        CONCAT(e.first_name, e.last_name) AS name,
        d.department_name AS department,
        COALESCE(SUM(o.total_amount), 0) AS total_sales,
        RANK() OVER (PARTITION BY d.department_id ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS dept_rank
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_id, d.department_name
)
SELECT name, department, total_sales, dept_rank
FROM emp_sales
WHERE dept_rank = 1
ORDER BY total_sales DESC;

ポイント：まずCTE + RANK() でランク付けし、次に WHERE でトップをフィルタリングします。ウィンドウ関数は WHERE で直接使用できないため、サブクエリやCTEでラップする必要があります。

シナリオ2：従業員の入社順と給与変動を追跡

SQL

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.hire_date,
    e.salary,
    LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS prev_salary,
    ROUND(e.salary - LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date), 2) AS salary_change,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS hire_order
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.hire_date;

ポイント：LAG() + PARTITION BY でグループ内の連続的な変動を追跡でき、トレンド分析に適しています。

❓ よくある質問

質問：ウィンドウ関数はWHERE句で使用できますか？ 回答： いいえ。WHERE はウィンドウ関数より前に実行されるため、ウィンドウ関数はまだ計算されていません。まずサブクエリやCTEでウィンドウ関数を計算し、外側のクエリで WHERE を使ってフィルタリングする必要があります。

質問：ROW_NUMBERとRANKはいつ使うべきですか？ 回答： 「一意の番号付け、同順位なし」が必要な場合（例：ページネーション、重複除去）は ROW_NUMBER を、「値によるランキング、同順位あり」が必要な場合（例：成績ランク、売上ランキング）は RANK を使用してください。

質問：PARTITION BYとGROUP BYの違いは何ですか？ 回答： GROUP BY は複数の行を1行にまとめ（行数を減らし）、PARTITION BY はウィンドウの範囲を定義するだけで行は減りません（各行が保持されます）。両方を組み合わせて使用することもできます：まず GROUP BY で集計し、次にウィンドウ関数でランキングを追加します。

質問：ウィンドウ関数のパフォーマンスはどのくらいですか？ 回答： ウィンドウ関数は通常、自己結合サブクエリより優れたパフォーマンスを発揮します。大規模データセットでは、PARTITION BY や ORDER BY で使用するカラムにインデックスを作成してください。

📖 まとめ

関数	ユースケース	例
ROW_NUMBER	一意の連番	ページネーション、重複除去
RANK	同順位ありのランキング（飛び番あり）	成績ランク
DENSE_RANK	同順位ありのランキング（飛び番なし）	ティア分類
NTILE	均等バケット分割	パーセンタイル分析
LAG	前の行にアクセス	前期比計算
LEAD	次の行にアクセス	トレンド予測
SUM/AVG/COUNT OVER	累積/グループ集計	比較分析

OVER() はウィンドウの範囲を定義し、PARTITION BY でパーティション分割、ORDER BY でソート
ウィンドウ関数は行数を減らさない — 詳細を保持しながら集計情報を追加
ウィンドウ関数を WHERE に配置することはできない；サブクエリやCTEでラップする

📝 演習

ROW_NUMBER() を使って従業員を入社日で番号付けし、最も最近入社した従業員を見つけてください。
RANK() を使って各部門内で給与でランク付けし、各部門の給与上位2名を見つけてください。
LAG() を使って、入社日順に並べた各部門内の従業員の給与変動を計算してください。
SUM() OVER() を使って、各注文（注文日順）の売上累積合計を計算してください。

次のレッスン

👉 20-cte-temp-table - CTEと一時テーブル：WITH句（CTE）の使い方、再帰的CTE、CTEとサブクエリの比較、一時テーブルの作成とユースケースを学びます。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて