《SQL 基础教程》第八章:SQL 高级处理

本章分为两个部分:

1. 窗口函数
2. GROUPING 运算符

它们用于以「窗口」为单位的排序、计算总和等任务.

OLAP 函数

OLAP 定义:OLAP 是 OnLIne Analytical Processing 的简称,意思是对数据库数据进行实时分析处理
OLAP 函数定义: 为了实现 OLAP 的标准 SQL 功能.

语法

<OLAP function> ([PARTITION BY <collist>]
ORDER BY <collist>)

窗口定义:通过 PARTITION BY 分组后的记录集合称为窗口.它代表「范围」的概念.

要点与 Keyword:

1. OLAP function :即窗口函数.可用的窗口函数有两类,第一类为专用窗口函数(例如: RANK, DENSE_RANK, ROW_NUMBE), 另一类为聚合函数(例如: SUM, AVG, COUNT, MAX, MIN)
2. PARTITION BY :[可选的]设定执行窗口函数的的对象范围.
3. ORDER BY: 指定按照哪一列、何种顺序(可指定 ASC/DESC 来指定升序和降序)执行窗口函数.
4. 窗口函数不能用在 WHERE 子句和 GROUP BY 子句中.

专用窗口函数

RANK 函数,DENSE_RANK 函数, ROW_NUMBER 函数均是专用窗口函数,可以用于排序,它们的不同之处在于:
若三条记录并列第一,结果分别为:

1. RANK: 1,1,1,4
2. DENSE_RANK: 1,1,1,2
3. ROW_NUMBER:1,2,3,4

框架

定义:在窗口中指定的更加详细的汇总范围的备选功能,该汇总功能的汇总范围称为框架.

语法

举例:
用于计算当前行、上一行、下一行的 sale_price 的平均值

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;

在 OVER 子句中指定 PRECEDING关键词(意为当前行的上一行) 和 FOLLOWING 关键词(意为当前行的下一行),来确定窗口函数执行的范围

GROUPING 运算符

问题源

由于 GROUP BY 子句只指定聚合键,因此结果中只有小计,因此不能同时得到总计.
普通解决办法是使用 UNION ,但标准 SQL 引入了 GROUPING 运算符,用于得到不同汇总范围下的汇总结果.
GROUPING 运算法包括以下3种:

1. ROLLUP
2. CUBE
3. GROUPING SET

语法:

用于 GROUP BY 子句中:格式如下
GROUP BY <function>(<col1>, <col2>,...)
<function> 为函数

ROLLUP 运算符

定义:

一次计算出不同聚合键集合的结果

语法:

比如:
GROUP BY ROLLUP(<col1>, <col2>)
得到下面三种组合的汇总结果:
① GROUP BY () :称为「超级分组」,其默认的键值为 NULL,结果为未使用 GROUP BY 的合集行.
② GROUP BY(<col1>)
③ GROUP BY(<col1>,<col2>)

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

GROUPING 函数——区分 NULL 与汇总

由于超级分组以 NULL 为聚合键,当存在以 NULL 为聚合键的其他分组时,就难以区分超级分组和其他分组,因此可以使用 GROUPING 函数配合 CASE 语句加以区分

格式:

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

以上为 GROUPING 配合 CASE 语句同时得到小计和总计的范例.

语法:

当参数列的值为超级分组产生的 NULL 时返回1, 其他情况返回 0

CUBE 运算符

定义:得到聚合键的所有组合的结果,组合的个数为 2ⁿ

GROUPING SETS 运算符

定义:只得到指定的列作为聚合键的汇总

三个运算符之间的关系

聚合键的数目从多到少依次为:

1. CUBE 
2. ROLLUP
3. GROUPING SETS