ORDER BYが重いときに見るべき実行計画とインデックスの直し方
k.w
SEへの道
【Oracle】RANK・DENSE_RANK・ROW_NUMBERの違い｜同順位・Top N・重複排除で使い分ける
k.w
Contents
スポンサーリンク
結論：3関数は「同順位」と「返したい行数」で選ぶ
RANK、DENSE_RANK、ROW_NUMBERは構文が似ていますが、同じ値が並んだ瞬間に結果の意味が変わります。
最初に「同順位を残すか」「順位に欠番を許すか」「必ず1行ずつ番号を振るか」を決めると、選択を間違えにくくなります。
実務では、関数名を先に決めるより「同値が3件あったとき、何件返して何位と表示するか」を例で確認する方が確実です。
要件定義の段階でこの境界ケースを決めておけば、後からRANKとDENSE_RANKを入れ替えるような手戻りを減らせます。
また、集計結果を別処理へ渡す場合は、順位そのものが業務キーとして使われるのか、単なる表示用なのかも分けて考える必要があります。
さらに、集計結果を後続処理の条件に使う場合は、順位値が欠番になることで閾値判定や件数集計へどのような影響が出るかも確認します。
表示上は同じように見える順位でも、RANKとDENSE_RANKでは「3以下」という条件が選ぶ値の集合を変えるため、SQL単体だけでなく後続ロジックまで含めて仕様を確かめることが大切です。
3関数の違いを最短で整理
ROW_NUMBERは各行へ1、2、3と必ず異なる番号を割り当てるため、同値でも同順位という概念を持ちません。
RANKは同値の行へ同じ順位を与え、その人数ぶん次の順位が飛ぶので、1位、2位、2位、4位のような並びになります。
DENSE_RANKも同値の行へ同じ順位を与えますが、次の順位は連続するため、1位、2位、2位、3位のように欠番がありません。
順位表として「何位か」を表したいならRANK、異なる値の段階を1、2、3と数えたいならDENSE_RANK、行を一意に並べたいならROW_NUMBERという整理が実務では扱いやすいです。
たとえば給与が5000、3000、3000、2500の順なら、RANKは1、2、2、4、DENSE_RANKは1、2、2、3、ROW_NUMBERは1、2、3、4になります。
この違いはテストデータに重複がないと見えないため、ランキングSQLを検証するときは必ず同値データを含めるのが重要です。
選び方は3つの質問で決まる
第1の質問は、同じ評価値の行を同順位として扱いたいかどうかです。
同順位を認めないならROW_NUMBERが候補になり、同順位を認めるならRANKかDENSE_RANKを選びます。
第2の質問は、同順位のあとに順位を飛ばしたいかどうかで、競技順位のように位置を表すならRANK、値の種類の順位を連続番号で表すならDENSE_RANKが合います。
第3の質問は、上位Nを「N行」で切るのか「N位」で切るのかで、ここを曖昧にすると返却件数が想定より増減します。
画面で常に20件だけ表示したいページングはROW_NUMBER寄りですが、売上上位3位の同率店舗を全員表示したい集計はRANKやDENSE_RANK寄りです。
迷ったときの実務基準
重複排除、最新1件抽出、ページングのように「各行を一意に選ぶ」処理ではROW_NUMBERを第一候補にすると設計意図が明確です。
表彰順位や順位表ではRANKが自然で、2位が2人なら次を4位とする競技型の意味を保てます。
価格帯や売上額の異なる値を上から第1グループ、第2グループと数える用途ではDENSE_RANKが読みやすくなります。
関数名より先に業務ルールを文章化し、「同率を残す」「欠番を許す」「件数固定」のどれが必要かをSQLレビューで確認すると、後からの仕様変更にも強くなります。
3関数の動作を同じデータで比較する
違いを正確に理解するには、3関数を別々に試すより同じORDER BYで同時に実行するのが早道です。
同値が2件以上ある小さなデータを使うと、順位の飛び方と連番の違いを一目で確認できます。
比較用SQLでは、3関数のOVER句をできるだけ同じ条件にして差分をTIE処理だけへ絞ると理解しやすくなります。
ただしROW_NUMBERだけは結果の再現性を高めるために一意列を追加するケースがあり、その場合は「順位の意味を比較するSQL」と「実務で安定動作させるSQL」を分けて示すと混乱しません。
検証結果は順位列だけでなく元のORDER BY列も並べて確認します。
サンプルデータの作り方
説明用データにはemployee_id、department_id、salaryの3列があれば十分で、同じ部門に同じsalaryを持つ行を2件以上入れます。
たとえば部門10にA=5000、B=3000、C=3000、D=2500を用意すると、3000の2行がTIE、つまり同値になります。
この小さな例なら、業務データの複雑さに邪魔されず順位関数そのものの挙動を確認できます。
検証ではNULLも1件含めると、後でNULLS FIRSTまたはNULLS LASTを指定したときの違いまで同じデータで確認できます。
3関数を同時に実行する
SQL例：SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk, ROW_NUMBER() OVER (ORDER BY salary DESC, employee_id) AS row_num FROM emp。
3列を横に並べると、同じsalaryに対してRANKとDENSE_RANKは同じ値を返し、ROW_NUMBERだけが異なる番号を振ることが確認できます。
さらに次のsalaryを見ると、RANKだけ順位が飛び、DENSE_RANKは連続するため、3関数の差が1回の実行で把握できます。
ROW_NUMBERのORDER BYへemployee_idを加えているのは、salaryが同じときも並び順を一意にするためです。
表示順まで確認したい場合は分析関数内のORDER BYだけに頼らず、SELECT文の最後にもORDER BY salary DESC, employee_idのような最終表示順を指定します。
TIEの本質はORDER BYの同値
同順位になるかどうかは行全体が同じかではなく、各分析関数のORDER BYに指定した式が同じ値になるかで決まります。
ORDER BY salary DESCだけなら給与が同じ行はTIEですが、ORDER BY salary DESC, employee_idをRANKへ指定するとemployee_idが異なるため同順位ではなくなります。
つまりタイブレーク列を追加するとROW_NUMBERの順序は安定しますが、RANKやDENSE_RANKでは同順位を意図的に解消することにもなるため、目的に応じてORDER BY列を分ける必要があります。
順位の意味を残したい場合はRANKやDENSE_RANKをsalaryだけで計算し、最終表示の安定化は外側のORDER BYでemployee_idを追加する方法が分かりやすいです。
OVER句を理解するとランキングSQLが読みやすくなる
3関数の違いだけでなく、OVER句のPARTITION BYとORDER BYを理解すると応用範囲が大きく広がります。
特に「全体順位か部門内順位か」「NULLを上位扱いするか」「同値時の順序を固定するか」をOVER句で明示すると、SQLの意図が伝わりやすくなります。
OVER句はランキング関数の対象範囲と順序を宣言する場所なので、ここを読めばSQLの意図の大半を把握できます。
レビューではSELECT句の関数名だけでなく、PARTITION BYの粒度、ORDER BYの方向、NULLの扱い、タイブレーク列の有無を順番に確認すると見落としを減らせます。
特に複数の分析関数を同じSQLで使う場合、OVER句の差が結果の意味を変える点を意識します。
PARTITION BYは順位をリセットする境界
PARTITION BY department_idを指定すると、部門ごとに別のランキングが作られ、各部門で順位が1から始まります。
PARTITION BYを省略した場合は検索結果全体が1つのパーティションとして扱われるため、全社員の給与順位になります。
部門別Top 3やカテゴリ別最新商品など、グループごとに同じ処理を繰り返したいときにPARTITION BYが中心になります。
複数列で区切りたい場合はPARTITION BY department_id, job_idのように指定でき、部門と職種の組み合わせごとに順位をリセットできます。
ORDER BYは順位の基準を決める
ORDER BY salary DESCなら給与が高い行から1位になり、ASCなら低い行から1位になります。
分析関数内のORDER BYは関数がどの順序で値を評価するかを決めますが、クエリの最終表示順を保証するものではありません。
画面や帳票の並びを確定したいなら、外側のSELECTにも必要なORDER BYを記述するのが安全です。
複数列のORDER BYは優先順位を左から評価するため、ORDER BY score DESC, updated_at DESC, idのように業務上の優先規則をそのまま表現できます。
NULLS FIRSTとNULLS LASTを明示する
Oracleの分析関数では昇順の既定はNULLS LAST、降順の既定はNULLS FIRSTなので、DESCだけを書くとNULLが先頭側に来る点に注意が必要です。
給与や売上のランキングでNULLを未計測として最後へ回したいなら、ORDER BY salary DESC NULLS LASTのように意図を明示すると読み手が迷いません。
NULLの扱いを省略すると、ASCとDESCの変更時に順位の意味が変わりやすいため、業務上重要な列では明示指定が有効です。
NULLを0と同じとみなす要件ならNULLS LASTだけでなくNVLなどで値そのものを変換する設計になるため、NULLの並び替えと値の置換は別の要件として考えます。
ROW_NUMBERは一意なタイブレーク列を加える
ROW_NUMBERはORDER BY値が同じでも各行に異なる番号を付けますが、同値行の処理順が一意でないと番号の割当が実行ごとに変わる可能性があります。
ページングや重複排除でどの1行を残すかが重要なら、updated_at DESC, id DESCのように最後まで一意になる列をORDER BYへ加えます。
主キーや一意キーを最後のタイブレークとして使うと、同じ入力に対して同じ行を選びやすくなります。
日時だけでは同一タイムスタンプが発生し得るため、updated_atだけで「最新1件」を決めず、最後にidなどの一意列を足す設計が実務では安定します。
実務でよく使う4つのパターン
ランキング関数は順位表だけでなく、Top N、最新1件、重複排除、ページングにも使われます。
ここでは関数の性質と業務要件が直接結び付く代表パターンを整理します。
4つのパターンに共通するのは、「順位を表示したい」のではなく「順位を使って行を選びたい」ケースが多いことです。
そのため外側のWHERE相当でrnやrnkを条件にする構造をよく使いますが、分析関数の計算タイミングを考えると同一SELECTのWHEREへ直接書けない場面があり、サブクエリやCTEで段階を分ける設計が基本になります。
可読性を優先するなら中間列へ意味のある別名を付けます。
部門別給与Top 3
部門ごとに上位3人だけを出したいなら、ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, employee_id)を付け、外側でrn <= 3と絞ります。
この書き方は各部門で最大3行にしたい要件に向き、3位と同額の4人目がいても返却件数を固定できます。
一方で同率3位を全員含める要件ならROW_NUMBERではなくRANKやDENSE_RANKを使い、rnk <= 3の意味を確認します。
RANK <= 3は順位として3位以内を返すため欠番の影響を受け、DENSE_RANK <= 3は異なる給与額の上位3段階を返すため、同じ「Top 3」でも件数と意味が異なります。
重複排除で最新1件を残す
同じcustomer_idに複数の履歴がある場合、ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, id DESC) AS rnを付け、rn = 1だけ残す方法が定番です。
この用途では同時刻の履歴が複数あっても必ず1行を選ぶ必要があるため、RANKやDENSE_RANKよりROW_NUMBERが適しています。
削除処理へ応用する場合は、まずSELECTでrn > 1になる行を確認してからDELETE対象へ結び付けると事故を減らせます。
重複の定義がemailだけなのかemailとtenant_idの組み合わせなのかでPARTITION BYが変わるため、SQLを書く前に「同一グループ」を業務ルールとして明文化します。
各カテゴリの最新商品を1件だけ取得
カテゴリ別に最新商品を1件だけ選ぶ場合もROW_NUMBERとPARTITION BYの組み合わせが分かりやすいです。
ORDER BY published_at DESC, product_id DESCとしてrn = 1を選べば、公開日時が同じ商品があっても一意に決められます。
最新日付の商品をすべて返したい要件ならRANKを使う選択肢もあり、「最新1件」と「最新日時の全件」を区別することが重要です。
この違いはAPIレスポンス件数にも影響するため、仕様書では「1カテゴリ1件」なのか「最新時刻の同率を全件」なのかを明示します。
Webページングで範囲を切り出す
ROW_NUMBERで一意な連番を作れば、21〜40番のような範囲を外側で絞り込むページングを実装できます。
ただし現行OracleではOFFSETやFETCHによる行制限も使えるため、新規実装でROW_NUMBERだけに固定する必要はありません。
大切なのはどの方式でもORDER BYを決定的にし、ページをまたいで同じ行が重複表示されたり欠落したりしにくい順序を作ることです。
頻繁に更新される大規模データではOFFSETが深くなるほどコストや表示の揺れが問題になりやすいため、要件によってはキーセットページングも比較対象になります。
Top Nでは「N行」と「N位」を分けて考える
Top 3という言葉は、3行だけ欲しい場合と3位までの全員が欲しい場合で意味が違います。
境界に同値があると返却件数が変わるため、ランキング関数を選ぶ前にNの定義を決める必要があります。
Top Nの仕様は件数制御だけでなく、同率境界の公平性や画面レイアウトにも関わります。
たとえばランキング画面なら同率3位を全員載せたい一方、APIが常に3件を返す契約なら件数固定が優先されるかもしれません。
SQLの正解は業務要件によって変わるため、「Top 3」という曖昧な表現をそのまま実装条件にせず、サンプル入力と期待出力で合意するのが安全です。
返却件数を監視指標や課金単位に使う処理では、同率によって件数が増える可能性を事前に見積もり、API仕様やバッチ上限と矛盾しないかも確認します。
ROW_NUMBERならN行に固定しやすい
ROW_NUMBERを付けてrn <= 3と絞ると、各パーティションで最大3行に固定できます。
同値があっても番号は一意なので、画面のカードを3件に固定したい場合やバッチ対象を100件ずつ切りたい場合に扱いやすいです。
ただし同値のどちらを残すかが業務上重要なら、ORDER BYへ明確なタイブレーク条件を追加しなければなりません。
件数固定は便利ですが、同率3位の片方だけを落とす可能性があるため、ランキング表示では仕様に合っているか確認します。
RANKなら競技順位のN位を表せる
RANK <= 3は競技型の順位で3位以内を返すため、同率2位が複数あると次が4位になり、3位の行自体が存在しない場合があります。
この性質は順位表として自然ですが、「上位3種類の値」を取りたい要件には向きません。
同率を含めた結果件数はNを超えることがあるため、UIや後続処理が可変件数を受け入れられるかも確認が必要です。
順位に欠番があることを利用者が期待している競技結果や評価ランキングなら、DENSE_RANKよりRANKの意味が伝わりやすくなります。
DENSE_RANKなら上位N種類の値を取れる
DENSE_RANK <= 3は異なるORDER BY値の上位3段階を返すため、同率が何人いても次の異なる値を2位、3位と連続で数えます。
たとえば価格の高い上位3価格帯や、売上額の異なる上位3水準を抽出する考え方に向いています。
結果行数は各段階に何行あるかで増えるため、3行だけ欲しい要件には使いません。
RANKとDENSE_RANKの違いは「同率後に順位を飛ばすか」だけですが、Top N条件ではその違いが抽出対象そのものを変えます。
FETCH FIRST WITH TIESとの使い分け
OracleのFETCH FIRST n ROWS WITH TIESは、ORDER BYで指定した境界行と同じソートキーを持つ行を追加で返せます。
単純な全体Top Nなら読みやすい選択肢ですが、部門別Top Nのようにパーティションごとに順位を付けたい場合は分析関数が分かりやすいです。
WITH TIESを使う場合もORDER BYが必要で、どの列までを同順位の判定キーとするかを意識します。
ORDER BY salary DESC, employee_idまで指定するとemployee_idが異なる行はTIEにならないため、同率給与を全員含めたい場合は境界判定の列を設計意図に合わせます。
姉妹分析関数も役割で使い分ける
RANK系を理解すると、NTILE、PERCENT_RANK、CUME_DIST、LAG、LEAD、FIRST_VALUE、LAST_VALUEも読みやすくなります。
これらは順位そのものではなく、分位、相対位置、前後行、端点値を求めるための道具です。
分析関数は似たOVER句を共有するため、構文を覚えるだけでは役割を混同しやすくなります。
順位を付ける、全体を分位へ分ける、前後行を参照する、先頭値や末尾値を参照するという目的ごとに整理すると、関数選びが単純になります。
1つのSQLで複数関数を併用するときは、同じPARTITION BYとORDER BYを再利用できるか確認し、異なる意味の窓を不用意に同一視しないようにします。
NTILEで件数をほぼ均等に分ける
NTILE(4)はORDER BY後の行を4グループへできるだけ均等に分けるため、四分位のような区分を作るときに使えます。
DENSE_RANKは異なる値の順位を付ける関数なので、同値を同じグループへ必ず入れるという意味ではNTILEと同じではありません。
件数を均等化したいのか、値の同一性を保った順位を作りたいのかで使い分けます。
同値が境界にまたがることを許容できない要件では、NTILEをそのまま採用せず順位やパーセンタイルの定義を見直します。
PERCENT_RANKとCUME_DISTで相対位置を見る
PERCENT_RANKは順位を0から1の範囲へ変換して相対的な位置を示し、CUME_DISTは現在値以下または以上に相当する累積分布の位置を表す用途で使われます。
単純な1位、2位の表示ではRANK系の方が分かりやすく、全体の中でどの程度上位かを比率で見たい分析ではこれらが候補になります。
同値があるときの扱いも関数ごとに意味が異なるため、出力値を業務指標へ直接使う前に小さなサンプルで確認します。
割合を画面表示する場合は0〜1を百分率へ変換するなど表示層の責務もあり、SQLの戻り値とUI表現を分けて設計します。
LAGとLEADで前後行を参照する
LAGは同じパーティション内の前の行、LEADは次の行の値を参照できるため、前月比や直前イベントとの差分計算に向いています。
順位を付ける関数ではないため、ROW_NUMBERと組み合わせて順序確認用の連番を表示するとデバッグしやすくなります。
ORDER BYが分析の時間軸になるため、日付が同じ場合の並び順まで必要ならタイブレーク列を追加します。
売上の前日差分ならPARTITION BY店舗、ORDER BY日付のように、比較単位と時間順を分けて考えると設計しやすくなります。
FIRST_VALUEとLAST_VALUEはウィンドウ範囲に注意
FIRST_VALUEとLAST_VALUEは並び順の先頭値や末尾値を参照できますが、LAST_VALUEはウィンドウフレームの既定範囲を意識する必要があります。
パーティション全体の最後の値が欲しい場合は、必要に応じてウィンドウ範囲を明示し、現在行までの範囲になっていないか確認します。
ランキング関数と同じOVER句を使うため見た目は似ていますが、行の順位ではなく参照値を返す点が本質的な違いです。
NULLを無視するか含めるかの指定が結果へ影響する関数もあるため、欠損値をどう扱うかを要件へ含めます。
パフォーマンスはソート量と実行計画で確認する
分析関数は便利ですが、PARTITION BYとORDER BYに応じた並び替えが必要になるため、大量データではソートコストを無視できません。
関数名だけで速さを判断せず、絞り込み、並び替え、索引、実行計画を一緒に確認します。
ランキング処理の性能はデータ件数だけでなく、パーティション数、各パーティションの偏り、ORDER BY列の幅、絞り込み条件によって変わります。
小さな開発データでは問題が出なくても、本番で一部のパーティションだけ極端に大きいとTEMP領域やメモリ使用量が増えることがあります。
平均件数だけでなく最大グループ件数も確認し、代表的な条件で実測することが重要です。
同じSQLでも統計情報やデータ分布の変化で計画が変わるため、定期的な性能確認では実データに近い条件を使い、過去の実行時間だけで安全と判断しないようにします。
先に絞れる条件はWHEREで絞る
分析関数はWHEREなどの後に評価されるため、業務上不要な行を先に除外できれば、順位計算の対象を減らせます。
たとえば当月データだけをランキングするなら、全期間へ順位を付けてから当月を抜くのではなく、意味が同じ範囲で先に期間条件を適用できるか検討します。
ただし絞り込み前後で順位の母集団が変わるため、性能だけを理由にWHERE位置を変えて結果の意味を壊してはいけません。
「全期間順位を計算して当月行だけ表示」と「当月だけで順位付け」は別の問いなので、チューニング前にどちらが要件かを確認します。
インデックスは万能ではない
PARTITION BY列とORDER BY列に関連する索引が役立つ場合はありますが、分析関数のソートが必ず消えるとは限りません。
複合索引の列順、WHERE条件、データ分布、取得列、並列実行などで計画は変わるため、「この索引を作れば必ず速い」と断定しない方が安全です。
本番相当の件数と統計情報で実行計画を確認し、必要ならSQL Monitorや実測時間も併せて比較します。
ランキング列を追加しただけで大きく遅くなった場合は、WINDOW SORTの有無やソート対象行数、TEMP使用量を確認すると原因を切り分けやすくなります。
Top Nは早めに行数を減らせるかを見る
全体Top Nではrow limiting句などにより早い段階で必要行だけ取得できる可能性がありますが、部門別Top Nでは各パーティションの順位計算が必要です。
同じTop 10でも全体10行と100部門それぞれ10行では処理内容が大きく異なるため、SQLの目的を実行計画と対応させます。
返却行数だけでなく、順位計算前に何行読み、何行ソートするかを見ることが重要です。
アプリ側で全件取得してから上位だけ切る方法は通信量も増えるため、データベース側で意味を保ったまま絞れるならその方が扱いやすいケースが多くなります。
決定的ORDER BYは性能より先に正しさを守る
ROW_NUMBERのタイブレーク列を増やすとソートキーは長くなりますが、どの行を残すかが業務上重要なら一意な順序を優先します。
高速でも実行ごとに採用行が変わる重複排除は正しい処理とはいえないため、性能調整は決定性を保った状態で行います。
一意性を保証する列がない場合は、データモデル側で識別子を持つべきかも含めて検討します。
特にページングでは順序が揺れるとページ間の重複や欠落につながるため、速度計測と同時に結果の再現性もテストします。
Rails・Java・Pythonから使うときの考え方
RANK系はOracle SQLの機能なので、Rails、Java、Pythonから使う場合もSQLの意味は同じです。
違うのはSQLの組み立て方と結果列の受け取り方であり、順位ロジックをアプリ側へ重複実装しないことが保守性につながります。
アプリケーションから使う場合は、DB側で確定した順位ロジックを複数言語で再実装しない方が一貫性を保ちやすくなります。
Rails、Java、Pythonの各層で同じランキング規則を別々に持つと、タイブレーク条件やNULL処理がずれる原因になります。
SQLを共通ビューやRepository層へ閉じ込めるか、少なくともテストデータと期待順位を共有して、言語ごとの呼び出し差だけを検証します。
Rails ActiveRecordではSQL断片を限定して使う
ActiveRecordだけで複雑な分析関数を表現しにくい場合は、selectへROW_NUMBER() OVER (…) AS row_numのようなSQL断片を入れる方法があります。
外部入力をORDER BY式へそのまま連結するとSQLインジェクションの危険があるため、並び替え候補はアプリ側で許可リストから選びます。
ビューやスコープとして共通化する場合も、同順位ルールとタイブレーク列をコメントやテストで残すと意図が伝わります。
ページング用途では既存gemのOFFSET方式と独自ROW_NUMBER方式を混在させると順序条件がずれやすいため、どちらが正本かを決めます。
Java JDBCでは別名を明示する
JDBCではSELECT句にrnk、dense_rnk、row_numなどの別名を付けておくとResultSetから値を取り出しやすくなります。
SQLをPreparedStatementで実行し、値条件はバインド変数へ渡すことで文字列連結を減らせます。
ランキングのORDER BY列名を動的変更する場合は列名自体をバインドできないため、許可済みの列だけを分岐で選ぶ設計にします。
Spring系でもRepositoryのネイティブSQLへ分析関数を置けますが、戻り値の型やページング機能との重複をテストします。
Python oracledbではSQLをそのまま活かせる
PythonのoracledbからもRANK、DENSE_RANK、ROW_NUMBERを含むSELECTを通常のSQLとして実行できます。
検索値はバインド変数へ渡し、取得した順位列は整数としてアプリ側の表示や集計へ利用します。
データ分析でpandasへ読み込む場合でも、同順位の意味をSQL側で確定しておくと後段の処理が単純になります。
大量取得ではarraysizeなどドライバ側設定も影響しますが、まずSQLが必要行だけ返しているかを確認してから通信側の調整へ進みます。
よくある失敗とトラブルシューティング
ランキングSQLの不具合は、関数そのものよりORDER BY、PARTITION BY、NULL、Top Nの定義のずれから起こることが多くあります。
結果が正しそうに見える小規模テストだけで終わらせず、同値と境界条件を含むケースで確認します。
トラブル調査では、まず関数を疑うのではなく入力行とOVER句を小さく再現し、同値、NULL、パーティション境界、N位境界の4種類を確認します。
次に外側の絞り込み条件と最終ORDER BYを分離して見ると、順位計算の問題か表示順の問題かを切り分けやすくなります。
性能問題の場合も、正しい結果を保った再現SQLを作ってから実行計画を比較すると、安全に原因を追えます。
ORDER BYが一意でなくROW_NUMBERが揺れる
ROW_NUMBERのORDER BYがsalaryだけで、同じsalaryの行が複数あると、どちらが先の番号になるかは安定しない可能性があります。
重複排除や最新1件抽出で採用行を固定したいなら、最後に主キーなどの一意列を加えて全順序を作ります。
テストでは同じSQLを複数回実行するだけでなく、同値データを増やしてどの行が選ばれても仕様上問題ないかを確認します。
「今は同じ結果が返る」ことと「仕様として順序が保証される」ことは別なので、偶然安定している実行計画へ依存しないようにします。
PARTITION BYを忘れて全体順位になる
部門別順位のつもりでPARTITION BY department_idを忘れると、全社員で1つのランキングが作られます。
結果件数自体はそれらしく見えるため、複数部門のデータを混ぜたテストで各部門が1位から始まるか確認します。
逆に全体順位が欲しい場合はPARTITION BYを入れず、コードレビューで意図的な省略だと分かるようにします。
カテゴリ別、店舗別、顧客別などグループ境界を言語化すると、PARTITION BYに入れる列の漏れを見つけやすくなります。
RANKとDENSE_RANKを取り違える
同値がないデータではRANKとDENSE_RANKの結果が完全に同じになるため、違いを見逃しやすくなります。
最低でも同率1位や同率2位を含むデータを作り、その次の行が何位になるべきかを期待値としてテストします。
上位N条件ではこの取り違えが抽出行そのものを変えるため、関数名だけでなく「欠番あり」「欠番なし」をテスト名へ入れると分かりやすいです。
仕様書に「同順位の次は何位か」の例を1つ載せるだけでも、RANKとDENSE_RANKの認識違いを減らせます。
NULLの位置が想定と違う
DESCの既定ではNULLが先頭側に来るため、未入力値が1位のように見える結果になることがあります。
未入力を最下位へ回したいならNULLS LASTを明示し、NULLを0扱いしたいなら別途値変換の要件を検討します。
NULLS FIRSTまたはNULLS LASTは並び位置を決める指定であり、NULLそのものを別の数値へ変換する機能ではありません。
移行案件では別DB製品の既定NULL順序と異なる場合があるため、明示指定して差を小さくする方法が有効です。
ページングが遅い・重複する
深いページまでOFFSETで進む方式は読み飛ばす行が増えやすく、更新頻度が高いデータではページ間の重複や欠落も起こり得ます。
ROW_NUMBER方式でもORDER BYが不安定なら同じ問題を抱えるため、まず一意な並び順を作ることが先です。
大量データでは実行計画とアクセスパターンを確認し、必要ならキーセットページングなど別方式も比較します。
性能問題を関数の置換だけで解決しようとせず、WHERE条件、索引、ソート、返却件数、通信量を分けて測定します。
FAQ：RANK・DENSE_RANK・ROW_NUMBERの疑問
最後に、実装時に迷いやすい点を短く整理します。
関数の違いだけでなく、ORDER BYの決定性とTop Nの定義まで一緒に確認すると判断しやすくなります。
FAQで共通する判断軸は、同順位の扱い、順位の連続性、結果件数の固定、順序の再現性の4点です。
関数を選ぶときはこの4点を順に確認し、必要なら小さなサンプルで出力を比較します。
特に「ROW_NUMBERは連番だから常に安定している」という誤解と、「RANKとDENSE_RANKは同じ」という誤解は、同値データを入れたテストで簡単に見つけられます。
迷った場合は、期待する出力例を先に書いてから関数を選ぶと判断がぶれません。
RANKとDENSE_RANKの一番大きな違いは？
同順位の次に順位を飛ばすのがRANK、飛ばさず連続させるのがDENSE_RANKです。
1、2、2の次を4にしたいならRANK、3にしたいならDENSE_RANKと覚えると判断しやすくなります。
どちらも同値の行には同じ順位を付けるため、違いはTIEの次の行で現れます。
ROW_NUMBERのTIE順序は保証される？
ORDER BYが全順序を保証していない場合、同値行へどちらの番号が付くかは非決定的になり得ます。
採用行を固定したい処理では主キーなどを追加し、ORDER BYを一意にします。
最終表示順も別途外側のORDER BYで指定すると、計算順と表示順の意図を分離できます。
PARTITION BYを省略するとどうなる？
PARTITION BYを省略すると結果全体が1つのパーティションになり、全体順位や全体連番になります。
部門別やカテゴリ別で順位を1から振り直したい場合だけ、必要なグループ列をPARTITION BYへ指定します。
PARTITION BYはグループごとの順位リセット、ORDER BYはその中での並び順という役割分担です。
Top 3はどの関数を使う？
3行だけ欲しいならROW_NUMBER、競技順位の3位以内を同率込みで出したいならRANK、異なる値の上位3段階を出したいならDENSE_RANKが候補です。
同じTop 3という言葉でも結果件数が変わるため、仕様でN行かN位かを明記します。
全体Top Nで境界と同じソートキーも含めたいだけならFETCH FIRST … WITH TIESも比較できます。
重複排除にはどれを使う？
グループごとに必ず1行だけ残す重複排除にはROW_NUMBERが使いやすく、PARTITION BYで重複単位、ORDER BYで残す優先順位を定義します。
同値を全部残すRANKやDENSE_RANKは、通常の「1件だけ残す」重複排除には適しません。
削除へつなげる前にSELECTでrn = 1とrn > 1を確認し、意図した行が残るかを検証します。
NULLS FIRST／LASTは指定した方がいい？
業務上NULLの位置が重要なら明示する方が安全で、特にDESCでは既定がNULLS FIRSTになる点を意識します。
未入力を最下位にしたいランキングならDESC NULLS LASTのように、SQLだけで意図が分かる記述にします。
既定値に依存しないことで、並び順を変更したときの思わぬ仕様変化も防ぎやすくなります。
まとめ：TIE処理を先に決めれば関数選びは迷わない
3関数の構文を暗記するより、同値が出たときにどう扱うかを先に決める方が実務では役立ちます。
最後に選択基準と確認ポイントを一度に見直せる形で整理します。
最終的には、RANK系の使い分けはSQL文法の問題ではなく、同率をどう扱うかという業務ルールの問題です。
要件をN行、N位、N種類の値という言葉で分け、ROW_NUMBERでは決定的なORDER BYを作り、RANK系では同順位を崩す列を不用意に追加しないようにすれば、多くの実装ミスを避けられます。
運用開始後も同値データが増えることを前提に、境界ケースを回帰テストへ残しておくと安心です。
本番投入前には、同率1位、同率N位、NULL、同一日時、空パーティションに近い小規模グループなどを含むテストケースを用意し、期待順位と返却件数を固定します。
これによりデータ量や実行計画が変わっても、関数選択の意味が保たれているかを継続的に確認できます。
3関数の本質を再確認
ROW_NUMBERは行を一意に番号付けし、RANKは競技型順位、DENSE_RANKは欠番のない段階順位を作る関数です。
同値がないデータでは3関数の差が見えにくいため、同率データを含めたテストを必ず用意します。
PARTITION BYで順位をリセットする単位を決め、ORDER BYで順位基準を決めるという基本を押さえれば、応用パターンも読みやすくなります。
ROW_NUMBERを採用行の決定に使う場合は、最後まで一意になるORDER BYを作ることが最重要の予防策です。
実務チェックリスト
実装前に「N行かN位か」「同率を残すか」「順位を飛ばすか」「NULLをどこへ置くか」「同値時の順序を固定するか」を確認します。
部門別やカテゴリ別ならPARTITION BYの列を確認し、重複排除や最新1件ではROW_NUMBERのタイブレーク列を確認します。
性能面ではソート対象行数と実行計画を確認し、索引の効果を推測だけで決めません。
このチェックを満たしてから関数を選べば、RANK、DENSE_RANK、ROW_NUMBERの使い分けは単なる暗記ではなく業務ルールに基づく判断になります。
