group by 和 over(partition by)的区别

1>建表并插入数据，因为over是开窗函数，mysql不支持开窗函数，其他如oracle,sql server,db2...等新版本都支持(这里借用他人的数据)

CREATE TABLE Employee
(
  ID number(10) not null primary key,
  EmpName varchar(20),
  EmpSalary varchar(10),
  EmpDepartment varchar(20)
);
 
insert all into Employee values(1,'张三','5000','开发部')
into Employee values(2,'李四','2000','销售部')
into Employee values(3,'王麻子','2500','销售部')
into Employee values(4,'张三表叔','8000','开发部')
into Employee values(5,'李四表叔','5000','开发部')
into Employee values(6,'王麻子表叔','5000','销售部')
select 1 from dual

2>计算每个部门的总薪水？

2.1>group by

SELECT EmpDepartment,SUM(EmpSalary) sum_sala FROM Employee GROUP BY EmpDepartment

2.2>over(partition by)

SELECT EmpSalary,EmpDepartment,SUM(EmpSalary) OVER(PARTITION BY EmpDepartment) sum_sala FROM Employee

此处小结：group by 和 partition by 都有分组统计的功能，但是partition by并不具有group by的汇总功能。partition by统计的每一条记录都存在，而group by将所有的记录汇总成一条记录(类似于distinct EmpDepartment 去重)。partition by可以和聚合函数结合使用，同时具有其他高级功能。

3>在partition by 后在加上order by

SELECT EmpSalary,EmpDepartment,SUM(EmpSalary) OVER(PARTITION BY EmpDepartment ORDER BY EmpSalary) sum_sala FROM Employee

此处小结：加上order by 后，类似于累加功能(sum_sala += EmpSalary)，先观察销售部的结果，从第4条记录开始，其sum(EmpSalary)即sum_sala=2000，第5条记录,sum(EmpSalary)=sum_sala+2500=4500，即第4条sum_sala与第5条EmpSalary的和，依次类推；开发部，由于2个5000是并列的，所以计算的时候是几个并列数据之和即5000+5000=10000。

---

开窗函数 over ( partition by)以及和group by 的区别

1.over函数的写法：

over（partition by class order by sroce）

按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区。

2、开窗的窗口范围：

 over（order by sroce range between 5 preceding and 5 following）

窗口范围为当前行数据幅度减5加5后的范围内的。

 over（order by sroce rows between 5 preceding and 5 following）

窗口范围为当前行前后各移动5行。

 sum() over(partition by … order by …)：求分组后的总和。 
 first_value() over(partition by … order by …)：求分组后的第一个。 
 last_value() over(partition by … order by …)：求分组后的最后一个。 
 count() over(partition by … order by …)：求分组后的总数。 
 max() over(partition by … order by …)：求分组后的最大值。 
 min() over(partition by … order by …)：求分组后的最小值。 
 avg() over(partition by … order by …)：求分组后的平均值。 
 lag() over(partition by … order by …)：取出前n行数据。　　 
 lead() over(partition by … order by …)：取出后n行数据。

3.over partition by与group by 的区别

原表:

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 partition by会把每个数据的明细都显现出来,聚合显示多条
group by 聚合只会显示一条

---

over partition by与group by 的区别

group by 只能得到分组后的统计数据，over partition by 不仅可以得到分组后的统计数据，还可以同时显示明细数据。
group by 是在where子句之后；over partition by 是from子句之前。

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 partition 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

用group by 也没办法做到这个.不知道我的理解正不正确，请各位朋友指点，特别是over partition by 与group by 的更多区别请各位一起分享，谢谢！