最小可行结构是：先按时间粒度（如ym=TO_CHAR(date,'YYYYMM')）和业务维度（如region）分组聚合，再用LAG(value,1) OVER(PARTITION BY region ORDER BY ym)算环比、LAG(value,12) OVER(PARTITION BY region ORDER BY ym)算同比，但必须确保数据连续且处理NULL与除零。
什么是窗口函数计算同比/环比的最小可行结构
直接用 LAG() 或 LEAD() 是最常用也最容易出错的起点。它们本身不关心“年”或“月”，只按排序顺序取上一行或下一行值，所以必须确保 ORDER BY 精确到时间粒度（比如 YEAR_MONTH），且数据中不能缺失某个月份——否则 2023-02 的 LAG(value, 12) 会跳到 2022-11，而不是真正的 2022-02。
- 时间字段必须先规整为可排序的连续序列，推荐用
DATE_FORMAT(order_date, '%Y%m')或TO_CHAR(order_date, 'YYYYMM')（PostgreSQL）生成ym字段 - 必须用
PARTITION BY隔离不同业务线/地区，否则 A 地区 12 月的值可能被 B 地区 1 月拉去做同比分母 -
LAG(value, 1) OVER (PARTITION BY region ORDER BY ym)是环比，LAG(value, 12) OVER (PARTITION BY region ORDER BY ym)是同比，但前提是数据按月全量存在
如何处理缺失月份导致的同比错位
现象：原始表只有销售发生日，2022-06 和 2023-06 都有数据，但 2022-07 缺失 → LAG(value, 12) 在 2023-07 行返回的是 2022-06 值，结果错误。
解决思路不是补数据，而是先构造完整的时间维度，再左连业务表：
- 用递归 CTE 或数字表生成连续
ym序列（如从 202201 到 202412） -
LEFT JOIN原始聚合结果（按region, ym分组求和） - 再对这个「补齐后」的结果集开窗：
SELECT ym, region, COALESCE(sales_amt, 0) AS sales_amt, LAG(COALESCE(sales_amt, 0), 12) OVER (PARTITION BY region ORDER BY ym) AS last_year_sales FROM full_ym LEFT JOIN monthly_agg USING (ym, region)
注意：COALESCE 要在窗口函数外部做，否则 LAG(NULL, 12) 还是 NULL，无法参与计算。
多维度组合时 PARTITION BY 的写法陷阱
常见错误是把多个维度塞进一个 PARTITION BY 却没意识到组合爆炸：比如 PARTITION BY region, product_category, channel，当某个 channel 在某个月某类目下无销量，整个分区就断了，LAG 无法跨 channel 找同 region 同类目的值。
更合理的做法是分层计算：
- 先按
region, ym计算区域级同比 →PARTITION BY region ORDER BY ym - 再按
region, product_category, ym计算类目级环比 →PARTITION BY region, product_category ORDER BY ym - 不要试图在一个查询里同时满足“全国同比”+“华东手机类目环比”，拆成两个 CTE 更清晰、易调试
另外，MySQL 8.0+ 支持 WINDOW 子句复用定义：
SELECT ym, region, sales, sales / LAG(sales, 12) OVER w - 1 AS yy_ratio FROM t WINDOW w AS (PARTITION BY region ORDER BY ym)避免重复写冗长的
PARTITION BY ... ORDER BY ...。
性能与 NULL 处理的实际细节
窗口函数本身不慢，慢在前期聚合和补月逻辑。真实场景中容易忽略三点：
-
LAG(..., 12)要求排序字段ym有索引，否则每次开窗都触发全表排序 - 如果同比分母为 0，
sales / LAG(sales, 12)会报错或返回 NULL，应统一用NULLIF(LAG(sales, 12), 0)包裹分母 - PostgreSQL 中
LAG(value, 12, 0)可设默认值，但 MySQL 不支持第三个参数，必须用COALESCE(LAG(...), 0)，且注意这个 0 是“无数据时的替代值”，不是“分母为 0 时的处理”
复杂点在于：时间维度补齐、多层级 PARTITION、分母为零、以及不同数据库对默认值的支持差异——这些地方一漏，结果看着像对的，其实某几个区域的同比已经漂移了。
