SQL带条件PARTITION BY解决缺失年份3年滑动窗口求和问题

表结构与数据规则 #

核心字段如下：

• id：客户唯一标识
• year：记录对应年份
• month：记录对应月份
• nb_purch：客户当月购买次数

数据存储约束：

表中仅存储客户产生过购买行为的年份数据；若客户在年份X有购买记录，会拆分生成X年12个月份的对应行，当月无购买时nb_purch取值为0，客户完全无购买行为的整年不会存入表中。

统计需求 #

需要按照3年大小的滑动时间窗口，统计每个客户每个月对应的近3年累计购买次数。

计算逻辑示例：统计截至2003年3月的近3年购买量总和，统计范围为2001年3月至2003年3月所有对应记录的nb_purch累加值。

初始方案存在的问题 #

最初尝试使用基于行数偏移的窗口函数实现统计，SQL代码如下：

SELECT 
  SUM(nb_purch) OVER (
    PARTITION BY id 
    ORDER BY year, month ASC 
    ROWS BETWEEN 36 PRECEDING AND CURRENT ROW
  ) AS LAST_3_YEARS 
FROM T

该方案计算结果不准确：由于表不会存储客户无购买行为的整年数据，比如2001-2003年区间内客户2002年完全没有购买记录，就不会生成2002年的12行月度数据，此时基于行数向前取36行的逻辑会跨过缺失的2002年，取到更早时间段的购买数据，最终导致统计结果失真。

实现限制 #

不需要为每个客户补全所有缺失年份、填充NULL值，需直接基于现有表结构实现准确的3年窗口购买量统计。

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