Greenplum 排序nulls first last的 SQL写法实现
背景
Greenplum并不支持nulls first或last语法,例如:
select * from tbl order by id nulls first;
select id, last_value(key) over (partition by gid order by crt_time nulls first) from tbl ;
这两句在PostgreSQL可以支持,但是在Greenplum中不支持。
Greenplum实现排序nulls first|last
在PG中支持多列排序,同时支持boolean类型,true 大于 false。
利用这个特性,我们可以在排序时调整字段nulls排在前面还是后面。
select * from tbl order by (id is not null), id;
相当于
select * from tbl order by id nulls first;
select * from tbl order by (id is null), id;
相当于
select * from tbl order by id nulls last;
这样就能实现nulls first或last了。
postgres=# select (id is null),* from tbl order by (id is null), id;
?column? | id
----------+----
f | 1
f | 2
f | 3
t |
(4 rows)
postgres=# select (id is not null),* from tbl order by (id is not null), id;
?column? | id
----------+----
f |
t | 1
t | 2
t | 3
(4 rows)
窗口函数中使用也一样。
select id, last_value(key) over (partition by gid order by (crt_time is not null), crt_time) from tbl ;
参考
https://discuss.pivotal.io/hc/en-us/articles/205803497-Sorting-ORDER-BY-of-data-which-has-NULL-values