SQL学习|窗口函数

窗口函数也称为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 子句使用系统并不会报错。