PostgreSQL DISTINCT ON

用法：
　　DISTINCT ON ( expression [,…] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. […]。 Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. […]。 The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s)。
　　意思是DISTINCT ON ( expression [,…] )把记录根据[,…]的值进行分组，分组之后仅返回每一组的第一行。需要注意的是，如果你不指定ORDER BY子句，返回的第一条的不确定的。如果你使用了ORDER BY 子句，那么[,…]里面的值必须靠近ORDER BY子句的最左边。

例子测试数据参考http://www.52php.cn/article/p-mygxkbch-zc.html。

1. 当没用指定ORDER BY子句的时候返回的记录是不确定的。

postgres=# select distinct on(course)id,name,course,score from student;
 id | name | course | score ----+--------+--------+-------
 10 | 周星驰 | 化学 | 83
 8 | 周星驰 | 外语 | 88
 2 | 周润发 | 数学 | 99
 14 | 黎明 | 物理 | 90
 6 | 周星驰 | 语文 | 91
(5 rows)

2. 获取每门课程的最高分

postgres=# select distinct on(course)id,score from student order by course,score desc;
 id | name | course | score ----+--------+--------+-------
 5 | 周润发 | 化学 | 87
 13 | 黎明 | 外语 | 95
 2 | 周润发 | 数学 | 99
 14 | 黎明 | 物理 | 90
 6 | 周星驰 | 语文 | 91
(5 rows)

3. 如果指定ORDER BY 必须把分组的字段放在最左边

postgres=# select distinct on(course)id,score from student order by score desc;
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on(course)id,score from student ... 

postgres=# select distinct on(course)id,score from student order by score desc,course;
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on(course)id,score from student ...

4. 获取每门课程的最高分同样可以使用IN子句来实现

postgres=# select * from student where(course,score) in(select course,max(score) from student group by course);
 id |  name  | course | score 
----+--------+--------+-------
  2 | 周润发 | 数学   |    99
  5 | 周润发 | 化学   |    87
  6 | 周星驰 | 语文   |    91
 13 | 黎明   | 外语   |    95
 14 | 黎明   | 物理   |    90
(5 rows)

5. 在 row_number() over(),distinct on和in子句之间有一个小区别
　　比如当数学的最高分同时有两个人时候，row_number() over(),distinct on经过排序后是返回这两个人中的随机一个人，除非你根据人名在进行排序，而in子句会全部返回

• 先将数学的最高分更新有两个人

postgres=# update student set score =99 where name='黎明' and course='数学';
UPDATE 1 postgres=# select * from student where course ='数学';
 id |  name  | course | score 
----+--------+--------+-------
 31 | 周润发 | 数学   |    99
 36 | 周星驰 | 数学   |    81
 41 | 黎明   | 数学   |    99
(3 rows)

• 获取每门课程的最高分的人
  
  • 使用 rom_number() over()

postgres=# select id,score from (select *,row_number() over(partition by course order by score desc)rn from student)t where t.rn=1 order by course;
 id |  name  | course | score 
----+--------+--------+-------
 34 | 周润发 | 化学   |    87
 42 | 黎明   | 外语   |    95
 41 | 黎明   | 数学   |    99
 43 | 黎明   | 物理   |    90
 35 | 周星驰 | 语文   |    91
(5 rows)

• 使用 distinct on

postgres=# select distinct on(course) * from student order by course,score desc,course;
 id | name | course | score ----+--------+--------+-------
 34 | 周润发 | 化学 | 87
 42 | 黎明 | 外语 | 95
 41 | 黎明 | 数学 | 99
 43 | 黎明 | 物理 | 90
 35 | 周星驰 | 语文 | 91
(5 rows)

• 使用 in子句

postgres=# select * from student where(course,score) in (select course,max(score) from student group by course) order by course;
 id |  name  | course | score 
----+--------+--------+-------
 34 | 周润发 | 化学   |    87
 42 | 黎明   | 外语   |    95
 31 | 周润发 | 数学   |    99
 41 | 黎明   | 数学   |    99
 43 | 黎明   | 物理   |    90
 35 | 周星驰 | 语文   |    91
(6 rows)

　　因为row_number() over()是根据行号来取的，distinct on是返回排序之后的第一行，所以它们只是返回一个最高分。

　　为了解决rom_number() over()和distinct on的问题，可以使用rank() over()窗口函数，rank() 窗口函数和 row_number() 窗口函数类似，但 rank() 窗口函数会将结果集分组后相同值的记录的标记相等。

postgres=# select id,score from(select *,rank() over(partition by course order by score desc)sn from student)t where sn=1;
 id |  name  | course | score 
----+--------+--------+-------
  5 | 周润发 | 化学   |    87
 13 | 黎明   | 外语   |    95
 12 | 黎明   | 数学   |    99
  2 | 周润发 | 数学   |    99
 14 | 黎明   | 物理   |    90
  6 | 周星驰 | 语文   |    91
(6 rows)

参考：
http://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by
http://blog.163.com/digoal@126/blog/static/16387704020124239390354/
http://francs3.blog.163.com/blog/static/4057672720126209947340/