MySQL ROW_NUMBER関数の完全ガイド｜基本から応用例・代替手法まで詳しく解説

1. はじめに

読者ターゲット

• SQLの基本的な知識を持つ初級者から中級者の方
• MySQLを使用してデータ処理や分析を行うエンジニアやデータアナリスト
• 最新バージョンのMySQLに移行を検討している方

ROW_NUMBER()関数のメリット

2. ROW_NUMBER関数とは

ROW_NUMBER関数の基本構文

SELECT
    列名,
    ROW_NUMBER() OVER (PARTITION BY グループ列 ORDER BY 並べ替え列) AS 行番号
FROM
    テーブル名;

各要素の意味

• ROW_NUMBER(): 各行に連番を付ける関数です。
• OVER: ウィンドウ関数を定義するためのキーワードです。
• PARTITION BY: データを指定した列でグループ化します。省略可能で、指定しない場合はすべての行に対して連番が振られます。
• ORDER BY: 番号を割り振る順序を指定します。これにより、並べ替え基準を設定できます。

基本的な使用例

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

実行結果

PARTITION BYの使い方

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

実行結果

ROW_NUMBER()関数の特徴と注意点

• 一意の番号付け: 同じ値であっても連番は一意に付けられます。
• NULL値の扱い: ORDER BYにNULL値が含まれる場合、昇順では最初に、降順では最後に配置されます。
• パフォーマンスへの影響: 大規模データセットでは、ORDER BYの処理負荷が高くなるため、インデックスを適切に設定することが重要です。

3. 実践的な使用例

3-1. グループごとのランキング付け

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

3-2. 上位N件のデータ抽出

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

3-3. 重複データの抽出と削除

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

まとめ

1. グループごとのランキング付け
2. 上位N件のデータ抽出
3. 重複データの検出と削除

4. 他のウィンドウ関数との比較

4-1. RANK()関数

基本構文

SELECT
    列名,
    RANK() OVER (PARTITION BY グループ列 ORDER BY 並べ替え列) AS 順位
FROM
    テーブル名;

使用例

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

• 同じ売上額（800）を持つAとBは「1位」として扱われます。
• その次の順位である「2位」はスキップされ、Cは「3位」になります。

4-2. DENSE_RANK()関数

基本構文

SELECT
    列名,
    DENSE_RANK() OVER (PARTITION BY グループ列 ORDER BY 並べ替え列) AS 順位
FROM
    テーブル名;

使用例

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

• 同じ売上額（800）を持つAとBは「1位」として扱われます。
• RANK()と異なり、次の順位は「2位」から始まるため、順位の連続性が保たれます。

4-3. ROW_NUMBER()関数との違い

使用例

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

• 値が同じであっても、一意の番号が割り振られるため順位の重複はありません。
• データの順序を厳密に制御したい場合や、行ごとの一意性が必要な場合に適しています。

4-4. 各関数の用途まとめ

まとめ

1. ROW_NUMBER()は一意の番号が必要な場合に適しています。
2. RANK()は同じ値を持つデータに同順位を付けつつ、順位のギャップを強調したい場合に有用です。
3. DENSE_RANK()は連続的な順位付けを行うため、順位の隙間が不要なシナリオに適しています。

5. MySQL 8.0未満の代替手法

5-1. ユーザー変数を利用した連番付け

使用例: 部門ごとの売上ランキング

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

5-2. 上位N件のデータ抽出

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

5-3. 重複データの検出と削除

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. ユーザー変数の注意点

1. セッション依存性

• ユーザー変数はセッション内でのみ有効です。別のクエリやセッションでは利用できません。

1. 処理順序の依存

• ユーザー変数はクエリの実行順序に依存するため、ORDER BY句の設定が重要です。

1. SQLの可読性と保守性

• コードが複雑化しやすいため、MySQL 8.0以降ではウィンドウ関数の使用を推奨します。

まとめ

6. 注意点とベストプラクティス

6-1. パフォーマンスの注意点

1. ORDER BYの負荷

• インデックスの活用: ORDER BYに使用する列にはインデックスを設定し、ソート処理を高速化します。
• LIMITの利用: 必要なデータ件数だけを取得し、処理対象データ量を抑えます。

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. メモリ使用量とディスクI/Oの増大

• クエリの分割: 処理を小さなクエリに分け、段階的にデータを抽出することで負荷を軽減します。
• 一時テーブルの活用: 一度抽出したデータを一時テーブルに格納し、そこから集計処理を行うことで負担を分散します。

6-2. クエリチューニングのポイント

1. 実行計画の確認

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

2. インデックスの最適化

• 単一列インデックス: シンプルなソート条件の場合に適用
• 複合インデックス: 複数列を条件に含む場合に有効

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. バッチ処理の活用

SELECT * FROM sales WHERE department = '営業部' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = '営業部' LIMIT 1000 OFFSET 1000;

6-3. データ整合性の維持

1. データの更新と再計算

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

6-4. クエリ例のベストプラクティス

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

まとめ

1. インデックスの最適化で処理速度を向上させる。
2. 実行計画の確認でパフォーマンスのボトルネックを特定する。
3. データ更新への対応を考慮し、常に整合性を維持する仕組みを導入する。
4. バッチ処理やCTEを活用して負荷を分散する。

7. まとめ

7-1. ROW_NUMBER()関数の利便性

1. グループごとの連番付け: 部門ごとの売上ランキングやカテゴリ別の順位付けが簡単に実現できます。
2. 上位N件のデータ抽出: 特定条件に基づくデータ抽出やフィルタリングが効率的に行えます。
3. 重複データの検出と削除: データの整理やクリーニング作業に役立ちます。

7-2. 他のウィンドウ関数との比較

7-3. 旧バージョンへの対応策

• コードの複雑化による可読性の低下
• クエリ処理の最適化が難しい場合がある
• データの整合性を保つための追加処理が必要

7-4. パフォーマンス最適化のポイント

1. インデックスの活用: ORDER BYに使用する列にインデックスを設定して処理速度を向上させる。
2. 実行計画の確認: EXPLAINステートメントでパフォーマンスを事前検証する。
3. バッチ処理の採用: 大規模データセットでは小分けに処理し、負荷を分散する。
4. ビューやCTEの活用: コードの再利用性を高め、複雑なクエリをシンプルに整理する。

7-5. 最後に

付録: 参考リソース

• 公式ドキュメント: MySQLウィンドウ関数
• SQL実行環境: SQL Fiddle（オンラインでSQLを実行・テストできるツール）