SQL：八 SQL高级处理

窗口函数

什么是窗口函数

窗口函数也称为 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);

posted @ 2020-10-11 22:26 颗粒成仓阅读(132) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部