SQL如何实现日期范围聚合计算近3个月滚动加权平均值

以下方案默认业务事件明细表名为business_events，DATE字段为标准日期类型，若实际存储为字符串需先通过日期转换函数转为日期格式再参与计算。

核心计算逻辑 #

• 单日ADJUSTED指标规则：当日所有记录Score*Multiplier之和 / 当日Weighting之和
• 90天滚动规则：对每个统计日期，取日期落在「统计日期前90天至统计当日」区间内的所有记录，用区间内全量数据计算加权值，即区间总分子（所有记录Score*Multiplier之和）除以区间总分母（所有记录Weighting之和）
• 性能优化点：单日存在多条记录时，先聚合得到日维度的分子、分母值，再做滚动计算，能大幅降低后续计算的数据量，结果和直接用明细计算完全一致。

兼容多数SQL引擎的通用写法 #

该写法适配MySQL 8.0+、PostgreSQL、Hive、Spark SQL、Presto等主流引擎，通过预聚合+自关联实现，语法调整成本低：

WITH daily_agg AS (
    -- 预聚合：先计算每个自然日的分子、分母值
    SELECT
        DATE,
        SUM(Score * Multiplier) AS daily_numerator,
        SUM(Weighting) AS daily_denominator
    FROM business_events
    GROUP BY DATE
)
SELECT
    curr.DATE,
    -- NULLIF处理分母为0的除零异常
    SUM(hist.daily_numerator) / NULLIF(SUM(hist.daily_denominator), 0) AS rolling_90d_adjusted
FROM daily_agg curr
LEFT JOIN daily_agg hist
    -- 关联90天窗口内的所有日度数据
    ON hist.DATE BETWEEN DATE_SUB(curr.DATE, INTERVAL 90 DAY) AND curr.DATE
GROUP BY curr.DATE
ORDER BY curr.DATE;

支持窗口范围函数的引擎优化写法 #

如果你用的引擎支持RANGE间隔窗口（比如MySQL 8.0、PostgreSQL、BigQuery、ClickHouse），可以直接用窗口函数实现，不需要自关联，执行效率更高：

WITH daily_agg AS (
    SELECT
        DATE,
        SUM(Score * Multiplier) AS daily_numerator,
        SUM(Weighting) AS daily_denominator
    FROM business_events
    GROUP BY DATE
)
SELECT
    DATE,
    SUM(daily_numerator) OVER (
        ORDER BY DATE
        RANGE BETWEEN INTERVAL 90 DAY PRECEDING AND CURRENT ROW
    ) / NULLIF(SUM(daily_denominator) OVER (
        ORDER BY DATE
        RANGE BETWEEN INTERVAL 90 DAY PRECEDING AND CURRENT ROW
    ), 0) AS rolling_90d_adjusted
FROM daily_agg
ORDER BY DATE;

适配调整说明 #

• 日期语法调整：部分引擎的日期计算语法有差异，比如Hive中DATE_SUB直接传天数即可，写为DATE_SUB(curr.DATE, 90)，不需要加INTERVAL关键字，按实际使用的引擎规范调整即可
• 窗口边界调整：如果业务要求前90天不含统计当日，把关联条件上限改为curr.DATE - INTERVAL 1 DAY，窗口函数写法中把CURRENT ROW改为INTERVAL 1 DAY PRECEDING即可
• 空值规则：如果需要窗口无数据时返回0而不是空值，可以在除法逻辑外套一层COALESCE(..., 0)

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