目录
开窗函数又叫做窗口函数或是分析函数
开窗函数的解释
--函数出现位置
开窗函数,出现在select子句中
--窗口划分
对于FROM表中的数据集根据partition by 后的关键字进行划分窗口,这也就是窗口的来源
如若over内的partition by 关键字省略 则整个数据集看作一个窗口
各个窗口内的数据相互独立 互不干涉
而在over前面函数是在窗口划分之后针对窗口内的数据进行计算
rows control 子句中是用来控制窗口的大小
需要声明一点:
在实际计算的过程中是以行为单位进行计算的, 而正在计算的行视为当前行
窗口划分图示
窗口内计算图示
窗口大小控制
在控制窗口大小的过程中,不会超过每个窗口的界限,也就是说不会进入别的窗口,只会在自己的窗口内进行计算
开窗函数语法格式
select 函数(参数) over([partition by colname] [order by colname] [ROWS Control])
from tableName
ROWS Control 是用来控制窗口的
常用的组合如下:
| --ROWS BETWEEN x PRECEDING AND y FOLLOWING
当前行和前面x行以及后面y行构成窗口进行计算
| --ROWS BETWEEN x PRECEDING AND CURRENT ROW
当前行和前面x行构成窗口进行计算
| --ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
当前行和前面所有行进行计算 不会超过自己的窗口大小
开窗函数的类型
'开窗函数分为三类'
'排序开窗'
row_number() over() --123
rank() over() --113
dense_rank() over() --112
'聚合开窗'
sum() over()
avg() over()
max() over()
min() over()
count() over()
'偏移开窗'
lead() over()
lag() over()
'切片开窗'
ntile() over()
排序开窗
row_nuber() over()
作用:对数据进行排序并施加序号 不会重复排名
范例1:全部排序
select sal,row_number() over(order by sal desc) rank
from emp
SAL RANK
1 25000.00 1
2 25000.00 2
3 22990.00 3
4 20691.00 4
5 19000.00 5
6 18100.00 6
7 17500.00 7
8 17250.00 8
9 16950.00 9
10 500.00 10
11 222.00 11
12 -5025.00 12
13 -5550.00 13
范例2:组内排序
select deptno,sal,row_number() over(partition by deptno order by sal desc) rank
from emp
DEPTNO SAL RANK
1 10 500.00 1
2 10 -5550.00 2
3 20 22990.00 1
4 20 20691.00 2
5 20 19000.00 3
6 20 18100.00 4
7 20 222.00 5
8 20 -5025.00 6
9 30 25000.00 1
10 30 25000.00 2
11 30 17500.00 3
12 30 17250.00 4
13 30 16950.00 5
rank() over()
作用:对数据进行排序并施加序号 重复数据会并列排名 并跳过排名
范例
select sal,rank() over(order by sal desc) rank
from emp
SAL RANK
1 25000.00 1
2 25000.00 1
3 22990.00 3
4 20691.00 4
5 19000.00 5
6 18100.00 6
7 17500.00 7
8 17250.00 8
9 16950.00 9
10 500.00 10
11 222.00 11
12 -5025.00 12
13 -5550.00 13
dense_rank() over()
作用:对数据进行排序并施加序号 重复数据会并列排名 不会跳过排名
select sal,dense_rank() over(order by sal desc) rank
from emp
SAL RANK
1 25000.00 1
2 25000.00 1
3 22990.00 2
4 20691.00 3
5 19000.00 4
6 18100.00 5
7 17500.00 6
8 17250.00 7
9 16950.00 8
10 500.00 9
11 222.00 10
12 -5025.00 11
13 -5550.00 12
排序开窗总结
1. 三种排序都可以全部排序也都可以组内排序, 主要看需求
2. 三种排序的区别
row_number() | 不重复排名 | 123 |
rank() | 重复且跳过 | 113 |
dense_rank() | 重复不跳过 | 112 |
聚合开窗
作用: 对窗口内的数据进行聚合分析
select
sum()|count()|max()|min()|avg() over(partition by colname order by rows contrl)
from tablename
范例1:组内平均
select
deptno,
ename,
sal,
avg(sal) over(partition by deptno order by deptno) avg_sal
from emp
DEPTNO ENAME SAL AVG_SAL
1 10 谢大脚 -5550.00 -2525
2 10 小屁孩 500.00 -2525
3 20 美丽 22990.00 12663
4 20 TheShy 19000.00 12663
5 20 Carsa 20691.00 12663
6 20 李铁柱儿 222.00 12663
7 20 allry 18100.00 12663
8 20 李宏伟 -5025.00 12663
9 30 Joker 17500.00 20340
10 30 拴柱儿 17250.00 20340
11 30 Lina 16950.00 20340
12 30 王德发 25000.00 20340
13 30 狗蛋儿 25000.00 20340
范例2:前一个人和自己的平均
select
deptno,
ename,
sal,
avg(sal) over(partition by deptno order by deptno rows between 1 PRECEDING AND CURRENT ROW) avg_sal
from emp
DEPTNO ENAME SAL AVG_SAL
1 10 谢大脚 -5550.00 -5550
2 10 小屁孩 500.00 -2525
3 20 美丽 22990.00 22990
4 20 TheShy 19000.00 20995
5 20 Carsa 20691.00 19845.5
6 20 李铁柱儿 222.00 10456.5
7 20 allry 18100.00 9161
8 20 李宏伟 -5025.00 6537.5
9 30 Joker 17500.00 17500
10 30 拴柱儿 17250.00 17375
11 30 Lina 16950.00 17100
12 30 王德发 25000.00 20975
13 30 狗蛋儿 25000.00 25000
ps:需要注意的是, 在窗口内计算时,是根据窗口内真实的数据行数进行计算的; 例如在这里第一条数据在计算时只有自己前面没有数据 那么就是自己和自己求平均
平均值计算公式 sum(字段) / count(字段)
范例3:sum累加值
常用于对每个月的销售额数据进行累加, 并将累加的数据于年目标进行除法,来计算年任务完成率
但由于此处的数据不合适,无奈之下只能对工资进行累加,读者领会语法和用途即可
select
ename,
sal,
sum(sal) over(order by sal rows between unbounded preceding and current row) sums
from emp
ENAME SAL SUMS
1 谢大脚 -5550.00 -5550
2 李宏伟 -5025.00 -10575
3 李铁柱儿 222.00 -10353
4 小屁孩 500.00 -9853
5 Lina 16950.00 7097
6 拴柱儿 17250.00 24347
7 Joker 17500.00 41847
8 allry 18100.00 59947
9 TheShy 19000.00 78947
10 Carsa 20691.00 99638
11 美丽 22990.00 122628
12 王德发 25000.00 147628
13 狗蛋儿 25000.00 172628
范例4:计算近月份的移动平均值
聚合平均开窗可以和rows control一起使用,用来求移动平均值
偏移开窗
作用: 可以让多行之间的数据变成同一行进行计算
一般用于计算每个月数据的同比环比率
lead 列向上偏移
lag 列向后偏移
语法格式
select lead(colname[,step][,defaultChar]) OVER(ORDER BY colname)
from tablename
step 偏移量
defaultChar 默认填充字符
范式
select ename
from emp
ENAME
1 李铁柱儿
2 小屁孩
3 狗蛋儿
4 王德发
5 李宏伟
6 拴柱儿
7 谢大脚
8 美丽
9 Joker
10 Carsa
11 Lina
12 TheShy
13 allry
select ename,lead(ename,2,'aaa') OVER(ORDER BY sal)
from emp
ENAME LEAD(ENAME,2,'AAA')OVER(ORDERB
1 谢大脚 李铁柱儿
2 李宏伟 小屁孩
3 李铁柱儿 Lina
4 小屁孩 拴柱儿
5 Lina Joker
6 拴柱儿 allry
7 Joker TheShy
8 allry Carsa
9 TheShy 美丽
10 Carsa 王德发
11 美丽 狗蛋儿
12 王德发 aaa
13 狗蛋儿 aaa
切片开窗
作用: 一般用于计算排名百分比
如果不分窗口 就将全部数据根据字段划分为指定的片数
如果分窗口 就在窗口内划分为指定的片数
例如划分为4片 每个片内都会相应的赋予片编号1 2 3 4
这样1号片内的数据就是前百分之25
范例 计算百分比
select sal, ntile(4) over(order by sal desc) rank
from emp
SAL RANK
1 25000.00 1
2 25000.00 1
3 22990.00 1
4 20691.00 1
5 19000.00 2
6 18100.00 2
7 17500.00 2
8 17250.00 3
9 16950.00 3
10 500.00 3
11 222.00 4
12 -5025.00 4
13 -5550.00 4
--根据切片计算前百分之25
select *
from (select sal, ntile(4) over(order by sal desc) r
from emp)
where r = 1
SAL R
1 25000.00 1
2 25000.00 1
3 22990.00 1
4 20691.00 1
注意: 当记录条数与切片数相除结果不是整数时,
会先按整数划分
余下的数据在进行循环划分,并且每次只给一个值,让数据条数的误差始终保持在1条数据
例如11条记录
划分为4个片
11/4=2 ..3
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 1
10 2
11 3