学习SQL【10】-SQL高级处理

作者: 爱吃西瓜的番茄酱 | 来源:发表于2017-12-13 20:58 被阅读98次

学习.png

所谓高级处理，从用户的角度来讲，就是那些对数值进行排序，计算销售总额等我们熟悉的处理；从SQL的角度来讲，就是近几年才添加的新功能，这些新功能使得SQL的工作范围不断得到扩展。

窗口函数

窗口函数可以进行排序、生成序列号等一般的聚合函数无法完成的操作。

什么是窗口函数

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

窗口函数的语法

窗口函数：

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

其中重要的关键字是PARTITON BY 和ORDER BY，理解这两个关键字的作用是帮助我们理解窗口函数的关键。

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

窗口函数大致可以分为两种：

能够作为窗口函数的聚合函数（SUM, AVG,COUNT,MAX,MIN）
RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数

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

RANK是用来计算记录排序的函数。
例如，对于Product表，根据不同的商品种类，按照销售单价从低到高的顺序创建排序表：

 --根据不同的商品种类，按照销售单价从低到高的顺序创建排序表
 SELECT product_name, product_type, sale_price,
        RANK () OVER (PARTITION BY product_type                                                       
                          ORDER BY sale_price) AS ranking      
  FROM Product;

执行结果：

 product_name | product_type | sale_price | ranking--------------+--------------+------------+---------
 圆珠笔       | 办公用品     |        100 |       1
 打孔器       | 办公用品     |        500 |       2
 叉子         | 厨房用具     |        500 |       1
 擦菜板       | 厨房用具     |        880 |       2
 菜刀         | 厨房用具     |       3000 |       3
 高压锅       | 厨房用具     |       6800 |       4
 T衫          | 衣服         |       1000 |       1
 运动T衫      | 衣服         |       4000 |       2
(8 行记录)

PARTITON BY能够指定排序的对象范围，在上例中，为了按照商品种类排序，我们指定了Product_type.。

ORDER BY能够指定按照哪一列、何种顺序进行排序，为了按照销售单价的升序进行排序，我们指定了sale_product，默认进行升序排序，（也可以通过指定关键字DESC进行降序排序）。

通过上述例子，我们很容易就理解了PARTITION BY和ORDER BY关键字的作用：PARTITION BY在横向上对表进行分组，ORDER BY决定了纵向排序的规则。

窗口函数兼具了GROUP BY子句的分组功能以及ORDER BY子句的排序功能。
但是PARTITION BY不具备GROUP BY子句的汇总功能。所以使用RANK函数不会减少原表中记录的行数。

通过PARTITION BY分组后的记录集合称为窗口。此处的窗口表示范围。

无需指定PARTITION BY

使用窗口函数时，PARTITION BY并不是必需的，如果我们不使用PARTITION BY，也就是将整个表作为一个大的窗口来使用。

--不指定PARTITION BY
 SELECT product_name, product_type, sale_price,
        RANK () OVER (ORDER BY sale_price) AS ranking      
   FROM Product;

执行结果：

 product_name | product_type | sale_price | ranking--------------+--------------+------------+---------
 圆珠笔       | 办公用品     |        100 |       1
 叉子         | 厨房用具     |        500 |       2
 打孔器       | 办公用品     |        500 |       2
 擦菜板       | 厨房用具     |        880 |       4
 T衫          | 衣服         |       1000 |       5
 菜刀         | 厨房用具     |       3000 |       6
 运动T衫      | 衣服         |       4000 |       7
 高压锅       | 厨房用具     |       6800 |       8
(8 行记录)

专用窗口函数的种类

RANK函数

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例，有3条记录排在第一位时：1位、1位、1位、4位…….
DENSE_RANK函数

同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例，有3条记录排在第一位时：1位、1位、1位、2位…….
ROW_NUMBER函数

赋予唯一的连续位次。
例，有3条记录排在第一位时：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_number      
   FROM Product;

执行结果：

 product_name | product_type | sale_price | ranking | dense_ranking | row_number--------------+--------------+------------+---------+---------------+------------
 圆珠笔       | 办公用品     |        100 |       1 |             1 |          1
 叉子         | 厨房用具     |        500 |       2 |             2 |          2
 打孔器       | 办公用品     |        500 |       2 |             2 |          3
 擦菜板       | 厨房用具     |        880 |       4 |             3 |          4
 T衫          | 衣服         |       1000 |       5 |             4 |          5
 菜刀         | 厨房用具     |       3000 |       6 |             5 |          6
 运动T衫      | 衣服         |       4000 |       7 |             6 |          7
 高压锅       | 厨房用具     |       6800 |       8 |             7 |          8
(8 行记录)

使用RANK或ROW_NUMBER是无需使用任何参数，因此只需要像RANK()这样保持括号为空就可以了。
注释：专用窗口函数无需使用参数。

窗口函数的适用范围

目前为止我们学过的函数大多数都没有使用位置的限制，最多也就是在WHERE子句不能使用聚合函数。但是，使用窗口函数的位置却有很大的限制，确切的说，窗口函数只能在SELECT子句中使用。

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

所有的聚合函数都能用作窗口函数，且使用语法与专用窗口函数完全相同。
例1，将SUM函数作为窗口函数使用：

--将SUM函数作为窗口函数使用
 SELECT product_id, product_name, sale_price,                
        SUM(sale_price) OVER (ORDER BY product_id) AS current_sum     
   FROM Product;

执行结果：

 product_id | product_name | sale_price | current_sum------------+--------------+------------+-------------
 0001       | T衫          |       1000 |        1000
 0002       | 打孔器       |        500 |        1500
 0003       | 运动T衫      |       4000 |        5500
 0004       | 菜刀         |       3000 |        8500
 0005       | 高压锅       |       6800 |       15300
 0006       | 叉子         |        500 |       15800
 0007       | 擦菜板       |        880 |       16680
 0008       | 圆珠笔       |        100 |       16780
(8 行记录)

使用聚合函数作为窗口函数时，需要在其括号内指定相应的列。像上例中，使用sale_price(销售单价)作为累加的对象, current——sum的结果为在它之前的销售单价的合计。这种统计方法称为累计。

例2，将AVG函数作为窗口函数使用：

--将AVG函数作为窗口函数使用
 SELECT product_id, product_name, sale_price,               
        AVG(sale_price) OVER (ORDER BY product_id) AS current_avg      
   FROM Product;

执行结果：

 product_id | product_name | sale_price |      current_avg------------+--------------+------------+-----------------------
 0001       | T衫          |       1000 | 1000.0000000000000000
 0002       | 打孔器       |        500 |  750.0000000000000000
 0003       | 运动T衫      |       4000 | 1833.3333333333333333
 0004       | 菜刀         |       3000 | 2125.0000000000000000
 0005       | 高压锅       |       6800 | 3060.0000000000000000
 0006       | 叉子         |        500 | 2633.3333333333333333
 0007       | 擦菜板       |        880 | 2382.8571428571428571
 0008       | 圆珠笔       |        100 | 2097.5000000000000000
(8 行记录)

current_avg的结果为在它之前的销售单价的平均值。像这样以“自身记录”（当前记录）作为基准进行统计，就是将聚合函数作为窗口函数使用时的最大特征。

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其中还包含在窗口中指定更详细的汇总范围的备选功能，这种备选功能中的汇总范围称为框架。
例如，指定“最靠近的3行”作为汇总对象：

--指定“最靠近的3行”作为汇总对象
 SELECT product_id, product_name, sale_price,                
        AVG(sale_price) OVER (ORDER BY product_id                               
                              ROWS 2 PRECEDING) AS moving_avg      
   FROM Product;

执行结果：

 product_id | product_name | sale_price |      moving_avg------------+--------------+------------+-----------------------
 0001       | T衫          |       1000 | 1000.0000000000000000
 0002       | 打孔器       |        500 |  750.0000000000000000
 0003       | 运动T衫      |       4000 | 1833.3333333333333333
 0004       | 菜刀         |       3000 | 2500.0000000000000000
 0005       | 高压锅       |       6800 | 4600.0000000000000000
 0006       | 叉子         |        500 | 3433.3333333333333333
 0007       | 擦菜板       |        880 | 2726.6666666666666667
 0008       | 圆珠笔       |        100 |  493.3333333333333333
(8 行记录)

指定框架（汇总范围）

上例中，我们使用了ROWS（行）和PRECEDING(之前)两个关键字，将框架指定为“截止到之前~行”，因此，“ ROWS 2 PRECEDING”意思就是将框架指定为“截止到之前2行”，也就是“最靠近的3行”。

自身（当前记录）
之前1行的记录
之前2行的记录

如果将条件中的数字改为“ROWS 5 PRECEDING”，就是“截止到之前5行”（最靠近的6行）的意思。

这样的统计方法称为移动平均。

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

将当前记录的前后行作为汇总对象

如果希望将当前记录的前后行作为汇总对象，可以同时使用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;

执行结果：

product_id | product_name | sale_price |      moving_avg------------+--------------+------------+-----------------------
 0001       | T衫          |       1000 |  750.0000000000000000
 0002       | 打孔器       |        500 | 1833.3333333333333333
 0003       | 运动T衫      |       4000 | 2500.0000000000000000
 0004       | 菜刀         |       3000 | 4600.0000000000000000
 0005       | 高压锅       |       6800 | 3433.3333333333333333
 0006       | 叉子         |        500 | 2726.6666666666666667
 0007       | 擦菜板       |        880 |  493.3333333333333333
 0008       | 圆珠笔       |        100 |  490.0000000000000000
(8 行记录)

当前记录的前后行的具体含义就是：

之前1行的记录
自身（当前记录）
之后1行的记录

如果能够熟练掌握框架功能，就可以称为窗口函数高手了。

GROUPING运算符

只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的，如果想要同时得到，可以使用GROUPING运算符。

ROLLUP—同时得出合计和小计

使用GROUPING运算符可以很容易就得到合计和小计。
GROUPING运算符包含下列三种：

ROLLUP
CUBE
GROUPING SETS

ROLLUP的使用方法

用一个例子说明：

--使用ROLLUP同时得出合计和小计
 SELECT product_type, SUM(sale_price) AS sum_price     
   FROM Product   
  GROUP BY ROLLUP(product_type);

执行结果：

 product_type | sum_price--------------+-----------
 办公用品     |       600
 厨房用具     |     11180
 衣服         |      5000
              |     16780
(4 行记录)

从语法上来说，就是将GROUP BY子句中的聚合键清单像ROLLUP(<列1>, <列2>,…)这样使用。

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

对了，还有一个超级分组记录的概念。超级记录分组就是使用GROUP BY()时，未指定聚合键，这是会得到全部数据的合计行的记录，该合计行称为超级分组记录。超级分组记录默认使用NULL作为聚合键。

将“登记日期”添加到聚合键中

我们再来使用一个例子来理解理解ROLLUP的作用。
在GROUP BY中添加“登记日期”（不使用ROLLUP）:

--在GROUP BY中添加“登记日期”（不使用ROLLUP）
 SELECT product_type, regist_date, SUM(sale_price) AS sum_price      
   FROM Product    
  GROUP BY product_type, regist_date;

执行结果：

 product_type | regist_date | sum_price--------------+-------------+-----------
 厨房用具     | 2017-09-20  |      3500
 衣服         |             |      4000
 厨房用具     | 2016-04-28  |       880
 厨房用具     | 2017-01-15  |      6800
 办公用品     | 2017-11-11  |       100
 衣服         | 2017-09-20  |      1000
 办公用品     | 2017-09-11  |       500
(7 行记录)

再看看使用ROLLUP之后会是什么样子：

--在GROUP BY中添加“登记日期”（使用ROLLUP）
 SELECT product_type, regist_date, SUM(sale_price) AS sum_price      FROM Product    GROUP BY ROLLUP(product_type, regist_date);

执行结果：

 product_type | regist_date | sum_price--------------+-------------+-----------
 办公用品     | 2017-09-11  |       500
 办公用品     | 2017-11-11  |       100
 办公用品     |             |       600
 厨房用具     | 2016-04-28  |       880
 厨房用具     | 2017-01-15  |      6800
 厨房用具     | 2017-09-20  |      3500
 厨房用具     |             |     11180
 衣服         | 2017-09-20  |      1000
 衣服         |             |      4000
 衣服         |             |      5000
              |             |     16780
(11 行记录)

将上述两个结果进行比较后，我们就可以发现，使用ROLLUP时，多出了最下面的合计行以及3条不同商品种类的小计行。这4行就是我们所说的超级分组记录。

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

在上例中我们会发现，在超级分组记录中，regist_date列为NULL，而在原始记录中，“运动T衫”的登记日期同样为NULL，那么这两种NULL如何分辨呢？

为了避免混淆，SQL提供了一个用来判断超级分组记录的NULL的特定函数—GROUPING函数。该函数在其参数列的值为超级分组记录所产生的NULL时返回1，其他情况下返回0（其他情况包括原始记录为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);

执行结果：

 product_type | regist_date | sum_price--------------+-------------+-----------
            0 |           0 |       500
            0 |           0 |       100
            0 |           1 |       600
            0 |           0 |       880
            0 |           0 |      6800
            0 |           0 |      3500
            0 |           1 |     11180
            0 |           0 |      1000
            0 |           0 |      4000
            0 |           1 |      5000
            1 |           1 |     16780
(11 行记录)

这样就能分辨超级分组记录中的NULL和原始记录中的NULL了。

CUBE—用数据来搭积木

ROLLUP之后我们学习另一个GROUPING运算符—CUBE。CUBE是“立方体”的意思。它的语法和ROLLUP相同，只需要将ROLLUP替换为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_type,                SUM(sale_price) AS sum_price      FROM Product    GROUP BY CUBE(product_type, regist_date);

执行结果：

product_type  |  regist_type  | sum_price---------------+---------------+-----------
 办公用品      | 2017-09-11    |       500
 办公用品      | 2017-11-11    |       100
 办公用品      | 登记日期 合计 |       600
 厨房用具      | 2016-04-28    |       880
 厨房用具      | 2017-01-15    |      6800
 厨房用具      | 2017-09-20    |      3500
 厨房用具      | 登记日期 合计 |     11180
 衣服          | 2017-09-20    |      1000
 衣服          |               |      4000
 衣服          | 登记日期 合计 |      5000
 商品种类 合计 | 登记日期 合计 |     16780
 商品种类 合计 | 2016-04-28    |       880
 商品种类 合计 | 2017-01-15    |      6800
 商品种类 合计 | 2017-09-11    |       500
 商品种类 合计 | 2017-09-20    |      4500
 商品种类 合计 | 2017-11-11    |       100
 商品种类 合计 |               |      4000
(17 行记录)

与ROLLUP相比，CUBE多出了几行，多出来的记录就是将regist_date作为聚合键所得到的汇总结果。

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

对于CUBE来说，一个聚合键就相当于立方体的一个轴，而结果就像是将数据像积木那样堆积起来。可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体（有点抽象了）。

GROUPING SETS—取得期望的积木

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_type,                 SUM(sale_price) AS sum_price      FROM Product    GROUP BY GROUPING SETS(product_type, regist_date);

执行结果：

 product_type  |  regist_type  | sum_price---------------+---------------+-----------
 办公用品      | 登记日期 合计 |       600
 厨房用具      | 登记日期 合计 |     11180
 衣服          | 登记日期 合计 |      5000
 商品种类 合计 | 2016-04-28    |       880
 商品种类 合计 | 2017-01-15    |      6800
 商品种类 合计 | 2017-09-11    |       500
 商品种类 合计 | 2017-09-20    |      4500
 商品种类 合计 | 2017-11-11    |       100
 商品种类 合计 |               |      4000
(9 行记录)

不过和ROLLUP或者CUBE比起来，使用GROUPING SETS的机会很少。

SQL的基础语法知识大致就是这些，如果你稍微会一点SQL，看到这些代码应该很好理解的。如果你没有编程基础，可能这些代码对于你来说过于难以理解。写的很粗糙，勉强可以用来温习SQL的语法。大概也只有这个作用了。

每天学习一点点，每天进步一点点。

学习SQL【10】-SQL高级处理

窗口函数

什么是窗口函数

窗口函数的语法

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

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

无需指定PARTITION BY

专用窗口函数的种类

窗口函数的适用范围

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

计算移动平均

指定框架（汇总范围）

将当前记录的前后行作为汇总对象

GROUPING运算符

ROLLUP—同时得出合计和小计

ROLLUP的使用方法

将“登记日期”添加到聚合键中

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

CUBE—用数据来搭积木

GROUPING SETS—取得期望的积木

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

程序员

大数据爬虫Python AI Sql

大数据

玩转大数据

学习SQL【10】-SQL高级处理

窗口函数

什么是窗口函数

窗口函数的语法

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

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

无需指定PARTITION BY

专用窗口函数的种类

窗口函数的适用范围

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

计算移动平均

指定框架（汇总范围）

将当前记录的前后行作为汇总对象

GROUPING运算符

ROLLUP—同时得出合计和小计

ROLLUP的使用方法

将“登记日期”添加到聚合键中

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

CUBE—用数据来搭积木

GROUPING SETS—取得期望的积木

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

程序员

大数据 爬虫Python AI Sql

大数据

玩转大数据

大数据爬虫Python AI Sql