over partition by与group by

Over与Group By的区别

最新推荐文章于 2025-09-22 10:50:17 发布

转载最新推荐文章于 2025-09-22 10:50:17 发布 · 2.8k 阅读

Oracle 专栏收录该内容

70 篇文章

订阅专栏

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

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

区别

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

[sql] view plain copy print ?

-- 创建表并插入数据
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

-- 创建表并插入数据
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 分组，并取得最高工资的员工信息，我们看看会有什么问题

[sql] view plain copy print ?

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 行受影响)

 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 这样的分组） 
  
   
    
    [sql] 
    view plain 
     copy 
     print 
    ? 
    
   
  SELECT EmpDepartment,MAX(EmpSalary) FROM Employee   
GROUP BY EmpDepartment  
  
-- 执行结果  
EmpDepartment          
-------------------- ----------  
开发部                  8000  
销售部                  5000  
  
(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)

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