SQL 移动窗口

提示

Hive SQL 教程欢迎使用。提供建议、纠错、催更等加作者微信: gairuo123（备注：sql ）和关注公众号「盖若」ID: gairuo。跟作者学习，请进入 Python学习课程。欢迎关注作者出版的书籍：《深入浅出Pandas》和《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 实现了一个累加的效果，后边的行值是前边计算值与本行值的加和。

< SQL 窗口计算 Hive SQL 教程 Hive sql 专用窗口函数 >

更新时间：2021-03-14 20:18:03 标签：sql 移动窗口