over （partition by xxx order by yyy）开窗函数介绍

1、函数介绍

over（order by xxx） 按照xxx字段排序进行累计，order by是一个默认的开窗函数；

over （partition by xxx，yyy）按照 xxx，yyy 字段分区；

over （partition by xxx order by yyy）按照 xxx 字段分区，并按照yyy字段排序进行累计。

2、测试表结构以及数据

声明：以下数据均为测试数据。

测试表 test_2021 的表结构如下：

测试表 test_2021的部分数据如下：

3、测试SQL

3.1、测试 over （partition by xxx，yyy）按照xxx，yyy字段分区

需求：按照岗位编码，岗位名称，试验类型编码，试验类型名称以及年份进行分区求产值的累计和，求出了同一年份同一岗位同一试验类型的不同月份的产值的和。

select
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt,--产值
sum(cost_amt) over (partition by post_code,post_name,testtype_code,testtype_name,year_name) as leiji_amt--累计产值
from test_2021
where year_name = '2016年'
order by year_name,month_name
1
2
3
4
5
6
7
8
9
10
11
12

执行结果如下：

我们将以上数据按累计产值排序以后，会更直观的看出同一年份同一岗位同一试验类型的不同月份的产值的和。

比如累计产值为464的值，就是2016年 电连接器器件检测岗 复验 这种试验11月份和12月份的和。

那么我们猜想，如果按照年份，以及试验类型分区求和的话，那么出来的累计产值是不是只有两个值，一个是2016年复验试验的产值，一个是2016年筛选试验的产值，测试SQL如下：

select
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt,--产值
sum(cost_amt) over (partition by year_name,testtype_code,testtype_name) as leiji_amt--累计产值
from test_2021
where year_name = '2016年'
order by year_name,month_name
1
2
3
4
5
6
7
8
9
10
11
12

该SQL执行结果如下（按累计产值排序）：

3.2、测试over （order by xxx，yyy）按照xxx，yyy字段排序进行累计

需求：按照年月进行累计，即2016年11月的累计产值为11的和（2016年只有11月以后的数据），12月为11月和12月的和，SQL如下

select
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt,--产值
sum(cost_amt) over (order by year_name,month_name) as leiji_amt--累计产值
from test_2021
where year_name = '2016年'
order by year_name,month_name
1
2
3
4
5
6
7
8
9
10
11
12

执行结果如下：

作为对比，同样的SQL，我们将 order by 改为 partition by ，看一下结果，SQL以及执行结果如下：

select
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt,--产值
sum(cost_amt) over (partition by year_name,month_name) as leiji_amt--累计产值
from test_2021
where year_name = '2016年'
order by year_name,month_name
1
2
3
4
5
6
7
8
9
10
11
12

以上11月产值5705加上12月的产值4376等于10081，刚好为 order by 中的12月累计值10081。

3.3、over （partition by xxx order by yyy）按照 xxx 字段分区，并按照yyy字段排序进行累计

需求：求出同一年份，同一岗位，同一试验类型每个月的产值累计值。这里既要按年份、岗位、试验类型分区，又要求月份的累计值，所以就要用over （partition by xxx order by yyy），SQL如下：

select
year_name,--年份名称
month_name,--月份名称
post_code,--岗位编码
post_name,--岗位名称
testtype_code,--试验类型编码
testtype_name,--试验类型名称
cost_amt,--产值
sum(cost_amt) over (partition by year_name,post_code,post_name,testtype_code,testtype_name order by month_name) as leiji_amt
from test_2021
where year_name = '2016年'
order by year_name,month_name
1
2
3
4
5
6
7
8
9
10
11
12

执行结果如下：

数据核对：以2016年阻容元件检测岗的筛选试验为例，在11月的产值为884（第1行数据），累计产值为884，在12月的产值为214（第14行数据），12月的累计产值为1098，即为11月的884与12月的214的和。