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