文章目录
一、什么是窗口函数
1.1 定义
窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
1.2 基本语法
窗口函数的基本语法:
<窗口函数> over ( [partition by <用于分组的列名>]
[order by <用于排序的列名>] )
语法中<窗口函数>的位置,可以放以下两种函数:
- 1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
- 2) 聚合函数,如sum. avg, count, max, min等
1.3 注意事项
- partition by 不会改变原表的行数;group by 会改变原表的行数;
- 专用窗口函数里的()不需要任何参数,保持空着就行,反之聚合函数()不能为空;
- 因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中;
- partition子句可以省略,省略就是不指定分组,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响;
- order by 子句也可以省略;
- 传统的聚合、排序等函数都是基于全局整表的,窗口函数可以基于表中的每个细分部分。
1.4 窗口函数分类
窗口函数可以分为专用窗口函数和聚合函数。
- 排名函数:根据SQL标准能够支持4种窗口函数用于排名计算。
- ROW_NUMBER:根据指定的顺序,从1开始计算连续的行号。
- NTILE:函数对一个数据分区中的有序结果集进行划分,将其分成数量大致相等的块,根据输入的块数和指定的窗口进行排序。
- RANK与DENSE_RANK:计算一组数值中的排序值
- 分布函数:分布函数主要作用是为静态统计服务提供数据的分布情况。
- 排名分布函数:PERCENT_RANK(百分位排名)和CUME_DIST(累积分布)
- 逆分布函数:PERCENT_CONT(百分位连续)和PERCENTILE_DISC(百分位离散)
- 偏移量函数:LAG和LEAD
- LAG(col,n,DEFAULT): 用于统计窗口内往上第n行值;
- LEAD(col,n,DEFAULT):与LAG相反,用于统计窗口内往下第n行值
- 取值函数
- FIRST_VALUE():取分组内排序后,截止到当前行,第一个值;
- LAST_VALUE():取分组内排序后,截止到当前行,最后一个值。
1.4 窗口函数的万能模板
万能模板3:
select * from(select* rows number () over(partition by 分组 order by 排序 desc)as ranking from 表名)as a where ranking <=N;
二、窗口函数的作用
2.1 解决的问题类型
在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:
- 排名问题:每个部门按业绩来排名
- topN问题:找出每个部门排名前N的员工进行奖励
面对这类需求,就需要使用sql的高级功能窗口函数了。
2.2 具备的功能
窗口函数有以下功能:
- 1)同时具有分组和排序的功能
- 2)不减少原表的行数
接下来,就结合实例,给大家介绍几种窗口函数的用法。
三.几种常用窗口函数的使用
3.1 专用窗口函数rank, dense_rank, row_number45
三个排序函数的差异:
- RANK(): 生成数据项在分组中的排名,排名相等会在名次中留下空位。比如会出现1、2、2、4、4、6、7
- ROW_NUMBER(): 从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列。结果只会是1、2、3、4、5、6、7
- DENSE_RANK(): 生成数据项在分组中的排名,排名相等会在名次中不会留下空位,比如会出现1、2、2、3、3、4、5这种
几个不同排序函数的一些差异,可以根据不同的业务场景选择合适的函数。
示例:
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_num
from 班级表
得到结果:
![](https://i-blog.csdnimg.cn/blog_migrate/98945a9ef48f0fe39408f975dd8dd0e1.png)
3.2 聚合函数作为窗口函数5
聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。
示例:
select *,
sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over (order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
from 班级表
得到结果:
![](https://i-blog.csdnimg.cn/blog_migrate/5ab8083f7d74699f57a64b77d1b30bfa.png)
如上图,聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号,在使用sum窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。
不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算
比如0005号后面的聚合窗口函数结果是:学号0001~0005五人成绩的总和、平均、计数及最大最小值。
如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可。