SQLウィンドウ関数入門〜OVER句・PARTITION BYの仕組みから実務活用まで〜

はじめに

ウィンドウ関数は「行を集約せずに、集計やランキングができる」SQLの強力な機能です。GROUP BYとの違いで混乱しやすいポイントですが、本記事で仕組みから整理します。

この後の流れ

1. ウィンドウ関数とは何か
2. 基本構文（OVER句、PARTITION BY、ORDER BY）
3. ランキング系関数（ROW_NUMBER, RANK, DENSE_RANK）
4. 集計系ウィンドウ関数（SUM, AVG等のOVER利用）
5. フレーム指定（累計・移動平均）
6. LAG／LEAD（前後の行を参照）
7. まとめ

1. ウィンドウ関数とは何か

通常の集計関数（GROUP BY+SUM等）は行を集約して行数を減らしますが、ウィンドウ関数は行数を保ったまま、各行に対して集計値を付与します。

SELECT
    employee_name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

上記は「部署ごとの平均給与」を各社員の行に付けたまま出力します。GROUP BYでは実現できません。

2. 基本構文

関数名() OVER (
    [PARTITION BY 列名]  -- グループ分け（省略可、省略時は全体が対象）
    [ORDER BY 列名]       -- ウィンドウ内の並び順（ランキング・累計で必須）
    [フレーム指定]         -- 集計範囲の限定（省略時は関数依存）
)

句	役割
PARTITION BY	GROUP BYに近いグループ分け。省略すると全行が対象
ORDER BY	ウィンドウ内の順序を決定。ランキングや累計で必須
フレーム	「現在行から何行前まで」等、集計範囲を限定

3. ランキング系関数

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_num
FROM employees;

関数	同順位の扱い	次の順位
ROW_NUMBER	同順位でも連番を振る	1,2,3,4...
RANK	同順位は同じ順位	1,2,2,4（3が飛ぶ）
DENSE_RANK	同順位は同じ順位	1,2,2,3（飛ばない）

「部署内給与TOP3を出す」ならROW_NUMBER() <= 3、「同額なら同じ順位で扱いたい」ならRANKまたはDENSE_RANKを使います。

4. 集計系ウィンドウ関数

SUM, AVG, COUNT, MAX, MINは集計関数としてもウィンドウ関数としても使えます。

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

ORDER BYを指定すると、デフォルトで「先頭行から現在行まで」の累計になります。

5. フレーム指定（累計・移動平均）

集計範囲を明示的に絞りたい場合はROWS BETWEENを使います。

-- 直近3日間（自分含む）の移動平均
SELECT
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3days
FROM orders
ORDER BY order_date;

フレーム指定	意味
UNBOUNDED PRECEDING	先頭行から
N PRECEDING	現在行のN行前から
CURRENT ROW	現在行まで
UNBOUNDED FOLLOWING	最終行まで

6. LAG／LEAD（前後の行を参照）

SELECT
    order_date,
    amount,
    LAG(amount, 1)  OVER (ORDER BY order_date) AS prev_day_amount,
    LEAD(amount, 1) OVER (ORDER BY order_date) AS next_day_amount,
    amount - LAG(amount, 1) OVER (ORDER BY order_date) AS diff_from_prev
FROM orders
ORDER BY order_date;

前日比・前月比の算出は、自己結合を使わずLAG/LEADで簡潔に書けます。

7. まとめ

やりたいこと	使う関数
行を維持したまま集計値を付ける	SUM/AVG等 + OVER
部署・カテゴリ内での順位	RANK / DENSE_RANK / ROW_NUMBER
累計・移動平均	フレーム指定付きSUM/AVG
前後の行との比較	LAG / LEAD

さいごに

ウィンドウ関数は「行を減らさない集計」という発想さえ掴めば一気に応用が効きます。次回はGROUP BYとの使い分けを整理します。