窗口函数
什么是窗口函数
- 窗口函数也称为 OLAP函数
- OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据 进行实时分析处理
- 如市场分析、创建财务报表、创建计划等日常性商务工作
窗口函数的语法
-
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)
-
能够作为窗口函数使用的函数
- ①能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
- ②RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
语法的基本使用方法——使用RANK函数
-
RANK 是用来计算记录排序的函数
-
PARTITION BY 能够设定排序的对象范围
- 在横向上对表进行分组
-
ORDER BY 能够指定按照哪一列、何种顺序进行排序
- 决定纵向排序的规则
-
窗口函数兼具分组和排序两种功能
- 通过 PARTITION BY 分组后的记录集合称为窗口，是代表范围
-- 根据不同的商品种类，按照销售单价从低到高的顺序创建排序表 SELECT product_name, product_type, sale_price, RANK () OVER (PARTITION BY product_type ORDER BY sale_price) AS ranking FROM Product;
无需指定PARTITION BY
- 和使用没有 GROUP BY 的聚合函数时的效果一样，从上到下没有分组的全部排序
-- 不指定PARTITION BY SELECT product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) AS ranking FROM Product;
专用窗口函数的种类
-
RANK函数
- 计算排序时，如果存在相同位次的记录，则会跳过之后的位次
- 例：有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
-
DENSE_RANK函数
- 同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次
- 例：有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
-
ROW_NUMBER函数
- 赋予唯一的连续位次
- 例：有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……
-
由于专用窗口函数无需参数，因此通常括号中都是空的
-- 比较RANK、DENSE_RANK、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子句中使用
- 在SELECT 子句之外“使用窗口函数是没有意义的”
作为窗口函数使用的聚合函数
- 所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同
-- 将SUM函数作为窗口函数使用 SELECT product_id, product_name, sale_price, SUM (sale_price) OVER (ORDER BY product_id) AS current_sum FROM Product; -- 将AVG函数作为窗口函数使用 SELECT product_id, product_name, sale_price, AVG (sale_price) OVER (ORDER BY product_id) AS current_avg FROM Product;
计算移动平均
-
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。
-
在窗口中指定更加详细的汇总范围的备选功能，该备选功 能中的汇总范围称为框架
-
指定框架（汇总范围）
-
ROWS（“行”）和 PRECEDING（“之前”）
-
“ ROWS 2 PRECEDING”，
- 就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限 定为如下的“最靠近的 3行”
- 1.自身（当前记录） 2.之前1行的记录 3.之前2行的记录
-
由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化
- 这样的统计方法称为移动平均（moving average）
- 由于这种方法在希 望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中
-
使用关键字FOLLOWING（“之后”）替换 PRECEDING，就可以指 定“截止到之后 ~ 行”作为框架了
-
-
将当前记录的前后行作为汇总对象
- 同时使用 PRECEDING（“之前”）和 FOLLOWING（“之后”）关 键字来实现
-- 指定“最靠近的3行”作为汇总对象 SELECT product_id, product_name, sale_price, AVG (sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg FROM Product; -- 将当前记录的前后行作为汇总对象 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
- OVER 子句中的 ORDER BY 只是用来决定 窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响
- 在SELECT 语句的最后，使用ORDER BY 子句进行指定。这样就能保证SELECT 语句的结果中记录的排列顺序了
- 将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。
-- 无法保证如下SELECT语句的结果的排列顺序 SELECT product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) AS ranking FROM Product; -- 在语句末尾使用ORDER BY子句对结果进行排序 SELECT product_name, product_type, sale_price, RANK () OVER (ORDER BY sale_price) AS ranking FROM Product ORDER BY ranking;
GROUPING运算符
同时得到合计行
- UNION ALL
-- 使用GROUP BY无法得到合计行 SELECT product_type, SUM(sale_price) FROM Product GROUP BY product_type; -- 分别计算出合计行和汇总结果再通过UNION ALL进行连接 SELECT '合计' AS product_type, SUM(sale_price) FROM Product UNION ALL SELECT product_type, SUM(sale_price) FROM Product GROUP BY product_type;
GROUPING 运算符包含以下 3种：ROLLUP，CUBE，GROUPING SETS
ROLLUP
-
同时得出合计和小计
-
使用方法
- 从语法上来说，就是将 GROUP BY 子句中的聚合键清单像 ROLLUP （<列1>,< 列2>,...）这样使用
- 该运算符的作用，“一次计算出不同聚合键组合的结果”
-
超级分组记录（super group row）
- GROUP BY () 表示没有聚合键，也就相当于没有 GROUP BY 子句（这时会得到全部数据的合计行的记录）
-
将“登记日期”添加到聚合键当中
-- 使用ROLLUP同时得出合计和小计 。 SELECT product_type, SUM(sale_price) AS sum_price FROM Product GROUP BY ROLLUP(product_type); -- 在GROUP BY中添加“登记日期”（不使用ROLLUP） SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type, regist_date; -- 在GROUP BY中添加“登记日期”（使用ROLLUP） SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product GROUP BY ROLLUP(product_type, regist_date);
GROUPING函数——让NULL更加容易分辨
- 用来判断超级分组记录的 NULL 的 特定函数 —— GROUPING 函数
- 该函数在其参数列的值为超级分组记录 所产生的 NULL 时返回 1，其他情况返回0
- 使用GROUPING函数能够简单地分辨出原始数据中的NULL和超级分组记录中的NULL
-- 使用GROUPING函数来判断NULL 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); -- 在超级分组记录的键值中插入恰当的字符串 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);
CUBE——用数据来搭积木
- 所谓 CUBE，就是将 GROUP BY 子句中聚合键的“所有可能的组合” 的汇总结果集中到一个结果中
- 组合的个数就是 2n（n 是聚合键的 个数）
- 可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体
-- 使用CUBE取得全部组合的结果 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——取得期望的积木
- 该运算符可以用于从 ROLLUP 或者 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);