学习目标:
学习产出:
窗口函数(OLAP函数)
<窗口函数> OVER ([PARTITION BY <列名>]
ORDER BY <排序用列名>)
[]中的内容可以省略掉
PARTITON BY是用来分组
ORDER BY用于字段规则排序
几个专用窗口函数
- RANK 如果有并列,则跳过之后的位次。如有三个并列第一名则:1,1,1,4,
- DENSE_RANK 如果有并列,但是不会跳过之后的位次。如有三个并列第一名则:1,1,1,2,
- ROW_NUMBER 赋予唯一的连续位次,有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位
给出个实例
SELECT product_name
,product_type
,sale_price
,RANK() OVER (ORDER BY sale_price) AS ranking
,DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking
,ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
FROM product
聚合函数在窗口函数上的使用
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;
窗口函数的的应用 - 计算移动平均
指定更加详细的汇总范围。该汇总范围称为框架(frame)。
<窗口函数> 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 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。
运算符
- ROLLUP计算合计及小计
SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP
练习题
2.
SELECT product_name
,regist_date
,sale_price
,SUM(sale_price) OVER(partition by regist_date order by regist_date)
from product;
- ① 窗口函数不指定PARTITION BY的效果是什么?
将当前表格看成一个大窗口进行计算
② 为什么说窗口函数只能在SELECT子句中使用?实际上,在ORDER BY 子句使用系统并不会报错。
窗口函数是对where或者group by子句处理后的结果进行操作