一.窗口函数
首先看窗口函数的使用格式:
<窗口函数> 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 数据进行重命名格式。