postgresql distinct on用法

除了我们熟知的distinct外，postgresql还支持distinct on，它的用途跟mysql没有启用ONLY_FULL_GROUP_BY SQL选项是一样的，针对声明的字段做分组，分组外的字段返回随机一行。如下：

lightdb@oradb=# create table t_distincton(id int,v text);
CREATE TABLE
lightdb@oradb=# 
lightdb@oradb=# 
lightdb@oradb=# insert into t_distincton values (1,'v1'),(2,'v2'),(3,'v3');
INSERT 0 3
lightdb@oradb=# insert into t_distincton values (1,'v1'),(2,'v2'),(3,'v4');
INSERT 0 3
lightdb@oradb=# select * from t_distincton ;
 id | v  
----+----
  1 | v1
  2 | v2
  3 | v3
  1 | v1
  2 | v2
  3 | v4
(6 rows)

lightdb@oradb=# select distinct on(id) v from t_distincton ;
 v  
----
 v1
 v2
 v3
(3 rows)

lightdb@oradb=# select distinct on(v) id,v from t_distincton ;
 id | v  
----+----
  1 | v1
  2 | v2
  3 | v3
  3 | v4
(4 rows)

lightdb@oradb=# select distinct on(v) id,v from t_distincton order by id;
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: select distinct on(v) id,v from t_distincton order by id;
                           ^
lightdb@oradb=# select distinct on(v) id,v from t_distincton order by v,id;
 id | v  
----+----
  1 | v1
  2 | v2
  3 | v3
  3 | v4
(4 rows)

lightdb@oradb=# select distinct on(v,id) id,v from t_distincton order by v;
 id | v  
----+----
  1 | v1
  2 | v2
  3 | v3
  3 | v4
(4 rows)

如果不使用distinct on，也可以通过分析函数row_number()达到相同的效果，如下：

select * from (select row_number() over (partition by a) as rn, * from t_distinct) t where rn=1; 

需要注意的是，如果有distinct on(a,b,c)的值有null，null被认为都相同，这个null != null不一样。

关于order by中不支持表达式的说明是SQL92的限制，可以参考https://postgrespro.com/list/thread-id/2097072。