如何在存在缺失年月值时计算滚动12个月求和？

原SQL使用rows between 11 preceding and current row是按行数取前11条数据，当存在月份缺失时，实际时间跨度会超过1年。要严格限定在1年以内，应该改用时间范围定义窗口，而非行数。

核心思路 #

1. 将month_year（如202208格式）转换为标准日期类型（比如当月第一天2022-08-01）
2. 窗口函数中基于日期范围筛选，仅包含当前日期往前11个月到当前月的区间（刚好覆盖12个月）

适配不同SQL方言的代码示例 #

1. PostgreSQL

select
    city,
    month_year,
    person,
    sum(total) over (
        partition by person,city 
        order by month_date 
        range between interval '11 months' preceding and current row
    ) rolling_one_year
from
    (select
      city,
      month_year,
      to_date(month_year::text, 'YYYYMM') as month_date,
      person,
      sum(amount_dollar) as total
    from db1 d
    group by 1,2,4) t;

2. MySQL

MySQL不支持直接在range中使用interval，需通过日期函数计算边界：

select
    city,
    month_year,
    person,
    sum(total) over (
        partition by person,city 
        order by month_date 
        range between 
            unix_timestamp(date_add(month_date, interval -11 month)) 
            and unix_timestamp(month_date)
    ) rolling_one_year
from
    (select
      city,
      month_year,
      str_to_date(concat(month_year, '01'), '%Y%m%d') as month_date,
      person,
      sum(amount_dollar) as total
    from db1 d
    group by 1,2,4) t;

3. BigQuery

select
    city,
    month_year,
    person,
    sum(total) over (
        partition by person,city 
        order by month_date 
        range between interval 11 month preceding and current row
    ) rolling_one_year
from
    (select
      city,
      month_year,
      parse_date('%Y%m', cast(month_year as string)) as month_date,
      person,
      sum(amount_dollar) as total
    from db1 d
    group by 1,2,4) t;

关键说明 #

• 转换month_year为标准日期是让数据库能正确识别时间间隔的前提
• 使用range而非rows，确保窗口内数据严格落在当前月往前推11个月的时间范围内，即便中间有月份缺失，也不会纳入超过1年的历史数据

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