标签
PostgreSQL , Greenplum
背景
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 ;