目录
rows between 和range between 对比
窗口函数:函数() over (partition by col1 order by col2)
聚合类窗口函数顾名思义,是指窗口函数中的函数部分为聚合函数,包括:
求和sum()、最大值max()、最小值min()、均值avg()、标准差std()等
这些函数在窗口上的使用规则都是类似的,我们以sum()求和函数为例来看一看
简单的数据示例
student | class | score |
1 | 1班 | 90 |
2 | 1班 | 80 |
3 | 2班 | 96 |
4 | 2班 | 85 |
5 | 2班 | 70 |
partition by 分组字段:
partition by class 是指在班级内部进行sum()函数统计
partition by不设置,就是指在全部数据范围内进行sum函数统计
student | class | score | sum()over(partition by class ) | sum()over() |
1 | 1班 | 90 | 170 | 410 |
2 | 1班 | 80 | 170 | 410 |
3 | 2班 | 90 | 240 | 410 |
4 | 2班 | 80 | 240 | 410 |
5 | 2班 | 70 | 240 | 410 |
order by 排序字段
order by score 是指将分数从低到高排序(倒序的话:order by score desc)
student | class | score | sum(score)over(partition by class order by score desc ) |
1 | 1班 | 90 | 90 |
2 | 1班 | 80 | 170 |
3 | 2班 | 90 | 90 |
4 | 2班 | 80 | 170 |
5 | 2班 | 70 | 240 |
rows between 和range between 对比
当数据不重复时,这样写没有问题,但是如果数据重复,就会出现下面的问题
student | class | score | sum(score)over(order by score desc) |
1 | 1班 | 90 | 180 |
3 | 2班 | 90 | 180 |
2 | 1班 | 80 | 340 |
4 | 2班 | 80 | 340 |
5 | 2班 | 70 | 410 |
因为在排序时,同样是90分和80分的记录无法区分,所以只能全部加入结果。如果想要避免这样的问题就需要引入order by参数中的:
- rows between 1 preceding and 2 following
含义:在当前行往前1行,往后2行,一共4行范围内进行计算
- range between 1 preceding and 2 following
含义:在当前行的数值往前1个数值,往后1个数值,进行计算,范围不一定是4行,因为可能会出现重复值
如果是简单的order by col,默认使用的事range between。所以说:如果存在重复值的时候,需要写成rows between。
unbound和current row
其中数值1和2可以替换成任意值,也可以直接使用unbounded和current row ,其中unbounded表示不做限制,current row 表示当前行
- rows between unbounded preceding and unbounded following
含义:按照分组内全部行求和,不做任何限制
- rows between unbounded preceding and current row
含义:从分组内排序的起始行到当前行
- range between unbounded preceding and unbounded following
含义:按照分组内全部行求和,不做任何限制
- range between unbounded preceding and unbounded following
含义:从分组内排序的起始行的值到当前行的值
由此可知,在上述案例中,如果我们想得到1、2班全部同学成绩从高到低排序的累计求和可以这样写:
sum(score)over (order by score desc rows between unbounded preceding and current row)
具体说明如下:
- 因为不需要对班级分组,所以partition by class不写
- order by score desc 表示按分数倒序排序
- rows between 是因为成绩中存在重复值,使用rows between可以保证和当前行的数值相同的行不会被计算进去
- unbounded preceding 表示从起始行开始
- current row 表示到当前行
实用价值
窗口函数的移动(proceding 和following)不仅可以实现上述累加求和的效果,在计算移动平均值的时候也非常有用,可以使用如下写法实现:
avg(求均值字段)over(order by date rows between 3 proceding and current row)