Spark/SQL 无ID列按周日期计算近5周累计计数的实现方案
周维度五周滚动值计算实现方案
核心逻辑对齐
你需要计算的Five_Week_Count本质是按时间顺序排列后,当前周与往前连续4个周的Count值求和,该逻辑实现根本不依赖表内自带的ID列。之前方案失败的核心原因是误将连续整数ID作为窗口range的参考维度,实际上可以直接用现有表的Week_Start(或Week_End)日期字段作为全局时间排序依据。
实现步骤
1. 窗口定义规则
使用固定行数的滑动窗口,而非带数值范围的range窗口:
- 全局按
Week_Start日期升序排序,不存在分组维度(所有周属于同一条时间序列) - 窗口范围固定为:当前行 + 往前连续4行,刚好覆盖当前周及之前4个周,共5个时间单位
- 自动兼容历史数据不足的场景:当往前的周数不足4个时,仅对已存在的历史周值求和,和你提供的示例输出规则完全匹配
2. Spark SQL 实现代码
SELECT Count, Week_Start, Week_End, SUM(Count) OVER ( ORDER BY Week_Start -- 窗口范围:当前行往前4行,到当前行本身 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) AS Five_Week_Count FROM 原始周统计表 -- 按示例的倒序规则输出,即最新周在前 ORDER BY Week_Start DESC
3. Scala DataFrame API 实现代码
import org.apache.spark.sql.expressions.Window import org.apache.spark.sql.functions._ // 定义全局滑动窗口 val fiveWeekWindow = Window .orderBy(col("Week_Start")) // 对应范围:当前行往前4行 到 当前行 .rowsBetween(Window.currentRow - 4, Window.currentRow) // 计算目标字段 val resultDF = originalWeekStatDF .withColumn("Five_Week_Count", sum(col("Count")).over(fiveWeekWindow)) .orderBy(col("Week_Start").desc)
结果匹配验证
对照你提供的示例数据,上述方法输出的数值可完全和期望值对齐:
- 最旧的2020-09-26周,因无前置周,求和值为121145,和示例一致
- 2020-10-03周,仅含2个周的数值,求和值为60290+121145=181435,和示例一致
- 2020-10-10周,含3个周的数值,求和值为225311+60290+121145=406746,和示例一致
- 当累计满5个周数据后,窗口将固定滑动,始终取当前周及前4周的总和,和后续示例值完全匹配
注意事项
- 全程不需要额外生成虚拟ID列,直接用现有日期字段做窗口排序即可
- 不要强行使用
rangeBetween的日期差值窗口:如果存在周数据缺失(某一周无统计值),range类窗口会因日期间隔超出范围导致计算偏差,固定行数的rows窗口更贴合「连续4个统计周」的业务规则
内容的提问来源于stack exchange,提问作者hsenpaws
相关产品推荐
相关产品推荐

