【SQL基礎】ウィンドウ関数を使用してみよう

本項は実際にウィンドウ関数のRANK、DENSE_RANK、ROW_NUMBER関数を使用して動きを見ていきます。

RANK、DENSE_RANK、ROW_NUMBER関数の処理の違い

RANK関数は、その名の通り、パーティション内のランキングを算出する関数です。
実際に下図のテーブルを使用して、各教科ごとに高得点順のランキングを出してみましょう

SELECT *, RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS ranking 
FROM score;

まずはじめに、今回は教科ごとにランキング付けをしたいので「PARTITION BY」で「subject」を範囲指定しています。
さらに、高得点順にランク付けしたいので「ORDER BY」で「score」を「DESC（降順）」にランク付けするように指定しています。
このように「PARTITION BY」は「GROUP BY」句と同様に指定した範囲内の同じ値のものでグループ分けしてくれます。
ただし、「GROUP BY」句のように1行には集約しません。
「ORDER BY」は、クエリ全体の順序を指定する「ORDER BY」句と同様にASC（昇順）又はDESC（降順）を指定でき、省略した場合はASCとなります。
今回は高得点順にしたいのでDESC（降順）を指定しています。

さて、国語や数学をみると、高得点順に1、2、3、4とランクがカウントされているのが確認できますね。
ただし、他の教科はそのようにはなっておらず、同じ番号が重複している部分があります。
特に、英語などは、1が3つ重複した後に4に飛んでますね。
このように、RANK関数は、同点のものがあった場合は同じランクとし、ランク番号は連続しません。
さらに、英語のように、同点の行の分だけ次のランク番号が飛びランク間にギャップが生じます。
このギャップをなくしたい場合は、DENSE_RANK関数を使用します。
また、ランクとは少し違いますが、完全に一意な連番を付けたい場合は、ROW_NUMBER関数を使用します。
ROW_NUMBER関数は、パーティション内の行番号を表示する関数なので一意な連番となるわけです。

実際に三つを並べてみて違いを確認してみましょう。

SELECT *,
RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS ranking,
DENSE_RANK() OVER(PARTITION BY subject ORDER BY score DESC) AS dense_ranking,
ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS row_num
FROM score;

先程の例の通り、赤枠で囲んだ英語の部分で確認してみると、説明通りDENSE_RANK関数ではランク間がギャップが無くなって
1、1、1、2となっていることが確認できるかと思います。
ROW_NUMBER関数も同点かどうか関係なく、上から順に1、2、3、4となっていますね。

名前付きウィンドウ

先程の、RANK、DENSE_RANK、ROW_NUMBERを三つ並べたSQLを見てみるとOVER句に全く同じものが指定されています。
この部分は、WINDOW句を使って名前付きウィンドウを定義してあげるとすっきりと記述することが可能になります。

SELECT *,
RANK() OVER w AS ranking,
DENSE_RANK() OVER w AS dense_ranking,
ROW_NUMBER() OVER w AS row_num
FROM score
WINDOW w AS (PARTITION BY subject ORDER BY score DESC);

だいぶすっきりしましたね。こうすると見た目がすっきりするだけでなく、WINDOW句の部分を修正するだけで全体に変更をかけることができます。
また、WINDOW句やOVER句でウィンドウ定義に使用していないプロパティとウィンドウ定義は併用することが可能です。
試しに、1つのWINDOW定義で「PARTITION BY」を定義しそれぞれで「ORDER BY」を併用してみましょう。

SELECT *,
RANK() OVER (w1 ORDER BY score) AS ranking,
DENSE_RANK() OVER w2 AS dense_ranking
FROM score
WINDOW w1 AS (PARTITION BY subject), w2 AS (w1 ORDER BY score);

それぞれ、昇順でランキングされていることが確認できました。

今回の学習ポイント

・「PARTITION BY」では、GROUP BY句のようにグループ分けできる。ただし、1行にはまとめない
・「ORDER BY」で、順序付けのしかたを指定できる
・ギャップ有りのランキングはRANK関数、ギャップ無しのランキングはDENSE_RANK関数を使う
・WINDOW句を使用すると、OVER句の繰り返し部分を簡潔に記述できる

練習問題

問題として使用するデータは「SQL練習問題」のものを使用しています。
以下からダウンロード可能です。
また、ダウンロードしたら以下のようにしてworldcup2014データベースを作成し、インポートしてください。
※worldcup2014.zip解凍してできるworldcup2014.sqlファイルをCドライブ直下に配置した場合。

worldcup2014.zip;

[sql] CREATE DATABASE worldcup2014;

use worldcup2014

source C:\worldcup2014.sql
[/sql]

１．RANK関数を使用し、出場選手のポジション(players.position)ごとの高身長順(players.height)のランキングを表示しさない

答え

[sql] SELECT *, RANK() OVER(PARTITION BY position ORDER BY height DESC) AS ranking
FROM players
[/sql]