【完全ガイド】SQLのROW_NUMBER関数とは？

データベース・SQL

よくある課題：

  • データに番号を振って見やすくしたい
  • 各カテゴリで1位のデータだけを取得したい
  • 重複したデータから最新の1件だけを選びたい
  • ランキング表を作成したい

従来の方法では：

  • 複雑なサブクエリが必要
  • パフォーマンスが悪い
  • 可読性が低い

ROW_NUMBERを使えば：

  • シンプルな構文
  • 高いパフォーマンス
  • 直感的でわかりやすい

実際の使用場面

  • 売上ランキングを表示したい
  • 各部署で1番若い社員を取り出したい
  • 重複データの中から最新の1件だけを取得したい
  • ページネーションを実装したい
スポンサーリンク

ROW_NUMBERの基本構文

基本の形

SELECT 
    ROW_NUMBER() OVER (
        [PARTITION BY グループ列] 
        ORDER BY 並び順列
    ) AS 行番号,
    その他の列
FROM テーブル名;

各要素の詳細説明

要素説明必須
ROW_NUMBER()行番号を生成する関数○
OVER()ウィンドウ関数の範囲を定義○
PARTITION BYグループ単位で番号を振る×
ORDER BY行番号の振り方の順序指定○

最もシンプルな例

-- 全データに1から始まる連続番号を振る
SELECT 
    ROW_NUMBER() OVER (ORDER BY id) AS row_num,
    name,
    age
FROM users;

実行結果例：

row_num | name   | age
--------|--------|----
1       | 田中   | 25
2       | 佐藤   | 30
3       | 山田   | 22
4       | 鈴木   | 35

ORDER BYによる順序の制御

昇順（ASC）の場合

-- 年齢が若い順に番号を振る
SELECT 
    ROW_NUMBER() OVER (ORDER BY age ASC) AS row_num,
    name,
    age
FROM users;

実行結果例：

row_num | name   | age
--------|--------|----
1       | 山田   | 22
2       | 田中   | 25
3       | 佐藤   | 30
4       | 鈴木   | 35

降順（DESC）の場合

-- 年齢が高い順に番号を振る
SELECT 
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num,
    name,
    age
FROM users;

実行結果例：

row_num | name   | age
--------|--------|----
1       | 鈴木   | 35
2       | 佐藤   | 30
3       | 田中   | 25
4       | 山田   | 22

PARTITION BYを使ったグループ別の番号付け

基本概念

PARTITION BYを使うと、グループごとに1から始まる番号を振ることができます。

例1：部署別の番号付け

-- 部署ごとに年齢順で番号を振る
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY age ASC
    ) AS row_num,
    department,
    name,
    age
FROM employees;

実行結果例：

row_num | department | name   | age
--------|------------|--------|----
1       | 営業部     | 青木   | 24
2       | 営業部     | 田中   | 28
3       | 営業部     | 山田   | 32
1       | 開発部     | 佐藤   | 26
2       | 開発部     | 鈴木   | 29
3       | 開発部     | 高橋   | 31

重要なポイント：

  • 各部署内で1から番号が振られる
  • 部署が変わると番号がリセットされる

例2：月別の売上ランキング

-- 月別に売上順でランキングを作成
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY MONTH(sales_date) 
        ORDER BY amount DESC
    ) AS monthly_rank,
    MONTH(sales_date) AS month,
    product_name,
    amount
FROM sales
WHERE YEAR(sales_date) = 2023;

各グループ内で1番目だけを取り出す

WITH句を使った抽出

最も一般的で推奨される方法です。

-- 各部署で最も年齢が若い社員を1名ずつ抽出
WITH ranked_employees AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY age ASC
        ) AS rn
    FROM employees
)
SELECT 
    department,
    name,
    age
FROM ranked_employees 
WHERE rn = 1;

実行結果例：

department | name   | age
-----------|--------|----
営業部     | 青木   | 24
開発部     | 佐藤   | 26
総務部     | 石田   | 23

サブクエリを使った方法

-- サブクエリ版（同じ結果）
SELECT 
    department,
    name,
    age
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY age ASC
        ) AS rn
    FROM employees
) ranked
WHERE rn = 1;

実用例：最新の注文情報を取得

-- 各顧客の最新注文を取得
WITH latest_orders AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
)
SELECT 
    customer_id,
    order_date,
    total_amount,
    status
FROM latest_orders 
WHERE rn = 1;

ランキング機能としての活用

基本的なランキング

-- 成績順のランキングを作成
SELECT 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank,
    student_name,
    score,
    class_name
FROM students;

実行結果例：

rank | student_name | score | class_name
-----|--------------|-------|------------
1    | 山田太郎     | 98    | A組
2    | 佐藤花子     | 95    | B組
3    | 田中一郎     | 92    | A組
4    | 鈴木美咲     | 90    | C組

クラス別ランキング

-- クラス内でのランキングを作成
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY class_name 
        ORDER BY score DESC
    ) AS class_rank,
    student_name,
    score,
    class_name
FROM students;

売上ランキングの実用例

-- 四半期別の営業成績ランキング
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY QUARTER(sales_date)
        ORDER BY total_sales DESC
    ) AS quarterly_rank,
    QUARTER(sales_date) AS quarter,
    salesperson_name,
    total_sales
FROM (
    SELECT 
        sales_date,
        salesperson_name,
        SUM(amount) AS total_sales
    FROM sales
    WHERE YEAR(sales_date) = 2023
    GROUP BY sales_date, salesperson_name
) quarterly_sales;

重複データからの最新1件抽出

よくある重複データの問題

例：ユーザーログテーブル

user_id | action    | updated_at
--------|-----------|--------------------
1       | login     | 2023-06-01 09:00:00
1       | view_page | 2023-06-01 09:15:00
1       | logout    | 2023-06-01 09:30:00
2       | login     | 2023-06-01 10:00:00
2       | logout    | 2023-06-01 10:45:00

最新レコードの抽出

-- 各ユーザーの最新アクションを取得
WITH latest_actions AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY updated_at DESC
        ) AS rn
    FROM user_logs
)
SELECT 
    user_id,
    action,
    updated_at
FROM latest_actions 
WHERE rn = 1;

実行結果：

user_id | action | updated_at
--------|--------|--------------------
1       | logout | 2023-06-01 09:30:00
2       | logout | 2023-06-01 10:45:00

重複データの削除

-- 重複データを削除（最新のみ残す）
DELETE FROM user_logs
WHERE (user_id, updated_at) NOT IN (
    SELECT user_id, updated_at
    FROM (
        SELECT 
            user_id,
            updated_at,
            ROW_NUMBER() OVER (
                PARTITION BY user_id 
                ORDER BY updated_at DESC
            ) AS rn
        FROM user_logs
    ) ranked
    WHERE rn = 1
);

他のRANKING関数との違い

ROW_NUMBER vs RANK vs DENSE_RANK

テストデータ：

name   | score
-------|------
田中   | 95
佐藤   | 90
山田   | 90
鈴木   | 85

各関数の結果比較

SELECT 
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
    RANK() OVER (ORDER BY score DESC) AS rank_func,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

実行結果：

name | score | row_number | rank_func | dense_rank
-----|-------|------------|-----------|------------
田中 | 95    | 1          | 1         | 1
佐藤 | 90    | 2          | 2         | 2
山田 | 90    | 3          | 2         | 2
鈴木 | 85    | 4          | 4         | 3

各関数の特徴

関数重複時の扱い特徴使用場面
ROW_NUMBER()毎行に連番重複があっても異なる番号一意の識別子が必要
RANK()同順位でスキップ1,2,2,4…一般的なランキング
DENSE_RANK()同順位でも連番1,2,2,3…順位の飛びを避けたい

使い分けの指針

ROW_NUMBER()を使う場面

  • 一意の行番号が必要
  • 重複データの除去
  • ページネーション
  • データの並び順確認

RANK()を使う場面

  • 一般的なランキング表示
  • 競技の順位表
  • 成績の順位付け

DENSE_RANK()を使う場面

  • 順位の飛びを避けたいランキング
  • カテゴリ分類
  • レベル分け

実践的な応用例

応用例1：ページネーション

-- 1ページ20件で3ページ目のデータを取得
WITH numbered_products AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY product_name) AS row_num
    FROM products
    WHERE active = 1
)
SELECT 
    product_id,
    product_name,
    price
FROM numbered_products
WHERE row_num BETWEEN 41 AND 60;  -- 3ページ目（41〜60件目）

応用例2：N番目のデータ取得

-- 各カテゴリの売上2位の商品を取得
WITH ranked_products AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY total_sales DESC
        ) AS sales_rank
    FROM product_sales
)
SELECT 
    category,
    product_name,
    total_sales
FROM ranked_products
WHERE sales_rank = 2;

応用例3：時系列データの間引き

-- 1時間ごとのデータから1日1件を抽出
WITH daily_samples AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(recorded_at)
            ORDER BY ABS(HOUR(recorded_at) - 12)  -- 12時に最も近い時刻
        ) AS daily_rank
    FROM sensor_data
    WHERE recorded_at >= '2023-01-01'
)
SELECT 
    DATE(recorded_at) AS date,
    recorded_at,
    temperature,
    humidity
FROM daily_samples
WHERE daily_rank = 1;

応用例4：重複チェックと品質管理

-- 重複データの確認
WITH duplicate_check AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY created_at ASC
        ) AS occurrence
    FROM users
)
SELECT 
    email,
    COUNT(*) AS total_occurrences,
    MIN(created_at) AS first_created,
    MAX(created_at) AS last_created
FROM duplicate_check
GROUP BY email
HAVING COUNT(*) > 1;

パフォーマンスの考慮事項

インデックスの重要性

効率的なクエリ

-- ORDER BY句の列にインデックスがある場合（高速）
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date DESC
    ) AS rn,
    *
FROM orders;

非効率なクエリ

-- 計算結果でORDER BYする場合（低速）
SELECT 
    ROW_NUMBER() OVER (
        ORDER BY price * quantity DESC  -- 計算結果のため遅い
    ) AS rn,
    *
FROM order_items;

大量データでの注意点

メモリ使用量の考慮

-- 大量データの場合はWHERE条件で事前に絞り込む
WITH filtered_data AS (
    SELECT *
    FROM large_table
    WHERE created_at >= '2023-01-01'  -- 事前に絞り込み
      AND status = 'active'
),
numbered_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
    FROM filtered_data
)
SELECT * FROM numbered_data WHERE rn <= 100;

よくあるエラーと対処法

エラー1：ORDER BYの省略

-- ❌ エラー：ORDER BYが必須
SELECT 
    ROW_NUMBER() OVER (PARTITION BY department) AS rn,
    name
FROM employees;

-- ✅ 正解：ORDER BYを追加
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY employee_id
    ) AS rn,
    name
FROM employees;

エラー2：ウィンドウ関数をWHEREで直接使用

-- ❌ エラー：ウィンドウ関数はWHEREで直接使えない
SELECT *
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY age) = 1;

-- ✅ 正解：サブクエリまたはWITH句を使用
WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY age) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn = 1;

エラー3：GROUP BYとの混在

-- ❌ エラー：集計関数とウィンドウ関数の混在
SELECT 
    department,
    COUNT(*),
    ROW_NUMBER() OVER (ORDER BY COUNT(*)) AS rn
FROM employees
GROUP BY department;

-- ✅ 正解：段階的に処理
WITH dept_counts AS (
    SELECT 
        department,
        COUNT(*) AS emp_count
    FROM employees
    GROUP BY department
)
SELECT 
    department,
    emp_count,
    ROW_NUMBER() OVER (ORDER BY emp_count DESC) AS rn
FROM dept_counts;

データベース別の対応状況

主要データベースの対応

データベースROW_NUMBER対応バージョン備考
MySQL○8.0以降8.0未満では変数を使用
PostgreSQL○8.4以降古くから対応
SQL Server○2005以降豊富なウィンドウ関数
Oracle○8i以降ROWNUM との使い分け
SQLite○3.25以降比較的新しい機能

MySQL 8.0未満での代替方法

-- MySQL 5.7以前での行番号付け
SELECT 
    @row_number := @row_number + 1 AS row_num,
    name,
    age
FROM users, (SELECT @row_number := 0) AS r
ORDER BY age;

まとめ：ROW_NUMBERを使いこなすために

基本パターンの整理

用途SQL構文のポイント使用場面
全体に行番号ROW_NUMBER() OVER (ORDER BY 列)データ確認、デバッグ
グループ別1件取得PARTITION BY グループ列 + WHERE rn=1最新データ、代表値取得
ランキング作成ORDER BY スコア DESC成績表、売上順位
重複除去PARTITION BY キー ORDER BY 日時 DESCデータクリーニング
ページングWHERE row_num BETWEEN 開始 AND 終了Web画面表示

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました