MySQL 8.0 のウィンドウ関数活用：ランキング・移動平均・累計を SQL だけで

概要

MySQL 8.0 で追加されたウィンドウ関数（Window Functions）を使うと、これまでアプリ側やサブクエリの力技で書いていた「グループ内のランキング」「移動平均」「累計」「前後の行との比較」が、1 本の SELECT で素直に書けるようになります。

この記事では、サンプルデータを作りながら、実務で頻出する以下のパターンをハンズオンで確認します。

• ROW_NUMBER / RANK / DENSE_RANK によるグループ内順位付け
• SUM() OVER (...) による累計（running total）
• AVG() OVER (...) によるフレーム指定の移動平均
• LAG / LEAD による前後行との差分
• NTILE による N 分割

集計関数（GROUP BY）が「行を畳み込む」のに対し、ウィンドウ関数は「行を残したまま、関連する行の集計値を各行に添える」点が本質的な違いです。

環境

• MySQL 8.0 系（8.0.2 以降でウィンドウ関数が利用可能）
• クライアントは mysql CLI を想定
• サンプル DB は使い捨てのローカル環境で作成

バージョンを確認しておきます。

SELECT VERSION();

8.0.x が返れば OK。5.7 系ではウィンドウ関数が使えないため、以下は動きません。

サンプルデータの準備

店舗ごとの日次売上を模した架空データを用意します。

CREATE TABLE sales (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    store   VARCHAR(20) NOT NULL,
    sold_on DATE        NOT NULL,
    amount  INT         NOT NULL
);

INSERT INTO sales (store, sold_on, amount) VALUES
    ('tokyo', '2026-01-01', 12000),
    ('tokyo', '2026-01-02', 15000),
    ('tokyo', '2026-01-03',  9000),
    ('tokyo', '2026-01-04', 18000),
    ('osaka', '2026-01-01',  8000),
    ('osaka', '2026-01-02', 11000),
    ('osaka', '2026-01-03', 13000),
    ('osaka', '2026-01-04',  7000);

基本構文

ウィンドウ関数は 関数() OVER (ウィンドウ定義) の形を取ります。

関数() OVER (
    PARTITION BY 列   -- どの単位でグループ分けするか（省略可）
    ORDER BY 列       -- グループ内の並び順（省略可）
    フレーム指定       -- 集計対象の行範囲（省略可）
)

GROUP BY と違い、PARTITION BY は行をまとめません。各行はそのまま残り、同じパーティションの行を見渡した集計値が付与されます。

ランキング：ROW_NUMBER / RANK / DENSE_RANK

SELECT
    store, sold_on, amount,
    ROW_NUMBER() OVER (PARTITION BY store ORDER BY amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY store ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY store ORDER BY amount DESC) AS dense_rnk
FROM sales
ORDER BY store, amount DESC;

同値（タイ）の扱いに違いが出ます。

• ROW_NUMBER … 同値でも必ず連番（1,2,3,4）
• RANK … 同値は同順位、次は飛ぶ（1,1,3,4）
• DENSE_RANK … 同値は同順位、次は飛ばない（1,1,2,3）

各グループの上位 N 件

ウィンドウ関数は WHERE で直接使えないため、CTE で包みます。

WITH ranked AS (
    SELECT
        store, sold_on, amount,
        ROW_NUMBER() OVER (PARTITION BY store ORDER BY amount DESC) AS rn
    FROM sales
)
SELECT store, sold_on, amount
FROM ranked
WHERE rn <= 2
ORDER BY store, amount DESC;

「各店舗の売上トップ 2 日」が取れます。従来は相関サブクエリで書いていた処理が素直に読める形になります。

累計（running total）：SUM() OVER

SELECT
    store, sold_on, amount,
    SUM(amount) OVER (
        PARTITION BY store
        ORDER BY sold_on
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales
ORDER BY store, sold_on;

ORDER BY を付けた集計のデフォルトフレームは「先頭から現在行まで」です。累計だけなら ROWS BETWEEN ... は省略可能ですが、同値の扱いで差が出るため、意図を明確にしたいときは明示しておくと安全です。

移動平均：フレーム指定の AVG() OVER

直近 3 日（当日＋前 2 日）の移動平均を出します。

SELECT
    store, sold_on, amount,
    AVG(amount) OVER (
        PARTITION BY store
        ORDER BY sold_on
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM sales
ORDER BY store, sold_on;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW が「現在行を含む直近 3 行」を意味します。先頭部分では存在する行だけで平均されます。

ROWS と RANGE の違い

• ROWS … 物理的な行数で数える
• RANGE … ORDER BY の値の範囲で数える（同値はまとめて 1 グループ扱い）

同値があるケースで挙動が変わるため、要件に応じて選びます。

前後行との比較：LAG / LEAD

SELECT
    store, sold_on, amount,
    LAG(amount, 1) OVER (PARTITION BY store ORDER BY sold_on) AS prev_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY store ORDER BY sold_on) AS diff_from_prev
FROM sales
ORDER BY store, sold_on;

先頭行は前日が存在しないため NULL になります。既定値は第 3 引数で指定できます。

LAG(amount, 1, 0) OVER (PARTITION BY store ORDER BY sold_on)  -- 無ければ 0

N 分割：NTILE

SELECT
    store, sold_on, amount,
    NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY quartile, amount DESC;

NTILE(4) は対象行をできるだけ均等に 4 グループへ分け、1〜4 の番号を付けます。割り切れないぶんは先頭グループから 1 行ずつ多く割り当てられます。

確認方法

累計の最終行が、その店舗の合計と一致するかで検算できます。

SELECT store, SUM(amount) AS total FROM sales GROUP BY store;

注意点

• ウィンドウ関数は WHERE / HAVING / GROUP BY で直接使えない → CTE / サブクエリで包む
• ORDER BY が非一意だと ROW_NUMBER がブレる → PK など一意キーを同順キーに足す
• ORDER BY 付き集計のデフォルトフレームは RANGE。行単位で積みたいなら ROWS を明示
• 大量データでは PARTITION BY / ORDER BY 列のインデックス有無でソートコストが変わる → EXPLAIN で確認

まとめ

MySQL 8.0 のウィンドウ関数は、「行を残したまま集計値を添える」という性質を理解すると使いどころが一気に見えてきます。

• 順位付け → ROW_NUMBER / RANK / DENSE_RANK
• 累計 → SUM() OVER (ORDER BY ...)
• 移動平均 → AVG() OVER (... ROWS BETWEEN ...)
• 前後比較 → LAG / LEAD
• N 分割 → NTILE

アプリ側のループや相関サブクエリで書いていた処理を SQL に寄せられると、コード量が減り、DB 側で完結するぶん転送量も減ります。まずは手元の使い捨て DB でサンプルを動かし、フレーム指定の挙動を体で覚えるのがおすすめです。