南大通用GBase 8a 排序支持nulls first / last的使用样例，包括开窗函数

在GBase 8a 8.6.2Build43版本，开始支持在开窗函数和普通排序order 部分指定null值的位置，包括 nulls first, 和 nulls last，不之指定时默认是nulls last效果。

数据

请关注里面的values 为 888的数据。有2行。

gbase> select * from t1;
+------+--------+---------------------+
| id   | value  | birth               |
+------+--------+---------------------+
|    1 |    234 | NULL                |
|    2 |    567 | NULL                |
|    3 |    888 | NULL                |
|    4 |    999 | NULL                |
|    5 | 555555 | 2020-01-03 00:00:00 |
|    4 |    888 | 2021-04-13 00:00:00 |
+------+--------+---------------------+
6 rows in set (Elapsed: 00:00:00.00)

开窗查询

默认是nulls last

gbase> select id,value,birth,row_number() over(partition by value order by birth) num from t1;
+------+--------+---------------------+-----+
| id   | value  | birth               | num |
+------+--------+---------------------+-----+
|    4 |    888 | 2021-04-13 00:00:00 |   1 |
|    3 |    888 | NULL                |   2 |
|    4 |    999 | NULL                |   1 |
|    1 |    234 | NULL                |   1 |
|    2 |    567 | NULL                |   1 |
|    5 | 555555 | 2020-01-03 00:00:00 |   1 |
+------+--------+---------------------+-----+
6 rows in set (Elapsed: 00:00:00.10)

gbase> select id,value,birth,row_number() over(partition by value order by birth nulls last) num from t1;
+------+--------+---------------------+-----+
| id   | value  | birth               | num |
+------+--------+---------------------+-----+
|    4 |    888 | 2021-04-13 00:00:00 |   1 |
|    3 |    888 | NULL                |   2 |
|    4 |    999 | NULL                |   1 |
|    1 |    234 | NULL                |   1 |
|    2 |    567 | NULL                |   1 |
|    5 | 555555 | 2020-01-03 00:00:00 |   1 |
+------+--------+---------------------+-----+
6 rows in set (Elapsed: 00:00:00.06)

row_number nulls first

可以看到 null 值排在了最前面了。

gbase> select id,value,birth,row_number() over(partition by value order by birth nulls first) num from t1;
+------+--------+---------------------+-----+
| id   | value  | birth               | num |
+------+--------+---------------------+-----+
|    3 |    888 | NULL                |   1 |
|    4 |    888 | 2021-04-13 00:00:00 |   2 |
|    4 |    999 | NULL                |   1 |
|    1 |    234 | NULL                |   1 |
|    2 |    567 | NULL                |   1 |
|    5 | 555555 | 2020-01-03 00:00:00 |   1 |
+------+--------+---------------------+-----+
6 rows in set (Elapsed: 00:00:00.07)

rank nulls first

gbase> select id,value,birth,rank() over(partition by value order by birth nulls first)  num from t1;
+------+--------+---------------------+-----+
| id   | value  | birth               | num |
+------+--------+---------------------+-----+
|    3 |    888 | NULL                |   1 |
|    4 |    888 | 2021-04-13 00:00:00 |   2 |
|    4 |    999 | NULL                |   1 |
|    1 |    234 | NULL                |   1 |
|    2 |    567 | NULL                |   1 |
|    5 | 555555 | 2020-01-03 00:00:00 |   1 |
+------+--------+---------------------+-----+
6 rows in set (Elapsed: 00:00:00.11)

普通排序

数据

其中id=10有2行，name有一个数值为null。

gbase> select * from t1;
+------+--------------+
| id   | name         |
+------+--------------+
|   10 | 10101010     |
|  100 | 100100100100 |
|   10 | NULL         |
|    1 | 1111         |
+------+--------------+
4 rows in set (Elapsed: 00:00:00.01)

单列排序（NULL列)

结果和预期相符，默认是null last。

gbase> select * from t1 order by name;
+------+--------------+
| id   | name         |
+------+--------------+
|  100 | 100100100100 |
|   10 | 10101010     |
|    1 | 1111         |
|   10 | NULL         |
+------+--------------+
4 rows in set (Elapsed: 00:00:00.03)

gbase> select * from t1 order by name nulls first;
+------+--------------+
| id   | name         |
+------+--------------+
|   10 | NULL         |
|  100 | 100100100100 |
|   10 | 10101010     |
|    1 | 1111         |
+------+--------------+
4 rows in set (Elapsed: 00:00:00.03)

gbase> select * from t1 order by name nulls last;
+------+--------------+
| id   | name         |
+------+--------------+
|  100 | 100100100100 |
|   10 | 10101010     |
|    1 | 1111         |
|   10 | NULL         |
+------+--------------+
4 rows in set (Elapsed: 00:00:00.04)

多列排序（NULL列靠后)

和预期相符。

gbase> select * from t1 order by id,name;
+------+--------------+
| id   | name         |
+------+--------------+
|    1 | 1111         |
|   10 | 10101010     |
|   10 | NULL         |
|  100 | 100100100100 |
+------+--------------+
4 rows in set (Elapsed: 00:00:00.03)

gbase> select * from t1 order by id,name nulls first;
+------+--------------+
| id   | name         |
+------+--------------+
|    1 | 1111         |
|   10 | NULL         |
|   10 | 10101010     |
|  100 | 100100100100 |
+------+--------------+
4 rows in set (Elapsed: 00:00:00.02)

gbase> select * from t1 order by id,name nulls last;
+------+--------------+
| id   | name         |
+------+--------------+
|    1 | 1111         |
|   10 | 10101010     |
|   10 | NULL         |
|  100 | 100100100100 |
+------+--------------+
4 rows in set (Elapsed: 00:00:00.03)