over partition by与group by 区别

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

区别

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

 

     

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

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 这样的分组）

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)

 

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