sql 7 窗口函数 grouping运算符

2019-03-31

窗口函数

窗口函数也叫OLAP（online analytical processing）函数。意思是对数据库进行实时分析处理，就是利用SQL语句对数据进行实时分析。暂时MySQL并不支持窗口函数。而且只能用在select子句中，不能使用在where，group by子句中。

语法格式：<窗口函数> over ([partition by <列清单>] order by <序号用列清单>)

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

1）能作为窗口函数使用的聚合函数（sum avg count max min）
2）专用窗口函数（rank dense_rank row_number）

rank函数

1 2	select product_name, product_type, sale_price, rank () over (partition by product_type order by sale_price) as ranking from Product;

rank函数是计算记录排序
partition by 设定排序的对象范围（分组），这里是指定按照商品种类排序
order by 指定按照哪列来排序默认是asc升序（排序），这里指定按照商品销售价格升序排序

通过partition by进行分组的后的记录集合称为“窗口”，它代表范围的意思。当不指定partition by时，相当于把这个表作为一个大窗口来看待。

1 2	select product_name, product_type, sale_price, rank () over ( order by sale_price) as ranking from Product;

专用窗口函数

专用窗口函数	含义
rank	计算排序存在相同位次的记录，会跳过之后的位次有3条记录排在第一位 1位 1位 1位 4位
dense_rank	计算排序存在相同位次的记录，不会跳过之后的位次有3条记录排在第一位 1位 1位 1位 2位
row_number	唯一连续位次有3条记录排在第一位 1位 2位 3位 4位

select product_name, product_type, sale_price, 
rank() over (order by sale_price) as ramking,
dense_rank() over (order by sale_price) as dense_ramking,
row_number() over (order by sale_price) as row_num
from Product;

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

按照 product_id升序并做出按照sale_price来计算合计值

1
2
3

select product_id, product_name, sale_price, 
sum(sale_price) over (order by product_id) as current_sum
from Product;

1
2
3

select product_id, product_name, sale_price, 
avg(sale_price) over (order by product_id) as current_sum
from Product;

计算移动平均

窗口函数是将表以窗口为单位来分割，并在其中进行排序的函数。还可以在窗口中指定汇总范围（框架）的功能。

指定最靠近的3行为汇总对象

1
2
3

select product_id, product_name, sale_price, 
avg(sale_price) over (order by product_id rows 2 preceding) as current_sum
from Product;

通过rows 2 preceding（截止到之前的2行）来指定框架范围

使用rows 2 following （截止到之后的2行）作为框架

也可以同时使用他们两个来做框架

1
2
3

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子句中order by 只是决定窗口函数按照什么样的顺序来计算的，对结果的排序的顺序没有影响的。因此可以在句尾再添加order by子句来对结果进行排序。

select product_id, product_name, sale_price, 
rank() over (order by  sale_price) as ranking
from Product
order by  ranking;

groping算术符

为了同时得到合计和小计，以下结果

1
2
3

select '合计' as product_type, sum(sale_price) from Product
union all
select product_type, sum(sale_price) from Product group by product_type;

但是这个命令实在太繁琐并且处理速度也慢，因此引用grouping运算符

rollup 同时得出合计和小计

rollup是“卷起”的意思，类似百叶窗，窗帘。从小聚合逐渐扩大。

得出合计

1	select product_type, sum(sale_price) as sum_price from Product group by rollup (product_type);

select语句union对2模式的聚合级不同结果进行连接，而且合计的行叫做超级分组记录。

得出合计和小计

1	select product_type, regist_date, sum(sale_price) as sum_price from Product group by rollup (product_type, regist_date);

select语句等于使用union对3模式的聚合级不同结果进行连接

grouping 让null更容易分辨

grouping函数可以判断该列的值为超级分组记录所产生的null返回1，其他情况返回0

上例中不能判断哪个null是超级分组记录所产生的null

使用grouping函数显示null对应的值

1
2
3

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

让对应的值显示为字符串

select case when grouping(product_type) = 1 then  '商品种类 合计' else product_type as end 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);

cast(regist_date as varchar(16)) 转换类型为了满足case表达式所有分支的返回值要一致的条件，否则会报错。一般用grouping和rollup会一起使用。

cube 用数据来搭积木

cube意思是立方体，用法和grouping一样。就是把group by子句中聚合建“所有可能的组合”汇总结果集合到一个结果中。组合的个数是2^n次方（n是聚合键的个数），因此每个聚合键可以看作为一个轴，3个聚合建就类似立方体了。

select case when grouping(product_type) = 1 then  '商品种类 合计' else product_type as end 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的相比，多出几行记录是把regist_date作为聚合键汇聚到的结果

grouping sets 取得期望的积木

grouping sets用于从rolluup和cube结果中却出部分记录

将“商品种类”和“登录日期”单独作为聚合建或不想得到“合计记录和使用2个聚合键的记录”

select case when grouping(product_type) = 1 then  '商品种类 合计' else product_type as end 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 set (product_type, regist_date);

结果和rollup,cube比少了合计和使用2个聚合建的记录