第8章 SQL高级处理-SQL基础教程

8-1　窗口函数

什么是窗口函数

窗口函数也称为 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 能够指定按照哪一列、何种顺序进行排序

窗口函数兼具 GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备GROUP BY 子句的汇总功能

通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围

无需指定PARTITION BY

/*
不指定PARTITION BY和使用没有 GROUP BY 的聚合函数时的效果一样，也就是将整个表作为一个大的窗口来使用
当希望先将表中的数据分为多个部分（窗口），再使用窗口函数时，可以使用 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 位……

-- 比较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子句，ORDER BY子句或者UPDATE语句的SET子句中使用
原因：在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作

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

-- 将SUM函数作为窗口函数使用
select product_id,product_name,sale_price,
sum(sale_price) over (
	order by product_id) as currernt_sum	-- 累计销售额
from product;

-- 将AVG函数作为窗口函数使用
select product_id,product_name,sale_price,
avg(sale_price) over (
	order by product_id) as currernt_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	-- 截止到之前2行
from product;

●指定框架（汇总范围）
截止到之前2行：
● 自身（当前记录）
● 之前1行的记录
● 之前2行的记录

**移动平均（moving average）在希望实时把握“最近状态”**时非常方便，因此常常会应用在对股市趋势的实时跟踪当中

-- 将当前记录的前后行作为汇总对象
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，OVER 子句中的ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响

-- 在语句末尾使用ORDER BY子句对结果进行排序
select product_name,product_type,sale_price,
rank() over (
	order by sale_price) as ranking
from produc
order by ranking;

8-2　GROUPING运算符

同时得到合计行

-- 分别计算出合计行和汇总结果再通过UNION ALL进行连接
select '合计' as product_type,sum(sale_price)
from product
union all
/*
虽然也可以使用UNION来代替UNION ALL，但由于两条SELECT语句的聚合键不同，一定不会出现重复行，因此可以使用UNION ALL
UNION ALL和UNION的不同之处在于它不会对结果进行排序，因此比UNION的性能更好
*/
select product_type,sum(sale_price)
from product
group by product_type;

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

GROUPING 运算符包含以下 3 种：
●ROLLUP
●CUBE
●GROUPING SETS

■ROLLUP的使用方法

-- 使用ROLLUP同时得出合计和小计
select product_type, sum(sale_price) as sum_price
from product
group by product_type
with rollup;

一次计算出不同聚合键组合的结果：
① GROUP BY ()
超级分组记录（默认使用NULL作为聚合键）：相当于没有 GROUP BY子句（这时会得到全部数据的合计行的记录）
② GROUP BY (product_type)

-- 　在GROUP BY中添加“登记日期”
select product_type, sum(sale_price) as sum_price
from product
group by product_type,regist_dat
with rollup;

① GROUP BY ()
② GROUP BY (product_type)
③ GROUP BY (product_type, regist_date)

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

GROUPING 函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1，原始数据为NULL时返回 0

-- 使用GROUPING函数来判断NULL
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_dat
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))
/*
为什么还要将 SELECT 子句中的 regist_date 列转换为 CAST（regist_date AS VARCHAR（16））形式的字符串呢？
这是为了满足 CASE 表达式所有分支的返回值必须一致的条件
*/
end as regist_date,
sum(sale_price) as sum_price
from product
group by product_type, regist_date
with rollup;

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

① GROUP BY ()
② GROUP BY (product_type)
③ GROUP BY (regist_date) ←添加的组合
④ GROUP BY (product_type, regist_date)

组合的个数是 2^n（n 是聚合键的个数）
使用ROLLUP时组合的个数是n + 1

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

GROUPING SETS——取得期望的积木

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