大家好，我是讯享网，很高兴认识大家。

目录

开窗函数的解释

开窗函数语法格式

开窗函数的类型

排序开窗

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  12 王德发 25000.00  13 狗蛋儿 25000.00  

范例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