MySQLウィンドウ関数入門：ROW_NUMBER・RANK・DENSE

MySQLのウィンドウ関数は、集計しながら元の行を保持できる機能です。GROUP BYで行を集約すると個別の行が消えてしまいますが、ウィンドウ関数は集計値を保ちながら各行に追加できます。この記事ではOVER句の基本構文とともに、混同しやすい三つのランキング関数——ROW_NUMBER・RANK・DENSE_RANK——の挙動の違いを実例で解説します。

このシナリオで考える
OVER句とウィンドウ関数の基本構文
ROW_NUMBERで一意の連番を振る
RANKとDENSE_RANKの違い
よくある落とし穴
まとめ
参考リンク

このシナリオで考える

架空の社内販売データを例に使います。salesテーブルに営業担当者の名前・所属部門・月次売上金額が入っているとします。「各部門内で売上に基づいた順位を付ける」シナリオで、三つのランキング関数の挙動の違いを比べていきます。

まず、テーブルとサンプルデータを作成します。東京部門では佐藤と鈴木が同額（38万円）で並んでおり、同点がある場合の挙動を確認するのが今回のポイントです。

CREATE TABLE sales (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(50),
    dept   VARCHAR(50),
    amount INT
);

INSERT INTO sales (name, dept, amount) VALUES
    ('田中', '東京', 500000),
    ('佐藤', '東京', 380000),
    ('鈴木', '東京', 380000),
    ('高橋', '東京', 270000),
    ('渡辺', '大阪', 450000),
    ('伊藤', '大阪', 310000),
    ('山本', '大阪', 310000);

OVER句とウィンドウ関数の基本構文

ウィンドウ関数は次の構文で書きます。

SELECT
    関数名() OVER (
        [PARTITION BY 列名]
        [ORDER BY 列名 ASC|DESC]
    )
FROM テーブル名;

PARTITION BYはウィンドウの「仕切り」を指定します。GROUP BYと似た記述ですが、行を畳み込まない点が決定的な違いです。PARTITION BY deptと指定すると部門ごとに独立した計算範囲が作られ、その中でランキングを計算します。

ORDER BYは各ウィンドウ内での並び順を指定します。ランキング関数では「何を基準に順位を決めるか」に相当します。PARTITION BYを省略するとテーブル全体が一つのウィンドウになり、全行を対象として計算します。

三つの関数を一度に確認するクエリを書きます。

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

ROW_NUMBERで一意の連番を振る

ROW_NUMBERは、同点の行があっても必ず一意の番号を振ります。同点行の内部的な順序はMySQLの処理に依存しますが、番号が重複することはありません。常に1・2・3・4と連続した整数が付きます。

東京部門の実行結果です。

name	amount	row_num	rnk	dense_rnk
田中	500,000	1	1	1
佐藤	380,000	2	2	2
鈴木	380,000	3	2	2
高橋	270,000	4	4	3

row_numは同点の佐藤と鈴木に2と3を一意に振っています。rnkとdense_rnkはどちらも2・2と同じ順位を付けています。高橋に注目すると、rnkは4、dense_rnkは3になっており、二つの関数の違いが見えてきます。

ROW_NUMBERの典型的な使い道は「各グループから上位N件だけ取り出す」クエリです。

-- 各部門の売上トップ1名だけ取り出す
WITH ranked AS (
    SELECT
        name,
        dept,
        amount,
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) AS rn
    FROM sales
)
SELECT name, dept, amount
FROM ranked
WHERE rn = 1;

ウィンドウ関数はWHERE句に直接書けません。上記のようにCTEやサブクエリで一度ラップしてから絞り込む必要があります。詳しくは「よくある落とし穴」で説明します。

RANKとDENSE_RANKの違い

二つの関数はどちらも同点に同じ順位を付けますが、次の順位のスキップ有無が異なります。

関数	同点の扱い	次の順位	出力例
RANK	同じ順位を付ける	スキップする	1・2・2・4
DENSE_RANK	同じ順位を付ける	スキップしない	1・2・2・3

先ほどの結果で、高橋はRANKが4位、DENSE_RANKが3位でした。RANKは「2位タイが2人いたので3位を飛ばした」という考え方です。DENSE_RANKは「実際に存在するランクの番号」を連続して返す、と覚えると理解しやすいです。

どちらを選ぶかはビジネス要件によります。「上位3位以内の人を取り出す」という絞り込みでは、スキップがないDENSE_RANKが結果件数のブレを小さくできます。一方、「表彰式で同点者を同格にしたい」場面ではRANKが自然な表現になります。

大阪部門の結果も確認します。

SELECT
    name,
    dept,
    amount,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY dept ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY amount DESC) AS dense_rnk
FROM sales
WHERE dept = '大阪';

name	amount	row_num	rnk	dense_rnk
渡辺	450,000	1	1	1
伊藤	310,000	2	2	2
山本	310,000	3	2	2

大阪では3人なのでRANKでも順位スキップは発生していません。スキップが起きるのは「同点行の次にそれより低い行が存在する場合」だけです。同点が2人いると、その次の人がRANKでは（2+2=）4位、DENSE_RANKでは3位になります。

よくある落とし穴

① ウィンドウ関数をWHERE句に書けない

MySQLの処理順序ではWHERE句の評価がSELECT句より先に行われます。SELECT句で計算されるウィンドウ関数の結果はWHERE評価の時点ではまだ存在しないため、WHERE句に書くとエラーになります。

-- NG: エラーになります
SELECT * FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) = 1;

-- OK: CTEで先に計算してから絞り込みます
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY amount DESC) AS rn
    FROM sales
)
SELECT * FROM ranked WHERE rn = 1;

② OVER句のORDER BYと最終出力の並び順は別物

OVER内のORDER BYはウィンドウ内での並び順を指定するもので、クエリ全体の出力順とは関係ありません。結果を特定の順序で出力したい場合は、クエリ末尾に別途ORDER BY句を書きます。

③ PARTITION BYの書き忘れ

部門ごとに順位を付けたいのにPARTITION BY deptを省略すると、全社を一つのウィンドウとして扱い、部門をまたいだ一つの順位になります。意図しない結果が出たらまずPARTITION BYの有無を確認しましょう。