【PostgreSQL】ROW_NUMBERの使い方 — 初心者向け実践ガイド

PostgreSQLでROW_NUMBERの使い方に悩んでいませんか？

データの順位付けや重複削除、ページングを実装したいけれど、どう書けば良いか迷うことは多いです。

よくある詰まりポイントは次の通りです。

結論から言うと、ROW_NUMBERはPostgreSQLで行の順位付けや重複排除、ページングを簡潔に実装できる強力なツールです。

有効な解決方法は次の3つです。

この記事を読むと、PostgreSQLのROW_NUMBERの基礎から実践的な使い方、注意点までを理解できます。

サンプルSQLも豊富にあるので、すぐに自分のDBで試せます。

ROW_NUMBERとは何か

ROW_NUMBERはSQLのウィンドウ関数の一つです。

各行に一意の連番を付与するために使います。

使い方の要点はシンプルです。

OVER句の中でORDER BYを指定すると、行ごとに1から始まる番号が割り振られます。

基本概念の説明

ROW_NUMBER()はテーブル全体、あるいはグループごとに番号を振ります。

PARTITION BYを使うとグループ単位で再び1から番号が始まります。

ORDER BYの書き方によって同順位の扱いが変わります。

ROW_NUMBERは同順位でも一意な番号を割り当てます（RANKやDENSE_RANKとは異なります）。

基礎的な使い方（実例付き）

まずは基本的な例で使い方を確認します。

最も単純なのは全行に連番を振るケースです。

SELECT
  id,
  name,
  ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
FROM users;

このクエリはcreated_atの降順に基づいて

各行にrnという連番を付与します。

次はグループごとに連番を振る例です。

部門ごとに社員を順位付けしたいときに有用です。

SELECT
  department_id,
  employee_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

PARTITION BY department_idで部門単位に分割し、

それぞれの部門の中で給料の高い順に1,2,3…と番号を付けます。

実践例1：重複行の削除（データクレンジング）

データベースに重複レコードが存在する場合、ROW_NUMBERで簡単に1行だけ残して削除できます。

基本の流れはROW_NUMBERでグループごとに順序を付け、不要な行を削除することです。

よくあるケースとして、同一のemailが複数レコードに存在する場面を考えます。

最新レコードだけを残す例を示します。

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

この手順は安全に重複を除外できます。

注意点としてはDELETEを実行する前にSELECTでrnの分布を確認することです。

実践例2：ページング（OFFSETの代替）

大きなテーブルをページングする場合、OFFSET … LIMITは性能問題を起こすことがあります。

ROW_NUMBERを使ってより柔軟にページングを実装できます。

例えば、キーセットページングの代わりにROW_NUMBERを使ったページ選択を行う方法です。

以下はページングのサンプルです。

WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
  FROM posts
)
SELECT * FROM numbered
WHERE rn BETWEEN 51 AND 100;

この方法は一度番号付けをした結果に対して範囲指定できるため、

複雑なソートやフィルタと組み合わせやすい利点があります。

ROW_NUMBERとRANK、DENSE_RANKの違い

同じ順位付けでもROW_NUMBER、RANK、DENSE_RANKは振る舞いが異なります。

それぞれの違いを理解することが重要です。

• ROW_NUMBER：常に一意の連番を振る。同順位があっても連続番号になる。
• RANK：同順位があると次の順位が飛ぶ（ギャップができる）。
• DENSE_RANK：同順位があっても順位は詰めて付けられる。

例えば得点で同点がある場合、順位の付け方を要件に応じて選んでください。

ROW_NUMBERは重複排除によく使われますが、順位付け表示にはRANKやDENSE_RANKが適切な場合もあります。

パフォーマンスと注意点

ROW_NUMBERは便利ですが、使い方次第でパフォーマンスに影響します。

特に大規模テーブルでORDER BYを含む場合は注意が必要です。

考慮すべきポイントは以下です。

・ORDER BYに使う列にインデックスを張ると速度改善につながることがある。

また、PARTITION BYで多くのグループができるとメモリやソート負荷が増えます。

必要に応じてクエリを分割することも検討しましょう。

大量データでページングする場合、キーセットページング（最後のキーを基準にする方法）を優先することも有効です。

ROW_NUMBERは一時的な番号付けが必要な場面に適しています。

実務でのよくあるユースケース

ROW_NUMBERは以下のような場面で頻繁に使われます。

例えば、イベントログから最新の状態だけを抽出する場合にも便利です。

JOINやフィルタと組み合わせて1行だけ抜き出す用途が典型です。

WITH latest_per_user AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC) AS rn
  FROM events
)
SELECT * FROM latest_per_user WHERE rn = 1;

このようにして各ユーザーについて最新イベントのみを効率的に取得できます。

単純で分かりやすいためメンテナンス性も高いです。

よくある誤解とその対処法

よくある誤解として、ROW_NUMBERが常に決まった順序で同じ結果を返すと思い込む点があります。

ORDER BYで指定した列に同値があると、どの行に小さい番号が付くかは不定です。

対策としてはORDER BYに複数列を指定することです。

例えば主キーを最後に追加して順序を決定すると再現性が保たれます。

ROW_NUMBER() OVER (ORDER BY score DESC, created_at DESC, id ASC)

このように明示的な順位基準を設けることで予期せぬ挙動を回避できます。

特に重複削除の際は重要です。

まとめ

PostgreSQLのROW_NUMBERは、順位付け、重複排除、ページングに有効なウィンドウ関数です。

使い方を押さえれば日常的なクエリで大きな力を発揮します。

ポイントを振り返ると次の通りです。

この記事の例を参考に、まずは小さなテーブルで試してみてください。

手を動かしてみることで理解が深まります。

さらに詳しいサンプルや応用が必要であれば、具体的なテーブル定義や要件を教えてください。

実務向けの最適なクエリを一緒に考えます。