在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)