よく使われる順位付け関数 1 - ROW_NUMBER
よく使われる順位付け関数 1 - ROW_NUMBER
前回は 「 よく使われる日付と時刻の関数 2 - DATEADD, DATEDIFF, ISDATE 」 で、 よく使われる日付と時刻の組み込み文字列関数のうち、DATEADD, DATEDIFF, ISDATE を使ってみました。
次は、便利な順位付け関数を見ていきましょう。
こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。
ROW_NUMBER ファンクション
ROW_NUMBER ファンクションは、結果セットにシーケンス番号を振ってくれる関数です。
構文は次の通りです。
ROW_NUMBER ( ) OVER ( [ PARTITION BY [パティションカラム 1 ], [パティションカラム 2], ... ] ORDER BY [ソートカラム 1], [ソートカラム 2], ... )
パティションが指定されていない場合は結果セット全体を 1 から順番に、パティションを指定した場合は各パティション内で 1 から順番に、ORDER BY で指定した順番でシーケンス番号が振られます。
ROW_NUMBER ファンクションは、指定したカラムの値が同じレコードが複数あっても、同じ番号を返すことはありません。
ですので、結果セットに一時的に ID を振りたい時なんかにも便利で、よく使っています。
例えば、次のような Student テーブルがあります。
誕生日の年が古い -> 新しい、男性 -> 女性という順に 1, 2, 3 ... とシーケンス番号を振りたい場合は次のように取得することができます。
SELECT ROW_NUMBER() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS RowNo, * FROM Student;
[ 実行結果 ]
ORDER BY の後ろに、まず YEAR(Birthday) で誕生日の年の部分を取得して降順で、次に性別の降順でソートするように指定しています。
昇順 は ASC、降順は DESC で指定しますが、何も指定しなければ昇順になります。
青で囲まれた学生はどちらも同じ年生まれの女性です。 このように ORDER BY で指定しきれていないレコードの順序は、毎回同じになる保障はありません。
どちらでも良い場合はいいですが、ハッキリと取得したい順序が決まっている場合は、そこまで ORDER BY で指定したほうが良いと思います。
パティション分けして ROW_NUMBER で順位付けする
続いて、パティションと共に ROW_NUMBER を使ってみましょう。
次のような TestResult テーブルがあります。
TestID ごとに、Score の高い人から順位を取得して、1 位の結果のみ取得してみましょう。
SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY Score DESC) AS RowNo, * FROM TestResult) AS T WHERE RowNo = 1;
[ 実行結果 ]
PARTITION BY で TestID カラムを指定して、Score の降順に番号を振り、その結果をサブクエリーとして使って RowNo が 1 のレコードのみを取得しています。
ちなみに、サブクエリーが返す結果は以下の通りです。
SELECT ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY Score DESC) AS RowNo, * FROM TestResult;
[ 実行結果 ]
次は、順位付け関数の RANK と DENSE_RANK を使ってみましょう。