南大通用GBase 8a窗口函数Rank和Dense_rank使用样例

本文介绍GBase 8a 数据库支持的窗口函数Rank和Dense_rank的语法和使用样例。这2个函数都是根据 ORDER BY 子句中表达式的值，从查询返回的每一行计算它们与其它行的相对位置。组内的数据按 ORDER BY 子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从 1 开始，往后累加。区别是Rank在数值重复时，排序号重复，后面的号跳过重复的号，而Dense则会给连续的值，不跳过重复的号。

参考

GBase 8a窗口函数使用样例索引
GBase 8a窗口函数percent_rank使用样例

数据

如下数据一共5行，其中90的有2行。 共分成了2组。

gbase> select * from test_rank order by sales desc;
+------+------+-------+------+
| id   | name | sales | type |
+------+------+-------+------+
|    1 | 111  |   100 |    1 |
|    5 | 555  |    95 |    2 |
|    4 | 444  |    90 |    2 |
|    2 | 222  |    90 |    2 |
|    3 | 333  |    85 |    2 |
+------+------+-------+------+
5 rows in set (Elapsed: 00:00:00.03)

gbase> select * from test_rank order by type,sales desc;
+------+------+-------+------+
| id   | name | sales | type |
+------+------+-------+------+
|    1 | 111  |   100 |    1 |
|    5 | 555  |    95 |    2 |
|    4 | 444  |    90 |    2 |
|    2 | 222  |    90 |    2 |
|    3 | 333  |    85 |    2 |
+------+------+-------+------+
5 rows in set (Elapsed: 00:00:00.02)

Rank

Rank语法

RANK() OVER([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [ASC/DESC], col_name2 [ASC/DESC],…)

Rank说明

Partition by 部分是可选的，如果不写，则所有数据当成一个窗口操作。
Order by 是一个窗口内的排序列，靠前的编号小，从1开始。

无Partition的Rank样例

可以看到90分的222和444，其顺序号都是3，而后面的333则跳到了5。中间的4因为重复值而跳过了。

gbase> select id,name,sales,rank() over(order by sales desc) rk from test_rank order by rk;
+------+------+-------+----+
| id   | name | sales | rk |
+------+------+-------+----+
|    1 | 111  |   100 |  1 |
|    5 | 555  |    95 |  2 |
|    2 | 222  |    90 |  3 |
|    4 | 444  |    90 |  3 |
|    3 | 333  |    85 |  5 |
+------+------+-------+----+
5 rows in set (Elapsed: 00:00:00.04)

有Partition的 Rank样例

其中type=2的分类里，222和444的顺序号为2，后面333的顺序号4，跳过了顺序号3。

gbase> select type,id,name,sales,rank() over(partition by type order by sales desc) rk from test_rank order by type,rk;
+------+------+------+-------+----+
| type | id   | name | sales | rk |
+------+------+------+-------+----+
|    1 |    1 | 111  |   100 |  1 |
|    2 |    5 | 555  |    95 |  1 |
|    2 |    2 | 222  |    90 |  2 |
|    2 |    4 | 444  |    90 |  2 |
|    2 |    3 | 333  |    85 |  4 |
+------+------+------+-------+----+
5 rows in set (Elapsed: 00:00:00.06)

Dense_rank

与Rank的区别就是在一个窗口内，重复值的号不会被跳过，是连续的。

Dense_rank语法

DENSE_RANK( ) over([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [ASC/DESC], col_name2 [ASC/DESC],…)

Dense_rank说明

Partition by 部分是可选的，如果不写，则所有数据当成一个窗口操作。
Order by 是一个窗口内的排序列，靠前的编号小，从1开始。

无Partition的Dense_rank样例

可以看到90分的222和444都是需要3， 后面的333序号是4， 而在Rank里序号是5，是连续的。

gbase> select id,name,sales,dense_rank() over(order by sales desc) rk from test_rank order by rk;
+------+------+-------+----+
| id   | name | sales | rk |
+------+------+-------+----+
|    1 | 111  |   100 |  1 |
|    5 | 555  |    95 |  2 |
|    2 | 222  |    90 |  3 |
|    4 | 444  |    90 |  3 |
|    3 | 333  |    85 |  4 |
+------+------+-------+----+
5 rows in set (Elapsed: 00:00:00.04)

无Partition的Dense_rank样例

其中type=2的分类里，222和444的顺序号为2，后面333的顺序号3，是连续的。

gbase> select type,id,name,sales,dense_rank() over(partition by type order by sales desc) rk from test_rank order by type,rk;
+------+------+------+-------+----+
| type | id   | name | sales | rk |
+------+------+------+-------+----+
|    1 |    1 | 111  |   100 |  1 |
|    2 |    5 | 555  |    95 |  1 |
|    2 |    2 | 222  |    90 |  2 |
|    2 |    4 | 444  |    90 |  2 |
|    2 |    3 | 333  |    85 |  3 |
+------+------+------+-------+----+
5 rows in set (Elapsed: 00:00:00.06)