MySQL高级应用之窗口函数

1.什么是窗口函数

MySQL从8.0开始支持窗口函数，通过窗口函数我们可以处理相对复杂的报表统计需求。窗口类似窗户，我们透过窗户在总体数据中能指定的部分数据集，再在这部分指定的数据集上进行函数计算，这便是我们说的窗口函数。

根据窗口特点，可分为以下两类：

静态窗口：窗口的大小是固定的，多个不同记录对应同一个窗口，即对应相同数据集

动态窗口：窗口是可滑动的，所有也叫滑动窗口，即不同的记录对应不同的窗口，即对应不同数据集

2.语法结构

函数 （字段名） OVER ( 子句 ）
函数 （统计字段名） OVER ( PARTITION BY 分组字段名 ORDER BY 排序字段名 ROWS BETWEEN ...AND ...)

 

1. OVER 为关键字，后面指定函数执行窗口的范围，包含三部分：分组（PARTITION BY ）、排序（ ORDER BY 字段名）、窗口（ROWS BETWEEN ...AND ...）

2. PARTITION BY ：根据分组字段名将数据划分为不同的组，不同的组可以理解为不同的窗口

3. ORDER BY：根据排序字段对数据进行排序，如果前面已经有PARTITION BY 字段，那ORDER BY 将在PARTITION BY分好的组内进行排序，组与组之间相互独立，互补干扰。

4. ROWS BETWEEN ...AND ...：指定行数统计的的范围；如果其前面有PARTITION BY 字段，将在分好的组的组内指定输出行数，即在分组后的窗口再划分为一个个小窗口，用作滑动窗口在通常有以下几种情况

ROWS BETWEEN  2 preceding  AND   current row   #包含前面两行和本行
ROWS BETWEEN  unbounded preceding  AND   1 preceding   #包含前面所有行到前面1行之间
ROWS BETWEEN  unbounded preceding  AND   current row   #包含前面所有行到本行之间
ROWS BETWEEN  current row AND   unbounded following    #包含当前行后后面所有行
ROWS BETWEEN  3 preceding AND  1 following    #包含前面3行和后面1行，一共5行 

当ORDER BY () 括号内没有窗口函数，窗口默认为起始行到当前行

当ORDER BY () 和括号内的窗口函数都缺失，窗口函数默认从起始行到结束行

3.窗口函数应用

从应用角度，我们将窗口函数分为两种：

（1）专用窗口函数

rank()  

rank函数最常用的是求某一个数值在某一区域内的排名。

rank函数语法形式：rank(number,ref,[order])

dense_rank()

“DENSE_RANK()是一个窗口函数,它为分区或结果集中的每一行分配排名,而排名值没有间隙。 DENSE_RANK()。如果使用 DENSE_RANK() 进行排名会得到:1,1,2,3,4。 RANK()。如果使用 RANK() 进行排名会得到:1,1,3,4,5。

ROW_NUMBER()。如果使用 ROW_NUMBER() 进行排名会得到:1,2,3,4,5。

row_number()

（2）聚合类窗口函数

sum()   求和

count()   计数

avg()   平均值

max()  最大值

min()   最小值

聚合类窗口函数与普通聚合函数区别：

1.普通聚合函数将多个记录合为一条（多对一）

2.窗口聚合函数每一条记录执行得到一个结果，有几条记录执行完后还是几条（多对多）

4.案例应用

有如下表：test.trande_amount

 需求：计算每年中每个月之前的累计销售和

select *,sum(amount) over(partition by years order by months ) sum_amount 
from trande_amount ;

结果：