ウィンドウ関数

ウィンドウ関数とは

ウィンドウ関数? OVER()句を使って、グループ化せずに集計・順位付けを行う関数。ROW_NUMBER()・RANK()・LAG()・SUM() OVER() など。GROUP BYと違い、元の行を保持したまま計算できる。 は「行を集約せずに集計する」関数です。GROUP BYでは複数行がひとつの行に集約されますが、ウィンドウ関数では元の行を保ちながら集計結果を各行に付加できます。

GROUP BY との違い

-- GROUP BYの場合: 行が集約される
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- 結果は部門数分の行になる

-- ウィンドウ関数の場合: 行は集約されない
SELECT
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;
-- 元の全員分の行に部門平均が付加される

---

OVER句の構造

OVER句は次の3つのオプションで構成されます。

関数名() OVER (
    PARTITION BY 列名        -- ウィンドウをどの単位で分割するか
    ORDER BY 列名            -- ウィンドウ内の並び順
    ROWS BETWEEN ... AND ... -- フレームの範囲
)

PARTITION BY

GROUP BY に似ていますが、行を集約せずグループを分割します。省略すると全行が1つのウィンドウになります。

-- 各部門内での給与ランキング
SELECT
    name,
    department_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

ORDER BY（OVER内）

ウィンドウ内の行の並び順を決めます。累積集計（SUM、COUNT）では ORDER BY によってフレームが変わります。

ROWS BETWEEN / RANGE BETWEEN

フレーム（計算対象の範囲）を指定します。

-- よく使うフレーム指定
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭から現在行まで（累積）
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW          -- 直前2行から現在行まで（移動平均）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- 現在行から末尾まで

---

ROW_NUMBER・RANK・DENSE_RANK

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

name	score	ROW_NUMBER	RANK	DENSE_RANK
A	95	1	1	1
B	90	2	2	2
C	90	3	2	2
D	85	4	4	3

• ROW_NUMBER: 同点でも連番を振る（重複なし）
• RANK: 同点は同じ順位、次の順位はスキップ（4位へ飛ぶ）
• DENSE_RANK: 同点は同じ順位、次の順位はスキップしない（3位が続く）

---

LAG・LEAD（前後の行を参照）

LAG() は前の行の値、LEAD() は後ろの行の値を参照します。前月比や前日比の計算に便利です。

SELECT
    sale_month,
    total_sales,
    LAG(total_sales, 1) OVER (ORDER BY sale_month) AS prev_month_sales,
    total_sales - LAG(total_sales, 1) OVER (ORDER BY sale_month) AS diff,
    ROUND(
        100.0 * total_sales / LAG(total_sales, 1) OVER (ORDER BY sale_month) - 100,
        1
    ) AS growth_rate_pct
FROM monthly_sales;

| sale_month | total_sales | prev_month | diff  | growth_rate |
|------------|-------------|------------|-------|-------------|
| 2025-01    | 100000      | NULL       | NULL  | NULL        |
| 2025-02    | 120000      | 100000     | 20000 | 20.0        |
| 2025-03    | 110000      | 120000     | -10000| -8.3        |

LAG(列, N, デフォルト値) の形でN行前を参照でき、前の行が存在しない場合のデフォルト値も指定できます。

---

SUM() OVER()（累積合計）

SELECT
    sale_date,
    daily_sales,
    SUM(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sales
FROM daily_sales;

| sale_date  | daily_sales | cumulative_sales |
|------------|-------------|-----------------|
| 2025-01-01 | 10000       | 10000           |
| 2025-01-02 | 15000       | 25000           |
| 2025-01-03 | 8000        | 33000           |

ORDER BY のみ指定した場合も（フレームのデフォルトが RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW なので）同様の結果になりますが、明示的に ROWS BETWEEN を指定する方が意図が明確です。

---

移動平均

-- 3日移動平均
SELECT
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM daily_sales;

---

実用例

各部門での上位3名を抽出

WITH ranked AS (
    SELECT
        name,
        department_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department_id, salary
FROM ranked
WHERE rn <= 3;

ウィンドウ関数はSELECTで評価されるため、WHERE句では直接フィルタできません。CTEやサブクエリで一度結果を作ってからフィルタする必要があります。

連続する日付のデータ欠損を検出

-- LAGで前の日付との差を計算し、1日より大きければデータ欠損
SELECT
    sale_date,
    LAG(sale_date) OVER (ORDER BY sale_date) AS prev_date,
    DATEDIFF(sale_date, LAG(sale_date) OVER (ORDER BY sale_date)) AS days_gap
FROM daily_sales
HAVING days_gap > 1;  -- MySQL では HAVING で集計後フィルタ可

---

まとめ

• ウィンドウ関数は行を集約せずに集計結果を各行に付加できる
• OVER(PARTITION BY ... ORDER BY ...) でウィンドウを定義する
• ROW_NUMBER: 連番、RANK: スキップあり順位、DENSE_RANK: スキップなし順位
• LAG/LEAD: 前後の行を参照して差分・比率を計算できる
• SUM OVER + ROWS BETWEEN で累積合計や移動平均を計算できる
• ウィンドウ関数の結果はWHEREで直接フィルタできない（CTEを使う）

次回は NULL? SQLにおける「値が存在しない」を表す特殊な状態。NULLは値ではないため、`= NULL` では比較できず、`IS NULL` / `IS NOT NULL` を使う。NULLを含む演算は基本的にNULLになる。 の正しい扱い方と3値論理について解説します。