はじめに
MySQL8.0からWindow関数が使用できるようになりましたが、今回はWindow関数でよく使われるROW_NUMBER、RANK関数の使い方とWindow関数が使用できないMySQL5.7での代替方法についてまとめました。
Window関数の説明
ROW_NUMBER
結果セットの各行に対して、その行が何番目に位置するかを割り振ります。
ROW_NUMBER() OVER(ORDER BY カラム名)
ROW_NUMBER() OVER(PARTITION BY カラム名 ORDER BY カラム名)
RUNK
結果セットの各行に対して、順位を割り振ります。同じ値を持つ行は同じ順位を割り振ります。
RANK() OVER(ORDER BY カラム名)
RANK() OVER(PARTITION BY カラム名 ORDER BY カラム名)
ORDER BYで指定されたカラムで結果セットをソートして番号を振ります。
PARTITION BYで指定されたカラムで結果セットをグループ化し、そのグループ内で番号を振ります。
Window関数を使ってみる
下記のデータが入っているテーブルを使用してWindow関数を試します。
mysql> SELECT * FROM SCORE_DATA;
+----------+-------------+-------+
| GROUP_NO | NAME | SCORE |
+----------+-------------+-------+
| A | David | 74 |
| A | Emily | 82 |
| A | Jessica | 91 |
| A | Michael | 65 |
| A | Sarah | 87 |
| B | Amanda | 85 |
| B | Ashley | 77 |
| B | Christopher | 77 |
| B | Joshua | 67 |
| B | Matthew | 93 |
| C | Brittany | 89 |
| C | Daniel | 68 |
| C | Megan | 79 |
| C | Nicholas | 96 |
| C | Rachel | 81 |
+----------+-------------+-------+
下記のSQLを実行して確認します。
SELECT GROUP_NO,
NAME,
SCORE,
ROW_NUMBER() OVER(ORDER BY NAME) AS ROWNO,
RANK() OVER(ORDER BY SCORE DESC) AS TOTAL_RANK,
RANK() OVER(PARTITION BY GROUP_NO ORDER BY SCORE DESC) AS GROUP_RANK
FROM SCORE_DATA
ORDER BY GROUP_NO, SCORE DESC;
+----------+-------------+-------+-------+------------+------------+
| GROUP_NO | NAME | SCORE | ROWNO | TOTAL_RANK | GROUP_RANK |
+----------+-------------+-------+-------+------------+------------+
| A | Jessica | 91 | 8 | 3 | 1 |
| A | Sarah | 87 | 15 | 5 | 2 |
| A | Emily | 82 | 7 | 7 | 3 |
| A | David | 74 | 6 | 12 | 4 |
| A | Michael | 65 | 12 | 15 | 5 |
| B | Matthew | 93 | 10 | 2 | 1 |
| B | Amanda | 85 | 1 | 6 | 2 |
| B | Ashley | 77 | 2 | 10 | 3 |
| B | Christopher | 77 | 4 | 10 | 3 |
| B | Joshua | 67 | 9 | 14 | 5 |
| C | Nicholas | 96 | 13 | 1 | 1 |
| C | Brittany | 89 | 3 | 4 | 2 |
| C | Rachel | 81 | 14 | 8 | 3 |
| C | Megan | 79 | 11 | 9 | 4 |
| C | Daniel | 68 | 5 | 13 | 5 |
+----------+-------------+-------+-------+------------+------------+
RANK関数を使用するとSCOREが同じ行は同じ順位が割り振られます。
Window関数を使わない代替方法
MySQL5.7ではWindow関数は使用できない為、代替方法を使用します。
変数を使用する方法
よく紹介されているのは、下記の様な変数を使用する方法ですが、条件によって複雑化してしまい、分かりにくくなるという難点があります。
下記の例ではTOTAL_RANKだけを表示していますが、Window関数を使った例の様にROWNOやGROUP_RANKも同時に表示させるには、更に複雑になります。
SET @rank = 1;
SET @prev_score = NULL;
SET @real_rank = 0;
SELECT S.GROUP_NO,
S.NAME,
S.SCORE,
@real_rank := IF(@prev_score = S.SCORE, @real_rank, @rank) AS TOTAL_RANK,
@rank := @rank + 1,
@prev_score := S.SCORE
FROM (SELECT * FROM SCORE_DATA ORDER BY SCORE DESC) AS S;
+----------+-------------+-------+------------+--------------------+------------------------+
| GROUP_NO | NAME | SCORE | TOTAL_RANK | @rank := @rank + 1 | @prev_score := S.SCORE |
+----------+-------------+-------+------------+--------------------+------------------------+
| C | Nicholas | 96 | 1 | 2 | 96 |
| B | Matthew | 93 | 2 | 3 | 93 |
| A | Jessica | 91 | 3 | 4 | 91 |
| C | Brittany | 89 | 4 | 5 | 89 |
| A | Sarah | 87 | 5 | 6 | 87 |
| B | Amanda | 85 | 6 | 7 | 85 |
| A | Emily | 82 | 7 | 8 | 82 |
| C | Rachel | 81 | 8 | 9 | 81 |
| C | Megan | 79 | 9 | 10 | 79 |
| B | Ashley | 77 | 10 | 11 | 77 |
| B | Christopher | 77 | 10 | 12 | 77 |
| A | David | 74 | 12 | 13 | 74 |
| C | Daniel | 68 | 13 | 14 | 68 |
| B | Joshua | 67 | 14 | 15 | 67 |
| A | Michael | 65 | 15 | 16 | 65 |
+----------+-------------+-------+------------+--------------------+------------------------+
サブクエリーで対応する方法
下記の様なサブクエリーを使用すると、Window関数を使用した時と全く同じ結果を取得することができます。変数を使用するよりもシンプルに対応できると思います。
SELECT S.GROUP_NO,
S.NAME,
S.SCORE,
(SELECT COUNT(1)+1 FROM SCORE_DATA S2 WHERE S.NAME > S2.NAME) ROWNO,
(SELECT COUNT(1)+1 FROM SCORE_DATA S3 WHERE S.SCORE < S3.SCORE) TOTAL_RANK,
(SELECT COUNT(1)+1 FROM SCORE_DATA S4 WHERE S.SCORE < S4.SCORE AND S.GROUP_NO = S4.GROUP_NO) GROUP_RANK
FROM SCORE_DATA S
ORDER BY S.GROUP_NO, S.SCORE DESC;
+----------+-------------+-------+-------+------------+------------+
| GROUP_NO | NAME | SCORE | ROWNO | TOTAL_RANK | GROUP_RANK |
+----------+-------------+-------+-------+------------+------------+
| A | Jessica | 91 | 8 | 3 | 1 |
| A | Sarah | 87 | 15 | 5 | 2 |
| A | Emily | 82 | 7 | 7 | 3 |
| A | David | 74 | 6 | 12 | 4 |
| A | Michael | 65 | 12 | 15 | 5 |
| B | Matthew | 93 | 10 | 2 | 1 |
| B | Amanda | 85 | 1 | 6 | 2 |
| B | Ashley | 77 | 2 | 10 | 3 |
| B | Christopher | 77 | 4 | 10 | 3 |
| B | Joshua | 67 | 9 | 14 | 5 |
| C | Nicholas | 96 | 13 | 1 | 1 |
| C | Brittany | 89 | 3 | 4 | 2 |
| C | Rachel | 81 | 14 | 8 | 3 |
| C | Megan | 79 | 11 | 9 | 4 |
| C | Daniel | 68 | 5 | 13 | 5 |
+----------+-------------+-------+-------+------------+------------+
Discussion