Oracle vs PostgreSQL Develop(15) - DISTINCT ON
程序员文章站
2022-06-11 21:43:30
...
平时在客户业务的数据分析中,经常有这么一种需求,那就是希望得到某个组里面某些字段最大或最小的记录.
比如雇员表:
[local]:5432 [email protected]=# \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 [email protected]=# select count(*) from employee;
count
-------
1000
(1 row)
Time: 22.747 ms
[local]:5432 [email protected]=# 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 [email protected]=# select a.* from employee a,(select department,max(salary) as salary from employee group by department) b
[email protected]# 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 [email protected]=#
这种方法有个问题是如果max salary有多条记录的话,上述查询的结果会有多条.
PostgreSQL提供了DISTINCT ON,可简单实现该需求
[local]:5432 [email protected]=# SELECT DISTINCT ON (department)
[email protected]# *
[email protected]# FROM
[email protected]# employee
[email protected]# ORDER BY
[email protected]# department,
[email protected]# 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!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2654543/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/6906/viewspace-2654543/
上一篇: oracle 建表空间、用户
下一篇: php.ini的配置
推荐阅读
-
Oracle vs PostgreSQL,研发注意事项(10)- PostgreSQL数据类型转换规则#2
-
Oracle vs PostgreSQL,研发注意事项(5)- 字符类型
-
Oracle vs PostgreSQL DBA(13)- 拆分(split)分区
-
PostgreSQL vs Oracle checksum 配置与性能
-
Oracle vs PostgreSQL Develop(15) - DISTINCT ON
-
Oracle vs PostgreSQL,研发注意事项(9)- PostgreSQL数据类型转换规则#1
-
Oracle vs PostgreSQL,研发注意事项(7)- 类型转换
-
Oracle vs PostgreSQL,研发注意事项(11)- PostgreSQL数据类型转换规则#3
-
Oracle vs PostgreSQL Develop(16) - Prepared Statement
-
Oracle vs PostgreSQL Develop(17) - ARRAY