GBase 8a窗口函数row_number的使用样例

刚发现，这个最常用的GBase 8a的窗口函数竟然没有写个单独的例子，只是在其它帖子里提了一下，这里做个单独的这里吧。

参考

GBase 8a窗口函数使用样例索引
GBase 8a 排序支持nulls first / last的使用样例，包括开窗函数

语法

ROW_NUMBER( ) OVER([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [asc/desc], col_name2 [asc/desc],…)

说明

返回每一行对应的【连续】顺序位置。和Rank 的区别，就是连续，而在Rank里相同的值位置号会相同，类似并列第几名，而row_number则会顺序连续排下去。

• PARTITION BY : 可选，多个字段用逗号分割。如果指定了，则每个开窗分区的顺序号各自独立。
• ORDER BY ： 必选，排序的字段，多个字段用逗号分割。

样例

表数据

gbase> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| type  | int(11) | YES  |     | NULL    |       |
| val   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * from t2 order by type,val desc;
+------+------+------+
| id   | type | val  |
+------+------+------+
|    3 |    1 |  133 |
|    2 |    1 |  122 |
|    1 |    1 |  111 |
|    6 |    2 |  244 |
|    5 |    2 |  233 |
|    4 |    2 |  222 |
|    7 |    3 |  333 |
+------+------+------+
7 rows in set (Elapsed: 00:00:00.03)

无PARTITION

与直接order by无异，只是多了一个【行号】。

gbase> select t2.*,row_number() over(order by val desc) from t2;
+------+------+------+--------------------------------------+
| id   | type | val  | row_number() over(order by val desc) |
+------+------+------+--------------------------------------+
|    7 |    3 |  333 |                                    1 |
|    6 |    2 |  244 |                                    2 |
|    5 |    2 |  233 |                                    3 |
|    4 |    2 |  222 |                                    4 |
|    3 |    1 |  133 |                                    5 |
|    2 |    1 |  122 |                                    6 |
|    1 |    1 |  111 |                                    7 |
+------+------+------+--------------------------------------+
7 rows in set (Elapsed: 00:00:00.03)

带Partition

按照type开窗分组，每个分组内，各自做排序后的行号。可以看到type=1,2,3的做了三组的结果。

gbase> select t2.*,row_number() over(partition by type order by val desc) row from t2;
+------+------+------+-----+
| id   | type | val  | row |
+------+------+------+-----+
|    3 |    1 |  133 |   1 |
|    2 |    1 |  122 |   2 |
|    1 |    1 |  111 |   3 |
|    6 |    2 |  244 |   1 |
|    5 |    2 |  233 |   2 |
|    4 |    2 |  222 |   3 |
|    7 |    3 |  333 |   1 |
+------+------+------+-----+
7 rows in set (Elapsed: 00:00:00.05)

查询每个Partition的前2名

这个类似获取每个班成绩最好的前几名的需求。需要使用嵌套查询，将row_number返回的行号，作为一个列做过滤。

gbase> select * from (select t2.*,row_number() over(partition by type order by val desc) row from t2) t where row<=2 order by type,row;
+------+------+------+-----+
| id   | type | val  | row |
+------+------+------+-----+
|    3 |    1 |  133 |   1 |
|    2 |    1 |  122 |   2 |
|    6 |    2 |  244 |   1 |
|    5 |    2 |  233 |   2 |
|    7 |    3 |  333 |   1 |
+------+------+------+-----+
5 rows in set (Elapsed: 00:00:00.07)