SQL 基础教程—SQL 高级处理

运行环境: postgreSQL。

第八章:SQL 高级处理

窗口函数

窗口函数也称为 OLAP(OnLine Analytical Processing) 函数,意思是对数据库数据进行实时分析处理,如市场分析,创建财务报表,创建计划等日常性商务工作。

窗口函数的语法

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

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

  • 能够作为窗口函数的聚合函数(SUM, AVG, COUNT, MAX, MIN)
  • RANK, DENSE_RANK, ROW_NUMBER 等专用窗口函数

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

示例,根据不同商品种类,按照销售单价从低到高的顺序创建排序表:

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

PARTITION BY 能够设定排序的对象范围。

ORDER BY 能够指定按照哪一列,以何种顺序进行排序,同样地,也可以通过指定 AES/DESC 指定升序或者降序。

以上可以看出窗口函数具有分组排序的功能,通过 PARTITION BY 分组后的记录集合称为窗口,其代表范围。

无需指定 PARTITION BY

PARTITION BY 并不是必须的,省略后,整个表将被作为一个大的窗口使用,示例,删除上述 SQL 语句的 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,……

    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 子句中使用。

其理由是,在 DBMS 内部,窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的结果进行的操作。在得到用户想要的结果前即使进行了排序处理,结果也是错误的。在得到排序结果之后,通过 WHERE 子句除去记录或者使用 GROUP BY 子句进行汇总,那么得到的排序结果就无法使用了。

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

示例,将 SUM 作为窗口函数使用:

SELECT product_id, product_name, sale_price,
		SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
	FROM Product;

观察执行结果:

image-20191208235414758

其按照 ORDER BY 子句指定的 product_id 升序排列,计算出商品编号“小于自身”的销售单价的合计值,通常在计算按照时间序列的顺序排列计算各个时间的销售总额时,就会使用这种累计的统计方法。

AVG 函数也和 SUM 类似:

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;

执行结果:

image-20191209000307779

  • 指定框架(汇总范围)

使用 ROWS 和 PRECEDING 两个关键字,将框架指定为“截止到之前~行”,也就是将包括自身(当前记录)和之前~条记录进行汇总计算,因此其范围会随着当前的记录的变化而变化。

以上的统计方法称为移动平均(moving average),适用于对股市趋势的实时跟踪当中。

使用关键字 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;

执行结果:

image-20191209002010046

其汇总结果将之前 1 行的记录,自身,之后 1 行的记录进行汇总计算。

两个 ORDER BY

窗口函数中 OVER 子句的 ORDER BY 只是用来决定窗口函数是按照什么顺序进行计算的,对结果的排列顺序并没有影响,执行下面 SQL 语句,结果中的记录并不是按照 ORDER BY 子句指定的列进行排序的,当然部分 DBMS 实现会按照此对结果进行排序:

SELECT product_name, product_type, sale_price,
			RANK() OVER (ORDER BY sale_price) AS ranking
	FROM Product;
	
-- 对 ranking 进行显示排序
SELECT product_name, product_type, sale_price,
			RANK() OVER (ORDER BY sale_price) AS ranking
	FROM Product
 ORDER BY ranking;

GROUPING 运算符

同时得到合计行

使用以下 SQL 语句得到按照商品种类计算销售单价总额的结果,并计算合计行:

SELECT '合计' AS product_type, SUM(sale_price)
	FROM Product
UNIN ALL
SELECT product_type, SUM(sale_price)
	FROM Product GROUP BY product_type;

但以上 SQL 语句需要运行两次 SELECT 再进行连接,是个十分繁琐且 DBMS 内部处理成本非常高的方式。

ROLLUP — 同时得出合计和小计

GROUPING 运算符包括以下 3 种:

  • ROLLUP
  • CUBE
  • GROUPING SETS

ROLLUP 使用方法

示例,使用 ROLLUP 同时得出合计和小计

SELECT product_type, SUM(sale_price) AS sum_price
	FROM Product
--MySQL 中修改为 GROUPY BY product_type WITH ROLLUP
 GROUP BY ROLLUP(product_type);

ROLLUP 就是“一次计算出不同聚合键组合的结果”。如本例中就是一次计算出以下两种组合的汇总结果:

  • GROUP BY():超级分组记录(super group row)
  • GROUP BY(product_type)

讲“登记日期”添加到聚合建当中

示例,添加聚合建登记日期(regist_date):

-- 不使用 ROLLUP
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
	FROM Product
 GROUP BY product_type, regist_date;

SELECT product_type, regist_date, SUM(sale_price) AS sum_price
	FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

下面为使用 ROLLUP 的 SQL 语句的执行结果:

image-20191212132014321

可以看出,使用 ROLLUP 多出了最上方的合计行以及 3 条不同商品种类的小计行(即未使用登记日期作为聚合键的记录),这 4 行记录称为超级分组记录。该 SELECT 语句相当于使用 UNION 对如下 3种模式的聚合级的不同结果进行连接:

  • GROUP BY()
  • GROUP BY(product_type)
  • GROUP BY(product_type, regist_date)

下面是 3 种模式的聚合级:

image-20191212132525429

ROLLUP 意为卷起,形象地说明了该操作就是从小计到合计,从最小的聚合级开始,聚合单位逐渐扩大的结果。

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

上面例子的“衣服”分组种出现了两条 regist_date 为 NULL 的记录,但意义却大不相同,sum_price 为 4000 的记录是因为其 regist_date 本身就是 NULL, 而 5000 的记录是因为其为超级分组记录,因此 regist_date 为 NULL,为此,标准 SQL 提供了 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 ROLLUP(product_type, regist_date);

当然,使用 GROUPING 还能在超级分组记录的键值中插入字符串:

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);

在实际业务中需要包含合计或者小计的汇总结果时,就可以使用 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_date,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY CUBE(product_type, regist_date);

执行结果:

image-20191212234421786

和 ROLLUP 相比, CUBE 结果多出了几行记录,多出的记录为 regist_date 作为聚合键所得到的汇总结果,即:

  • GROUP BY()
  • GROUP BY(product_type)
  • GROUP BY(regist_date) — 新添加的组合
  • GROUP BY(product_type, regist_date)

所谓 CUBE ,就是讲 GROUP BY 子句中所有的组合汇总结果集中到一个结果中。因此,组合个数是 $$2^n$$ (n 是聚合键的个数),以下是 CUBE 的执行图示:

image-20191212235527768

GROUPING SETS — 取得期望的积木

该运算符可以从 ROLLUP 或者 CUBE 结果中取出部分记录,如从之前 CUBE 的结果中选取出将“商品种类” 和 “登记日期”各自作为聚合键的结果,或者不想得到“合计记录和使用 2 个聚合键的记录”,就可以使用 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);

执行结果:

image-20191213000132975

GROUPING SETS 用于从中选出个别条件对应的不固定的结果。