RANK関数とは
RANK関数は、クエリのデータセットに順位付けする関数のことです。結果セットに新たな列として「順位」の列を追加してくれます。
例えば、ある高校でのテスト結果についての次のようなデータセットを考えてみましょう。
| 生徒ID | 科目 | 点数 |
|---|---|---|
| １ | 数学 | 95 |
| ２ | 英語 | 63 |
| ３ | 数学 | 77 |
| ４ | 数学 | 77 |
| ５ | 英語 | 83 |
このデータセットに、「点数」カラムで降順で順位付けをします。
すると、「科目」には関係なく、点数のみによって順位がつけられることになります。
| 生徒ID | 科目 | 点数 | RANK |
|---|---|---|---|
| １ | 数学 | 95 | 1 |
| ２ | 数学 | 63 | 5 |
| ３ | 数学 | 77 | 3 |
| ４ | 英語 | 77 | 3 |
| ５ | 英語 | 83 | 2 |
点数が高い順に順位付けされ、「RANK」のカラムが追加されています。
生徒ID「3」と「4」の生徒に注目してみると、両方とも77点と同点ですね。そして、「RANK」はどちらも3となっています。
RANK関数を使った場合、同じスコアに対しては同じ順位が割り当てられます。DENSE_RANK関数とも同じです。これは後ほど紹介するROW_NUMBER関数とは異なる特徴です。
※ちなみに、データ表示を並べ替えて一位、二位…と順番に表示したい場合は、こちらの記事を参考にしてください。今回は順位付けの基準(降順)は指定していますが、データ表示の並べ替えの指示は行っていないので、初めの表そのままの順番となっています。
PARTITION BY句について
RANK関数において、グループ化してそのグループごとに順位をつけたいときに用いるのが、“PARTITION BY”句です。
「○○(カテゴリ)」ごとに順位付け」の「○○」を指定する句になります。
先ほどの例では、「数学」と「英語」の点数が混合していました。
| 生徒ID | 科目 | 点数 | RANK |
|---|---|---|---|
| １ | 数学 | 95 | 1 |
| ２ | 数学 | 63 | 5 |
| ３ | 数学 | 77 | 3 |
| ４ | 英語 | 77 | 3 |
| ５ | 英語 | 83 | 2 |
「数学」と「英語」を別々で順位付けしたいときは、“PARTITION BY”句で「科目」カラムを指定します。
先ほどと同じように降順で順位をつけると、以下のようになります。
| 生徒ID | 科目 | 点数 | RANK |
|---|---|---|---|
| １ | 数学 | 95 | 1 |
| ２ | 数学 | 63 | 3 |
| ３ | 数学 | 77 | 2 |
| ４ | 英語 | 77 | 2 |
| ５ | 英語 | 83 | 1 |
今回はデータの表示順を変えていないので見づらいですが…「数学」「英語」の科目ごとに順位がつけられました。このように、“PARTITION BY”句は部門ごとの分析を行う際に非常に便利なものです。
DENSE_RANK関数との違い
RANK関数との違いは、同列順位の後に対する順位のつけかたです。例えば、RANK関数で順位を付けた先ほどの例では、このようになっていました。(科目によって分けない順位)
| 生徒ID | 科目 | 点数 | RANK |
|---|---|---|---|
| １ | 数学 | 95 | 1 |
| ２ | 数学 | 63 | 5 |
| ３ | 数学 | 77 | 3 |
| ４ | 英語 | 77 | 3 |
| ５ | 英語 | 83 | 2 |
点数が同じであるID「3」と「4」の生徒は、同列3位という順位がつけられていて、その下のID「2」の生徒には、4位を飛ばして5位がつけられています。
これがDENSE_RANK関数であった場合は、以下のようになります。
| 生徒ID | 科目 | 点数 | RANK |
|---|---|---|---|
| １ | 数学 | 95 | 1 |
| ２ | 数学 | 63 | 4 |
| ３ | 数学 | 77 | 3 |
| ４ | 英語 | 77 | 3 |
| ５ | 英語 | 83 | 2 |
同列3位の下のID「2」の生徒が、4位という順位をつけられています。DENSE_RANK関数の場合は、同列順位の後も次の順位が連番で続きます。
RANK関数であれば、同列順位の個数に応じて次の順位がスキップされます。
ROW_NUMBER関数との違い
RANK関数とROW_NUMBER関数の大きな違いは、同じスコアの列に対して同一の順位をつけるか、同じスコアの列に対しても個別の番号を割り振るかです。
上述したように、RANK関数においては、同じスコアの列に対して同一の順位を付けます。
一方、ROW_NUMBER関数では同じスコアの列に対しても個別の番号を割り振るため、番号が重複することはありません。
設定方法
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
【流れ】
①テーブル選択
②フィールド選択
③RANK関数の設定１：関数の選択
④RANK関数の設定２：PARTITION BY句の設定
⑤RANK関数の設定３：ORDER BYの設定
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
仮に、商品カテゴリごとに商品価格が安い順に商品をランキング形式で表示したいとします。
※説明内画像は、例題で使用しているものと同じデータセットを使っています。
①商品テーブル(ec_sample_jp.item)を選択、②必要なフィールド(商品カテゴリitem_category、商品名item_name、価格price_with_tax)を抽出します。
次に、③RANK関数の設定です。フィールド選択欄の右のプルダウンから「関数」モードを設定し、RANK関数を選択します。
RANK関数の設定①
「OVER句＋」ボタンを押すと、④PARTITION BY句の設定、⑤ORDER BYの設定を行うボタンが出現します。
今回は商品カテゴリごとの価格の安い順のランキングなので、PARTITON BY句は商品カテゴリ、ORDER BYは商品価格で昇順に設定します。
RANK関数の設定②
クエリを実行すると、以下のような結果が出現します。(「結果プレビュー」では最初の10件のみ表示)
カテゴリごとに、商品が安い順にランキングできたことがわかります。
