はじめに
MySQL8.0がリリースされたことにより、Window関数の利用が可能になりました。
Oracle、PostgreSQL、SQL Server等でも利用できる関数ですが、「難しそう」、「どういう場合に使えば良いかわからない」というイメージを持たれている方もいると思いますので、この記事ではシンプルにどのようなことが出来るのかを紹介していきます。
過去の記事でもWindow関数について分かりやすく記載しておりますので、確認されていない方は是非こちらの記事もご参照ください。
・MySQL8.0のWindow関数を分かりやすく紹介する（１.「集計関数×Window関数」のコラボ）
今回は第二弾として「順位・連番の付け方（基本編）」というお題で記載します。
Window関数の中でも利用頻度が高い関数を3つほど利用し、基本的な使い方、考え方を紹介していきますので、この機会に是非覚えていただけると幸いです。
データ準備
以下のように、好きな食べ物の情報と投票数(votes)を管理する検証用テーブルを作成します。
もし「好きな食べ物が見当たらない！」、「この投票数はちょっと納得できない！！」と思われた方は、お手元の環境で自由にデータを登録頂いた後に、後ほど紹介する方法を実施して結果の出力ができるとスッキリすると思います。是非テストデータの作成から拘って試してみてください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
mysql> #Create Table mysql> CREATE TABLE favorite_food -> ( -> food_name VARCHAR(50), -> food_kana VARCHAR(50), -> votes INT -> ); Query OK, 0 rows affected (0.05 sec) mysql> #Data Insert mysql> INSERT INTO favorite_food(food_name, food_kana, votes) VALUES -> ( 'Sushi', 'Sushi', 6158), -> ( 'Grilled Meat', 'Yakiniku', 4717), -> ( 'Fried Chicken', 'Karaage', 3335), -> ( 'French Fries', 'Fried Potato', 2669), -> ( 'Ice Cream', 'Aisukuri-mu', 4717), -> ( 'Hamburger Steak', 'Hanba-gu', 2669), -> ( 'Steak', 'Sute-ki', 1735), -> ( 'Curry Rice', 'Kare-Raisu', 1500), -> ( 'Gyoza', 'Gyoza', 1250), -> ( 'Takoyaki', 'Takoyaki', 1250), -> ( 'Pizza', 'Piza', 1873), -> ( 'Omelette Rice', 'Omuraisu', 997), -> ( 'Okonomiyaki', 'Okonomiyaki', 1382), -> ( 'Hamburger', 'Hanba-ga', 1143), -> ( 'Ramen', 'Ramen', 2178) -> ; Query OK, 15 rows affected (0.01 sec) Records: 15 Duplicates: 0 Warnings: 0 mysql> #confirmation mysql> select * from favorite_food; +-----------------+--------------+-------+ | food_name | food_kana | votes | +-----------------+--------------+-------+ | Sushi | Sushi | 6158 | | Grilled Meat | Yakiniku | 4717 | | Fried Chicken | Karaage | 3335 | | French Fries | Fried Potato | 2669 | | Ice Cream | Aisukuri-mu | 4717 | | Hamburger Steak | Hanba-gu | 2669 | | Steak | Sute-ki | 1735 | | Curry Rice | Kare-Raisu | 1500 | | Gyoza | Gyoza | 1250 | | Takoyaki | Takoyaki | 1250 | | Pizza | Piza | 1873 | | Omelette Rice | Omuraisu | 997 | | Okonomiyaki | Okonomiyaki | 1382 | | Hamburger | Hanba-ga | 1143 | | Ramen | Ramen | 2178 | +-----------------+--------------+-------+ 15 rows in set (0.01 sec) |
「順位を付けたい！」でもSQL文はどう記述するの？
例えば「投票数」に対して順位付けしたい時、どのようにSQL文を記述すべきでしょうか？
実はWindow関数を利用する/しない場合、どちらのケースでも記述することができます。また順位付けの考え方（Window関数の種類）は大きく分けて２つありますので、ケース毎に紹介します。
ケース1：RANK関数で順位付け
1つ目のケースとして（1位、2位、2位、4位・・・）のように、
同順位に対して同じ番号をつけ、次の順位（3位）はスキップしたい場合です。
この場合は以下のようにWindow関数のRANK関数が利用できます。
ポイントとしてはOVER句の後ろにORDER BY votes DESCと指定している点です。このORDER BY句を記述することによって、votesの降順で並び替えた「順位」が表示できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> SELECT -> * -> , RANK() OVER ( ORDER BY votes DESC) AS ranking -> FROM -> favorite_food main -> ORDER BY -> ranking -> ; +-----------------+--------------+-------+---------+ | food_name | food_kana | votes | ranking | +-----------------+--------------+-------+---------+ | Sushi | Sushi | 6158 | 1 | | Grilled Meat | Yakiniku | 4717 | 2 | | Ice Cream | Aisukuri-mu | 4717 | 2 | | Fried Chicken | Karaage | 3335 | 4 | | French Fries | Fried Potato | 2669 | 5 | | Hamburger Steak | Hanba-gu | 2669 | 5 | | Ramen | Ramen | 2178 | 7 | | Pizza | Piza | 1873 | 8 | | Steak | Sute-ki | 1735 | 9 | | Curry Rice | Kare-Raisu | 1500 | 10 | | Okonomiyaki | Okonomiyaki | 1382 | 11 | | Gyoza | Gyoza | 1250 | 12 | | Takoyaki | Takoyaki | 1250 | 12 | | Hamburger | Hanba-ga | 1143 | 14 | | Omelette Rice | Omuraisu | 997 | 15 | +-----------------+--------------+-------+---------+ 15 rows in set (0.00 sec) |
もしRANK関数を利用しない場合は、以下のようにSELECT句にサブクエリを記述するような方法があります。MySQL8.0より前のバージョンでは「ずっと試行錯誤していた・・・」という方もいると思いますので、この機会にRANK関数を利用してみてください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> SELECT -> * -> , (SELECT count(sub.votes) -> FROM favorite_food sub -> WHERE main.votes < sub.votes) + 1 AS ranking -> FROM -> favorite_food main -> ORDER BY -> ranking -> ; +-----------------+--------------+-------+---------+ | food_name | food_kana | votes | ranking | +-----------------+--------------+-------+---------+ | Sushi | Sushi | 6158 | 1 | | Grilled Meat | Yakiniku | 4717 | 2 | | Ice Cream | Aisukuri-mu | 4717 | 2 | | Fried Chicken | Karaage | 3335 | 4 | | French Fries | Fried Potato | 2669 | 5 | | Hamburger Steak | Hanba-gu | 2669 | 5 | | Ramen | Ramen | 2178 | 7 | | Pizza | Piza | 1873 | 8 | | Steak | Sute-ki | 1735 | 9 | | Curry Rice | Kare-Raisu | 1500 | 10 | | Okonomiyaki | Okonomiyaki | 1382 | 11 | | Gyoza | Gyoza | 1250 | 12 | | Takoyaki | Takoyaki | 1250 | 12 | | Hamburger | Hanba-ga | 1143 | 14 | | Omelette Rice | Omuraisu | 997 | 15 | +-----------------+--------------+-------+---------+ 15 rows in set (0.00 sec) |
ケース2：DENSE_RANK関数で順位付け
2つ目のケースとして（1位、2位、2位、3位・・・）のように、
同順位に対して同じ番号をつけ、次の順位（3位）はスキップしない場合です。
以下のようにWindow関数のDENSE_RANK関数が利用でき、ORDER BY votes DESCと指定している点、考え方はケース1のRANK関数と全く同じです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> SELECT -> * -> , DENSE_RANK() OVER ( ORDER BY votes DESC) AS ranking -> FROM -> favorite_food main -> ORDER BY -> ranking -> ; +-----------------+--------------+-------+---------+ | food_name | food_kana | votes | ranking | +-----------------+--------------+-------+---------+ | Sushi | Sushi | 6158 | 1 | | Grilled Meat | Yakiniku | 4717 | 2 | | Ice Cream | Aisukuri-mu | 4717 | 2 | | Fried Chicken | Karaage | 3335 | 3 | | French Fries | Fried Potato | 2669 | 4 | | Hamburger Steak | Hanba-gu | 2669 | 4 | | Ramen | Ramen | 2178 | 5 | | Pizza | Piza | 1873 | 6 | | Steak | Sute-ki | 1735 | 7 | | Curry Rice | Kare-Raisu | 1500 | 8 | | Okonomiyaki | Okonomiyaki | 1382 | 9 | | Gyoza | Gyoza | 1250 | 10 | | Takoyaki | Takoyaki | 1250 | 10 | | Hamburger | Hanba-ga | 1143 | 11 | | Omelette Rice | Omuraisu | 997 | 12 | +-----------------+--------------+-------+---------+ 15 rows in set (0.00 sec) |
次にDENSE_RANK関数を利用しない場合は、やはり以下のようにSELECT句にサブクエリを記述して頑張っていくような方法があります。distinctを使用することで、votesの重複データを除外した上で、件数をカウントしていることがポイントになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
mysql> SELECT -> * -> , (SELECT count(distinct sub.votes) -> FROM favorite_food sub -> WHERE main.votes < sub.votes) + 1 AS ranking -> FROM -> favorite_food main -> ORDER BY -> ranking -> ; +-----------------+--------------+-------+---------+ | food_name | food_kana | votes | ranking | +-----------------+--------------+-------+---------+ | Sushi | Sushi | 6158 | 1 | | Grilled Meat | Yakiniku | 4717 | 2 | | Ice Cream | Aisukuri-mu | 4717 | 2 | | Fried Chicken | Karaage | 3335 | 3 | | French Fries | Fried Potato | 2669 | 4 | | Hamburger Steak | Hanba-gu | 2669 | 4 | | Ramen | Ramen | 2178 | 5 | | Pizza | Piza | 1873 | 6 | | Steak | Sute-ki | 1735 | 7 | | Curry Rice | Kare-Raisu | 1500 | 8 | | Okonomiyaki | Okonomiyaki | 1382 | 9 | | Gyoza | Gyoza | 1250 | 10 | | Takoyaki | Takoyaki | 1250 | 10 | | Hamburger | Hanba-ga | 1143 | 11 | | Omelette Rice | Omuraisu | 997 | 12 | +-----------------+--------------+-------+---------+ 15 rows in set (0.00 sec) |
ベスト、ワーストランキングの作成
ここでは「投票数」に対してのベスト、ワーストランキングを作成します。
「ベスト」、「ワースト」の切り替えはORDER BY句の並び順を指定することで制御できます。
以下ではORDER BY votes DESC、ORDER BY votes ASCとすることで、DESC（降順）、ASC（昇順）で並び替えをしていることがわかります。
また前回の記事（MySQL8.0のWindow関数を分かりやすく紹介する（１.「集計関数×Window関数」のコラボ））で紹介した「名前付きWindow」を利用することで、より簡潔なSQL文になっていると思います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
mysql> SELECT -> * -> , RANK() OVER ( b ) AS best_ranking -> , DENSE_RANK() OVER ( b ) AS best_dense_rranking -> , RANK() OVER ( w ) AS worst_ranking -> , DENSE_RANK() OVER ( w ) AS worst_dense_rranking -> FROM -> favorite_food main -> WINDOW b AS (ORDER BY votes DESC) -> , w AS (ORDER BY votes ASC) -> ORDER BY -> best_ranking -> ; +-----------------+--------------+-------+--------------+---------------------+---------------+----------------------+ | food_name | food_kana | votes | best_ranking | best_dense_rranking | worst_ranking | worst_dense_rranking | +-----------------+--------------+-------+--------------+---------------------+---------------+----------------------+ | Sushi | Sushi | 6158 | 1 | 1 | 15 | 12 | | Grilled Meat | Yakiniku | 4717 | 2 | 2 | 13 | 11 | | Ice Cream | Aisukuri-mu | 4717 | 2 | 2 | 13 | 11 | | Fried Chicken | Karaage | 3335 | 4 | 3 | 12 | 10 | | French Fries | Fried Potato | 2669 | 5 | 4 | 10 | 9 | | Hamburger Steak | Hanba-gu | 2669 | 5 | 4 | 10 | 9 | | Ramen | Ramen | 2178 | 7 | 5 | 9 | 8 | | Pizza | Piza | 1873 | 8 | 6 | 8 | 7 | | Steak | Sute-ki | 1735 | 9 | 7 | 7 | 6 | | Curry Rice | Kare-Raisu | 1500 | 10 | 8 | 6 | 5 | | Okonomiyaki | Okonomiyaki | 1382 | 11 | 9 | 5 | 4 | | Gyoza | Gyoza | 1250 | 12 | 10 | 3 | 3 | | Takoyaki | Takoyaki | 1250 | 12 | 10 | 3 | 3 | | Hamburger | Hanba-ga | 1143 | 14 | 11 | 2 | 2 | | Omelette Rice | Omuraisu | 997 | 15 | 12 | 1 | 1 | +-----------------+--------------+-------+--------------+---------------------+---------------+----------------------+ 15 rows in set (0.00 sec) |
「連番を付けたい」→「迷わずROW_NUMBER関数を使おう！」
まず「順位」と「連番」の違いについて、簡単に整理すると以下のようになります。
| 用語 | 説明 |
|---|---|
| 順位 | 同順位に対して同じ番号を割り振ります。 （例．1位、2位、2位、4位・・・） |
| 連番 | 他の行と重複しない一意な番号を割り振ります。（例．1、2、3、4・・・） |
例えば「投票数」に対して、「ROW_NUMBER」、「RANK」、「DENSE_RANK」関数を利用した場合、以下のようにROW_NUMBER関数の結果(serial_number)だけ、きちんと「連番」になっていることがわかります。
「連番」＝「ROW_NUMBER関数」という意識を１つだけ持っていただけると間違いはないですね。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> SELECT -> * -> , ROW_NUMBER() OVER ( v ) AS serial_number -> , RANK() OVER ( v ) AS ranking -> , DENSE_RANK() OVER ( v ) AS dense_ranking -> FROM -> favorite_food main -> WINDOW v AS (ORDER BY votes DESC) -> ORDER BY -> serial_number -> ; +-----------------+--------------+-------+---------------+---------+---------------+ | food_name | food_kana | votes | serial_number | ranking | dense_ranking | +-----------------+--------------+-------+---------------+---------+---------------+ | Sushi | Sushi | 6158 | 1 | 1 | 1 | | Grilled Meat | Yakiniku | 4717 | 2 | 2 | 2 | | Ice Cream | Aisukuri-mu | 4717 | 3 | 2 | 2 | | Fried Chicken | Karaage | 3335 | 4 | 4 | 3 | | French Fries | Fried Potato | 2669 | 5 | 5 | 4 | | Hamburger Steak | Hanba-gu | 2669 | 6 | 5 | 4 | | Ramen | Ramen | 2178 | 7 | 7 | 5 | | Pizza | Piza | 1873 | 8 | 8 | 6 | | Steak | Sute-ki | 1735 | 9 | 9 | 7 | | Curry Rice | Kare-Raisu | 1500 | 10 | 10 | 8 | | Okonomiyaki | Okonomiyaki | 1382 | 11 | 11 | 9 | | Gyoza | Gyoza | 1250 | 12 | 12 | 10 | | Takoyaki | Takoyaki | 1250 | 13 | 12 | 10 | | Hamburger | Hanba-ga | 1143 | 14 | 14 | 11 | | Omelette Rice | Omuraisu | 997 | 15 | 15 | 12 | +-----------------+--------------+-------+---------------+---------+---------------+ 15 rows in set (0.00 sec) |
まとめ
今回はWindow関数を利用して、順位・連番の付け方の基本を紹介しました。
Window関数を利用することでSQL文が簡潔になり、ソース（SQL文）のメンテナンス性も高くなると思いますので、是非興味を持って利用していただけると幸いです。
