over partition by与group by 的区别

最新推荐文章于 2025-05-26 16:35:08 发布

原创最新推荐文章于 2025-05-26 16:35:08 发布 · 6.3k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#table #c

PL/SQL 专栏收录该内容

26 篇文章

订阅专栏

本文对比了SQL中Over(Partition By)与Group By的功能差异。通过实例展示了Over(Partition By)如何保留每行记录的同时计算聚合信息，如部门总薪资及个人薪资占比；而Group By则仅提供分组后的汇总数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

over partition by 与 group by 都是与统计类函数用，这两个有什么区别呢？
目前我只知道一个这样的区别：
比如有一张表saraly:CREATE TABLE SALARY AS SELECT 'A' NAME,10 DEPT,1000 SALARY FROM DUAL UNION ALL SELECT 'B',10,2000 FROM DUAL UNION ALL SELECT 'C' ,20,1500 FROM DUAL UNION ALL SELECT 'D',20,3000 FROM DUAL UNION ALL
SELECT 'E',10,1000 FROM DUAL;
NAME DEPT SALARY
A 10 1000
B 10 2000
C 20 1500
D 20 3000
E 10 1000
用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资：
select name,dept,salary,sum(salary) over (partition by dept) total_salary from salary;
name dept salary tatal_salary
A 10 1000 4000
B 10 2000 4000
E 10 1000 4000
C 20 1500 4500
D 20 3000 4500

用goup by 就没办法做到这点，只能查询到每个部门的总工资：
select dept,sum(salary) total_salary from salary group by dept
dept total_salary
10 4000
20 4500
另外over partion by 还可以做到查询每位员工占部门总工资的百分比：
select name,dept,salary,salary*100/sum(salary) over (partition by dept) percent from salary;

name dept salary percent
A 10 1000 25
B 10 2000 50
E 10 1000 25
C 20 1500 33.3333333333333
D 20 3000 66.6666666666667