一、窗口函数介绍
1 窗口函数语法
- 专用窗口函数，比如rank，dense_rank，row_number等
- 聚合函数，如sum，avg，count，max，min等
2 窗口函数功能
- 不减少原表的行数，所以经常用来在每组内排名
- 同时具有分组（partition by)和排序（order by)的功能
3 窗口函数使用场景
业务需求“在每组内排名”，比如：
- 排名问题：每个部门按业绩来排名
- topN问题：找出每个部门排名前N的员工进行奖励
4 注意事项
- 窗口函数原则上只能写在select子句中
- partition by 子句可以省略，省略就是不指定分组，但是这就失去了窗口函数的功能，所以一般不要这么使用
5 group by、order by 子句与窗口函数的区别
group by 分组汇总后改变了表的行数，一行只有一个类别，而partition by和rank函数不会减少原表中的行数
二、标准聚合函数
标准的聚合函数有avg、count、sum、max和min，接下来分别介绍这些聚合函数的窗口函数形式。
1 移动平均窗口函数
移动平均值的定义：若依次得到测定值（x1,x2,x3,...,xn）时，按顺序取一定个数所做的全部算数平均值。例如（x1+x2+x3）/3，（x2+x3+x4）/3，（x3+x4+x5）/3，...就是移动平均值。其中，x可以是日或者月，以上的可以成为3日移动平均，或3月移动平均，常用于股票分析中
例子
2 计数（count）窗口函数
窗口函数count(*) over()对于查询返回的每一行，它返回了表中所有行的计数
例子1
查询出成绩在90分以上的人数
例子2
按照课程号进行分组，找出成绩大于等于80分的学生人数
吐槽：这两个例子举得不是很好，如果只是为了看学生人数，用group by 能更明了地看，应该说找出大于等于80分的学生人数及其相关信息。
3 累计求和（sum）窗口函数
例子 1
根据学号排序，对学生的成绩进行累计求和
例子 2
按照课程号分组，然后根据学号对成绩进行累计求和
注：一定要选择根据学号排序，要不然的出来的是最终的累计求和结果，如下图：
4 最大（max）、最小值（min）窗口函数
例子 1
求成绩的累计最大值和累计最小值
例子2
按照课程号进行分组，再求最大、最小值
例子3
根据学生号和课程号求成绩的累积最小值
例子 4
统计2019年10月1日-10月10日每天做新题的人的数量，重点在每天。
- 这个题的重点是在每天，所以需要求出count(时间) = 10的用户ID；
- 这个题可以使用min() over()窗口函数，先根据每个做题者和试卷号，找出每个做题者的最小日期，这里和前面（3）的解题思路是一样的
- 如果每天都做题，那么得到的日期是不一样的，所以count（时间）会等于10
- 再对这部分的用户ID进行求和，就可以找出每天都做新题的人了。
三、排序窗口函数
row_number()、rank()、dense_rank()，这三个函数的作用都是返回相应规则的排序序号。
1 row_number()
为查询出来的每一行记录都会生成一个序号，依次排序且不会重复。1，2，3，4
2 rank()
使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名排下一个，rank函数生成的序号有可能是不连续的，即排名可能为1，1，3，是跳跃式排名，有两个第一名时接下来就是第三名。1，1，1，4
3 dense_rank()
dense_rank函数在生成序号时是连续的，当出现相同排名时，将不跳过相同排名号，有两个第一名时仍跟着第二名，即排名为1，1，2这种。1，1，1，2.
注：在上述的三个排序专用窗口函数中，函数后面的括号不需要任何参数，保持（）空着就可以。
四、分组排序窗口函数
可以按照销售额的高低、点击次数的高低，以及成绩的高低为对用户和学生进行分组，这里的考点是：取销售额最高的25%的用户（将用户分成4组，取出第一组）、取成绩高的前10%的学生（将学生分成10组，取出第一组）等等。
- ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
- ntile不支持rows between 的用法
- 切片如果不均匀，默认增加第一个切片的分布
例子 1
取出成绩前25%的学生信息
- 第一步：按照成绩的高低，将学生按照成绩进行切片
- 第二步：按照rank筛选出第一组，则得到最终的结果如下：
五、偏移分析窗口函数
- lag() over()和lead() over()窗口函数，lag和lead分析函数可以在同一次查询中取出同一个字段的前N行数据（lag）和后N行（lead）作为独立的列。
- 在实际应用当中，若要用到取今天和昨天的某字段的差值时，lag和lead函数的应用就显得尤为重要了
- 适用场景：获取用户在某个页面停留的起始与结束时间
- 注意：LEAD()和LAG()函数始终与OVER()一起使用。缺少over子句将引发错误。
例子 1
向前推1个日期
例子 2
向后推1个日期
例子 3
统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻。
用户行为表racking_log(user_id,operate_id,log_time)
- 先根据用户ID和日期，用LEAD()窗口函数向后获取下一步的步骤
- AB必须相邻，则表明当前的步骤为A，而下一个步骤为B，即A向下移的步骤是B；
- “每天”，根据日期进行分组
到此这篇sql窗口函数是什么意思（sql窗口函数有哪些）的文章就介绍到这了,更多相关内容请继续浏览下面的相关推荐文章，希望大家都能在编程的领域有一番成就！
版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！
转载请注明出处，原文链接：https://www.xkablog.com/sqlbc/33623.html