1.什么是窗口函数
MySQL从8.0开始支持窗口函数,通过窗口函数我们可以处理相对复杂的报表统计需求。窗口类似窗户,我们透过窗户在总体数据中能指定的部分数据集,再在这部分指定的数据集上进行函数计算,这便是我们说的窗口函数。
根据窗口特点,可分为以下两类:
静态窗口:窗口的大小是固定的,多个不同记录对应同一个窗口,即对应相同数据集
动态窗口:窗口是可滑动的,所有也叫滑动窗口,即不同的记录对应不同的窗口,即对应不同数据集
2.语法结构
函数 (字段名) OVER ( 子句 ) 函数 (统计字段名) OVER ( PARTITION BY 分组字段名 ORDER BY 排序字段名 ROWS BETWEEN ...AND ...)
- 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 ;
结果: