本文介绍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)