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