窗口函数也称开窗函数OLAP函数
窗口函数语法
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排列用列清单>)
窗口函数大体分为以下两种
1能做窗口函数的聚合函数:SUM、AVG、COUNT、MAX、MIN
2专用窗口函数:RANK、DENSE_RANK、ROW_NUMBER
--根据不同的商品类型,按照销售单价从低到高的顺序创建排序表
select product_name,product_type,sale_price,
rank() over (partition by product_type order by sale_price) as ranking
from product
窗口函数兼具GROUP BY子句的分组功能以及ORDER BY子句的排序功能。但是,PARTITION BY子句并不具备GROUP BY子句的汇总功能。因此,RANK函数并不会减少原表中记录的行数。
专用窗口函数的种类
RANK函数:计算排序时,如果存在相同位次的记录,则会跳过之后的位次
例:有三条记录排在第1位时:1、1、1、4
DENSE_RANK函数:即使存在相同位次的记录,也不会跳过之后的位次
同上情况例:1、1、1、2
ROW_NUMBER函数:赋予唯一的连续位次
同上情况例:1、2、3、4
窗口函数的适用范围
窗口函数只能在SELECT子句中使用
作为窗口函数使用的聚合函数
--将SUM函数作为窗口函数使用
select product_id,product_name, sale_price
sum (sale_price) over (order by product_id) as current_sum from product;
执行结果
product_id | product_name | sale_price | current_sum |
1 | a | 1000 | 1000 |
2 | b | 500 | 1500 |
3 | c | 4000 | 5500 |
计算移动平均
--指定最靠近的3行作为汇总对象
select product_id, product_name, sale_price
avg(sale_price) over (order by product_id rows 2 preceding) as moving avg
关键字:ROWS行 PRECEDING之前 FOLLOWING之后
窗口函数中的ORDER BY并不会表现为最终展示的行的顺序,所以想要按照顺序排序可在WHERE之后添加另一个ORDER BY