窗口函数也称为OLAP函数。OLAP 是 OnLine AnalyticalProcessing 的简称,意思是对数据库数据进行实时分析处理。
窗口函数的一般形式:
<窗口函数> OVER ([ PARTITION BY <列名>]
ORDER BY
<排序用列名>)
- PARTITON BY 是用来分组,即选择要看哪个窗口,类似于 GROUP BY 子句的分组功能,但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能,并不会改变原始表中记录的行数。
- ORDER BY 是用来排序,即决定窗口内,是按那种规则(字段)来排序的。
举个例子:
SELECT
product_name,
product_type,
sale_price,
RANK() OVER ( PARTITION BY product_type ORDER BY sale_price ) AS ranking
FROM
product
这里用product_type进行一个分类,然后再对每一个类别下的价格进行排序。
窗口函数分类
排序专用窗口函数
- RANK函数
存在相同位次跳过,如1,2,2,4 - DENSE_RANK
不会跳过位次,如1,1,1,2 - ROW_NUMBER
不会出现相同位次,即使一样也会是不同次序
如下:
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;
计算移动平均
SELECT
product_id,
product_name,
sale_price,
AVG( sale_price ) OVER ( ORDER BY product_id ROWS 2 PRECEDING ) AS moving_avg,
AVG( sale_price ) OVER ( ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS moving_avg
FROM
product;
可以使用PRECEDING和FOLLOWING来指定聚合函数的作用范围。
如上面,ROWS 2 PRECEDING表示从当前行开始,加上当前行前面两行的数据,然后平均。
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING则表示当前行和当前行前一行与后一行,然后平均。
结果如下:
GROUPING运算符
ROLLUP - 计算合计及小计
SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP
练习题
5.1
下面语句的执行结果
SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product
这个语句的执行结果是从上往下当前所有记录中售价最高的价格。
5.2
继续使用product表,计算出按照登记日期(regist_date)升序进行排列的各日期的销售单价(sale_price)的总额。排序是需要将登记日期为NULL 的“运动 T 恤”记录排在第 1 位(也就是将其看作比其他日期都早)
SELECT
product_id,
product_name,
sale_price,
sum( sale_price ) OVER (
ORDER BY
IF
( ISNULL( regist_date ), 0, 1 )) AS Current_sum_price
FROM
product;
5.3
思考题
① 窗口函数不指定PARTITION BY的效果是什么?
② 为什么说窗口函数只能在SELECT子句中使用?实际上,在ORDER BY 子句使用系统并不会报错。