SQL基础教程笔记：第八章 SQL高级处理

目录

• 窗口函数
  • 学习重点
  • 什么是窗口函数
  • 窗口函数的语法
  • 语法的基本使用方法——使用RANK函数
  • 无需指定PARTITION BY
  • 专用窗口函数的种类
  • 窗口函数的适用范围
  • 作为窗口函数使用的聚合函数
  • 计算移动平均
  • 两个ORDER BY
• GROUPING运算符
  • 学习重点
  • 同时得到合计行
  • ROLLUP——同时得出合计和小计
  • GROUPING函数——让NULL更加容易分辨
  • CUBE——用数据来搭积木
  • GROUPING SETS——取得期望的积木
• 练习题

窗口函数

学习重点

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

什么是窗口函数

OLAP是OnLine Analytical Processing的简称，意思是对数据库数据进行实时分析处理。 例如，市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数也称为 OLAP 函数。为了让大家快速形成直观印象，才起了这样一个容易理解的名称，窗口函数就是为了实现OLAP而添加的标准SQL功能。从MySQL8.0开始已经支持窗口函数。

窗口函数的语法

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

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

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

正如其名称所示，RANK是用来计算记录排序的函数。

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

PARTITION BY能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了 product_type。ORDER BY 能够指定按照哪一列、何种顺序进行排序。

窗口函数兼具之前我们学过的 GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是， PARTITION BY 子句并不具备GROUP BY 子句的汇总功能。因此，使用 RANK 函数并不会减少原表中记录的行数，结果中仍然包含 8 行数据。
通过PARTITION BY分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是“窗口函数”名称的由来。从词语意思的角度考虑，可能“组”比“窗口”更合适一些，但是在SQL 中，“组”更多的是用来特指使用GROUP BY 分割后的记录集合，因此，为了避免混淆，使用PARTITION BY 时称为窗口。

无需指定PARTITION BY

使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中， PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。不指定PARTITION BY,这和使用没有GROUP BY 的聚合函数时的效果一样，也就是将整个表作为一个大的窗口来使用。

-- 不指定PARTITION BY
-- Oracle SQL Server DB2 PostgreSQL
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 位……

除此之外，各DBMS还提供了各自特有的窗口函数。上述3个函数（对于支持窗口函数的DBMS来说）在所有的DBMS中都能够使用。

-- 比较RANK、 DENSE_RANK、 ROW_NUMBER的结果
-- Oracle SQL Server DB2 PostgreSQL
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子句之中。反过来说，就是这类函数不能在WHERE子句或者GROUP BY子句中使用.

语法上，除了SELECT子句，ORDER BY子句或者UPDATE语句的SET子句中也可以使用。但因为几乎没有实际的业务示例，所以开始的时候大家只要记得“只能在SELECT 子句中使用”就可以了。

虽然我们可以把它当作一种规则死记硬背下来，但是为什么窗口函数只能在SELECT子句中使用呢（也就是不能在WHERE子句或者 GROUPBY子句中使用)?下面我们就来简单说明一下其中的理由。其理由就是，在 DBMS 内部，窗口函数是对WHERE子句或者GROUP BY子句处理后的“结果”进行的操作。大家仔细想一想就会明白，

• 在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的。
• 在得到排序结果之后，如果通过 WHERE 子句中的条件除去了某些记录，或者使用 GROUP BY 子句进行了汇总处理，那好不容易得到的排序结果也无法使用了。

正是由于这样的原因， 在SELECT子句之外“使用窗口函数是没有意义的”，所以在语法上才会有这样的限制。反之，之所以在ORDER BY 子句中能够使用窗口函数，是因为ORDER BY子句会在SELECT子句之后执行，并且记录保证不会减少。

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

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。

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

计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序,计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。

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

从结果中我们可以看到， current_avg 的计算方法确实是计算平均值的方法，但作为统计对象的却只是“排在自己之上”的记录。像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。

计算移动平均

窗口函数就是将表以窗口为单位进行分割,并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。其语法需要在ORDER BY子句之后使用指定范围的关键字.

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

这里我们使用了ROWS（“行”）和PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前 2 行”(个人：上边界)，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。

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

也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。
如果将条件中的数字变为“ROWS 5 PRECEDING”，就是“截止到之前 5 行”（最靠近的 6 行）的意思。这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

使用关键字FOLLOWING(“之后”)替换PRECEDING,就可以指定“截止到之后~ 行”作为框架了（图 8-3）.

如果希望将当前记录的前后行作为汇总对象时，可以同时使用PRECEDING（“之前”）和FOLLOWING（“之后”）关键字来实现。

-- 将当前记录的前后行作为汇总对象
-- Oracle SQL Server DB2 PostgreSQL
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;

在上述代码中，我们通过指定框架，将“1 PRECEDING”（之前 1 行）和“1 FOLLOWING”（之后 1 行）的区间作为汇总对象。具体来说，就是将如下3行作为汇总对象来进行计算（图 8-4）。

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

两个ORDER BY

OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。因此也有可能得到一个记录的排列顺序比较混乱的结果。有些DBMS也可以按照窗口函数的ORDER BY子句所指定的顺序对结果进行排序，但那也仅仅是个例而已。

-- 无法保证如下SELECT语句的结果的排列顺序
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;

那么，如何才能让记录切实按照ranking列的升序进行排列呢？答案非常简单。那就是在SELECT语句的最后，使用 ORDER BY子句进行指定。这样就能保证SELECT语句的结果中记录的排列顺序了，除此之外也没有其他办法了。

-- 在语句末尾使用ORDER BY子句对结果进行排序
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_name, product_type, sale_price,
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product
ORDER BY ranking;

也许大家会觉得在一条SELECT语句中使用两次ORDER BY会有点别扭，但是尽管这两个ORDER BY 看上去是相同的，但其实它们的功能却完全不同。

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

GROUPING运算符

学习重点

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

同时得到合计行

-- 分别计算出合计行和汇总结果再通过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;

虽然也可以使用UNION来代替UNION ALL，但由于两条SELECT语句的聚合键不同，一定不会出现重复行，因此可以使用UNION ALL。UNION ALL和UNION的不同之处在于它不会对结果进行排序，因此比UNION 的性能更好。

ROLLUP——同时得出合计和小计

标准 SQL 引入了 GROUPING 运算符，使用该运算符就能通过非常简单的SQL得到之前那样的汇总单位不同的汇总结果了。
GROUPING 运算符包含以下3种。

• ROLLUP
• CUBE
• GROUPING SETS

目前 PostgreSQL 和 MySQL 并不支持GROUPING 运算符（MySQL 仅支持ROLLUP）。

ROLLUP的使用方法:

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

在MySQL中执行代码时，请将GROUP BY子句改写为“GROUP BY product_type WITH ROLLUP;”。

从语法上来说，就是将GROUP BY子句中的聚合键清单像ROLLUP（< 列 1>,< 列 2>,...）这样使用。该运算符的作用，一言以蔽之，就是“一次计算出不同聚合键组合的结果”。例如，在本例中就是一次计算出了如下两种组合的汇总结果。

①中的 GROUP BY() 表示没有聚合键，这时会得到全部数据的合计行的记录,该合计行记录称为超级分组记录（super group row）,超级分组记录的 product_type列的键值（对DBMS来说)并不明确，因此会默认使用 NULL。之后会为大家讲解在此处插入恰当的字符串的方法。
超级分组记录默认使用NULL作为聚合键。

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

-- 在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）
-- Oracle SQL Server DB2 PostgreSQL
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
FROM Product
GROUP BY ROLLUP(product_type, regist_date);

在MySQL中执行代码，请将GROUP BY子句改写为“GROUP BY product_type, regist_date WITH ROLLUP;”。

该SELECT语句的结果相当于使用UNION对如下3种模式的聚合级的不同结果进行连接（图 8-5）。

ROLLUP 是“卷起”的意思，比如卷起百叶窗、窗帘卷，等等。其名称也形象地说明了该操作能够得到像从小计到合计这样，从最小的聚合级开始，聚合单位逐渐扩大的结果。

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

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

在“衣服”的分组之中，有两条记录的 regist_date 列为 NULL，但其原因却并不相同。

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

-- 使用GROUPING函数来判断NULL
-- Oracle SQL Server DB2 PostgreSQL
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);

这样就能分辨超级分组记录中的 NULL 和原始数据本身的 NULL 了。使用 GROUPING 函数还能在超级分组记录的键值中插入字符串。也就是说，当 GROUPING 函数的返回值为 1 时，指定“合计”或者“小计”等字符串，其他情况返回通常的列的值.

-- 在超级分组记录的键值中插入恰当的字符串
-- Oracle SQL Server DB2 PostgreSQL
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);

那为什么还要将SELECT子句中的regist_date列转换为CAST（regist_date AS VARCHAR（16））形式的字符串呢？这是为了满足CASE 表达式所有分支的返回值必须一致的条件。如果不这样的话，那么各个分支会分别返回日期类型和字符串类型的值，执行时就会发生语法错误。

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

CUBE——用数据来搭积木

CUBE 的语法和 ROLLUP 相同，只需要将 ROLLUP 替换为 CUBE 就可以了。

-- 使用CUBE取得全部组合的结果
-- Oracle SQL Server DB2 PostgreSQL
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);

与ROLLUP的结果相比，CUBE的结果中多出了几行记录。大家看一下应该就明白了，多出来的记录就是只把regist_date作为聚合键所得到的汇总结果。

所谓CUBE，就是将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是$2^n$(n是聚合键的个数)。本例中聚合键有 2 个，所以 $2^2 = 4$。使用ROLLUP时组合的个数是n + 1。随着组合个数的增加，结果的行数也会增加，因此如果使用CUBE 时不加以注意的话，往往会得到意想不到的巨大结果。顺带说一下，ROLLUP的结果一定包含在CUBE 的结果之中。
众所周知，立方体由长、宽、高3个轴构成。对于CUBE来说，一个聚合键就相当于其中的一个轴，而结果就是将数据像积木那样堆积起来,可以把CUBE理解为将使用聚合键进行切割的模块堆积成一个立方体。

GROUPING SETS——取得期望的积木

最后要介绍给大家的GROUPING运算符是GROUPING SETS。该运算符可以用于从ROLLUP或者CUBE 的结果中取出部分记录。

-- 从中选取出将“商品种类”和“登记日期”各自作为聚合键的结果，
-- 或者不想得到“合计记录和使用 2 个聚合键的记录”
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);

GROUPING SETS 用于从中取出个别条件对应的不固定的结果。然而，由于期望获得不固定结果的情况少之又少，因此与 ROLLUP 或者 CUBE 比起来，使用 GROUPING SETS 的机会也就很少了。

练习题

本题中 SELECT 语句的含义是“按照商品编号（product_id）的升序进行排序，计算出截至当前行的最高销售单价”。因此，在显示出最高销售单价的同时，窗口函数的返回结果也会变化。

有两种方法：

• 第一种方法：

SELECT regist_date, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY COALESCE(regist_date, CAST('0001-01-01' AS DATE))) 
AS current_sum_price
FROM Product;

使用COALESCE函数可以将NULL转换为“1年1月1日(公历）”。这样得到的结果就比其他任何日期都早了(即使同为“1年 1 月 1 日”也没有关系）。这种“欺骗”DBMS 的方法,这也是在所有 DBMS 中通用的方法。

• 第二种方法：

SELECT regist_date, product_name, sale_price,
SUM (sale_price) OVER (ORDER BY regist_date NULLS FIRST) AS current_sum_price
FROM Product;

使用NULLS FIRST选项的方法。通过在ORDER BY子句中指定该选项，可以显式地给DBMS下达指令，在排序时将NULL放在最前面。目前该方法也是在支持窗口函数的DBMS中通用的方法。该功能并不是标准SQL支持的功能，而是依存于DBMS的实现。关于NULL的顺序，标准SQL中只规定要“排列在开头或者末尾”，至于到底是开头还是末尾，以及显式地指定的方法，都依存于DBMS的实现。因此，大家需要注意，这些功能随时都有可能因为某个DBMS的需求改变而无法继续使用.

这两个方法的结果都如下所示，