SQL 移动窗口
更新时间：2021-03-14 20:18:03 标签：sql
移动窗口
提示
Hive SQL 教程 编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》 。作者开办 Python 数据分析培训，详情 Python 数据分析培训。
之前我们了解了窗口函数的原理和用法，本篇我们了解一下移动窗口（又称滑动窗口）。之前的窗口是固定的分组窗口，但有时候我们需要根据数据的前后再分配窗口，比如在股票、气温等数据场景下，数据的前后会有影响，就适用于移动窗口计算。
移动窗口语法
在之前我们做窗口分组时用的 PARTITION by 进行窗口分组，移动窗口就不能再做固定分组了，我们对数据 ORDER BY 排序后（也可以不加此语句，直接用数据库自身的排序），按顺序用：
over(ORDER BY xxx rows/range n precending/following)
来设定移动窗口，其中：
- 移动范围设定
- rows: rows 后的 n 是指当前行几位（当前行也参与计算），可以与 between 搭配使用表达范围
- range: 数据范围，与下文的 between 搭配使用
- 移动方向
- precending: 向当前行之前
- following: 向当前行之后
同时，preceding 与 following 可以相结合，用：
rows between 1 precending and 2 following
表示每一行（当前行）的前一行和后两行，共 4 行（包括当前行）作为汇总的依据。
基础案例
下边我们要实现将成绩表按出生年排序，然后增加一列 m_avg，它是当前行与上一行的平均数学成绩。
SELECT
b_year,
math,
AVG(math) over(ORDER BY b_year ROWS 1 PRECEDING) as m_avg
FROM
students
'''
b_year|math|m_avg|
------|----|-----|
1950| 66| 66.0|
1966| 88| 77.0|
1977| 78| 83.0|
1988| 78| 78.0|
1988| 77| 77.5|
1996| 55| 66.0|
2000| 77| 66.0|
2010| 88| 82.5|
2011| 54| 71.0|
'''
1950 年所在行的值由于没有上一行，所以平均值仍然是原来的值，如 1996 行的 e_avg 值为其与上一行 1988 的值的平均值（55+77）/2 = 66。
range
下边使用 range 实现了一个在本行前一行和本行后一行，加上本行行三行移动窗口：
SELECT
id,
math,
AVG(math) over(ORDER by id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) as m_avg
FROM
students
'''
id|math|m_avg |
--|----|-----------------|
1| 66| 77.0|
2| 88|69.66666666666667|
3| 55|65.66666666666667|
4| 54| 62.0|
5| 77|69.66666666666667|
6| 78| 81.0|
7| 88| 81.0|
8| 77| 81.0|
9| 78| 77.5|
'''
扩展窗口
在移动位数 n 上传入 UNBOUNDED 对上界（也可以放在 FOLLOWING 前作为下界），每次移动都从开头开始，这样形成了一个扩展窗口。扩展窗口就是起始点确定，向一个方向伸展，每次伸展就是一个新的窗口。
SELECT
id,
math,
SUM(math) over(ORDER by id ROWS UNBOUNDED PRECEDING) as m_avg
FROM
students
'''
id|math|m_avg|
--|----|-----|
1| 66| 66|
2| 88| 154|
3| 55| 209|
4| 54| 263|
5| 77| 340|
6| 78| 418|
7| 88| 506|
8| 77| 583|
9| 78| 661|
'''
以上 SQL 实现了一个累加的效果，后边的行值是前边计算值与本行值的加和。
