【SQL実践ドリル第8回】ウィンドウ関数 ― 行をまたぐ集計と順位付け

Posted at 2026-03-10

このシリーズでは共通のサンプルデータベースを使用します。初回（第1回）でCREATE TABLE文を掲載しています。

はじめに

通常の集約関数（SUM, AVG, COUNT など）は GROUP BY と組み合わせてグループ単位の集計値を返しますが、ウィンドウ関数 を使うと、集約しつつ元の行を保持したまま結果を得られます。第8回では、ウィンドウ関数の基本構文から実践的な活用パターンまでを扱います。

前提: ウィンドウ関数は MySQL 8.0 以降 で使用できます。MySQL 5.7 以前ではサポートされていません。PostgreSQL では 8.4 以降で利用可能です。

ウィンドウ関数の基本構文

関数名(...) OVER (
    [PARTITION BY 列名, ...]
    [ORDER BY 列名 [ASC|DESC], ...]
    [フレーム指定]
)

構成要素	説明
`OVER ()`	ウィンドウ関数であることを示す。空の `()` はテーブル全体が対象
`PARTITION BY`	ウィンドウを分割する列を指定。`GROUP BY` のように行をグループ化するが、行は集約されない
`ORDER BY`	ウィンドウ内の行の順序を指定。順位関数や累計で必須
フレーム指定	現在行を基準に計算対象の行範囲を指定（後述）

GROUP BY との違い

-- GROUP BY: 部署ごとに1行に集約される
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

-- ウィンドウ関数: 全行を保持したまま部署平均を付与
SELECT employee_name, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;

順位付け関数

3 つの順位付け関数の違いを以下の例で示します。

仮に給与が 450000, 420000, 420000, 380000 の 4 名に対して順位付けした場合:

salary	ROW_NUMBER	RANK	DENSE_RANK
450000	1	1	1
420000	2	2	2
420000	3	2	2
380000	4	4	3

関数	同順位の扱い	次の順位
`ROW_NUMBER()`	同値でも連番（どちらが先になるかは不定）	常に連番
`RANK()`	同値には同じ順位	同順位の分だけスキップ（2位が2人→次は4位）
`DENSE_RANK()`	同値には同じ順位	スキップしない（2位が2人→次は3位）

3 つの関数はすべて OVER (ORDER BY ...) が必須です。

集約ウィンドウ関数

通常の集約関数にも OVER を付けてウィンドウ関数として使えます。

SUM(salary)   OVER (PARTITION BY department_id) -- 部署別合計
AVG(salary)   OVER (PARTITION BY department_id) -- 部署別平均
COUNT(*)      OVER (PARTITION BY department_id) -- 部署別件数
MAX(salary)   OVER (PARTITION BY department_id) -- 部署別最大
MIN(salary)   OVER (PARTITION BY department_id) -- 部署別最小

オフセット関数

現在行から前後の行にアクセスする関数です。

関数	説明
`LAG(col, n, default)`	n 行前の値（デフォルト n=1）。前の行がなければ default（省略時 NULL）
`LEAD(col, n, default)`	n 行後の値（デフォルト n=1）。次の行がなければ default（省略時 NULL）
`FIRST_VALUE(col)`	ウィンドウフレーム内の最初の行の値
`LAST_VALUE(col)`	ウィンドウフレーム内の最後の行の値

LAST_VALUE の注意点: ORDER BY を指定すると、デフォルトのフレームは ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になります。そのため LAST_VALUE は現在行の値を返してしまいます。パーティション全体の最後の値を得るには、フレームを ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING と明示する必要があります。

フレーム指定

ORDER BY を指定した場合のデフォルトフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW です。フレームを明示する構文:

ROWS BETWEEN 開始点 AND 終了点

指定	意味
`UNBOUNDED PRECEDING`	パーティションの先頭
`n PRECEDING`	現在行の n 行前
`CURRENT ROW`	現在行
`n FOLLOWING`	現在行の n 行後
`UNBOUNDED FOLLOWING`	パーティションの末尾

例:

-- 累計（先頭から現在行まで）
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 移動平均（前1行 + 現在行 + 次1行の3行平均）
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

NTILE 関数

NTILE(n) はパーティション内の行を n 個のグループ にできるだけ均等に分割し、グループ番号（1〜n）を割り当てます。

NTILE(4) OVER (ORDER BY salary DESC)
-- 10人を4グループに分割 → 3,3,2,2人 (余りは先頭グループに割り当て)

行数が n で割り切れない場合、先頭のグループから 1 行ずつ多く割り当てられます。

練習問題

問題 1 ⭐（基本）― ROW_NUMBER / RANK / DENSE_RANK の比較

employees テーブルから、全社員を給与の降順に並べ、ROW_NUMBER()、RANK()、DENSE_RANK() の 3 つの順位を付けてください。

期待結果:

+-----------------+--------+-----+------+------------+
| employee_name   | salary | rn  | rnk  | dense_rnk  |
+-----------------+--------+-----+------+------------+
| 伊藤健太       | 450000 |   1 |    1 |          1 |
| 佐藤花子       | 420000 |   2 |    2 |          2 |
| 高橋美咲       | 380000 |   3 |    3 |          3 |
| 田中太郎       | 350000 |   4 |    4 |          4 |
| 山本大輔       | 330000 |   5 |    5 |          5 |
| 中村真理       | 310000 |   6 |    6 |          6 |
| 鈴木一郎       | 300000 |   7 |    7 |          7 |
| 加藤優子       | 290000 |   8 |    8 |          8 |
| 渡辺さくら     | 280000 |   9 |    9 |          9 |
| 小林誠         | 270000 |  10 |   10 |         10 |
+-----------------+--------+-----+------+------------+

補足: このデータでは全社員の給与が異なるため、3 つの関数の結果はすべて同じになります。同一給与の社員がいた場合に RANK と DENSE_RANK の差が現れます（解説の冒頭で示した例を参照）。

模範解答

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC;

解説:

3 つの関数はすべて OVER (ORDER BY salary DESC) で給与の降順に順位を付けます。
このデータでは給与に重複がないため全関数が同じ値を返しますが、実務データでは同額の給与が存在する可能性が高いため、目的に応じて関数を選ぶ必要があります。
- 一意の番号が必要 → ROW_NUMBER()（ページネーション等）
- 同値は同順位、次の順位をスキップ → RANK()（順位表等）
- 同値は同順位、次の順位をスキップしない → DENSE_RANK()（ランク分析等）

問題 2 ⭐⭐（応用）― 部署内での給与順位

employees テーブルと departments テーブルを結合し、各部署内で給与の高い順に RANK() を付けてください。department_id が NULL の社員は除外します。department_id の昇順、同じ部署内では給与の降順で並べてください。

期待結果:

+-----------------+-----------------+--------+-----------+
| department_name | employee_name   | salary | dept_rank |
+-----------------+-----------------+--------+-----------+
| 営業部         | 田中太郎       | 350000 |         1 |
| 営業部         | 中村真理       | 310000 |         2 |
| 営業部         | 鈴木一郎       | 300000 |         3 |
| 開発部         | 伊藤健太       | 450000 |         1 |
| 開発部         | 佐藤花子       | 420000 |         2 |
| 開発部         | 渡辺さくら     | 280000 |         3 |
| 人事部         | 高橋美咲       | 380000 |         1 |
| 人事部         | 小林誠         | 270000 |         2 |
| 経理部         | 山本大輔       | 330000 |         1 |
+-----------------+-----------------+--------+-----------+

計算根拠:

営業部(dept_id=1): 田中太郎(350000)→1位, 中村真理(310000)→2位, 鈴木一郎(300000)→3位

開発部(dept_id=2): 伊藤健太(450000)→1位, 佐藤花子(420000)→2位, 渡辺さくら(280000)→3位

人事部(dept_id=3): 高橋美咲(380000)→1位, 小林誠(270000)→2位

経理部(dept_id=4): 山本大輔(330000)→1位（1名のみ）

加藤優子(dept_id=NULL)→ INNER JOIN により除外

模範解答

SELECT
    d.department_name,
    e.employee_name,
    e.salary,
    RANK() OVER (
        PARTITION BY e.department_id
        ORDER BY e.salary DESC
    ) AS dept_rank
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
ORDER BY e.department_id, e.salary DESC;

解説:

PARTITION BY e.department_id で部署ごとにウィンドウを分割します。
ORDER BY e.salary DESC で各パーティション内を給与の降順に並べ、RANK() がその順序で順位を付けます。
INNER JOIN により department_id が NULL の社員は自動的に除外されます。
最外部の ORDER BY はウィンドウ関数の結果とは独立して、最終的な出力順を決めます。

問題 3 ⭐⭐（応用）― 注文金額の累計（ランニングトータル）

orders テーブルと order_details テーブル、products テーブルを結合し、注文日の昇順で注文ごとの金額と累計金額を計算してください。注文金額は price * quantity の合計です。

期待結果:

+----------+------------+--------------+--------------+
| order_id | order_date | order_amount | running_total|
+----------+------------+--------------+--------------+
|        1 | 2024-07-01 |       413500 |       413500 |
|        2 | 2024-07-05 |        81000 |       494500 |
|        3 | 2024-07-10 |        98600 |       593100 |
|        4 | 2024-08-01 |       224000 |       817100 |
|        5 | 2024-08-15 |        59000 |       876100 |
|        6 | 2024-09-01 |        75500 |       951600 |
|        7 | 2024-09-10 |       202000 |      1153600 |
|        8 | 2024-10-01 |       288000 |      1441600 |
|        9 | 2024-10-15 |        31500 |      1473100 |
|       10 | 2024-11-01 |       295000 |      1768100 |
+----------+------------+--------------+--------------+

計算根拠（注文金額 = SUM(price * quantity) per order）:

Order 1: (198000×2)+(3500×5) = 396000+17500 = 413500

Order 2: (45000×1)+(12000×3) = 45000+36000 = 81000

Order 3: (89000×1)+(4800×2) = 89000+9600 = 98600

Order 4: (198000×1)+(6500×4) = 198000+26000 = 224000

Order 5: (3500×10)+(12000×2) = 35000+24000 = 59000

Order 6: (65000×1)+(3500×3) = 65000+10500 = 75500

Order 7: (89000×2)+(4800×5) = 178000+24000 = 202000

Order 8: (198000×1)+(45000×2) = 198000+90000 = 288000

Order 9: (12000×1)+(6500×3) = 12000+19500 = 31500

Order 10: (89000×3)+(3500×8) = 267000+28000 = 295000

累計: 413500 → 494500 → 593100 → 817100 → 876100 → 951600 → 1153600 → 1441600 → 1473100 → 1768100

模範解答

SELECT
    o.order_id,
    o.order_date,
    SUM(p.price * od.quantity) AS order_amount,
    SUM(SUM(p.price * od.quantity)) OVER (
        ORDER BY o.order_date, o.order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
GROUP BY o.order_id, o.order_date
ORDER BY o.order_date, o.order_id;

解説:

SUM(p.price * od.quantity) で注文ごとの金額を集計し、GROUP BY o.order_id で注文単位にまとめます。
SUM(SUM(...)) OVER (...) というネストされた集約 + ウィンドウ関数の構文に注目してください。内側の SUM は GROUP BY による集約、外側の SUM ... OVER はウィンドウ関数です。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW で先頭行から現在行までの累計を計算します。ORDER BY を指定した場合のデフォルトフレームは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ですが、同じ order_date の注文がある場合に RANGE だと同日の注文がまとめて計算される可能性があるため、ROWS を明示する方が安全です。
ORDER BY o.order_date, o.order_id で日付が同じ場合も order_id で順序を確定させています。

問題 4 ⭐⭐（応用）― 部署平均との比較

employees テーブルと departments テーブルを結合し、各社員の給与と所属部署の平均給与を並べて表示してください。さらに、部署平均との差額も計算します。department_id が NULL の社員は除外します。department_id 昇順、salary 降順で並べてください。

期待結果:

+-----------------+-----------------+--------+-----------+-----------+
| department_name | employee_name   | salary | dept_avg  | diff      |
+-----------------+-----------------+--------+-----------+-----------+
| 営業部         | 田中太郎       | 350000 | 320000.00 |  30000.00 |
| 営業部         | 中村真理       | 310000 | 320000.00 | -10000.00 |
| 営業部         | 鈴木一郎       | 300000 | 320000.00 | -20000.00 |
| 開発部         | 伊藤健太       | 450000 | 383333.33 |  66666.67 |
| 開発部         | 佐藤花子       | 420000 | 383333.33 |  36666.67 |
| 開発部         | 渡辺さくら     | 280000 | 383333.33 |-103333.33 |
| 人事部         | 高橋美咲       | 380000 | 325000.00 |  55000.00 |
| 人事部         | 小林誠         | 270000 | 325000.00 | -55000.00 |
| 経理部         | 山本大輔       | 330000 | 330000.00 |      0.00 |
+-----------------+-----------------+--------+-----------+-----------+

計算根拠:

営業部平均: (350000+300000+310000)/3 = 960000/3 = 320000.00

田中太郎: 350000 - 320000 = 30000.00

中村真理: 310000 - 320000 = -10000.00

鈴木一郎: 300000 - 320000 = -20000.00

開発部平均: (420000+450000+280000)/3 = 1150000/3 = 383333.33

伊藤健太: 450000 - 383333.33 = 66666.67

佐藤花子: 420000 - 383333.33 = 36666.67

渡辺さくら: 280000 - 383333.33 = -103333.33

人事部平均: (380000+270000)/2 = 650000/2 = 325000.00

高橋美咲: 380000 - 325000 = 55000.00

小林誠: 270000 - 325000 = -55000.00

経理部平均: 330000/1 = 330000.00

山本大輔: 330000 - 330000 = 0.00

模範解答

SELECT
    d.department_name,
    e.employee_name,
    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 employees e
INNER JOIN departments d ON e.department_id = d.department_id
ORDER BY e.department_id, e.salary DESC;

解説:

AVG(e.salary) OVER (PARTITION BY e.department_id) は、各行に対して「その社員が所属する部署の平均給与」を返します。GROUP BY を使わないため、元の行はすべて保持されます。
ROUND(..., 2) で小数点以下 2 桁に丸めています。
e.salary - AVG(...) OVER (...) で各社員の給与と部署平均の差額を計算しています。正の値は部署平均を上回り、負の値は下回っていることを示します。
ウィンドウ関数を使わない場合、サブクエリで部署平均を計算して結合する必要がありますが、ウィンドウ関数ならクエリ 1 つで完結します。

問題 5 ⭐⭐（応用）― LAG を使った月次比較

orders テーブルと order_details テーブル、products テーブルを結合し、月ごとの注文金額を集計してください。さらに LAG を使って前月の金額と前月比（増減額） を表示してください。

期待結果:

+-----------+--------------+-----------+-----------+
| order_ym  | month_amount | prev_amount | diff     |
+-----------+--------------+-------------+----------+
| 2024-07   |       593100 |        NULL |     NULL |
| 2024-08   |       283000 |      593100 |  -310100 |
| 2024-09   |       277500 |      283000 |    -5500 |
| 2024-10   |       319500 |      277500 |    42000 |
| 2024-11   |       295000 |      319500 |   -24500 |
+-----------+--------------+-------------+----------+

計算根拠（月別注文金額）:

2024-07: Order1(413500) + Order2(81000) + Order3(98600) = 593100

2024-08: Order4(224000) + Order5(59000) = 283000

2024-09: Order6(75500) + Order7(202000) = 277500

2024-10: Order8(288000) + Order9(31500) = 319500

2024-11: Order10(295000) = 295000

前月比:

2024-07: 前月なし → NULL

2024-08: 283000 - 593100 = -310100

2024-09: 277500 - 283000 = -5500

2024-10: 319500 - 277500 = 42000

2024-11: 295000 - 319500 = -24500

模範解答

SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') AS order_ym,
    SUM(p.price * od.quantity) AS month_amount,
    LAG(SUM(p.price * od.quantity)) OVER (
        ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')
    ) AS prev_amount,
    SUM(p.price * od.quantity) - LAG(SUM(p.price * od.quantity)) OVER (
        ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')
    ) AS diff
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY order_ym;

解説:

DATE_FORMAT(o.order_date, '%Y-%m') で注文日を年月形式に変換し、月単位でグループ化します。
LAG(SUM(...)) OVER (ORDER BY ...) で前月の集計値を取得しています。GROUP BY 後のウィンドウ関数なので、LAG の引数にも SUM が必要です。
最初の月（2024-07）は前月が存在しないため、LAG は NULL を返します。diff も NULL - 593100 ではなく NULL になります（NULL との演算は NULL）。
PostgreSQL の場合: DATE_FORMAT の代わりに TO_CHAR(o.order_date, 'YYYY-MM') を使います。

問題 6 ⭐⭐（応用）― 部署内の給与構成比

employees テーブルと departments テーブルを結合し、各社員の給与が部署内の合計給与に占める割合（%） を計算してください。小数点以下 2 桁まで表示します。department_id が NULL の社員は除外します。department_id 昇順、salary 降順で並べてください。

期待結果:

+-----------------+-----------------+--------+-----------+---------+
| department_name | employee_name   | salary | dept_total| pct     |
+-----------------+-----------------+--------+-----------+---------+
| 営業部         | 田中太郎       | 350000 |    960000 |   36.46 |
| 営業部         | 中村真理       | 310000 |    960000 |   32.29 |
| 営業部         | 鈴木一郎       | 300000 |    960000 |   31.25 |
| 開発部         | 伊藤健太       | 450000 |   1150000 |   39.13 |
| 開発部         | 佐藤花子       | 420000 |   1150000 |   36.52 |
| 開発部         | 渡辺さくら     | 280000 |   1150000 |   24.35 |
| 人事部         | 高橋美咲       | 380000 |    650000 |   58.46 |
| 人事部         | 小林誠         | 270000 |    650000 |   41.54 |
| 経理部         | 山本大輔       | 330000 |    330000 |  100.00 |
+-----------------+-----------------+--------+-----------+---------+

計算根拠:

営業部合計: 350000+310000+300000 = 960000

田中太郎: 350000/960000×100 = 36.458... → 36.46

中村真理: 310000/960000×100 = 32.291... → 32.29

鈴木一郎: 300000/960000×100 = 31.25

開発部合計: 450000+420000+280000 = 1150000

伊藤健太: 450000/1150000×100 = 39.130... → 39.13

佐藤花子: 420000/1150000×100 = 36.521... → 36.52

渡辺さくら: 280000/1150000×100 = 24.347... → 24.35

人事部合計: 380000+270000 = 650000

高橋美咲: 380000/650000×100 = 58.461... → 58.46

小林誠: 270000/650000×100 = 41.538... → 41.54

経理部合計: 330000

山本大輔: 330000/330000×100 = 100.00

模範解答

SELECT
    d.department_name,
    e.employee_name,
    e.salary,
    SUM(e.salary) OVER (PARTITION BY e.department_id) AS dept_total,
    ROUND(
        e.salary * 100.0 / SUM(e.salary) OVER (PARTITION BY e.department_id),
        2
    ) AS pct
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
ORDER BY e.department_id, e.salary DESC;

解説:

SUM(e.salary) OVER (PARTITION BY e.department_id) で各行に部署ごとの給与合計を付与します。
e.salary * 100.0 / SUM(...) OVER (...) でパーセンテージを計算しています。100.0（浮動小数点数）を掛けることで整数除算を防いでいます。
ROUND(..., 2) で小数点以下 2 桁に丸めています。
ウィンドウ関数を使わない場合、サブクエリで部署合計を計算し結合する必要があります。

問題 7 ⭐⭐⭐（チャレンジ）― 移動平均の計算

orders テーブルと order_details テーブル、products テーブルを結合し、注文ごとの金額と 3 件移動平均（現在の注文を含む直近 3 件の平均）を計算してください。小数点以下は四捨五入して整数で表示します。

期待結果:

+----------+------------+--------------+--------------+
| order_id | order_date | order_amount | moving_avg_3 |
+----------+------------+--------------+--------------+
|        1 | 2024-07-01 |       413500 |       413500 |
|        2 | 2024-07-05 |        81000 |       247250 |
|        3 | 2024-07-10 |        98600 |       197700 |
|        4 | 2024-08-01 |       224000 |       134533 |
|        5 | 2024-08-15 |        59000 |       127200 |
|        6 | 2024-09-01 |        75500 |       119500 |
|        7 | 2024-09-10 |       202000 |       112167 |
|        8 | 2024-10-01 |       288000 |       188500 |
|        9 | 2024-10-15 |        31500 |       173833 |
|       10 | 2024-11-01 |       295000 |       204833 |
+----------+------------+--------------+--------------+

計算根拠（3件移動平均 = 直近3件の平均、端数四捨五入）:

Order 1: 413500のみ → 413500/1 = 413500

Order 2: (413500+81000)/2 = 494500/2 = 247250

Order 3: (413500+81000+98600)/3 = 593100/3 = 197700

Order 4: (81000+98600+224000)/3 = 403600/3 = 134533.33 → 134533

Order 5: (98600+224000+59000)/3 = 381600/3 = 127200

Order 6: (224000+59000+75500)/3 = 358500/3 = 119500

Order 7: (59000+75500+202000)/3 = 336500/3 = 112166.67 → 112167

Order 8: (75500+202000+288000)/3 = 565500/3 = 188500

Order 9: (202000+288000+31500)/3 = 521500/3 = 173833.33 → 173833

Order 10: (288000+31500+295000)/3 = 614500/3 = 204833.33 → 204833

模範解答

SELECT
    o.order_id,
    o.order_date,
    SUM(p.price * od.quantity) AS order_amount,
    ROUND(
        AVG(SUM(p.price * od.quantity)) OVER (
            ORDER BY o.order_date, o.order_id
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        )
    ) AS moving_avg_3
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
GROUP BY o.order_id, o.order_date
ORDER BY o.order_date, o.order_id;

解説:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW で「現在行を含む直近 3 行」をフレームに指定しています。
最初の 2 行ではフレーム内の行数が 3 未満（1行、2行）ですが、AVG は存在する行だけで平均を計算します。Order 1 は 1 件の平均（= そのまま）、Order 2 は 2 件の平均になります。
ROUND(...) で小数点以下を四捨五入して整数にしています。ROUND の第 2 引数を省略するとデフォルトで 0 桁（整数）に丸められます。
ROWS と RANGE の違い: ROWS は物理的な行数で数えます。RANGE は値ベースで範囲を指定するため、同じ日付の注文がある場合に挙動が異なります。移動平均では ROWS を使うのが一般的です。

問題 8 ⭐⭐⭐（チャレンジ）― 顧客別の累計注文金額

orders テーブルと order_details テーブル、products テーブル、customers テーブルを結合し、顧客ごとに 注文日の昇順で累計注文金額を計算してください。

期待結果:

+--------------------------+----------+------------+--------------+------------------+
| customer_name            | order_id | order_date | order_amount | cumulative_amount|
+--------------------------+----------+------------+--------------+------------------+
| 株式会社ABC              |        1 | 2024-07-01 |       413500 |           413500 |
| 株式会社ABC              |        3 | 2024-07-10 |        98600 |           512100 |
| 株式会社ABC              |        8 | 2024-10-01 |       288000 |           800100 |
| DEFコーポレーション      |        2 | 2024-07-05 |        81000 |            81000 |
| DEFコーポレーション      |        6 | 2024-09-01 |        75500 |           156500 |
| GHI商事                  |        4 | 2024-08-01 |       224000 |           224000 |
| GHI商事                  |        9 | 2024-10-15 |        31500 |           255500 |
| JKLテクノロジー          |        5 | 2024-08-15 |        59000 |            59000 |
| JKLテクノロジー          |       10 | 2024-11-01 |       295000 |           354000 |
| MNOサービス              |        7 | 2024-09-10 |       202000 |           202000 |
+--------------------------+----------+------------+--------------+------------------+

計算根拠:

株式会社ABC(customer_id=1):

Order 1 (2024-07-01): 413500, 累計=413500

Order 3 (2024-07-10): 98600, 累計=413500+98600=512100

Order 8 (2024-10-01): 288000, 累計=512100+288000=800100

DEFコーポレーション(customer_id=2):

Order 2 (2024-07-05): 81000, 累計=81000

Order 6 (2024-09-01): 75500, 累計=81000+75500=156500

GHI商事(customer_id=3):

Order 4 (2024-08-01): 224000, 累計=224000

Order 9 (2024-10-15): 31500, 累計=224000+31500=255500

JKLテクノロジー(customer_id=4):

Order 5 (2024-08-15): 59000, 累計=59000

Order 10 (2024-11-01): 295000, 累計=59000+295000=354000

MNOサービス(customer_id=5):

Order 7 (2024-09-10): 202000, 累計=202000

模範解答

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    SUM(p.price * od.quantity) AS order_amount,
    SUM(SUM(p.price * od.quantity)) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date, o.order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM orders o
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
INNER JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.order_id, o.order_date, o.customer_id, c.customer_name
ORDER BY o.customer_id, o.order_date, o.order_id;

解説:

PARTITION BY o.customer_id で顧客ごとにウィンドウを分割しています。各顧客の累計は独立して計算されます。
ORDER BY o.order_date, o.order_id で注文日順に並べ、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW で先頭から現在行までの累計を取ります。
問題 3 と同様に SUM(SUM(...)) OVER (...) のネスト構文を使っています。
累計はビジネスで非常によく使われるパターンです。顧客の LTV（ライフタイムバリュー）分析や、予算消化率の追跡などに活用されます。

問題 9 ⭐⭐⭐（チャレンジ）― NTILE で給与の四分位分類

employees テーブルから、全社員を給与の降順で 4 つのグループに分割し、各社員がどの四分位に属するか表示してください。グループ 1 が最高給与層、グループ 4 が最低給与層です。

期待結果:

+-----------------+--------+---------+
| employee_name   | salary | quartile|
+-----------------+--------+---------+
| 伊藤健太       | 450000 |       1 |
| 佐藤花子       | 420000 |       1 |
| 高橋美咲       | 380000 |       1 |
| 田中太郎       | 350000 |       2 |
| 山本大輔       | 330000 |       2 |
| 中村真理       | 310000 |       2 |
| 鈴木一郎       | 300000 |       3 |
| 加藤優子       | 290000 |       3 |
| 渡辺さくら     | 280000 |       4 |
| 小林誠         | 270000 |       4 |
+-----------------+--------+---------+

計算根拠:
10 人を 4 グループに分割: 10 ÷ 4 = 2 余り 2
余り 2 は先頭のグループから 1 名ずつ追加されるため、3, 3, 2, 2 名の配分になります。

グループ 1（上位 3 名）: 伊藤健太(450000), 佐藤花子(420000), 高橋美咲(380000)

グループ 2（次の 3 名）: 田中太郎(350000), 山本大輔(330000), 中村真理(310000)

グループ 3（次の 2 名）: 鈴木一郎(300000), 加藤優子(290000)

グループ 4（最後の 2 名）: 渡辺さくら(280000), 小林誠(270000)

模範解答

SELECT
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees
ORDER BY salary DESC;

解説:

NTILE(4) は行を 4 つのグループにできるだけ均等に分割します。
10 行を 4 グループに分ける場合、10 ÷ 4 = 2 余り 2 なので、先頭の 2 グループに 1 行ずつ多く割り当てられ、3, 3, 2, 2 の配分になります。
ORDER BY salary DESC により、給与の高い順にグループ番号が割り当てられます。
NTILE は四分位分析、パーセンタイル分析、データの偏り確認などに使えます。例えば「上位 25% の顧客」を抽出する際に NTILE(4) ... = 1 のように使います。

問題 10 ⭐⭐⭐（チャレンジ）― 部署別トップ 2 の抽出

employees テーブルと departments テーブルを結合し、各部署で給与が上位 2 名 の社員を抽出してください。department_id が NULL の社員は除外します。部署を department_id の昇順、同じ部署内では給与の降順で並べてください。

期待結果:

+-----------------+-----------------+--------+-----------+
| department_name | employee_name   | salary | dept_rank |
+-----------------+-----------------+--------+-----------+
| 営業部         | 田中太郎       | 350000 |         1 |
| 営業部         | 中村真理       | 310000 |         2 |
| 開発部         | 伊藤健太       | 450000 |         1 |
| 開発部         | 佐藤花子       | 420000 |         2 |
| 人事部         | 高橋美咲       | 380000 |         1 |
| 人事部         | 小林誠         | 270000 |         2 |
| 経理部         | 山本大輔       | 330000 |         1 |
+-----------------+-----------------+--------+-----------+

計算根拠:

営業部: 田中太郎(350000)→1位, 中村真理(310000)→2位, 鈴木一郎(300000)→3位 ← 除外

開発部: 伊藤健太(450000)→1位, 佐藤花子(420000)→2位, 渡辺さくら(280000)→3位 ← 除外

人事部: 高橋美咲(380000)→1位, 小林誠(270000)→2位（2名しかいないので全員対象）

経理部: 山本大輔(330000)→1位（1名しかいないので全員対象）

模範解答

SELECT
    department_name,
    employee_name,
    salary,
    dept_rank
FROM (
    SELECT
        d.department_name,
        e.employee_name,
        e.salary,
        e.department_id,
        ROW_NUMBER() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS dept_rank
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id
) ranked
WHERE dept_rank <= 2
ORDER BY department_id, salary DESC;

解説:

ウィンドウ関数の結果を WHERE で直接フィルタリングすることはできません。ウィンドウ関数は WHERE の後に評価されるためです。そのため、サブクエリ（派生テーブル） でウィンドウ関数を計算し、外側のクエリで WHERE dept_rank <= 2 とフィルタリングします。
ROW_NUMBER() を使っているため、同順位がある場合も必ず上位 2 名だけが選ばれます。同順位をすべて含めたい場合は RANK() や DENSE_RANK() を使い、WHERE dept_rank <= 2 とします。
この「Top-N per group」パターンは、ウィンドウ関数の最も実用的な活用例の一つです。
MySQL 8.0 以降の代替: CTE（共通テーブル式）を使うとより読みやすくなります:

WITH ranked AS (
    SELECT
        d.department_name,
        d.department_id,
        e.employee_name,
        e.salary,
        ROW_NUMBER() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS dept_rank
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id
)
SELECT
    department_name,
    employee_name,
    salary,
    dept_rank
FROM ranked
WHERE dept_rank <= 2
ORDER BY department_id, salary DESC;

参考

@kotaro_ai_lab
AI活用や開発効率化について発信しています。フォローお気軽にどうぞ！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

【SQL実践ドリル 第8回】ウィンドウ関数 ― 行をまたぐ集計と順位付け

はじめに

ウィンドウ関数の基本構文

GROUP BY との違い

順位付け関数

集約ウィンドウ関数

オフセット関数

フレーム指定

NTILE 関数

練習問題

問題 1 ⭐（基本）― ROW_NUMBER / RANK / DENSE_RANK の比較

問題 2 ⭐⭐（応用）― 部署内での給与順位

問題 3 ⭐⭐（応用）― 注文金額の累計（ランニングトータル）

問題 4 ⭐⭐（応用）― 部署平均との比較

問題 5 ⭐⭐（応用）― LAG を使った月次比較

問題 6 ⭐⭐（応用）― 部署内の給与構成比

問題 7 ⭐⭐⭐（チャレンジ）― 移動平均の計算

問題 8 ⭐⭐⭐（チャレンジ）― 顧客別の累計注文金額

問題 9 ⭐⭐⭐（チャレンジ）― NTILE で給与の四分位分類

問題 10 ⭐⭐⭐（チャレンジ）― 部署別トップ 2 の抽出

参考

【SQL実践ドリル第8回】ウィンドウ関数 ― 行をまたぐ集計と順位付け