SQL如何计算指定范围记录的近12个月累计和（存在缺失月份场景）

问题根因 #

你之前的实现用了ROWS滑动窗口，是按物理行数统计往前12条记录的和，完全不考虑月份的连续性，缺月份时会出现统计范围超出12个月、或者包含不符合时间要求的旧数据的问题。

解决方案 #

分两种实现方式，适配不同的数据库支持情况： #

方案1：RANGE滑动窗口（适用支持数值范围RANGE的数据库，如PostgreSQL、Spark SQL、Hive、SQL Server 2022+、MySQL 8.0.28+）

先将int类型的yearmonth转换为总月份数，直接按月份的数值范围滑动窗口，自动过滤不在12个月区间内的记录，不需要补全缺失月份：

WITH month_convert AS (
    SELECT
        tax,
        number,
        yearmonth,
        -- 将YYYYMM格式的int转为总月份数，方便计算月份差
        (yearmonth / 100) * 12 + (yearmonth % 100) AS total_month
    FROM 你的表名
)
SELECT
    number,
    yearmonth,
    SUM(tax) OVER (
        PARTITION BY number
        ORDER BY total_month
        -- 按总月份数的范围滑动，取当前月+往前11个月，共12个月的所有记录
        RANGE BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS last_12_month_tax_sum
FROM month_convert

如果你的需求是统计当前月份对应的上一个完整自然年的和（比如202105对应2020全年），只需要调整窗口的范围判断即可，把RANGE部分替换为对应时间区间的过滤逻辑。 #

方案2：自关联计算（兼容所有支持标准SQL的数据库）

如果你的数据库不支持RANGE数值范围滑动，用自关联的方式逐行匹配符合时间范围的记录做聚合：

SELECT
    a.number,
    a.yearmonth,
    SUM(COALESCE(b.tax, 0)) AS last_12_month_tax_sum
FROM 你的表名 a
LEFT JOIN 你的表名 b
    ON a.number = b.number
    -- 判断b表的月份是否落在a表当前行的近12个月区间内
    AND (b.yearmonth / 100) * 12 + (b.yearmonth % 100) 
        BETWEEN (a.yearmonth / 100) * 12 + (a.yearmonth % 100) - 11 
        AND (a.yearmonth / 100) * 12 + (a.yearmonth % 100)
GROUP BY a.number, a.yearmonth
ORDER BY a.number, a.yearmonth

示例验证 #

以你提供的测试数据为例，number=45、yearmonth=202105的行，近12个月的统计范围是202006~202105，符合条件的tax为100、2、35、47，总和为184，202005的记录因为超出时间范围不会被计入，完全符合需求。

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