PostgreSQL ROW_NUMBER()完全ガイド！連番付けから重複削除まで【実践例満載】

「結果に連番を付けたい…」「グループごとに1から番号を振りたい…」「重複データから1件だけ抽出したい…」

こんな要望を一発で解決するのがROW_NUMBER()ウィンドウ関数です！

ROW_NUMBER()は、単純な連番付けだけでなく、重複削除、ランキング作成、効率的なページングなど、様々な場面で活躍する万能関数。しかも、サブクエリを使わずに高速処理が可能！

この記事では、ROW_NUMBER()の基本から、ビジネスで即使える実践テクニックまで、すべてお伝えします。

ROW_NUMBER()の基本：連番を付ける魔法の関数
1. 🎯 ROW_NUMBER()とは？
2. 📝 最も簡単な使用例
PARTITION BY：グループごとの連番付け
1. 🔄 部門ごとに1から番号を振る
2. 📊 複数列でのパーティション
実践活用例1：重複データから最新の1件を取得
1. 🎯 ユーザーごとの最新ログイン履歴
2. 🗑️ 重複データの削除
実践活用例2：効率的なページング処理
1. 📄 LIMIT/OFFSETより高速なページング
2. 🎨 検索結果の表示用連番
実践活用例3：Top-N分析
1. 🏆 各カテゴリのTop3商品
2. 📈 月次売上の前月比較
ROW_NUMBER() vs 他のウィンドウ関数
1. 🆚 似た関数との違い
パフォーマンス最適化のコツ
1. ⚡ インデックスを活用した高速化
2. 🔧 メモリ設定の調整
よくある間違いと対処法
実用的なユースケース集
1. 📊 データ品質チェック
2. 🔄 ローテーション割り当て
まとめ：ROW_NUMBER()をマスターしよう！

ROW_NUMBER()の基本：連番を付ける魔法の関数

🎯 ROW_NUMBER()とは？

基本構文

ROW_NUMBER() OVER (
    [PARTITION BY 列名]  -- グループ分け（省略可）
    ORDER BY 列名        -- 並び順（必須）
)

特徴：

結果セットの各行に一意の連番を付与
1から始まる整数を返す
同じ値でも異なる番号を付ける（重要！）

📝 最も簡単な使用例

全体に連番を付ける

-- サンプルテーブル
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INTEGER,
    hire_date DATE
);

-- 基本的な連番付け
SELECT 
    ROW_NUMBER() OVER (ORDER BY id) AS row_num,
    id,
    name,
    department,
    salary
FROM employees
ORDER BY id;

-- 結果例：
-- row_num | id | name     | department | salary
-- 1       | 1  | 田中太郎 | 営業部     | 450000
-- 2       | 2  | 佐藤花子 | 開発部     | 520000
-- 3       | 3  | 鈴木一郎 | 営業部     | 480000

給与順でランキング

SELECT 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank,
    name,
    department,
    salary
FROM employees;

-- 結果：給与の高い順に番号が付く
-- salary_rank | name     | department | salary
-- 1           | 山田部長 | 経営企画   | 800000
-- 2           | 佐藤花子 | 開発部     | 520000
-- 3           | 鈴木一郎 | 営業部     | 480000

PARTITION BY：グループごとの連番付け

🔄 部門ごとに1から番号を振る

-- 部門ごとの連番
SELECT 
    department,
    name,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS dept_rank
FROM employees
ORDER BY department, dept_rank;

-- 結果：
-- department | name     | salary | dept_rank
-- 営業部     | 高橋次郎 | 550000 | 1
-- 営業部     | 鈴木一郎 | 480000 | 2
-- 営業部     | 田中太郎 | 450000 | 3
-- 開発部     | 山田五郎 | 600000 | 1
-- 開発部     | 佐藤花子 | 520000 | 2
-- 開発部     | 伊藤三郎 | 500000 | 3

📊 複数列でのパーティション

-- 部門×年度ごとの売上ランキング
SELECT 
    department,
    EXTRACT(YEAR FROM sale_date) AS year,
    salesperson,
    total_sales,
    ROW_NUMBER() OVER (
        PARTITION BY department, EXTRACT(YEAR FROM sale_date)
        ORDER BY total_sales DESC
    ) AS yearly_dept_rank
FROM sales_records;

-- 結果：部門と年度の組み合わせごとに1から番号が振られる

実践活用例1：重複データから最新の1件を取得

🎯 ユーザーごとの最新ログイン履歴

-- ログインテーブル
CREATE TABLE login_history (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    login_time TIMESTAMP,
    ip_address VARCHAR(15),
    device VARCHAR(50)
);

-- 各ユーザーの最新ログインのみ取得
WITH ranked_logins AS (
    SELECT 
        user_id,
        login_time,
        ip_address,
        device,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY login_time DESC
        ) AS rn
    FROM login_history
)
SELECT 
    user_id,
    login_time,
    ip_address,
    device
FROM ranked_logins
WHERE rn = 1;

-- これで各ユーザーの最新ログイン1件だけが取得できる！

🗑️ 重複データの削除

-- 重複レコードを特定して削除
WITH duplicates AS (
    SELECT 
        id,
        email,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY created_at DESC  -- 最新を残す
        ) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (
    SELECT id 
    FROM duplicates 
    WHERE rn > 1  -- 2番目以降を削除
);

-- 削除前に確認したい場合
WITH duplicates AS (
    SELECT 
        id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY email 
            ORDER BY created_at DESC
        ) AS rn
    FROM users
)
SELECT * FROM duplicates WHERE rn > 1;  -- 削除対象を表示

実践活用例2：効率的なページング処理

📄 LIMIT/OFFSETより高速なページング

-- 従来のLIMIT/OFFSET（遅い）
SELECT * FROM large_table 
ORDER BY id 
LIMIT 20 OFFSET 10000;  -- 10000件スキップは重い

-- ROW_NUMBER()を使った高速ページング
WITH numbered_rows AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM large_table
)
SELECT * FROM numbered_rows
WHERE rn BETWEEN 10001 AND 10020;  -- 直接範囲指定

-- さらに最適化：インデックスを活用
WITH numbered_rows AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM large_table
    WHERE id > 10000  -- インデックスでフィルタ
)
SELECT lt.* 
FROM numbered_rows nr
JOIN large_table lt ON lt.id = nr.id
WHERE nr.rn <= 20;

🎨 検索結果の表示用連番

-- 検索結果に表示用の番号を付ける
WITH search_results AS (
    SELECT 
        product_id,
        product_name,
        price,
        category
    FROM products
    WHERE product_name ILIKE '%スマホ%'
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY price) AS no,
    product_name,
    TO_CHAR(price, 'FM999,999円') AS price_formatted,
    category
FROM search_results
ORDER BY price;

-- 結果：
-- no | product_name        | price_formatted | category
-- 1  | エントリースマホA   | 29,800円        | スマートフォン
-- 2  | ミドルレンジスマホB | 49,800円        | スマートフォン
-- 3  | ハイエンドスマホC   | 128,000円       | スマートフォン

実践活用例3：Top-N分析

🏆 各カテゴリのTop3商品

-- 各カテゴリで売上Top3の商品を取得
WITH ranked_products AS (
    SELECT 
        category,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY total_sales DESC
        ) AS category_rank
    FROM product_sales
)
SELECT 
    category,
    category_rank,
    product_name,
    TO_CHAR(total_sales, 'FM999,999,999') AS sales
FROM ranked_products
WHERE category_rank <= 3
ORDER BY category, category_rank;

-- 結果：
-- category | category_rank | product_name | sales
-- 家電     | 1            | 冷蔵庫A      | 15,234,000
-- 家電     | 2            | エアコンB    | 12,456,000
-- 家電     | 3            | 洗濯機C      | 9,876,000
-- 食品     | 1            | お米10kg     | 8,234,000
-- 食品     | 2            | 牛肉セット   | 6,543,000
-- 食品     | 3            | 野菜セット   | 5,432,000

📈 月次売上の前月比較

-- 月次売上に連番を付けて前月と比較
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS total_sales,
        ROW_NUMBER() OVER (ORDER BY DATE_TRUNC('month', sale_date)) AS month_num
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
),
sales_with_prev AS (
    SELECT 
        m1.month,
        m1.total_sales AS current_sales,
        m2.total_sales AS prev_sales,
        ROUND(
            (m1.total_sales - m2.total_sales) * 100.0 / m2.total_sales, 
            2
        ) AS growth_rate
    FROM monthly_sales m1
    LEFT JOIN monthly_sales m2 
        ON m1.month_num = m2.month_num + 1
)
SELECT 
    TO_CHAR(month, 'YYYY年MM月') AS 年月,
    TO_CHAR(current_sales, 'FM999,999,999円') AS 当月売上,
    TO_CHAR(prev_sales, 'FM999,999,999円') AS 前月売上,
    growth_rate || '%' AS 成長率
FROM sales_with_prev
ORDER BY month;

ROW_NUMBER() vs 他のウィンドウ関数

🆚 似た関数との違い

-- サンプルデータ（同じ給与の人がいる）
WITH sample AS (
    SELECT * FROM (VALUES
        ('A', 100000),
        ('B', 90000),
        ('C', 90000),  -- Bと同じ給与
        ('D', 80000)
    ) AS t(name, salary)
)
SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
    NTILE(2) OVER (ORDER BY salary DESC) AS ntile_2
FROM sample;

-- 結果：
-- name | salary | row_number | rank | dense_rank | ntile_2
-- A    | 100000 | 1          | 1    | 1          | 1
-- B    | 90000  | 2          | 2    | 2          | 1
-- C    | 90000  | 3          | 2    | 2          | 2
-- D    | 80000  | 4          | 4    | 3          | 2

使い分けのポイント：

ROW_NUMBER()：必ず連番（同順位なし）
RANK()：同順位あり、次の順位は飛ぶ
DENSE_RANK()：同順位あり、次の順位は飛ばない
NTILE(n)：n個のグループに均等分割

パフォーマンス最適化のコツ

⚡ インデックスを活用した高速化

-- ORDER BY列にインデックスを作成
CREATE INDEX idx_employees_salary ON employees(salary DESC);
CREATE INDEX idx_employees_dept_salary ON employees(department, salary DESC);

-- PARTITION BY + ORDER BY の組み合わせにインデックス
CREATE INDEX idx_sales_dept_date ON sales(department, sale_date DESC);

-- 実行計画で確認
EXPLAIN (ANALYZE, BUFFERS) 
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS rank,
    *
FROM employees;

🔧 メモリ設定の調整

-- ウィンドウ関数用のメモリを増やす
SET work_mem = '256MB';  -- セッション単位

-- 大量データの場合
SET work_mem = '1GB';

-- 並列処理を有効化（PostgreSQL 9.6+）
SET max_parallel_workers_per_gather = 4;

よくある間違いと対処法

❌ 間違い1：WHERE句でウィンドウ関数を使う

-- ❌ エラーになる
SELECT * FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary) <= 10;

-- ✅ 正解：CTEまたはサブクエリを使用
WITH numbered AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT * FROM numbered WHERE rn <= 10;

❌ 間違い2：ORDER BYなしで使用

-- ❌ エラー：ORDER BY は必須
SELECT ROW_NUMBER() OVER () FROM employees;

-- ✅ 正解：意味のある順序を指定
SELECT ROW_NUMBER() OVER (ORDER BY id) FROM employees;

-- ✅ 順序を気にしない場合
SELECT ROW_NUMBER() OVER (ORDER BY 1) FROM employees;

❌ 間違い3：グループ化との混同

-- ❌ 間違い：GROUP BYとウィンドウ関数の混同
SELECT 
    department,
    COUNT(*),
    ROW_NUMBER() OVER (ORDER BY COUNT(*))
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 rank
FROM dept_counts;

実用的なユースケース集

📊 データ品質チェック

-- 連続性チェック（欠番検出）
WITH numbered_ids AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (ORDER BY id) AS expected_seq
    FROM transactions
)
SELECT 
    id,
    expected_seq,
    id - expected_seq AS gap
FROM numbered_ids
WHERE id != expected_seq;  -- 欠番がある箇所

🔄 ローテーション割り当て

-- 担当者を順番に割り当て
WITH tasks_numbered AS (
    SELECT 
        task_id,
        ROW_NUMBER() OVER (ORDER BY created_at) AS task_num
    FROM unassigned_tasks
),
staff_numbered AS (
    SELECT 
        staff_id,
        ROW_NUMBER() OVER (ORDER BY staff_id) AS staff_num
    FROM available_staff
)
SELECT 
    t.task_id,
    s.staff_id AS assigned_to
FROM tasks_numbered t
JOIN staff_numbered s 
    ON (t.task_num - 1) % (SELECT COUNT(*) FROM available_staff) + 1 = s.staff_num;

まとめ：ROW_NUMBER()をマスターしよう！

ROW_NUMBER()は、PostgreSQLで最も便利なウィンドウ関数の一つです。

重要ポイント：

基本構文を覚える：ROW_NUMBER() OVER (ORDER BY 列)
PARTITION BYでグループ化：部門別、カテゴリ別の連番
重複削除に活用：最新の1件だけ残す
効率的なページング：LIMIT/OFFSETより高速
Top-N分析：各グループの上位N件を取得

黄金パターン：

WITH numbered AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY グループ列 
            ORDER BY ソート列
        ) AS rn
    FROM テーブル
)
SELECT * FROM numbered WHERE rn = 1;  -- または rn <= N

この記事のテクニックを使えば、複雑なデータ処理も簡潔に書けるようになります！

ROW_NUMBER()で、SQLをもっとパワフルに！ 🚀📊