Spark SQL向前3天窗口平均值实现：正确RANGE区间语法问询

已知有包含user_id、date、value字段的DataFrame，已通过以下Spark SQL代码实现每个用户过去3天（含当日）的value平均值计算：

SELECT *, mean(value) OVER (
   PARTITION BY user_id
   ORDER BY CAST(date AS timestamp)
   RANGE BETWEEN INTERVAL 2 DAYS PRECEDING AND CURRENT ROW
) as mean FROM table1

需要调整语法以计算向前看的3天（含当日）平均值（即包含当日及之后2天的数据），尝试RANGE BETWEEN INTERVAL CURRENT ROW AND 2 DAYS AFTER等错误语法无效，使用负的PRECEDING天数虽无报错但mean字段全为null。

正确解决方案 #

正确的窗口范围语法应为BETWEEN CURRENT ROW AND INTERVAL 2 DAYS FOLLOWING，完整代码如下：

SELECT *, mean(value) OVER (
   PARTITION BY user_id
   ORDER BY CAST(date AS timestamp)
   RANGE BETWEEN CURRENT ROW AND INTERVAL 2 DAYS FOLLOWING
) as forward_mean FROM table1

在Spark SQL的窗口函数中，指定未来时间范围需使用FOLLOWING关键字，而非AFTER。INTERVAL 2 DAYS FOLLOWING表示包含当日之后的2天数据，结合CURRENT ROW即可实现向前看3天（含当日）的平均值计算。

内容的提问来源于stack exchange，提问作者BenjaminFranklinGates