在MySQL中实现过去指定天数的累计滚动求和（适配季节性销售）

我完全理解你的痛点——年度季节性的业务用周/月数据确实很难看清真实增长趋势，毕竟旺季和淡季的波动会掩盖跨年度的变化。针对你需要的过去365天滚动累计销售额，我给你整理了一套基于现有SQL的解决方案，分步骤来实现：

核心思路 #

你的现有查询已经能返回按Year、Week聚合的Sales数据，我们需要：

1. 将Year+Week转换为具体的日期（比如每周的结束日期），让SQL能识别时间维度的区间
2. 补全连续的周（避免某周无销售时出现日期间隙，导致滚动求和范围偏差）
3. 使用窗口函数计算过去365天内的累计销售额

示例SQL实现（以PostgreSQL为例） #

假设你的现有关联查询已经能输出Year、Week、Sales字段，我们可以用CTE来封装逻辑：

-- 第一步：给现有周度销售数据加上对应的日期
WITH weekly_sales_with_date AS (
    SELECT
        -- 生成每周的结束日期（可根据业务习惯改为开始日期）
        make_date(year, 1, 1) + (week - 1) * INTERVAL '7 days' + INTERVAL '6 days' AS week_end_date,
        sales
    FROM
        -- 替换成你现有的关联查询
        (SELECT Year, Week, SUM(Sales) AS Sales FROM your_joined_tables GROUP BY Year, Week) AS existing_weekly_data
),
-- 第二步：生成连续的周日期，补全无销售的空白周
continuous_weeks AS (
    SELECT
        generate_series(
            (SELECT MIN(week_end_date) FROM weekly_sales_with_date),
            (SELECT MAX(week_end_date) FROM weekly_sales_with_date),
            INTERVAL '7 days'
        ) AS week_end_date
)
-- 第三步：计算365天滚动累计销售额
SELECT
    c.week_end_date,
    -- 滚动求和：包含当前周在内的过去365天所有销售
    SUM(COALESCE(w.sales, 0)) OVER (
        ORDER BY c.week_end_date
        RANGE BETWEEN INTERVAL '365 days' PRECEDING AND CURRENT ROW
    ) AS rolling_365d_total_sales
FROM
    continuous_weeks c
LEFT JOIN
    weekly_sales_with_date w ON c.week_end_date = w.week_end_date
ORDER BY
    c.week_end_date;

关键细节说明 #

• 日期转换：不同数据库的日期生成函数略有不同：
  • SQL Server：用DATEADD(week, week-1, DATEFROMPARTS(year, 1, 1))生成周开始日期，再加6天得到周结束日期
  • MySQL：用DATE_ADD(DATE_FORMAT(CONCAT(year, '-01-01'), '%Y-%m-%d'), INTERVAL (week-1) WEEK)
• 补全连续周：如果跳过这一步，当某周没有销售数据时，窗口函数会忽略该周，导致滚动求和的时间范围不是严格的365天，而是最近有数据的时间段
• 窗口函数范围：用RANGE BETWEEN INTERVAL '365 days' PRECEDING而不是ROWS，因为我们要按时间区间而非行数来计算，避免日期间隙导致的错误
• COALESCE处理空值：确保无销售的周销售额按0计算，不影响累计结果

扩展优化 #

如果你的业务能获取到每日销售数据，可以直接基于每日数据计算滚动累计，结果会更精准：

SELECT
    sale_date,
    SUM(sales) OVER (
        ORDER BY sale_date
        RANGE BETWEEN INTERVAL '365 days' PRECEDING AND CURRENT ROW
    ) AS rolling_365d_sales
FROM daily_sales
ORDER BY sale_date;

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