8-1 窗口函数

一、什么是窗口函数

窗口函数也称为 OLAP 函数。OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

二、窗口函数的语法

<窗口函数> OVER ([PARTITION BY <列清单>]

ORDER BY <排序用列清单>)

能够做为窗口函数的函数：

① 能够作为窗口函数的聚合函数（ SUM、 AVG、 COUNT、 MAX、 MIN）

② RANK、 DENSE_RANK、 ROW_NUMBER 等专用窗口函数

三、语法的基本使用方法——使用RANK函数

RANK 是用来计算记录排序的函数。

例：select product_name,product_type,sale_price,

rank() over (partition by product_type

order by sale_price)as ranking

from product;

partition by 能够设定排序的对象范围；ORDER BY 能够指定按照哪一列、何种顺序进行排序。

通过PARTTION BY分组后的记录的集合可以称为窗口，这里的窗口代表的是范围。窗口函数兼具分组和排序两种功能。

四、无需指定PARTITION BY

不指定PARTITION BY时，是将整个数据作为一个大的窗口来进行使用。

指定PARTITION BY时，是将数据分为多个部分，然后再使用窗口函数。

五、专用窗口函数的种类

● RANK函数

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

● DENSE_RANK函数

同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

● ROW_NUMBER函数

赋予唯一的连续位次。

例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

由于专用窗口函数无需参数，因此通常括号中都是空的。

六、窗口函数的适用范围

原则上窗口函数只能在SELECT子句中使用。

七、作为窗口函数使用的聚合函数

例：select product_id,product_name,sale_price,

sum(sale_price) over(order by product_id) as current_sum

from product;

在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。

以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。

八、计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。

①SELECT product_id, product_name, sale_price,

AVG (sale_price) OVER (ORDER BY product_id

ROWS 2 PRECEDING) AS moving_avg

FROM Product;

上面的语句中我们使用ROWS（“行”）和 PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING“就是截止到之前2行”，即最靠近的3行：自身（当前的记录），之前1行的记录，之前2行的记录。

②使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指定“截止到之后 ~ 行”作为框架了。

例：SELECT product_id, product_name, sale_price,

AVG (sale_price) OVER (ORDER BY product_id

ROWS BETWEEN 1 PRECEDING AND 

1 FOLLOWING) AS moving_avg

FROM Product;

上面的语句是将自身的记录，前一行的记录及后一行的记录取出并且求平均值。

九、ORDER BY

OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。

如果想要将查询的结果进行排序，还是需要在聚合函数以外再使用ORDER BY进行排序。将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。

8-2 GROUPING 运算符

GROUPING 运算符包含以下 3 种 。

●ROLLUP

●CUBE

●GROUPING SETS

一、ROLLUP---同时得出合计和小计

例：select product_type,regist_date,sum(sale_price)

from product

group by rollup(product_type,regist_date);

超级分组记录默认使用NULL作为聚合键。

ROLLUP可以同时得出合计和小计，是非常方便的工具。

二、GROUPING函数——让NULL更加容易分辨

例：

SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类 合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期 合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_price

FROM Product

GROUP BY ROLLUP(product_type, regist_date);

使用GROUPING函数能够简单地分辨出原始数据中的NULL和超级分组记录中的NULL。

三、CUBE——用数据来搭积木

用法与rollup一样。

例:SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类 合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期 合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_price

FROM Product

GROUP BY cube(product_type, regist_date);

所谓 CUBE，就是将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是 2的n次方（n 是聚合键的个数）。

可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体。

四、GROUPING SETS——取得期望的积木

使用的机会比较少。

例:SELECT CASE WHEN GROUPING(product_type) = 1

THEN '商品种类 合计'

ELSE product_type END AS product_type,

CASE WHEN GROUPING(regist_date) = 1

THEN '登记日期 合计'

ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,

SUM(sale_price) AS sum_price

FROM Product

GROUP BY grouping sets(product_type, regist_date);

练习题：

8.1

得出的结果是按照产品编号排序，截止到目前排序的产品的最大单价

SELECT product_id, product_name, sale_price,

MAX (sale_price) OVER (ORDER BY product_id) AS current_max_price

FROM Product;

8.2

select product_id,product_name,sale_price,regist_date,

sum(sale_price)over (order by regist_date) as current_dateprice

from product;