SQLでデータを分析する際に「各グループ内での順位付け」や「重複排除」「トップN取得」など、細かな要件を実現したい場面が多くあります。特にWINDOW関数のROW_NUMBERは、そうした要件を満たす強力なツールです。この記事では「SQL WINDOW関数 ROW_NUMBER 使い方」というテーマを軸に、構文・実践例・他のランキング関数との違い・パフォーマンスの注意点などを詳しく解説します。実務で役立つ最新情報を含め理解を深めていきましょう。
SQL WINDOW関数 ROW_NUMBER 使い方とは何か
SQLのWINDOW関数は集計せずに行ごとの計算を可能にする機能であり、その中でROW_NUMBERは各行に一意の番号を付与する関数です。使い方をきちんと理解することで、グループごとの順位付け、重複レコードの選別、ページネーションなど、多くの場面で有効に使えます。ここではROW_NUMBERの基本構文や特徴、最新環境でのサポート状況を取り上げます。
ROW_NUMBERの構文とOVER句の組み立て方
ROW_NUMBERは以下のような構文で使います。PARTITION BYでグループを区切り、ORDER BYで順位付けの基準を決めます。ORDER BYは必須で、複数の列を組み合わせて曖昧さを防ぐことが推奨されます。
[ PARTITION BY カラム … ]
ORDER BY カラム … [ASC|DESC]
) AS row_num,
…FROM テーブル;
PARTITION BYを省略すると全行をひとつのグループとみなします。ORDER BY内で同値がある場合には順位付けが不安定になる可能性があるため、追加の列で安定さを確保することが大切です。
ROW_NUMBERが持つ特徴と利点
ROW_NUMBERの最大の特徴は「必ず一意」という点です。同じ値があっても、各行に異なる番号が割り振られ、重複があっても区別されます。これによりトップ1をグループごとにとったり、重複レコードを1つだけ残す用途に最適です。またORDERおよびPARTITIONの指定が柔軟で、さまざまな分析シナリオに対応可能です。
最新のSQL環境でのサポート状況
最新の主要なデータベース（SQL Server、PostgreSQL、MySQL 8以降、Oracle、SQLiteなど）ではROW_NUMBERが正式にサポートされています。構文やオーバーヘッドに関する最適化が継続的に改善されており、特に大型データやクラウド環境での処理ではパフォーマンスを考慮する必要があります。最新情報では、OVER句のフレーム指定やPARTITION粒度の調整がパフォーマンスに大きく影響することがわかっています。
実践例で学ぶROW_NUMBERの使い方
ここでは具体的なコード例を通じてROW_NUMBERの使い方を見ていきます。ビジネスでよくある課題である「グループ内トップレコード取得」「重複排除」「ページネーション」の3つを中心に解説します。最新SQL構文に準拠した正確な例です。
グループ内で最も売上の高い商品を1つ取得する方法
例えば商品カテゴリごとに最も売上が高い商品を抽出したいとき、ROW_NUMBERを使うことで簡潔に実現できます。PARTITION BYでカテゴリごとに区分けし、ORDER BYで売上の降順を指定。ROW_NUMBERが1のものをフィルタすることでグループトップを取得できます。
SELECT
category_id,
product_name,
sales_amount,
ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY sales_amount DESC) AS rn
FROM products
)
SELECT category_id, product_name, sales_amount
FROM ranked_products
WHERE rn = 1;
重複データの中で最新のレコードだけを残す方法
顧客IDなどで重複しているレコードがあり、その中で最新更新日のものだけを残したい場合。ROW_NUMBERを使って更新日時で区分けし、各グループで最上位（rn=1）を残して他を排除する設計が一般的です。CTEを使うと読みやすく管理しやすくなります。
ページネーション（特定範囲の行を取得する）の用途
ページネーションでは「○○番目から○○番目まで」を取得するためにROW_NUMBERを活用します。ORDER BYでソートし、ROW_NUMBERによって順序を付与した後、WHERE句で必要な番号の範囲を指定します。結果を安定化させるために複数のORDER BY列を組み合わせるとよいでしょう。
ROW_NUMBER vs RANK vs DENSE_RANK の違いと使い分け
順位付けに関してはROW_NUMBER以外にもRANKとDENSE_RANKという選択肢があります。これらはごく似ていますが、重複値（同率順位）がある場合の挙動が異なります。正しい関数を選ぶことで要求仕様に合った結果が得られます。ここでは最新情報に基づき、違いと使い分けを明確にします。
重複がある場合の順位付けの振る舞い比較
| 関数 | 重複値の扱い | 次の順位番号 |
|---|---|---|
| ROW_NUMBER() | 重複があっても一意な番号を振る | 連続 |
| RANK() | 重複に同じ順位を与える | ギャップあり |
| DENSE_RANK() | 重複に同じ順位を与える | ギャップなし |
例えば同じスコアを持つ2行があるとき、ROW_NUMBERでは別々に順位が振られ、RANKやDENSE_RANKでは同じ順位になります。ただしRANKはその後順位に飛びが生じ、DENSE_RANKは飛ばさずに次の順位が続きます。
どの関数を使うべきかの判断基準
目的によって、どのランキング関数を使うかが変わります。以下のような観点で選択してください：
- グループ内で1件だけ取得したいならROW_NUMBERが最適
- 同率順位を認識させたい報告書やリーダーボードならRANK
- 順位のギャップを避けたい場合はDENSE_RANK
例：複数の関数を同時に使って比較するシーン
複数関数を同時に使って比較することで、それぞれの違いを可視化できます。例えば売上ランキングなどでROW_NUMBERと他の関数を並べて取得し、それぞれの値を比較しながら仕様を決めるとミスが減ります。このような比較の理解が実務上役立ちます。
パフォーマンスと注意点
ROW_NUMBERを用いる際には、動作効率や安定性に関する注意点があります。特に大規模データやクラウドデータベースを扱うケースでは、構成や索引設計、ORDER BY句の選び方などがパフォーマンスに大きく影響します。ここではよくある落とし穴とその回避策を挙げます。
ORDER BY列の選定と一意性の確保
ORDER BYで指定する列が同値になるとROW_NUMBERの順序が非決定的となる可能性があります。実務ではタイブレーク用の列（例えば主キーなど）を付け加えることで順序の安定性を確保することが推奨されます。これを適切に設計しないと、毎回異なる結果が出ることがあります。
PARTITION BYによるデータ分割の影響とリソース消費
PARTITION BYを使うことでデータをグループ化できますが、グループ数が多かったり各グループが非常に大きいとメモリ使用量や中間ソートのコストが上がります。クエリオプティマイザの選択肢やインデックス設計によっては劇的に差が出るため、実行計画を確認して必要なら列の絞り込みやパーティション設計の見直しを行ってください。
大規模データでの実行時間と最適化戦略
テーブルやビューが巨大な場合、ROW_NUMBERを伴うクエリはソート処理やグループごとの処理が重くなります。以下の戦略が有効です：
- インデックスをORDER BY列とPARTITION BY列に合わせて設計する
- 必要な列のみ選択し、サブクエリやCTEで中間結果を絞る
- クラウドや分散処理環境なら並列処理を活用する
- トップNを取得するなど限定的にROW_NUMBERを使うパターンを前提とする
その他のWINDOW関数との組み合わせ活用術
ROW_NUMBERだけでも強力ですが、他のWINDOW関数と組み合わせることでさらに表現の幅が広がります。集計・遅延関数・ウィンドウフレームの制御などを交えて使いこなすことで、より高度な分析が可能です。ここでは代表的な組み合わせ例を紹介します。
LEAD／LAGとの併用で時系列分析を強化する
LEADやLAGは前後の行を参照するウィンドウ関数で、履歴の差分や前後の変化を捉えるのに役立ちます。これらとROW_NUMBERを組み合わせると、グループ内の経過順序を番号で確認しつつ、直前の値との差異や次の値との差異を容易に算出できます。履歴や順序に意味があるデータ分析で非常に有効です。
集計関数との併用でローリング集計・累積集計を実現
SUMやAVG、COUNTなどの集計関数にOVER句を付けてウィンドウフレームを指定することで、累積値や移動平均を出せます。ROW_NUMBERとこれらを使うと、「ある行がグループ内で何番目なのか」と「その行までの累積売上」などを同時に表示でき、データのトレンド把握に役立ちます。
ウィンドウフレーム（ROWS／RANGE）の制御
OVER句にはフレーム指定（ROWS BETWEEN／RANGE BETWEEN）を使って、計算対象の範囲を細かく制御できます。ROW_NUMBER自体は現在行までの番号を付与するだけですが、他の関数を含める際にこの制御が重要です。最新SQLではこれらフレームの挙動が標準化されつつあり、多くのDBで一致する挙動を確認できます。
よくある疑問とQ&A
実践でROW_NUMBERを使うときに出てくる典型的な質問をまとめ、それぞれに答えます。理解の定着と応用範囲拡大に役立ててください。
順位が毎回変わるのはなぜか
ORDER BY指定の列に同値が多いと、ROW_NUMBERはその中で任意の順序を割り当てるため実行ごとに変動する可能性があります。これを防ぐには、主キーなどの一意な列を追加でORDER BYに含めて順序を明確にすることが重要です。
PARTITION BYを省略したらどうなるか
PARTITION BYを省略すると全行がひとまとまりとして扱われ、ROW_NUMBERは全体に対して連続した番号を振ります。これはグループ別ではなく「全体順序」を付けたいときに使いますが、意図しない結果になることもあるため明示的に指定することが望まれます。
ROW_NUMBERで削除や更新を伴う場合の注意点
ROW_NUMBERを使って重複の古いレコードを削除したり最新だけを残したりする際は、サブクエリか共通テーブル式（CTE）を使うと読みやすく安全です。また、DELETEやUPDATE文と組み合わせるときは実行結果を事前にSELECTで検証するなど慎重さが求められます。
まとめ
WINDOW関数の中でもROW_NUMBERは順位付け・重複排除・ページネーションなどで中心的な役割を持つ強力な道具です。PARTITION BYとORDER BYの使い方を理解し、他のランキング関数との違いを踏まえて適切な関数を選べば、分析要件をクリアに満たすSQLを書くことができます。
パフォーマンス面ではORDER BY列の一意性確保やインデックス活用、GROUPの粒度の管理がポイントになります。さらにLEAD/LAGやウィンドウフレームとの併用により、より高度な分析が可能です。
この記事の内容を理解し実践すれば、SQLで順位付け関連の課題に自信を持って対応できるようになります。ぜひ自身の環境で例を試して応用力を磨いてください。
コメント