本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql;
本笔记是本人自己的上课的学习笔记
一、窗口函数
窗口函数又称OLAP函数:对数据库数据进行实时分析处理。
窗口函数的语法:<窗口函数> OVER ([PARTITION BY <列名>]
ORDER BY <排序用列名>)
其中PARTITON BY是用来分组,即选择要看哪个窗口,ORDER BY是用来排序,即决定窗口内,是按那种规则(字段)来排序的。
注意:[ ]中的内容可以省略。
二、窗口函数的种类
窗口函数可以分为两类:一类是将SUM、MAX、MIN等聚合函数用在窗口函数中,另一类是 RANK、DENSE_RANK等排序用的专用窗口函数。
三、专用窗口函数
专用窗口函数分为三种:
RANK函数(英式排序):在计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
例如;有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位……
DENSE RANK函数(中式排序):同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。
例如;有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位……
ROW_NUMBER函数:赋予唯一的连续位次。
例如;有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位
四、聚合函数在窗口函数上的使用
聚合函数在开窗函数中的使用方法和之前的专用窗口函数一样,只是出来的结果是一个累计的聚合函数值。
例如;SELECT product_id
,product_name
,sale_price
,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
FROM product;
五、窗口函数的应用-计算移动平均
语法:<窗口函数> OVER (ORDER BY <排序用列名> ROWS n PRECEDING )
<窗口函数> OVER (ORDER BY <排序用列名> ROWS BETWEEN n PRECEDING AND n FOLLOWING)
PRECEDING(“之前”), 将框架指定为 “截止到之前 n 行”,加上自身行
FOLLOWING(“之后”), 将框架指定为 “截止到之后 n 行”,加上自身行
BETWEEN 1 PRECEDING AND 1 FOLLOWING,将框架指定为 “之前1行” + “之后1行” + “自身”
注意:原则上,窗口函数只能在SELECT子句中使用,窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。