【数据库05】SQL高级处理

---

1. 窗口函数概述

窗口函数也称为OLAP函数，OLAP是OnLine AnalyticalProcessing 的简称，意思是对数据库数据
进行实时分析处理。
窗口函数的意义：保持原数据条数的基础上，增加一列，这一列可以是原数据排名、组内排名、分组后组内聚合值(均值、最大值、最小值)、排序后上下行聚合值、小计合计值等等
常规的 SELECT 语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

窗口函数通用形式，[]内容可以省略

<窗口函数> OVER([PARTITION BY<列名>]
				ORDER BY<排序用列名>)

其中

• PARTITON BY 是用来分组，即选择要看哪个窗口，类似于 GROUP BY 子句的分组功能，但是PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。
• ORDER BY 是用来排序，即决定窗口内，是按那种规则 (字段) 来排序的。
  例如

SELECT product_name,
		produce_type,
		sale_price,
		,RANK() OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM product;

得到的结果是

• PARTITION BY 能够设定窗口对象范围。本例中，为了按照商品种类进行排序，我们指定了 product_type。即一个商品种类就是一个小的" 窗口"。
• ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了 sale_price，这里默认使用的ASC升序排列，如果降序排列需要指明DESC
  

2. 窗口函数的种类

窗口函数大致可分为两类

• 将SUM、MAX、MIN等聚合函数用在窗口函数中
• RANK、DENSE_RANK、ROW_NUMBER函数等排序用的专用窗口函数

2.1 专用窗口函数

专用窗口函数	排序特点	如果存在三条记录排在第一位时的情况
RANK函数（英式排序）	如果存在相同位次的记录，则会跳过之后的位次	1 位、1 位、1 位、4 位……
DENSE_RANK（中式排序）	即使存在相同位次的记录，也不会跳过之后的位次	1 位、1 位、1 位、2 位……
ROW_NUMBER	赋予唯一的连续位次	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;

2.2 聚合函数在窗口函数上的应用

聚合函数在开窗函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值

SELECT product_id
,product_name
,sale_price
,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
FROM product;

注意新加入的current_sum列，可以发现聚合函数结果是，按我们指定的排序，这里是 product_id，当前所在行及之前所有的行的
合计，即累计到当前行的聚合。

3. 窗口函数的应用-计算移动平均

聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合。实际上，还可以指定更加详细的汇总范围。该汇总范围成为框架 (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 product_id
,product_name
,sale_price
,AVG(sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM product;

【注意】原则上，窗口函数只能在 SELECT 子句中使用。窗口函数 OVER 中的 ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

4. GROUPING运算符

4.1 ROLLUP 计算合计及小计

常规的 GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP 关键字。

SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP

这里 ROLLUP 对 product_type, regist_date 两列进行合计汇总。结果实际上有三层聚合，如下图模块 3 是常规的 GROUP BY 的结果，需要注意的是衣服有个注册日期为空的，这是本来数据就存在日期为空的，不是对衣服类别的合计；模块 2 和 1 是 ROLLUP 带来的合计，模块 2 是对产品种类的合计，模
块 1 是对全部数据的总计。

练习题

1. 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。

SELECT product_id
,product_name
,sale_price
,MAX(sale_price) OVER (ORDER BY product_id) AS Current_max_price
FROM product

2. 继续使用 product 表，计算出按照登记日期（regist_date）升序进行排列的各日期的销售单价（sale_price）
的总额。排序是需要将登记日期为 NULL 的“运动 T 恤”记录排在第 1 位（也就是将其看作比其他日期
都早）
使用COALESCE来实现

COALESCE ( expression,value1,value2……,valuen) 

COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。
如果expression不为空值则返回expression；否则判断value1是否是空值，
如果value1不为空值则返回value1；否则判断value2是否是空值，
如果value2不为空值则返回value2；……以此类推，
如果所有的表达式都为空值，则返回NULL。

SELECT regist_date, product_name, sale_price,
       SUM(sale_price) OVER (ORDER BY COALESCE(regist_date, CAST('0001-01-01' AS DATE)))
FROM Product;

3.思考题
(1)窗口函数不指定 PARTITION BY 的效果是什么？
(2)为什么说窗口函数只能在 SELECT 子句中使用？实际上，在 ORDER BY 子句使用系统并不会报错。
(1)排序，新增一列显示
根据order by的排序结果，按使用的聚合函数新增一列显示
(2)select中是为了不改变数据条数的情况下进行组内排序或者聚合操作，order by 中也可用，但是实际没有意义，直接按照想排序的字段进行排序就可以了