文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Oracle vs PostgreSQL Develop(15) - DISTINCT ON

2024-04-02 19:55

关注

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


[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

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 资料下载
  • 历年真题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯