平时在客户业务的数据分析中,经常有这么一种需求,那就是希望得到某个组里面某些字段最大或最小的记录.
比如雇员表:
[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-# FROM
pg12@testdb-# employee
pg12@testdb-# ORDER BY
pg12@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
