mysql基础 Task05：SQL高级处理

1. 窗口函数

1.1 窗口函数概念及基本的使用方法

窗口函数也称为OLAP函数。OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。

为了便于理解，称之为 窗口函数。常规的 SELECT 语句是对整张表进行查询，而窗口函数可以有选择的去某一部分数据进行汇总、计算和排序。

MySQL 5.7 不支持窗口函数。

窗口函数的通用形式：

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

窗口函数最关键的是搞明白关键字 PARTITON BY 和 ORDER BY 的作用。

• PARTITON BY ：用来分组，即选择要看哪个窗口，类似于 GROUP BY 子句的分组功能，但是 PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能，并不会改变原始表中记录的行数。

• ORDER BY ：用来排序，即决定窗口内，是按那种规则(字段)来排序的。

1.2 举个栗子

SELECT
	product_name,
	product_type,
	sale_price,
	RANK() OVER (
    PARTITION BY product_type
    ORDER BY sale_price
  	) AS ranking
FROM tbl_product;
+-----------------+-----------------+------------+---------+
| product_name    | product_type    | sale_price | ranking |
+-----------------+-----------------+------------+---------+
| T-shirt         | clothes         |       1000 |       1 |
| sports T-shirt  | clothes         |       4000 |       2 |
| fork            | kitchenware     |        500 |       1 |
| grater          | kitchenware     |        880 |       2 |
| kitchen knife   | kitchenware     |       3000 |       3 |
| pressure cooker | kitchenware     |       6800 |       4 |
| ballpoint       | Office supplies |        100 |       1 |
| puncher         | Office supplies |        500 |       2 |
+-----------------+-----------------+------------+---------+

先忽略生成的新列 - [ranking]， 看下原始数据在 PARTITION BY 和 ORDER BY 关键字的作用下发生了什么变化。

• PARTITION BY ：设定窗口对象范围。本例中，为了按照商品种类进行排序，指定了 product_type 。即一个商品种类就是一个小的 " 窗口 " 。

• ORDER BY：指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，指定了sale_price。窗口函数中的 ORDER BY 与SELECT 语句末尾的 ORDER BY 一样。

2. 窗口函数种类

窗口函数可以分为两类。

1. 将SUM、AVG、COUNT、MAX、MIN等聚合函数。

2. RANK、DENSE_RANK、ROW_NUMBER等排序用的专用窗口函数。

2.1 专用窗口函数

标准 SQL 定义的 OLAP 专用函数，都称为专用窗口函数。

2.1.1 RANK函数

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

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

2.1.2 ROW_NUMBER函数

赋予唯一的连续位次。

例如：有 3 条记录排在第 1 位时：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 tbl_product

2.2 聚合函数在窗口函数上的使用

聚合函数在窗口函数中的使用方法和之前的专用窗口函数一样，只是结果是一个 累计的聚合函数值 。

运行以下代码：

SELECT
  product_name,
  product_type,
  sale_price,
  SUM(sale_price) OVER (PARTITION BY product_type ORDER BY sale_price) AS current_sum,
  AVG(sale_price) OVER (PARTITION BY product_type ORDER BY sale_price) AS current_avg
FROM tbl_product;
+-----------------+-----------------+------------+-------------+-------------+
| product_name    | product_type    | sale_price | current_sum | current_avg |
+-----------------+-----------------+------------+-------------+-------------+
| T-shirt         | clothes         |       1000 |        1000 |   1000.0000 |
| sports T-shirt  | clothes         |       4000 |        5000 |   2500.0000 |
| fork            | kitchenware     |        500 |         500 |    500.0000 |
| grater          | kitchenware     |        880 |        1380 |    690.0000 |
| kitchen knife   | kitchenware     |       3000 |        4380 |   1460.0000 |
| pressure cooker | kitchenware     |       6800 |       11180 |   2795.0000 |
| ballpoint       | Office supplies |        100 |         100 |    100.0000 |
| puncher         | Office supplies |        500 |         600 |    300.0000 |
+-----------------+-----------------+------------+-------------+-------------+

3. 窗口函数的的应用

3.1 计算移动平均

在上面提到，聚合函数在窗口函数使用时，计算的是累积到当前行的所有的数据的聚合。

实际上，还可以指定更加详细的汇总范围。该汇总范围称为框架( frame )。

语法

<窗口函数> OVER (ORDER BY <排序用列清单>
                 ROWS n PRECEDING ) 
                 
<窗口函数> OVER (ORDER BY <排序用列清单>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

• PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行。
• FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行。
• BETWEEN n PRECEDING AND n FOLLOWING，将框架指定为 “之前n行” + “之后n行” + “自身行”。

执行以下代码：

SELECT
  product_id,
  product_name,
  sale_price,
  AVG(sale_price) OVER (ORDER BY product_id
                        ROWS 2 PRECEDING) AS moving_avg,
  AVG(sale_price) OVER (ORDER BY product_id
                        ROWS BETWEEN 1 PRECEDING 
                        AND 1 FOLLOWING) AS moving_avg  
FROM tbl_product;
+------------+-----------------+------------+------------+------------+
| product_id | product_name    | sale_price | moving_avg | moving_avg |
+------------+-----------------+------------+------------+------------+
| 0001       | T-shirt         |       1000 |  1000.0000 |   750.0000 |
| 0002       | puncher         |        500 |   750.0000 |  1833.3333 |
| 0003       | sports T-shirt  |       4000 |  1833.3333 |  2500.0000 |
| 0004       | kitchen knife   |       3000 |  2500.0000 |  4600.0000 |
| 0005       | pressure cooker |       6800 |  4600.0000 |  3433.3333 |
| 0006       | fork            |        500 |  3433.3333 |  2726.6667 |
| 0007       | grater          |        880 |  2726.6667 |   493.3333 |
| 0008       | ballpoint       |        100 |   493.3333 |   490.0000 |
+------------+-----------------+------------+------------+------------+

3.2 窗口函数适用范围和注意事项

• 原则上，窗口函数只能在 SELECT 子句中使用。
• 窗口函数 OVER 中的 ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

4. GROUPING 运算符

4.1 ROLLUP - 计算合计及小计

常规的 GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP 关键字。

SELECT
  product_type,
  register_date,
  SUM(sale_price) AS sum_price
FROM tbl_product
GROUP BY product_type, register_date WITH ROLLUP;

这里 ROLLUP 对 product_type 、register_date 两列进行合计汇总。结果实际上有三层聚合，如下图 模块 3 是常规的 GROUP BY 的结果，需要注意的是clothes 有个 register_date 为 NULL ，这是本来数据就存在日期为空的，不是对衣服类别的合计； 模块 2 和模块 1 是 ROLLUP 带来的合计，模块 2 是对产品种类的合计，模块 1 是对全部数据的总计。

MySQL 5.7 不支持 CUBE 和 GROUPING SETS。

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

4.2.1 为什么要使用 GROUPING 函数

上面使用 ROLLUP 所得到的结果是有问题的，问题出在 “ clothes ” 的分组之中，register_date 列有 2 条记录为 NULL，但其原因却并不相同。

+-----------------+---------------+-----------+
| product_type    | register_date | sum_price |
+-----------------+---------------+-----------+
| clothes         | NULL          |      4000 |
| clothes         | 2009-09-20    |      1000 |
| clothes         | NULL          |      5000 |

sum_price 为 4000 的记录，因为 sports T-shirt 的 register_date 为 NULL，所以就把 NULL 作为聚合键了。相反，sum_price 为 5000 的记录，就是超级分组记录的 NULL 了（具体为1000 + 4000 = 5000）。但两者看上去都是 “ NULL ”，实在是难以分辨。

为了避免混淆，SQL 提供了一个用来判断超级分组记录的 NULL 的特定函数—— GROUPING函数。该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1，其他情况返回 0。

4.2.2 使用GROUPING 函数判断 NULL

SELECT
  GROUPING(product_type) AS product_type,
  GROUPING(regist_date) AS register_date,
  SUM(sale_price) AS sum_price
FROM tbl_product
GROUP BY ROLLUP(product_type, register_date);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(product_type, register_date)' at line 6

说明 MySQL 8.0.26 还不支持 GROUPING 函数。

练习题

1.请说出针对本章中使用的 tbl_product（商品）表执行如下 SELECT 语句所能得到的结果。

SELECT
  product_id,
  product_name,
  sale_price,
  MAX(sale_price) OVER (ORDER BY product_id) AS current_max_price
FROM tbl_product;

查询 tbl_product 表的 product_id、product_name、sale_price 以及按 product_id 升序排列的累计最大售价。

# 运行结果
+------------+-----------------+------------+-------------------+
| product_id | product_name    | sale_price | current_max_price |
+------------+-----------------+------------+-------------------+
| 0001       | T-shirt         |       1000 |              1000 |
| 0002       | puncher         |        500 |              1000 |
| 0003       | sports T-shirt  |       4000 |              4000 |
| 0004       | kitchen knife   |       3000 |              4000 |
| 0005       | pressure cooker |       6800 |              6800 |
| 0006       | fork            |        500 |              6800 |
| 0007       | grater          |        880 |              6800 |
| 0008       | ballpoint       |        100 |              6800 |
+------------+-----------------+------------+-------------------+

2. 继续使用 tbl_product 表，计算出按登记日期（register_date）升序进行排列的各日期的售价（sale_price）的总额。排序是需要将登记日期为 NULL 的 “运动 T 恤” 记录排在第 1 位（也就是将其看作比其他日期都早）。

select
	product_name,
	register_date,
	sum(sale_price) as sum_sale_price
from tbl_product
group by register_date, product_name
order by register_date;
+-----------------+---------------+----------------+
| product_name    | register_date | sum_sale_price |
+-----------------+---------------+----------------+
| sports T-shirt  | NULL          |           4000 |
| grater          | 2008-04-28    |            880 |
| pressure cooker | 2009-01-15    |           6800 |
| puncher         | 2009-09-11    |            500 |
| fork            | 2009-09-20    |            500 |
| kitchen knife   | 2009-09-20    |           3000 |
| T-shirt         | 2009-09-20    |           1000 |
| ballpoint       | 2009-11-11    |            100 |
+-----------------+---------------+----------------+

3. 思考题

3.1 窗口函数不指定 PARTITION BY 的效果是什么？

不指定 PARTITION BY 表示，窗口函数的范围是表中的每一个记录行，此时窗口函数范围最小。

3.2 为什么说窗口函数只能在 SELECT 子句中使用？实际上，在 ORDER BY 子句使用系统并不会报错。

窗口函数的目的是对数据进行实时分析处理，对表中某一部分进行计算、统计、排序。select 子句是对数据筛选的，这时可以做处理。但是 order by子句是做排序的，此时无法处理，只能排序。

参考文献

1. https://github.com/datawhalechina/wonderful-sql
2. (日) MICK著; 孙淼, 罗勇译. 图灵程序设计丛书 SQL基础教程 第2版[M]. 北京: 人民邮电出版社, 2017.05.