PostgreSQLグループごとに連番を振る完全ガイド：ROW_NUMBERからシーケンスまで徹底解説

「部署ごとに社員番号を1から振り直したい」「カテゴリー別に商品コードを連番で管理したい」「月ごとに注文番号をリセットして採番したい」

データベースを使っていると、こんな要求がよく出てきますよね。

全体で一つの連番なら簡単ですが、グループごとに1から始まる連番を振るのは、ちょっとしたテクニックが必要です。でも、PostgreSQLのウィンドウ関数を使えば、驚くほど簡単に実現できるんです。

この記事では、ROW_NUMBER()関数を中心に、グループごとの連番を振る様々な方法を、実例を交えながら分かりやすく解説します。読み終わる頃には、複雑な採番要求にも対応できるようになっているはずです！

グループごとの連番が必要な場面
1. ビジネスでよくある要求
2. なぜデータベースで連番を管理するの？
サンプルデータの準備
1. テストテーブルの作成
ROW_NUMBER()関数の基本
RANK()とDENSE_RANK()との違い
1. 3つの関数の比較
実践的な連番付与パターン
連番をテーブルに保存する
1. UPDATE文での連番更新
2. トリガーで自動採番
ウィンドウ関数の応用
パフォーマンス最適化
複雑な要件への対応
1. 欠番を埋める連番
2. 階層構造での連番
トラブルシューティング
1. よくあるエラーと対処法
よくある質問と回答
まとめ：適切な方法を選んで効率的な連番管理を

グループごとの連番が必要な場面

ビジネスでよくある要求

実際の使用例：

請求書番号：年度ごとに「2024-001」「2024-002」…
社員コード：部署ごとに「営業部-01」「営業部-02」…
在庫管理番号：倉庫ごとに連番管理
受付番号：日付ごとにリセット
ランキング：カテゴリー内での順位付け

これらはすべて、グループごとの連番で解決できます。

なぜデータベースで連番を管理するの？

メリット：

一意性が保証される
欠番が発生しにくい
自動化できる
履歴管理が容易
同時アクセスに対応

Excelで手動管理するより、はるかに安全で効率的です。

サンプルデータの準備

テストテーブルの作成

まず、実例で使うサンプルデータを用意しましょう。

-- 従業員テーブルの作成
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    hire_date DATE,
    salary INTEGER
);

-- サンプルデータの挿入
INSERT INTO employees (name, department, hire_date, salary) VALUES
-- 営業部
('田中太郎', '営業部', '2023-04-01', 450000),
('鈴木花子', '営業部', '2023-06-15', 420000),
('佐藤次郎', '営業部', '2024-01-10', 380000),
('高橋美咲', '営業部', '2024-03-20', 400000),
-- 開発部
('山田一郎', '開発部', '2022-10-01', 550000),
('伊藤さくら', '開発部', '2023-02-15', 480000),
('渡辺健', '開発部', '2023-08-01', 520000),
('中村優子', '開発部', '2024-04-01', 460000),
-- 総務部
('小林明', '総務部', '2021-04-01', 420000),
('加藤綾', '総務部', '2023-09-01', 380000),
('吉田隆', '総務部', '2024-02-01', 360000);

-- 注文テーブルの作成
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    customer_name VARCHAR(100),
    product VARCHAR(100),
    amount INTEGER,
    status VARCHAR(20)
);

-- 注文データの挿入
INSERT INTO orders (order_date, customer_name, product, amount, status) VALUES
('2024-01-05', 'A社', 'ノートPC', 150000, '完了'),
('2024-01-10', 'B社', 'モニター', 45000, '完了'),
('2024-01-15', 'C社', 'キーボード', 8000, '処理中'),
('2024-02-03', 'D社', 'ノートPC', 180000, '完了'),
('2024-02-08', 'A社', 'マウス', 3500, '完了'),
('2024-02-20', 'E社', 'モニター', 48000, '処理中'),
('2024-03-01', 'B社', 'ノートPC', 160000, '完了'),
('2024-03-10', 'C社', 'マウス', 3000, '完了'),
('2024-03-15', 'F社', 'キーボード', 8500, '処理中');

ROW_NUMBER()関数の基本

シンプルな連番付与

まず、最も基本的な使い方から始めましょう。

-- 部署ごとに連番を振る
SELECT 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS 社員番号,
    department AS 部署,
    name AS 氏名,
    hire_date AS 入社日
FROM employees
ORDER BY department, 社員番号;

結果：

社員番号 | 部署    | 氏名      | 入社日
---------|---------|-----------|------------
1        | 営業部  | 田中太郎  | 2023-04-01
2        | 営業部  | 鈴木花子  | 2023-06-15
3        | 営業部  | 佐藤次郎  | 2024-01-10
4        | 営業部  | 高橋美咲  | 2024-03-20
1        | 開発部  | 山田一郎  | 2022-10-01
2        | 開発部  | 伊藤さくら| 2023-02-15
3        | 開発部  | 渡辺健    | 2023-08-01
4        | 開発部  | 中村優子  | 2024-04-01
1        | 総務部  | 小林明    | 2021-04-01
2        | 総務部  | 加藤綾    | 2023-09-01
3        | 総務部  | 吉田隆    | 2024-02-01

各部署で1から始まる連番が振られています！

PARTITION BY句の詳細

PARTITION BY句で、グループ分けの基準を指定します。

-- 複数の条件でグループ分け
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY department, EXTRACT(YEAR FROM hire_date)
        ORDER BY hire_date
    ) AS 年度別番号,
    department AS 部署,
    EXTRACT(YEAR FROM hire_date) AS 入社年,
    name AS 氏名,
    hire_date AS 入社日
FROM employees
ORDER BY department, 入社年, 年度別番号;

部署と入社年の組み合わせごとに連番が振られます。

ORDER BY句の重要性

ORDER BY句で、連番を振る順序を決定します。

-- 給与の高い順に連番を振る
SELECT 
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS 給与順位,
    department AS 部署,
    name AS 氏名,
    salary AS 給与
FROM employees
ORDER BY department, 給与順位;

RANK()とDENSE_RANK()との違い

3つの関数の比較

同じ値がある場合の動作が異なります。

-- 3つの関数を同時に使って比較
SELECT 
    department AS 部署,
    name AS 氏名,
    salary AS 給与,
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS rank_num,
    DENSE_RANK() OVER w AS dense_rank_num
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY department, salary DESC;

違いの説明：

ROW_NUMBER()：必ず連続した番号（1,2,3,4…）
RANK()：同じ値は同じ順位、次は飛ばす（1,2,2,4…）
DENSE_RANK()：同じ値は同じ順位、次は連続（1,2,2,3…）

実践的な連番付与パターン

日付ごとのリセット番号

-- 日付ごとに注文番号を採番
SELECT 
    TO_CHAR(order_date, 'YYYY-MM-DD') || '-' || 
    LPAD(
        ROW_NUMBER() OVER (
            PARTITION BY order_date 
            ORDER BY id
        )::TEXT, 
        3, 
        '0'
    ) AS 注文番号,
    order_date AS 注文日,
    customer_name AS 顧客名,
    product AS 商品,
    amount AS 金額
FROM orders
ORDER BY order_date, id;

「2024-01-05-001」のような形式の注文番号が生成されます。

月ごとの連番with年またぎ対応

-- 月ごとに請求書番号を採番
WITH monthly_orders AS (
    SELECT 
        *,
        TO_CHAR(order_date, 'YYYY-MM') AS year_month
    FROM orders
)
SELECT 
    year_month || '-' || 
    LPAD(
        ROW_NUMBER() OVER (
            PARTITION BY year_month 
            ORDER BY order_date, id
        )::TEXT, 
        4, 
        '0'
    ) AS 請求書番号,
    order_date AS 日付,
    customer_name AS 顧客,
    amount AS 金額
FROM monthly_orders
ORDER BY year_month, order_date;

条件付き連番

-- ステータスが「完了」の注文のみに連番を振る
SELECT 
    CASE 
        WHEN status = '完了' THEN 
            ROW_NUMBER() OVER (
                PARTITION BY status 
                ORDER BY order_date
            )
        ELSE NULL
    END AS 完了番号,
    order_date AS 日付,
    customer_name AS 顧客,
    product AS 商品,
    status AS ステータス
FROM orders
ORDER BY order_date;

連番をテーブルに保存する

UPDATE文での連番更新

既存のテーブルに連番カラムを追加して更新する方法：

-- 連番用のカラムを追加
ALTER TABLE employees ADD COLUMN emp_number VARCHAR(10);

-- 部署ごとの連番を更新
WITH numbered AS (
    SELECT 
        id,
        department || '-' || 
        LPAD(
            ROW_NUMBER() OVER (
                PARTITION BY department 
                ORDER BY hire_date
            )::TEXT, 
            3, 
            '0'
        ) AS new_number
    FROM employees
)
UPDATE employees e
SET emp_number = n.new_number
FROM numbered n
WHERE e.id = n.id;

-- 結果確認
SELECT emp_number, department, name, hire_date 
FROM employees 
ORDER BY department, emp_number;

トリガーで自動採番

新規挿入時に自動で連番を振る仕組み：

-- 連番管理テーブル
CREATE TABLE sequence_manager (
    table_name VARCHAR(50),
    group_key VARCHAR(100),
    last_number INTEGER,
    PRIMARY KEY (table_name, group_key)
);

-- 採番関数
CREATE OR REPLACE FUNCTION get_next_number(
    p_table_name VARCHAR,
    p_group_key VARCHAR
) RETURNS INTEGER AS $$
DECLARE
    v_next_number INTEGER;
BEGIN
    -- 排他ロックで番号を取得・更新
    UPDATE sequence_manager
    SET last_number = last_number + 1
    WHERE table_name = p_table_name 
      AND group_key = p_group_key
    RETURNING last_number INTO v_next_number;
    
    -- レコードがなければ新規作成
    IF NOT FOUND THEN
        INSERT INTO sequence_manager (table_name, group_key, last_number)
        VALUES (p_table_name, p_group_key, 1)
        ON CONFLICT (table_name, group_key) 
        DO UPDATE SET last_number = sequence_manager.last_number + 1
        RETURNING last_number INTO v_next_number;
    END IF;
    
    RETURN v_next_number;
END;
$$ LANGUAGE plpgsql;

-- 使用例
SELECT get_next_number('orders', '2024-01');  -- 1
SELECT get_next_number('orders', '2024-01');  -- 2
SELECT get_next_number('orders', '2024-02');  -- 1

ウィンドウ関数の応用

累積和と連番の組み合わせ

-- 部署ごとの累積給与と順位
SELECT 
    ROW_NUMBER() OVER w AS 順位,
    department AS 部署,
    name AS 氏名,
    salary AS 給与,
    SUM(salary) OVER (
        PARTITION BY department 
        ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS 累積給与
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY hire_date)
ORDER BY department, 順位;

前後のレコードとの比較

-- 前の従業員との給与差を計算
SELECT 
    ROW_NUMBER() OVER w AS 番号,
    department AS 部署,
    name AS 氏名,
    salary AS 給与,
    salary - LAG(salary, 1, 0) OVER w AS 前との差額,
    LEAD(name, 1, '最後') OVER w AS 次の人
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY hire_date)
ORDER BY department, 番号;

パーセンタイル順位

-- 部署内での給与パーセンタイル
SELECT 
    ROW_NUMBER() OVER w AS 番号,
    department AS 部署,
    name AS 氏名,
    salary AS 給与,
    ROUND(
        PERCENT_RANK() OVER (
            PARTITION BY department 
            ORDER BY salary
        ) * 100, 
        1
    ) AS "パーセンタイル%"
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY department, 番号;

パフォーマンス最適化

インデックスの活用

-- PARTITION BYとORDER BYに使うカラムにインデックス
CREATE INDEX idx_emp_dept_hire ON employees(department, hire_date);
CREATE INDEX idx_emp_dept_salary ON employees(department, salary DESC);

-- 複合インデックスで高速化
CREATE INDEX idx_orders_date_id ON orders(order_date, id);

マテリアライズドビューの活用

-- 連番付きのマテリアライズドビュー
CREATE MATERIALIZED VIEW mv_numbered_employees AS
SELECT 
    id,
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY hire_date
    ) AS emp_seq,
    department,
    name,
    hire_date,
    salary
FROM employees;

-- インデックスの作成
CREATE INDEX idx_mv_emp_dept_seq ON mv_numbered_employees(department, emp_seq);

-- 定期的な更新
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_numbered_employees;

大量データでの考慮事項

-- バッチ処理での連番付与
DO $$
DECLARE
    v_department VARCHAR(50);
BEGIN
    -- 部署ごとに処理
    FOR v_department IN 
        SELECT DISTINCT department FROM employees
    LOOP
        -- 部署単位で連番を更新
        WITH numbered AS (
            SELECT 
                id,
                ROW_NUMBER() OVER (ORDER BY hire_date) AS seq
            FROM employees
            WHERE department = v_department
        )
        UPDATE employees e
        SET emp_number = v_department || '-' || LPAD(n.seq::TEXT, 3, '0')
        FROM numbered n
        WHERE e.id = n.id;
        
        -- コミット（必要に応じて）
        RAISE NOTICE '部署 % の処理完了', v_department;
    END LOOP;
END $$;

複雑な要件への対応

欠番を埋める連番

-- 欠番を検出して埋める
WITH RECURSIVE number_series AS (
    -- 1から最大値までの連番を生成
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1
    FROM number_series
    WHERE num < (SELECT MAX(id) FROM orders)
),
existing_numbers AS (
    -- 既存の番号を取得
    SELECT id FROM orders
)
-- 欠番のリスト
SELECT 
    num AS 欠番,
    'ORDER-' || LPAD(num::TEXT, 5, '0') AS 新規番号
FROM number_series
WHERE num NOT IN (SELECT id FROM existing_numbers)
ORDER BY num
LIMIT 10;

階層構造での連番

-- 階層ごとの連番（組織図など）
WITH RECURSIVE org_tree AS (
    -- ルートレベル
    SELECT 
        id,
        name,
        NULL::INTEGER AS parent_id,
        1 AS level,
        ARRAY[ROW_NUMBER() OVER (ORDER BY name)] AS path
    FROM employees
    WHERE department = '経営層'
    
    UNION ALL
    
    -- 再帰部分
    SELECT 
        e.id,
        e.name,
        ot.id AS parent_id,
        ot.level + 1,
        ot.path || ARRAY[ROW_NUMBER() OVER (
            PARTITION BY ot.id 
            ORDER BY e.name
        )]
    FROM employees e
    JOIN org_tree ot ON e.department != '経営層'
    WHERE ot.level < 3
)
SELECT 
    REPEAT('  ', level - 1) || 
    ARRAY_TO_STRING(path, '.') || ' ' || 
    name AS 階層番号付き名前
FROM org_tree
ORDER BY path;

トラブルシューティング

よくあるエラーと対処法

エラー1：window関数はGROUP BY句では使えない

-- NG: GROUP BYと同時に使用
SELECT department, ROW_NUMBER() OVER (ORDER BY department)
FROM employees
GROUP BY department;  -- エラー！

-- OK: サブクエリで分離
SELECT department, row_num
FROM (
    SELECT 
        department,
        ROW_NUMBER() OVER (ORDER BY department) AS row_num
    FROM employees
) sub
GROUP BY department, row_num;

エラー2：ORDER BYの曖昧さ

-- NG: 同じ値で順序が不定
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary)

-- OK: 一意になるようにする
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary, id)

よくある質問と回答

Q: 連番を後から振り直したい

A: DELETE後も連番を維持したい場合は、ROW_NUMBER()で再計算するか、別テーブルで管理します。

Q: 同時アクセスで重複する

A: シーケンスやADVISORY LOCKを使用して排他制御します：

SELECT pg_advisory_lock(12345);
-- 採番処理
SELECT pg_advisory_unlock(12345);

Q: 文字列での並び順がおかしい

A: 数値を文字列でソートすると「1,10,2」の順になります。LPADで桁数を揃えるか、数値型でソートしてください。

Q: NULLの扱いは？

A: PARTITION BYでNULLは別グループ、ORDER BYでは最後（NULLS FIRST/LASTで制御可能）になります。

Q: パフォーマンスが遅い

A: 適切なインデックス、EXPLAIN ANALYZEでの分析、必要に応じてマテリアライズドビューを検討してください。

まとめ：適切な方法を選んで効率的な連番管理を

PostgreSQLでのグループごとの連番は、ROW_NUMBER()関数で簡単に実現できます。

方法の選び方：

一時的な表示：ROW_NUMBER()をSELECT文で使用
永続的な保存：UPDATE文で更新、またはトリガー
採番管理：専用テーブルとファンクション
高速化が必要：マテリアライズドビュー
同時実行制御：ADVISORY LOCKやシーケンス

ベストプラクティス：

ORDER BYで一意性を保証
適切なインデックスを設定
大量データはバッチ処理
要件に応じて最適な方法を選択

この記事で紹介した技術を使えば、どんな連番要求にも対応できるはずです。シンプルな要件から始めて、徐々に複雑な処理にチャレンジしてみてください。

グループごとの連番は、データ管理の基本中の基本。マスターすれば、業務システムの開発効率が大幅に向上します。さあ、あなたのデータベースでも試してみましょう！