Oracle vs PostgreSQL Develop（15） - DISTINCT ON

平时在客户业务的数据分析中,经常有这么一种需求,那就是希望得到某个组里面某些字段最大或最小的记录.
比如雇员表:

[local]:5432 pg12@testdb=# \d employee                       Table "public.employee"   Column   |         Type          | Collation | Nullable | Default ------------+-----------------------+-----------+----------+--------- id         | integer               |           |          |  name       | character varying(30) |           |          |  department | character varying(30) |           |          |  salary     | double precision      |

我们通过 MockData生成测试数据,共1000行,department共有12个.

[local]:5432 pg12@testdb=# select count(*) from employee; count -------  1000(1 row)Time: 22.747 ms[local]:5432 pg12@testdb=# select distinct department from employee;        department        -------------------------- Marketing Training Sales Business Development Product Management Research and Development Support Legal Accounting Services Human Resources Engineering(12 rows)Time: 2.616 ms

下面希望得到每个department中salary中最大的employee.
常规的做法是使用分组求得最大值/最小值,然后进行关联查询:

[local]:5432 pg12@testdb=# select a.* from employee a,(select department,max(salary) as salary  from employee group by department) b pg12@testdb-# where a.department = b.department and a.salary = b.salary order by a.department; id  |        name        |        department        | salary  -----+--------------------+--------------------------+--------- 453 | Ericha Hendrikse   | Accounting               |  9958.5 307 | Kyle Hartegan      | Business Development     | 9754.93 969 | Odelinda Marsden   | Engineering              |  9942.3 201 | Glen Kasperski     | Human Resources          | 9559.54 892 | Mirabelle Lesslie  | Legal                    | 9720.49 214 | Chane Koschek      | Marketing                | 9943.86 371 | Josy Ayliff        | Product Management       | 9975.48 191 | Meir Alvaro        | Research and Development |    9870 770 | Adoree de Guerre   | Sales                    | 9808.65 370 | Benoite Overlow    | Services                 | 9884.79 866 | Shirlee McIlherran | Support                  | 9884.08 586 | Renae Jerromes     | Training                 | 9904.24(12 rows)Time: 8.256 ms[local]:5432 pg12@testdb=#

这种方法有个问题是如果max salary有多条记录的话,上述查询的结果会有多条.
PostgreSQL提供了DISTINCT ON,可简单实现该需求

[local]:5432 pg12@testdb=# SELECT DISTINCT ON (department)pg12@testdb-#     *pg12@testdb-# FROMpg12@testdb-#     employeepg12@testdb-# ORDER BYpg12@testdb-#     department,pg12@testdb-#     salary DESC; id  |        name        |        department        | salary  -----+--------------------+--------------------------+--------- 453 | Ericha Hendrikse   | Accounting               |  9958.5 307 | Kyle Hartegan      | Business Development     | 9754.93 969 | Odelinda Marsden   | Engineering              |  9942.3 201 | Glen Kasperski     | Human Resources          | 9559.54 892 | Mirabelle Lesslie  | Legal                    | 9720.49 214 | Chane Koschek      | Marketing                | 9943.86 371 | Josy Ayliff        | Product Management       | 9975.48 191 | Meir Alvaro        | Research and Development |    9870 770 | Adoree de Guerre   | Sales                    | 9808.65 370 | Benoite Overlow    | Services                 | 9884.79 866 | Shirlee McIlherran | Support                  | 9884.08 586 | Renae Jerromes     | Training                 | 9904.24(12 rows)Time: 11.445 ms

Excellent Feature!

参考资料
The Many Faces of DISTINCT in PostgreSQL