MariaDB – ROW_NUMBER() , RANK() 與 DENSE_RANK()

測試環境為 CentOS 8 X86_64 (虛擬機)

要找出指定欄位的最大(小)值,可以透過 MAX() , MIN() 函數來搜尋,但是要知道是哪一行的資料(想一併得知其他欄位資料)時,可以透過 ROW_NUMBER() 函數來使用.

先建立一個資料庫 (testdb) , 與 Student tables 格式為 Class(20) , Name char(20), Score char(20) 各 20 個字元以及 ID int(11).

[root@localhost ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE testdb;
Database changed

MariaDB [testdb]> CREATE TABLE Student (ID INT(11) NOT NULL AUTO_INCREMENT , Class VARCHAR(20) , Name VARCHAR(20) , Score VARCHAR(20) , PRIMARY KEY (ID));
Query OK, 0 rows affected (0.003 sec)

透過 INSERT 新增加了四筆資料.

MariaDB [testdb]> INSERT INTO Student ( Class , Name , Score) VALUES ('A' , 'Ben' , 60 ) , ('A' , 'Afa', 85) , ('B' , 'Boss' , 75 ) , ('B' , 'Cars' , 62);
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM Student;
+----+-------+------+-------+
| ID | Class | Name | Score |
+----+-------+------+-------+
|  1 | A     | Ben  | 60    |
|  2 | A     | Afa  | 85    |
|  3 | B     | Boss | 75    |
|  4 | B     | Cars | 62    |
+----+-------+------+-------+
4 rows in set (0.000 sec)

找出最高分數.

MariaDB [testdb]> SELECT MAX(Score) FROM Student;
+------------+
| MAX(Score) |
+------------+
| 85         |
+------------+
1 row in set (0.000 sec)

找出該班級最高分數.

MariaDB [testdb]> SELECT Class , MAX(Score) FROM Student GROUP BY Class;
+-------+------------+
| Class | MAX(Score) |
+-------+------------+
| A     | 85         |
| B     | 75         |
+-------+------------+
2 rows in set (0.000 sec)

好奇分數最高的是誰?

MariaDB [testdb]> SELECT Class , Name , MAX(Score) FROM Student GROUP BY Class;
+-------+------+------------+
| Class | Name | MAX(Score) |
+-------+------+------------+
| A     | Ben  | 85         |
| B     | Boss | 75         |
+-------+------+------------+
2 rows in set (0.000 sec)

資料怎麼怪怪的,明明 A 班分數最高的是 Afa 85分,怎麼變成 Ben 了.這是因為 GROUP BY 後的資料是保留地一筆的資料(該範例是 Ben).

要怎麼解決這個問題,可以透過 ROW_NUMBER() 函數.

先看一下 ROW_NUMBER() 函數功能.

SELECT ID , Class , Name , Score ,
               ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank
          FROM Student;

執行結果.

MariaDB [testdb]> SELECT ID , Class , Name , Score ,
    ->                ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank
    ->           FROM Student;
+----+-------+------+-------+------+
| ID | Class | Name | Score | Rank |
+----+-------+------+-------+------+
|  2 | A     | Afa  | 85    |    1 |
|  1 | A     | Ben  | 60    |    2 |
|  3 | B     | Boss | 75    |    1 |
|  4 | B     | Cars | 62    |    2 |
+----+-------+------+-------+------+
4 rows in set (0.000 sec)

ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) 可以依據指定欄位(範例為 Class) 分組,並透過 指定欄位 (範例為Score) 來產生順序數字.

有了排序我們可以很簡單找出個別班級的第一名同學資料.

SELECT a.ID , a.Class , a.Name , a.Score 
  FROM (SELECT ID , Class , Name , Score ,
               ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank
          FROM Student) a
 WHERE a.Rank = 1 ;

執行結果.

MariaDB [testdb]> SELECT a.ID , a.Class , a.Name , a.Score
    ->   FROM (SELECT ID , Class , Name , Score ,
    ->                ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank
OM St    ->           FROM Student) a
    ->  WHERE a.Rank = 1 ;
+----+-------+------+-------+
| ID | Class | Name | Score |
+----+-------+------+-------+
|  2 | A     | Afa  | 85    |
|  3 | B     | Boss | 75    |
+----+-------+------+-------+
2 rows in set (0.000 sec)

與 ROW_NUMBER() 類似的函數還有 RANK() 與 DENSE_RANK() ,差別在於當排序遇到相同的值時所產生的數字排序略為不同,來看下面範例.

多增加相同的分數資料到資料表中.

MariaDB [testdb]> INSERT INTO Student ( Class , Name , Score) VALUES ('A' , 'Alan' , 60 ) , ('A' , 'May' , 55 );
Query OK, 2 row affected (0.001 sec)

MariaDB [testdb]> SELECT * FROM Student;
+----+-------+------+-------+
| ID | Class | Name | Score |
+----+-------+------+-------+
|  1 | A     | Ben  | 60    |
|  2 | A     | Afa  | 85    |
|  3 | B     | Boss | 75    |
|  4 | B     | Cars | 62    |
|  5 | A     | Alan | 60    |
|  6 | A     | May  | 55    |
+----+-------+------+-------+
6 rows in set (0.000 sec)

SELECT ID , Class , Name , Score ,
               ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS ROW_NUMBER ,
               RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank ,
               DENSE_RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS DENSE_RANK
          FROM Student;

執行結果.

MariaDB [testdb]> SELECT ID , Class , Name , Score ,
    ->                ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS ROW_NUMBER ,
    ->                RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS Rank ,
    ->                DENSE_RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS DENSE_RANK
    ->           FROM Student;
+----+-------+------+-------+------------+------+------------+
| ID | Class | Name | Score | ROW_NUMBER | Rank | DENSE_RANK |
+----+-------+------+-------+------------+------+------------+
|  2 | A     | Afa  | 85    |          1 |    1 |          1 |
|  5 | A     | Alan | 60    |          2 |    2 |          2 |
|  1 | A     | Ben  | 60    |          3 |    2 |          2 |
|  6 | A     | May  | 55    |          4 |    4 |          3 |
|  3 | B     | Boss | 75    |          1 |    1 |          1 |
|  4 | B     | Cars | 62    |          2 |    2 |          2 |
+----+-------+------+-------+------------+------+------------+
6 rows in set (0.000 sec)

上面範例可以看到 ROW_NUMBER 在遇到相同值時還是做排序(不清楚是依據什麼),但 Rank 與 DENSE_RANK 在處理相同值時會使用相同的排序值,差別在於後面的排序值會有變化,就看使用習慣來採用哪一個函數.