HybridDB for PG、Greenplum 排序nulls first|last的 SQL写法实现

标签

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 ;

参考

https://discuss.pivotal.io/hc/en-us/articles/205803497-Sorting-ORDER-BY-of-data-which-has-NULL-values