目次
はじめに
MySQL8.0がリリースされたことにより、Window関数の利用が可能になりました。
Oracle、PostgreSQL、SQL Server等でも利用できる関数ですが、「難しそう」、「どういう場合に使えば良いか分からない」というイメージを持たれている方もいると思いますので、この記事ではシンプルにどのようなことができるのかを紹介していきます。
過去の記事でもWindow関数について分かりやすく記載しておりますので、確認されていない方は是非こちらの記事もご参照ください。
・MySQL8.0のWindow関数を分かりやすく紹介する（１.「集計関数×Window関数」のコラボ）
・MySQL8.0のWindow関数を分かりやすく紹介する（２．順位・連番の付け方（基本編））
今回は第三弾として「順位・連番の付け方（発展編）」というお題で記載します。
データ準備
以下のように、好きな食べ物の情報と投票数（votes）を管理する検証用テーブルを作成します。
補足として分類（category）については大まかなデータを登録しています。
例えばラーメン（Ramen）は「中国人シェフによって日本で生み出された料理であるため、純粋な中華料理ではなく日本人のために作られた中華風の料理」のようです。
分類としては、「和食 or 中華」で人によって意見が分かれるかもしれませんが、あまり深く考えずに「和食（Japanese food）」としておりますので、ご了承ください。
|
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 53 |
mysql> #Create Table mysql> CREATE TABLE favorite_food -> ( -> category VARCHAR(50), -> food_name VARCHAR(50), -> food_kana VARCHAR(50), -> votes INT -> ); Query OK, 0 rows affected (0.02 sec) mysql> #Data Insert mysql> INSERT INTO favorite_food(category, food_name, food_kana, votes) VALUES -> ( 'Japanese food','Sushi', 'Sushi', 6158), -> ( 'Meet','Grilled Meat', 'Yakiniku', 4717), -> ( 'Meet','Fried Chicken', 'Karaage', 3335), -> ( 'Japanese food','French Fries', 'Fried Potato', 1130), -> ( 'Dessert','Ice Cream', 'Aisukuri-mu', 4717), -> ( 'Meet','Hamburger Steak', 'Hanba-gu', 2669), -> ( 'Meet','Steak', 'Sute-ki', 1735), -> ( 'Western food','Curry Rice', 'Kare-Raisu', 1500), -> ( 'Chinese food','Gyoza', 'Gyoza', 1382), -> ( 'Japanese food','Takoyaki', 'Takoyaki', 1250), -> ( 'Western food','Pizza', 'Piza', 1873), -> ( 'Japanese food','Omelette Rice', 'Omuraisu', 997), -> ( 'Japanese food','Okonomiyaki', 'Okonomiyaki', 1250), -> ( 'Western food','Hamburger', 'Hanba-ga', 1143), -> ( 'Japanese food','Ramen', 'Ramen', 2178) -> ; Query OK, 15 rows affected (0.01 sec) Records: 15 Duplicates: 0 Warnings: 0 mysql> #confirmation mysql> select * from favorite_food; +---------------+-----------------+--------------+-------+ | category | food_name | food_kana | votes | +---------------+-----------------+--------------+-------+ | Japanese food | Sushi | Sushi | 6158 | | Meet | Grilled Meat | Yakiniku | 4717 | | Meet | Fried Chicken | Karaage | 3335 | | Japanese food | French Fries | Fried Potato | 1130 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | | Meet | Hamburger Steak | Hanba-gu | 2669 | | Meet | Steak | Sute-ki | 1735 | | Western food | Curry Rice | Kare-Raisu | 1500 | | Chinese food | Gyoza | Gyoza | 1382 | | Japanese food | Takoyaki | Takoyaki | 1250 | | Western food | Pizza | Piza | 1873 | | Japanese food | Omelette Rice | Omuraisu | 997 | | Japanese food | Okonomiyaki | Okonomiyaki | 1250 | | Western food | Hamburger | Hanba-ga | 1143 | | Japanese food | Ramen | Ramen | 2178 | +---------------+-----------------+--------------+-------+ 15 rows in set (0.00 sec) |
「グループ単位」で順位を付けてみる
前回の記事でRANK、DENSE_RANK関数を利用した順位付けの方法を紹介しましたが、ワンポイントアレンジして「分類（category）」単位で順位付けする方法を紹介します。
ケース1：RANK関数で順位付け
まずはRANK関数を利用するケースです。
ここでのポイントはPARTITION BY categoryと指定することによって、「分類（category）」単位で順位付けすることができます。
|
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 |
mysql> SELECT -> * -> , RANK() OVER ( PARTITION BY category ORDER BY votes DESC) AS ranking -> FROM -> favorite_food main -> ORDER BY -> category,ranking -> ; +---------------+-----------------+--------------+-------+---------+ | category | food_name | food_kana | votes | ranking | +---------------+-----------------+--------------+-------+---------+ | Chinese food | Gyoza | Gyoza | 1382 | 1 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | 1 | | Japanese food | Sushi | Sushi | 6158 | 1 | | Japanese food | Ramen | Ramen | 2178 | 2 | | Japanese food | Takoyaki | Takoyaki | 1250 | 3 | | Japanese food | Okonomiyaki | Okonomiyaki | 1250 | 3 | | Japanese food | French Fries | Fried Potato | 1130 | 5 | | Japanese food | Omelette Rice | Omuraisu | 997 | 6 | | Meet | Grilled Meat | Yakiniku | 4717 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 3 | | Meet | Steak | Sute-ki | 1735 | 4 | | Western food | Pizza | Piza | 1873 | 1 | | Western food | Curry Rice | Kare-Raisu | 1500 | 2 | | Western food | Hamburger | Hanba-ga | 1143 | 3 | +---------------+-----------------+--------------+-------+---------+ 15 rows in set (0.01 sec) |
SQLの中で「グループ分けの機能」がある句として、「GROUP BY」と「PARTITION BY」の２つがあります。
簡単に機能面の説明をすると、どちらもデータをグループ分けしますが、「GROUP BY」は「（グループ分け後に）重複データを1行にまとめる」という処理が行われます。
もしRANK関数を利用しない場合は、前回の記事でも紹介したSELECT句にサブクエリを記述するような方法がありますが、頭を柔らかくして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 |
mysql> SELECT -> * -> , (SELECT count(sub.votes) -> FROM favorite_food sub -> WHERE main.category = sub.category -> AND main.votes < sub.votes) + 1 AS ranking -> FROM -> favorite_food main -> ORDER BY -> category,ranking -> ; +---------------+-----------------+--------------+-------+---------+ | category | food_name | food_kana | votes | ranking | +---------------+-----------------+--------------+-------+---------+ | Chinese food | Gyoza | Gyoza | 1382 | 1 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | 1 | | Japanese food | Sushi | Sushi | 6158 | 1 | | Japanese food | Ramen | Ramen | 2178 | 2 | | Japanese food | Takoyaki | Takoyaki | 1250 | 3 | | Japanese food | Okonomiyaki | Okonomiyaki | 1250 | 3 | | Japanese food | French Fries | Fried Potato | 1130 | 5 | | Japanese food | Omelette Rice | Omuraisu | 997 | 6 | | Meet | Grilled Meat | Yakiniku | 4717 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 3 | | Meet | Steak | Sute-ki | 1735 | 4 | | Western food | Pizza | Piza | 1873 | 1 | | Western food | Curry Rice | Kare-Raisu | 1500 | 2 | | Western food | Hamburger | Hanba-ga | 1143 | 3 | +---------------+-----------------+--------------+-------+---------+ 15 rows in set (0.01 sec) |
ケース2：DENSE_RANK関数で順位付け
次にDENSE_RANK関数を利用するケースです。
ケース1のRANK関数のSQLと比較して頂けると分かると思いますが、PARTITION BY categoryを指定する点、考え方は全く一緒です。
|
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 |
mysql> SELECT -> * -> , DENSE_RANK() OVER ( PARTITION BY category ORDER BY votes DESC) AS ranking -> FROM -> favorite_food main -> ORDER BY -> category,ranking -> ; +---------------+-----------------+--------------+-------+---------+ | category | food_name | food_kana | votes | ranking | +---------------+-----------------+--------------+-------+---------+ | Chinese food | Gyoza | Gyoza | 1382 | 1 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | 1 | | Japanese food | Sushi | Sushi | 6158 | 1 | | Japanese food | Ramen | Ramen | 2178 | 2 | | Japanese food | Takoyaki | Takoyaki | 1250 | 3 | | Japanese food | Okonomiyaki | Okonomiyaki | 1250 | 3 | | Japanese food | French Fries | Fried Potato | 1130 | 4 | | Japanese food | Omelette Rice | Omuraisu | 997 | 5 | | Meet | Grilled Meat | Yakiniku | 4717 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 3 | | Meet | Steak | Sute-ki | 1735 | 4 | | Western food | Pizza | Piza | 1873 | 1 | | Western food | Curry Rice | Kare-Raisu | 1500 | 2 | | Western food | Hamburger | Hanba-ga | 1143 | 3 | +---------------+-----------------+--------------+-------+---------+ 15 rows in set (0.00 sec) |
参考として、DENSE_RANK関数を利用しない場合の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 |
mysql> SELECT -> * -> , (SELECT count(distinct sub.votes) -> FROM favorite_food sub -> WHERE main.category = sub.category -> AND main.votes < sub.votes) + 1 AS ranking -> FROM -> favorite_food main -> ORDER BY -> category,ranking -> ; +---------------+-----------------+--------------+-------+---------+ | category | food_name | food_kana | votes | ranking | +---------------+-----------------+--------------+-------+---------+ | Chinese food | Gyoza | Gyoza | 1382 | 1 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | 1 | | Japanese food | Sushi | Sushi | 6158 | 1 | | Japanese food | Ramen | Ramen | 2178 | 2 | | Japanese food | Takoyaki | Takoyaki | 1250 | 3 | | Japanese food | Okonomiyaki | Okonomiyaki | 1250 | 3 | | Japanese food | French Fries | Fried Potato | 1130 | 4 | | Japanese food | Omelette Rice | Omuraisu | 997 | 5 | | Meet | Grilled Meat | Yakiniku | 4717 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 3 | | Meet | Steak | Sute-ki | 1735 | 4 | | Western food | Pizza | Piza | 1873 | 1 | | Western food | Curry Rice | Kare-Raisu | 1500 | 2 | | Western food | Hamburger | Hanba-ga | 1143 | 3 | +---------------+-----------------+--------------+-------+---------+ 15 rows in set (0.01 sec) |
「2位、3位じゃだめなんですか？」
データを取得するときに「1位のデータだけを取得してほしい！」と聞くことはあったとしても、
なかなか「2位、3位のデータだけを取得してほしい」とは聞かないと思います。
1位のデータを贔屓するわけではありませんが、
「分類（category）」単位で「1位のデータ」、「1～3位のデータ」を取得する方法を記載します。
2位、3位のデータだけを取得したい場合でも簡単に流用できますので、是非ご参考にしてください。
ケース1：「1位のデータ」の取得方法
今回はRANK関数を使用して順位付けします。
まずは失敗例から紹介します。
これまでのSQLの例を見返してみるとSELECT句でrankingという列別名を付けており、
ORDER BY句では、rankingを利用して昇順にソートしていました。
しかしWHERE句でrankingを利用しようとすると、以下のようにSQLを記述するとエラーになります。
なぜエラーになるのでしょうか？
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT -> * -> , RANK() OVER ( PARTITION BY category ORDER BY votes DESC) AS ranking -> FROM -> favorite_food main -> WHERE -> ranking = 1 -> ORDER BY -> category,ranking -> ; ERROR 1054 (42S22): Unknown column 'ranking' in 'where clause' |
結論としては「SELECT文には一般的な実行順序」が存在するので、
SELECT句で定義したrankingという列別名は後続のORDER BY句では利用できますが、
WHERE句はSELECT句の前で評価されるので、rankingという列別名を利用することができません。
ここでは実行順序の詳細についてはまた別の機会で紹介したいと思いますので割愛しますが、
今回の例ではSELECT、FROM、WHERE、ORDER BY句の４つの句が存在し、「FROM→WHERE→SELECT→ORDER BY」の順番で処理が行われます。
では、どうしたらWHERE句を利用することができるようになるかというと・・・
FROM句のサブクエリで「分類（category）」単位で順位付けしたSELECT文を記述します。
FROM句では、サブクエリの結果を1つのテーブル（仮想のテーブル）として扱えるため、
tmpテーブル（という名の仮想のテーブル）の全データをターゲットとし、WHERE句で絞り込むことができます。
最終的な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 |
mysql> SELECT -> * -> FROM -> ( -> SELECT -> * -> , RANK() OVER ( PARTITION BY category ORDER BY votes DESC) AS ranking -> FROM -> favorite_food main -> ) tmp -> WHERE -> tmp.ranking = 1 -> ORDER BY -> category,ranking -> ; +---------------+--------------+-------------+-------+---------+ | category | food_name | food_kana | votes | ranking | +---------------+--------------+-------------+-------+---------+ | Chinese food | Gyoza | Gyoza | 1382 | 1 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | 1 | | Japanese food | Sushi | Sushi | 6158 | 1 | | Meet | Grilled Meat | Yakiniku | 4717 | 1 | | Western food | Pizza | Piza | 1873 | 1 | +---------------+--------------+-------------+-------+---------+ 5 rows in set (0.01 sec) |
ケース2：「1～3位のデータ」の取得方法
ケース1と同様にRANK関数を使用して順位付けします。
基本的な考え方も同じですので、後はrankingの値が「3」以下のデータを取得するようにWHERE句の絞り込み条件だけ調整します。
|
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 |
mysql> SELECT -> * -> FROM -> ( -> SELECT -> * -> , RANK() OVER ( PARTITION BY category ORDER BY votes DESC) AS ranking -> FROM -> favorite_food main -> ) tmp -> WHERE -> tmp.ranking <= 3 -> ORDER BY -> category,ranking -> ; +---------------+-----------------+-------------+-------+---------+ | category | food_name | food_kana | votes | ranking | +---------------+-----------------+-------------+-------+---------+ | Chinese food | Gyoza | Gyoza | 1382 | 1 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | 1 | | Japanese food | Sushi | Sushi | 6158 | 1 | | Japanese food | Ramen | Ramen | 2178 | 2 | | Japanese food | Takoyaki | Takoyaki | 1250 | 3 | | Japanese food | Okonomiyaki | Okonomiyaki | 1250 | 3 | | Meet | Grilled Meat | Yakiniku | 4717 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 3 | | Western food | Pizza | Piza | 1873 | 1 | | Western food | Curry Rice | Kare-Raisu | 1500 | 2 | | Western food | Hamburger | Hanba-ga | 1143 | 3 | +---------------+-----------------+-------------+-------+---------+ 12 rows in set (0.01 sec) |
「不正ではありません。並び順を変えているだけですよ！」
以下は「和食（Japanese food）」を除いた「投票数（votes）」に対してのベストランキングを作成しています。
RANK関数の結果（ranking）、DENSE_RANK関数の結果（dense_ranking）に注目すると・・・
１行目の焼肉と２行目のアイスクリームは同順位ですが、並び順を見ると焼肉が勝っているように見えます。
|
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 |
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 -> WHERE category <> 'Japanese food' -> WINDOW v AS (ORDER BY votes DESC) -> ORDER BY -> ranking -> ; +--------------+-----------------+-------------+-------+---------------+---------+---------------+ | category | food_name | food_kana | votes | serial_number | ranking | dense_ranking | +--------------+-----------------+-------------+-------+---------------+---------+---------------+ | Meet | Grilled Meat | Yakiniku | 4717 | 1 | 1 | 1 | | Dessert | Ice Cream | Aisukuri-mu | 4717 | 2 | 1 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 3 | 3 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 4 | 4 | 3 | | Western food | Pizza | Piza | 1873 | 5 | 5 | 4 | | Meet | Steak | Sute-ki | 1735 | 6 | 6 | 5 | | Western food | Curry Rice | Kare-Raisu | 1500 | 7 | 7 | 6 | | Chinese food | Gyoza | Gyoza | 1382 | 8 | 8 | 7 | | Western food | Hamburger | Hanba-ga | 1143 | 9 | 9 | 8 | +--------------+-----------------+-------------+-------+---------------+---------+---------------+ 9 rows in set (0.00 sec) |
順位を変更するという不正をせずに並び替えたい場合は、
ORDER BY句を活用し、例えばranking, food_kana ASCとすることによって実現できます。
実際に実行してみると以下のように、
RANK関数の結果（ranking）、DENSE_RANK関数の結果（dense_ranking）は想定通りの並び順になりました。
|
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 |
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 -> WHERE category <> 'Japanese food' -> WINDOW v AS (ORDER BY votes DESC) -> ORDER BY -> ranking, food_kana ASC -> ; +--------------+-----------------+-------------+-------+---------------+---------+---------------+ | category | food_name | food_kana | votes | serial_number | ranking | dense_ranking | +--------------+-----------------+-------------+-------+---------------+---------+---------------+ | Dessert | Ice Cream | Aisukuri-mu | 4717 | 2 | 1 | 1 | | Meet | Grilled Meat | Yakiniku | 4717 | 1 | 1 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 3 | 3 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 4 | 4 | 3 | | Western food | Pizza | Piza | 1873 | 5 | 5 | 4 | | Meet | Steak | Sute-ki | 1735 | 6 | 6 | 5 | | Western food | Curry Rice | Kare-Raisu | 1500 | 7 | 7 | 6 | | Chinese food | Gyoza | Gyoza | 1382 | 8 | 8 | 7 | | Western food | Hamburger | Hanba-ga | 1143 | 9 | 9 | 8 | +--------------+-----------------+-------------+-------+---------------+---------+---------------+ 9 rows in set (0.00 sec) |
さらに連番を表すROW_NUMBER関数の結果（serial_number）の「１」と「２」を入れ替えたい場合は、
OVER句内の並び順をORDER BY votes DESC, food_kana ASCとする方法があります。
|
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 |
mysql> SELECT -> * -> , ROW_NUMBER() OVER ( s ) AS serial_number -> , RANK() OVER ( r ) AS ranking -> , DENSE_RANK() OVER ( r ) AS dense_ranking -> FROM -> favorite_food main -> WHERE category <> 'Japanese food' -> WINDOW s AS (ORDER BY votes DESC, food_kana ASC) -> , r AS (ORDER BY votes DESC) -> ORDER BY -> serial_number -> ; +--------------+-----------------+-------------+-------+---------------+---------+---------------+ | category | food_name | food_kana | votes | serial_number | ranking | dense_ranking | +--------------+-----------------+-------------+-------+---------------+---------+---------------+ | Dessert | Ice Cream | Aisukuri-mu | 4717 | 1 | 1 | 1 | | Meet | Grilled Meat | Yakiniku | 4717 | 2 | 1 | 1 | | Meet | Fried Chicken | Karaage | 3335 | 3 | 3 | 2 | | Meet | Hamburger Steak | Hanba-gu | 2669 | 4 | 4 | 3 | | Western food | Pizza | Piza | 1873 | 5 | 5 | 4 | | Meet | Steak | Sute-ki | 1735 | 6 | 6 | 5 | | Western food | Curry Rice | Kare-Raisu | 1500 | 7 | 7 | 6 | | Chinese food | Gyoza | Gyoza | 1382 | 8 | 8 | 7 | | Western food | Hamburger | Hanba-ga | 1143 | 9 | 9 | 8 | +--------------+-----------------+-------------+-------+---------------+---------+---------------+ 9 rows in set (0.01 sec) |
まとめ
今回はWindow関数を利用して、順位・連番の付け方の発展編を紹介しました。
基本編からアレンジを少し加えただけでも、できる範囲が広がっていることを理解して頂き、今後に活用して頂けますと幸いです。
