开窗函数及其作用、范式

目录

开窗函数的解释

开窗函数语法格式

开窗函数的类型

排序开窗

row_nuber() over()

范例1:全部排序

 范例2:组内排序

rank() over()

 范例

 dense_rank() over()

排序开窗总结

聚合开窗

 范例1:组内平均

 范例2:前一个人和自己的平均

范例3:sum累加值

范例4:计算近月份的移动平均值

偏移开窗

 范式

切片开窗

范例 计算百分比

---

开窗函数

又叫做窗口函数或是分析函数

---

开窗函数的解释

--函数出现位置

开窗函数，出现在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