直接partition by year(date)无法实现按月滚动年度累计，因其将数据硬切为自然年，切断跨年连续性；正确做法是放弃年分区，改用order by月份+rows between 11 preceding and current row，并补全空缺月份确保12个月窗口完整对齐。
为什么直接 PARTITION BY YEAR(date) 无法实现“按月滚动的年度累计”
很多人误以为只要用 YEAR(order_date) 分区 + SUM() OVER (ORDER BY month) 就能拿到滚动年累，结果发现：2024年1月累计只含1月、2月累计含1–2月……但2024年12月仍只算2024全年，而“滚动”要求的是最近12个月（比如2024年12月要算2024年1月–2024年12月；2025年1月则要算2024年2月–2025年1月）。PARTITION BY YEAR(...) 把数据硬切成自然年，彻底切断跨年连续性，根本做不到滚动。
正确做法：用 ROWS BETWEEN 11 PRECEDING AND CURRENT ROW 按月序滚动
核心是放弃按年分区，改用按实际月份排序后，用行数窗口限定“往前11个月 + 当前月”，共12个月。前提是你的数据已按月聚合（或可按月粒度对齐）：
SELECT
ym,
monthly_amount,
SUM(monthly_amount) OVER (
ORDER BY ym
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS rolling_12m_sum
FROM (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS ym,
SUM(amount) AS monthly_amount
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) t;
-
ym必须是可排序的字符串（如'2024-01'）或日期型（如STR_TO_DATE(ym, '%Y-%m')），不能是整数202401（否则 202412 会排在 20242 前面） - 如果原始表没按月聚合，
ROWS窗口需作用于日级数据，但必须确保ORDER BY order_date且日期无空缺——否则某天缺失会导致窗口跳过真实12个月 - 首11行结果为
NULL或部分和（因前面不足11行），这是正常行为，不是bug
当数据存在月份空缺时，必须补全再计算
销售数据常有零销量月份不入库，导致 ROWS BETWEEN 11 PRECEDING 实际跨不到12个自然月。这时不能靠填充 NULL 来蒙混，必须显式补月：
WITH months AS (
SELECT DATE_SUB('2025-01-01', INTERVAL (a.a + b.b) MONTH) AS ym
FROM (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS a
CROSS JOIN (SELECT 0 AS b UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11) AS b
WHERE DATE_SUB('2025-01-01', INTERVAL (a.a + b.b) MONTH) >= '2023-01-01'
),
monthly_data AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS ym, SUM(amount) AS amt
FROM orders WHERE order_date >= '2023-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
m.ym,
COALESCE(d.amt, 0) AS monthly_amount,
SUM(COALESCE(d.amt, 0)) OVER (
ORDER BY m.ym
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
) AS rolling_12m_sum
FROM months m
LEFT JOIN monthly_data d ON m.ym = d.ym
ORDER BY m.ym;
- 补月逻辑依赖确定的时间范围（示例中硬编码了 2023-01 至 2025-01），生产环境建议用
MIN/MAX(order_date)动态生成 -
COALESCE(d.amt, 0)很关键：空月份必须算作 0，否则SUM()遇到 NULL 会跳过整个窗口值 - 补月 +
ROWS是目前最稳的通用解法；想用日期运算（如order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)）做动态过滤再聚合，无法在单条 SQL 中对每行都生成不同范围，不适用于窗口函数场景
MySQL 8.0+ 与 PostgreSQL 的语法差异点
虽然窗口函数逻辑一致，但细节处理容易翻车：
- MySQL 中
DATE_FORMAT(order_date, '%Y-%m')返回字符串，可直接ORDER BY；PostgreSQL 需写成TO_CHAR(order_date, 'YYYY-MM')或更推荐用DATE_TRUNC('month', order_date)返回日期类型，避免字符串排序陷阱 - PostgreSQL 支持
RANGE BETWEEN INTERVAL '11 months' PRECEDING AND CURRENT ROW，但要求ORDER BY列是日期类型且无重复值，现实中月份聚合后必有重复（多笔订单同月），所以仍得用ROWS+ 补月 - SQL Server 不支持
DATE_FORMAT，得用FORMAT(order_date, 'yyyy-MM')或CONVERT(CHAR(7), order_date, 120)，且注意120格式返回'2024-01-01'，截取前7位才安全
滚动年累真正难的不是写法，而是意识到：它本质是时间序列对齐问题，不是简单分组求和。月份空缺、边界对齐、类型隐式转换，三者任一失控，结果就偏移一两个月——而这在业绩统计里，往往就是KPI是否达标的分水岭。
