怎样在SQL中利用窗口函数实现按月滚动的年度累计-SQL-PHP中文网

按月滚动的年度累计是基于日历月的滑动窗口累加，需用RANGE BETWEEN INTERVAL '11 months' PRECEDING AND CURRENT ROW实现；若数据库不支持，则改用LATERAL或子查询过滤日期范围。

怎样在sql中利用窗口函数实现按月滚动的年度累计

什么是按月滚动的年度累计

就是对每个日期，计算它往前推12个月（含当月）范围内的累计值，比如2024-06的数据，要累加2023-07到2024-06共12个月的值。这不是简单SUM() OVER (ORDER BY ...)能解决的——那种是“从头累加”，而这里是“滑动窗口累加”。

用 `ROWS BETWEEN` 无法直接实现按月滚动

因为月份天数不等（28–31），ROWS BETWEEN 11 PRECEDING AND CURRENT ROW 是按行数滑动，不是按日历月滑动。若某月有31条记录、某月只有1条，就会错位。真实业务中，数据通常按日或按月聚合后存储，但“滚动12个月”必须基于日期逻辑，不能依赖行序。

错误做法：SUM(sales) OVER (ORDER BY order_month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) → 假设每月只有一行才勉强可用，但一旦补数、缺数或存在多粒度（如日级明细），结果立刻失真
正确前提：确保每行代表一个自然月（如order_month是DATE类型且为每月1号），或先用DATE_TRUNC('month', order_date)规整

标准解法：用 `RANGE BETWEEN INTERVAL '11 months' PRECEDING AND CURRENT ROW`

PostgreSQL / Snowflake / BigQuery（标准SQL模式）支持RANGE配合INTERVAL，这才是按日历月滚动的正解。前提是排序字段是日期类型（DATE或TIMESTAMP），且已归一化到月初。

SELECT
  order_month,
  sales,
  SUM(sales) OVER (
    ORDER BY order_month
    RANGE BETWEEN INTERVAL '11 months' PRECEDING AND CURRENT ROW
  ) AS rolling_annual_sum
FROM (
  SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(amount) AS sales
  FROM orders
  GROUP BY 1
) t;

order_month 必须是DATE类型（如'2023-07-01'），不能是字符串，否则RANGE失效
MySQL 8.0+ 不支持INTERVAL在RANGE中使用；得改用自连接或LATERAL（见下一条）
SQL Server 需用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW + 子查询过滤日期范围，性能较差

兼容性兜底方案：用子查询或 `LATERAL`（适用于 MySQL / SQL Server）

当窗口函数不支持日期区间时，把“滚动12个月”转成“对每一行，查它往前12个月内的所有行再求和”，用LATERAL（PostgreSQL / BigQuery）或相关联子查询（MySQL）实现。

SELECT
  t1.order_month,
  t1.sales,
  (SELECT SUM(t2.sales)
   FROM monthly_sales t2
   WHERE t2.order_month >= t1.order_month - INTERVAL '11 months'
     AND t2.order_month

注意t1.order_month - INTERVAL '11 months'在不同库写法不同：MySQL用DATE_SUB(t1.order_month, INTERVAL 11 MONTH)，SQL Server用DATEADD(MONTH, -11, t1.order_month)
必须给order_month建索引，否则N²扫描，万级数据就明显变慢
如果源表是日级明细，别在子查询里重复聚合，先物化月度汇总表再计算

真正难的不是写出来，而是确认你的order_month字段是否严格代表自然月起点、有没有空月、以及目标数据库是否把INTERVAL当成窗口边界的一部分——这些细节漏掉，结果可能偏差几个月。