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