SQL进阶系列之0窗口函数

窗口函数

What's 窗口函数？

窗口函数也称为OLAP(OnLine Analytical Processing)函数，目前MySQL还不支持。

窗口函数的语法

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

能够作为窗口函数使用的函数

• 能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN)
• RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数

语法的基本使用方法-使用RANK函数

-- 使用PARTITION BY进行分组
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分组后的记录集合称为窗口

无需指定PARTITION BY

-- 将全部数据作为一个组
SELECT product_name,product_type,sale_price,RANK() OVER (ORDER BY sale_price) AS ranking FROM Product;

专用窗口函数的分类

• 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;

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

窗口函数的适用范围

窗口函数只能写在SELECT子句的位置，原因在于：窗口函数是对WHERE过滤和GROUP BY分组之后的结果进行的操作，所以在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) AS current_avg
FROM Product;

计算移动平均

-- moving average
SELECT product_id,product_name,sale_price,
AVG(sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg
FROM Product;

指定框架：

• ROWS+PROCEDING两个关键字，将框架指定为截止到之前~行
• FOLLOWING关键词可以指定截止到之后~行。
• 同时使用PRECEDING和FOLLOWING关键字，将当前记录的前后行作为汇总对象

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

-- 窗口函数中的ORDER BY并不能保证最终结果的排序顺序，下图纯属巧合
SELECT product_name,product_type,sale_price,
RANK() OVER (ORDER BY sale_price) AS ranking
FROM Product;

-- 在SELECT后使用ORDER BY子句可以对结果进行排序
SELECT product_name,product_type,sale_price,
RANK() OVER (ORDER BY sale_price) AS ranking
FROM Product ORDER BY ranking;

GROUPING运算符

• why? 只使用GROUP BY子句和聚合函数是无法同时得到小计和合计的。要解决这一问题，得使用GROUPING运算符。
• 理解GROUPING运算符中CUBE的关键在于形成"积木搭建出的立方体"的印象。
• 虽然GROUPING运算符是标准SQL的功能，但部分DBMS尚未支持这一功能。

同时得到合计行

-- 不是用GROUPING如何实现? union 缺点：无法保证总计行的顺序
SELECT '总计' AS product_type,SUM(sale_price) FROM Product
UNION 
SELECT product_type,SUM(sale_price) FROM Product GROUP BY product_type;

ROLLUP-同时得出合计和小计

GROUPING运算符包含：

• ROLLUP
• CUBE
• GROUPING SETS

-- Oracle/SQL Sever/DB2/PostgreSQL
SELECT product_type,SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type);
-- MySQL
SELECT product_type,SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type WITH ROLLUP;

SELECT product_type,regist_date,SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type,regist_date;

-- 同时得到ROLLUP的总计和product_type中个值的小计
-- Oracle/SQL Sever/DB2/PostgreSQL
SELECT product_type,regist_date,SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type,regist_date);
-- MySQL
SELECT product_type,regist_date,SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type,regist_date WITH ROLLUP;

GROUPING函数-让NULL更容易分辨

-- Oracle/SQL Sever/DB2/PostgreSQL
SELECT GROUPING(product_type) AS product_type,
GROUPING(regist_date) AS regist_date,SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type,regist_date);
-- 注意碰到超级分组记录中的NULL时返回1，原始数据为NULL时返回0，这是分辨超级分组记录中的NULL和原始数据中的NULL的方法

-- Oracle/SQL Sever/DB2/PostgreSQL
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,
       COUNT(regist_date)
FROM Product
GROUP BY ROLLUP(product_type,regist_date);

CUBE-用数据搭积木

-- Oracle/SQL Sever/DB2/PostgreSQL
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);

GROUPING SETS - 取得期望的积木

-- Oracle/SQL Sever/DB2/PostgreSQL
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);