over partition by与group by

本文对比了SQL中Over Partition By与Group By的功能及用途。通过具体示例展示了两种方法在处理分组统计时的不同表现,特别关注Over Partition By在保持每行记录的同时提供分组统计数据的能力。

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

over partition by与group by是都是分组统计的函数。

区别

1. over partition by 其中partition by 只是over一个子句参数,作用就是分组。over 子句可以与聚合函数结合使用(max、min、sum、avg、count等).下面我们看一个例子

 

     

  1. <strong>-- 创建表并插入数据  
  2. CREATE TABLE Employee  
  3. (  
  4.   ID int identity(1,1),  
  5.   EmpName varchar(20),  
  6.   EmpSalary varchar(10),  
  7.   EmpDepartment varchar(20)   
  8. );  
  9.   
  10. INSERT INTO Employee  
  11. SELECT '张三','5000','开发部' UNION ALL  
  12. SELECT '李四','2000','销售部'  UNION ALL  
  13. SELECT '王麻子','2500','销售部' UNION ALL  
  14. SELECT '张三表叔','8000','开发部' UNION ALL  
  15. SELECT '李四表叔','5000','开发部' UNION ALL  
  16. SELECT '王麻子表叔','5000','销售部'  
  17.   
  18. -- 现在使用来对 部门进行分组,使用over partition by分组,按照工资排序(使用ROW_NUMBER()函数来生成一列)  
  19. SELECT * ,ROW_NUMBER() OVER(PARTITION BY EmpDepartment ORDER BY EmpSalary) EmpNumber FROM Employee  
  20.   
  21. -- 执行结果如下:  
  22.   
  23. ID          EmpName              EmpSalary  EmpDepartment        EmpNumber  
  24. --------- -------------------- ---------- -------------------- --------------------  
  25. 1           张三                   5000       开发部                  1  
  26. 5           李四表叔                 5000       开发部                  2  
  27. 4           张三表叔                 8000       开发部                  3  
  28. 6           王麻子表叔                5000       销售部                   1  
  29. 2           李四                   2000       销售部                  1  
  30. 3           王麻子                  2500       销售部                  2  
  31. </strong>  
-- 创建表并插入数据
CREATE TABLE Employee
(
  ID int identity(1,1),
  EmpName varchar(20),
  EmpSalary varchar(10),
  EmpDepartment varchar(20) 
);

INSERT INTO Employee
SELECT '张三','5000','开发部' UNION ALL
SELECT '李四','2000','销售部'  UNION ALL
SELECT '王麻子','2500','销售部' UNION ALL
SELECT '张三表叔','8000','开发部' UNION ALL
SELECT '李四表叔','5000','开发部' UNION ALL
SELECT '王麻子表叔','5000','销售部'

-- 现在使用来对 部门进行分组,使用over partition by分组,按照工资排序(使用ROW_NUMBER()函数来生成一列)
SELECT * ,ROW_NUMBER() OVER(PARTITION BY EmpDepartment ORDER BY EmpSalary) EmpNumber FROM Employee

-- 执行结果如下:

ID          EmpName              EmpSalary  EmpDepartment        EmpNumber
--------- -------------------- ---------- -------------------- --------------------
1           张三                   5000       开发部                  1
5           李四表叔                 5000       开发部                  2
4           张三表叔                 8000       开发部                  3
6           王麻子表叔                5000       销售部                   1
2           李四                   2000       销售部                  1
3           王麻子                  2500       销售部                  2

 

 2. 假如现在 使用  over partition by 分组,并取得最高工资的员工信息,我们看看会有什么问题

  1. <p>SELECT * ,MAX(EmpSalary) OVER(PARTITION BY EmpDepartment) MaxSalary FROM Employee</p><p>-- 执行结果  
  2. ID          EmpName              EmpSalary  EmpDepartment        MaxSalary  
  3. 1           张三                   5000       开发部                  8000  
  4. 4           张三表叔                 8000       开发部                  8000  
  5. 5           李四表叔                 5000       开发部                  8000  
  6. 6           王麻子表叔                5000       销售部                  5000  
  7. 2           李四                   2000       销售部                  5000  
  8. 3           王麻子                  2500       销售部                  5000</p><p>(6 行受影响)</p>  

SELECT * ,MAX(EmpSalary) OVER(PARTITION BY EmpDepartment) MaxSalary FROM Employee

-- 执行结果 ID          EmpName              EmpSalary  EmpDepartment        MaxSalary ----------- -------------------- ---------- -------------------- ---------- 1           张三                   5000       开发部                  8000 4           张三表叔                 8000       开发部                  8000 5           李四表叔                 5000       开发部                  8000 6           王麻子表叔                5000       销售部                  5000 2           李四                   2000       销售部                  5000 3           王麻子                  2500       销售部                  5000

(6 行受影响)


查看执行结果不难发现,最大工资是获取到了,可是不需要的列也显示出来了。

使用Group by来分组就没有(相信可能使用over partition by 也可以实现如group by 这样的分组)

  1. SELECT EmpDepartment,MAX(EmpSalary) FROM Employee   
  2. GROUP BY EmpDepartment  
  3.   
  4. -- 执行结果  
  5. EmpDepartment          
  6. -------------------- ----------  
  7. 开发部                  8000  
  8. 销售部                  5000  
  9.   
  10. (2 行受影响)  
SELECT EmpDepartment,MAX(EmpSalary) FROM Employee 
GROUP BY EmpDepartment

-- 执行结果
EmpDepartment        
-------------------- ----------
开发部                  8000
销售部                  5000

(2 行受影响)


group by是对检索结果的保留行进行单纯分组,一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。 (官方参考文档:http://technet.microsoft.com/zh-cn/library/ms177673.aspx)

partition by虽然也具有分组功能,但同时也具有其他的高级功能。(可以参考官方文档:http://technet.microsoft.com/zh-cn/library/ms189461.aspx)

 

注:如上如有错误地方,欢迎大家指出,互相学习。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值