group by 和 over(partition by)的区别

落日映苍穹つ 2021-06-26 16:06 555阅读 0赞

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

![在这里插入图片描述][20210321162826120.png]  
2.2>`over(partition by)`

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

![在这里插入图片描述][20210321162851831.png]

此处小结：`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

![在这里插入图片描述][20210321162905132.png]

此处小结：加上`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` 的更多区别请各位一起分享，谢谢！

[20210321162826120.png]: /images/20210626/b4aa3f8c1a1c444cb46f11aab607fad0.png
[20210321162851831.png]: /images/20210626/8928ebc1bda94616810147b456efdbb1.png
[20210321162905132.png]: /images/20210626/6d66aa8171eb4d67b63ff6f8898b5e29.png