SQL怎样实现按月动态滚动合计_DATE结合SUM OVER技巧-SQL-PHP中文网

“按月动态滚动合计”指按日期排序后，对每行计算最近12个自然月（含当月）的累计和，窗口以日历月对齐滑动（如2024-03-15对应2023-04-01至2024-03-15），需用date_sub与join/cte模拟范围，不可用rows between。

sql怎样实现按月动态滚动合计_date结合sum over技巧

什么是“按月动态滚动合计”？

就是对某字段（比如销售额）按日期排序后，计算“最近12个月（含当月）的累计和”，且每行结果都随当前行日期自动滑动窗口——不是固定从年初算起，而是以ORDER BY date为轴心，往前数12个自然月（非365天）。关键在于：窗口边界必须按日历月对齐（如2024-03-15 的滚动区间是 2023-04-01 至 2024-03-15），不能简单用 ROWS BETWEEN 11 PRECEDING AND CURRENT ROW。

SUM OVER + DATE_SUB 实现月对齐滚动窗口

MySQL 8.0+ / PostgreSQL / BigQuery 等支持 INTERVAL 和函数式窗口边界的引擎可用此法。核心思路是：把当前行的 date 向前推12个月，得到滚动起点，再用 SUM() OVER 配合 RANGE BETWEEN 或子查询关联实现逻辑滚动。

MySQL 中无法直接在 OVER 子句里写 DATE_SUB(date, INTERVAL 12 MONTH) 作窗口下界，得改用自连接或 LATERAL（8.0.14+）
更通用、兼容性更好的做法是：先用变量或 CTE 衍生出每行对应的滚动起始月（DATE_FORMAT(DATE_SUB(date, INTERVAL 11 MONTH), '%Y-%m-01')），再通过 JOIN 或窗口内过滤模拟范围
PostgreSQL 可直接用 GENERATE_SERIES + LATERAL，但生产环境建议走 CTE + WHERE date >= rolling_start + 分组聚合替代窗口函数，避免笛卡尔积

示例（MySQL 8.0，假设表叫 sales，字段为 sale_date, amount）：

WITH monthly_base AS (
  SELECT 
    sale_date,
    amount,
    -- 滚动窗口左边界：当前月往前推11个月的第一天（保证覆盖12整月）
    DATE_FORMAT(DATE_SUB(sale_date, INTERVAL 11 MONTH), '%Y-%m-01') AS win_start
  FROM sales
)
SELECT 
  b1.sale_date,
  SUM(b2.amount) AS rolling_12m_sum
FROM monthly_base b1
JOIN monthly_base b2 
  ON b2.sale_date >= b1.win_start 
  AND b2.sale_date <h3>用 SUM OVER RANGE 需警惕的陷阱</h3><p>有人尝试用 <code>SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL 12 MONTH PRECEDING AND CURRENT ROW)</code>，这在 PostgreSQL 是可行的，但在 MySQL 中不支持 <code>RANGE</code> 配 <code>INTERVAL</code>；在 BigQuery 中需用 <code>DATE_SUB</code> 转成天数再配合 <code>UNBOUNDED PRECEDING</code> 手动截断——本质仍是近似（按天数而非日历月）。</p>

RANGE BETWEEN ... 依赖排序列的值可比较且连续，日期类型虽满足，但“12 MONTH”语义在各数据库中解析不一致：MySQL 忽略该语法，PostgreSQL 按月粒度对齐，BigQuery 要求转为 DAY 单位
若数据存在跨月空缺（比如某月无销售记录），RANGE 不会补零，但滚动合计通常需要“截至当月”的完整12个月逻辑，此时必须显式生成月维度再 LEFT JOIN
性能上，纯 OVER 写法在小数据量快，但一旦涉及百万级日期粒度，自连接 CTE 更可控，也方便加索引（如在 sale_date 上建 B-tree）

为什么不用 GROUP BY YEAR_MONTH + 窗口函数？

因为“动态滚动”要求每行独立计算其自身截止日的前12个月，不是按年月分组后再累加。例如 2024-03-15 和 2024-03-28 属于同一自然月，但滚动窗口终点不同，不能共用同一个 2024-03 分组结果。

如果先 GROUP BY DATE_FORMAT(sale_date, '%Y-%m') 汇总月度值，再对月度结果做 SUM() OVER (ORDER BY month ORDER ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)，得到的是“最近12个日历月的月度汇总和”，丢失了日粒度动态性
真实业务常需每日看板（如风控指标、活跃用户趋势），必须保留原始日期精度，否则 3 月最后一天的滚动值会比 3 月第一天高出整月量级
补救办法是：在日粒度表上加一列 rolling_month_start = DATE_FORMAT(DATE_SUB(sale_date, INTERVAL 11 MONTH), '%Y-%m-01')，再按此列聚合，但本质上又回到 CTE 关联的老路

真正省事的做法，是接受“滚动合计=逐行查子集”的事实，在数据量可控前提下优先保语义准确；若量大，提前物化滚动中间表并每日增量更新，比硬啃窗口函数更稳。