MySQL数据库入门与实践(八)：窗口函数与 grouping运算符

一.窗口函数

首先看窗口函数的使用格式：
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>
上面中熟悉的部分是 order by 这个排序命令，而 partition by 使用来 分组的，最为陌生的就是窗口函数。
窗口函数可以分为两类：

• 能够作为窗口函数的聚合函数（ SUM、 AVG、 COUNT、 MAX、 MIN）
• RANK、 DENSE_RANK、 ROW_NUMBER 等专用窗口函数

来看一个例子：rank 是用来计算记录排序的函数,对于之前使用过的 Product 表中的 8 件商品，让我们根据不
同的商品种类（product_type），按照销售单价（sale_price）从低到高的顺序排序, 也就是每种类排序，这点有点类似于每个班级都排前三名。

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

需要说明的是窗口函数在 MySQL8中可以使用，但是在5版本中并不可以用。
窗口函数的灵魂在于 over 后面接的。
partition by 类似于 group by ，而order by 就是进行排序。排序也可以用desc来实现反向排序
但是， PARTITION BY 子句并不具备GROUP BY 子句的汇总功能，只是用来排序的。通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是“窗口函数”名称的由来。

select product_name, product_type, sale_price,  
rank() over (order by sale_price desc) as ranking
from product;

但如果我们把order by 去除的话，只用 partition by 的话，看看效果：

select product_name, product_type, sale_price,  
rank() over (partition by product_type 
-- order by sale_price desc
) as ranking
from product;

我们发现没有使用 order by 的话，那就是说所有的行之间并不存在顺序，那就所有的行都是第一行。

• 排序函数的拓展，dense_rank,row_number,看上面的例子，rank(),就是一般的成绩表的形式，1，1，3这种形式。而dense_rank，则是 1，1，2这种形式的排序，而 row_number 故名思意就是1，2，3 每行拥有唯一的顺序。

select 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_numbering
from product;

可以看到 dense_rank 这一列出现了2，2，3，而rank 函数那一列则是 2，2，4.
原则上 窗口函数只在 select 中使用，在where,group by，having中不能使用。
这三个函数也叫专用窗口函数，专用函数并不需要参数，而对于另一类的窗口函数是聚合函数的话，则需要把列名作为其参数。
看一个例子，对于上述的表，将每一类的产品求平均值：

select product_name,product_type ,sale_price,
avg(sale_price) over (order by sale_price rows between unbounded preceding and current row) as avging
from product;

该平均值其实是这一类中当前行和当前行之前所有行的平均值。
还有一种滑动平均，所谓的滑动平均，就是在排序后，指定当前行和前面或者后面的行进行求平均。
可以类比于时间序列模型中的滑动窗口预测。

select product_name,product_type ,sale_price,  
avg(sale_price) over (order by sale_price rows between 2 preceding and current row) as avging
from product;

为了看的更加清楚，这里就没有进行 partition by了，可以看到的是，between 2 preceding and current row，表示的是当前行和之前的两行总共三行求平均值。
而类似的有

select product_name ,product_type ,
avg(sale_price) over (order by sale_price rows between 2 preceding and current row) as avging_preceding,
avg(sale_price) over (order by sale_price rows between current row and 2 following) as avging_following,
avg(sale_price) over (order by sale_price rows between 1 preceding and 1 following) as avging_centre
from product;

可以总结以下几种求平均中遇到的，rows between ____ and ___

关键词	含义
2 preceding	往前的两行
2 following	往后的两行
current row	当前行
unbound preceding	往前到这一类的最开始
unbound following	往后到这一类的最后面

这也就是 unbound 的直接定义。类似的求和，求最小，求数量也是类似的格式。
上面说到，order by 其实是进行平均，求和的灵魂，没有排序也就谈不上什么 前一个后一个，那么 order by 后面如果跟了两列的话，情况有时怎么样呢？

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

注意上面代码 order by 的形式，我们之前在讲 order by 的时候其实说到了 order by 是在select 之后执行的。仿佛这个 order by ranking 并没有出现的必要，但其实窗口函数的 order by 并没有对select 排序的作用。

二. grouping 运算符

在之前讲到 group by 的时候，我们讲过可以分组对商品的销售进行求和，也就是

select product_type,sum(sale_price)as sum
from product 
group by product_type ;

很简单的一个分组语句。
那如果想得到这三个行的一个合计，一个很自然的思路是计算出合计行，再和上表叠在一起：

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

grouping 运算符计算合计其实包含以下三种 roll up,cube,grouping set

1. rollup

select product_type, sum(sale_price) as sum_price
from product
group by product_type with rollup;

可以看到这是跟在分组后面的，也就是和分组有关。其实 这里的group by 实现了两个层次的求和，一个是整体也就是 group by()，无参数的，另一个是group by product_type,当group by 后面跟了两个列名的话，也就是多层次分组的情况，结果又是怎样呢？
首先我们看看按照两个列名进行分组的情形，然后进行合计：

select product_type, regist_date, sum(sale_price) as sum_price
from product
group by product_type ,regist_date;

如果向其中加入 with rollup

可以发现这个命令实现了三层求和，sum是一层，每种商品还有一层，最后总体还有一层。
在上面的表中可以看到，对于衣服这一类在日期这一列有两个 null 但是这两个 null 的含义其实并不相同其中一个是 把衣服这一类种缺少日期的行作为一类也就是 null 作为了一个聚合键，另一个是对所有衣服的行进行求和。
了避免混淆， SQL 提供了一个用来判断超级分组记录的 null 的特定函数 —— grouping函数,所谓的超级分组记录就是不进行任何的分类直接求和的行，也就是所谓的总计。

select product_type, regist_date,
       grouping (product_type) as product_type,
       grouping (regist_date) as regist_date, 
       sum(sale_price) as sum_price
from product 

可以明显看到因为求合计的 null 都是1，别的都是0。当然显示 1或者0不好看，我们可以使用小记和合计这样直观的词语

select case when grouping (product_type)=1 then '商品种类：合计' else product_type end as product_type,
       case when grouping (regist_date)=1  then '日期：合计' else regist_date end as regist_date,
       sum(sale_price) as sum
from product 
group by product_type, regist_date with rollup ;

为了满足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 char(20))
            end as regist_date,
       sum(sale_price) as sum
from product 
group by product_type, regist_date with rollup ;

和上面是一样的结果。

2.cube

所谓的 rollup 就是实现多层次求和，而 cube 和 rollup 的作用类似，但是在 mysql 种并没有该函数，下面简单看一看这个该函数究竟在干什么实际上就是多层次求和

以及

当然还有 grouping sets,这里也不多介绍了

三. 总结

这一小节介绍了窗口函数，主要是 avg,sum 一类的聚合函数以及rank,dense_rank,row number三个专用窗口函数，注意求平均值中的滑动窗口求平均值，采用 rows between and 的形式。以及grouping 函数中的 with rollup 实现多层求和，grouping 对求和带来的 null 数据进行重命名格式。