SQL基础教程 Chapter 8

---

typora-copy-images-to: SQL basic

SQL基础教程 Chapter 8 ——SQL高级处理

• 本章将要学习的是SQL 中的高级聚合处理。即使是“高级处理”，说到底
  也还是在SQL 中能够执行的处理。从用户的角度来说，就是那些对数值进行排序，计算销售总额等我们熟悉的处理。和自然语言一样，SQL 语言也会随着时间而不断变化，现在每隔几年就会对标准SQL 进行功能追加和语法修正。本章将要介绍的是最近才添加的功能。掌握了这些方便的新功能，使用SQL 能够完成的工作范围也会不断扩展。

---

8.1 窗口函数

• 窗口函数可以进行排序、生成序列号等一般的聚合函数无法实现的高级
  操作
• 理解PARTITION BY和ORDER BY这两个关键字的含义十分重要

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

• 窗口函数大体可以分为两种

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

• RANK函数

# RANK 函数是来计算记录排序的函数
# 根据不同的商品种类（product_type），按照销售单价（sale_price）从低到高的顺序排序

SELECT product_name, product_type, sale_price,
	rank() over (partition by product_type
                	order by sale_price) as ranking
from product;
# PARTITION BY 能够设定排序的对象范围
# 通过PARTITION BY分组后的记录集合称为“窗口”

• 无需指定PARTITION BY

SELECT product_name, product_type, sale_price,
rank() over (order by sale_price) as ranking
from product;

• 当希望先将表中的数据分为多个部分（窗口），再使用窗口函数时，可以使用PARTITION BY 选项

• 专用窗口函数的种类

  • Rank 函数计算排序时，如果存在相同位次的记录，则会跳过之后的位次
  • 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子句中使用
  • 在DBMS 内部，窗口函数是对WHERE 子句或者GROUP BY 子句处理后的“结果”进行的操作。

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

SELECT product_id, product_name, sale_price,
　SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;
# 计算该合计值的逻辑就像金字塔堆积那样
# 是累计的统计方法

• 用SUM 函数时，并不像RANK 或者ROW_NUMBER 那样括号中
  的内容为空

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

• 像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征

• 计算移动平均

# 指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
						ROWS 2 PRECEDING) AS moving_avg
FROM Product;
# 窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。
# 其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功
# 能中的汇总范围称为框架

• FOLLOWING 替换 PRECEDING

# 将当前记录的前后行作为汇总对象
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

SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product
ORDER BY ranking;
# 两个ORDER BY，功能完全不同

8.2 GROUPING 运算符

• 只使用GROUP BY子句和聚合函数是无法同时得出小计和合计的。如果想
  要同时得到，可以使用GROUPING运算符
• 理解GROUPING运算符中CUBE的关键在于形成“积木搭建出的立方体”的印象
• 虽然GROUPING运算符是标准SQL的功能，但还是有些DBMS尚未支持这
  一功能

---

• 同时得到合计行

# 使用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

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

# 在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 product_type, regist_date with rollup;

# 相当于使用UNION 对如下3种模式的聚合级的不同结果进行连接

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

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

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

# 　在超级分组记录的键值中插入恰当的字符串
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 product_type, regist_date with rollup;

• CUBE函数——用数据来搭积木

• 多了以regist_date 作为聚合键所得到的汇总结果

• GROUPING SETS 函数——取得期望的积木

  • 该运算符可以用于从ROLLUP 或者CUBE 的结果中取出部分记录

APPENDIX

法则8.1 窗口函数兼具分组和排序两种功能

法则8.2 通过PARTITION BY分组后的记录集合称为“窗口”

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

法则8.4 原则上窗口函数只能在SELECT子句中使用

法则8.5 将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录

法则8.6 超级分组记录默认使用NULL作为聚合键

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

法则8.8 使用GROUPING函数能够简单地分辨出原始数据中的NULL和超级分组记录中的NULL

法则8.9 可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体