窗口聚合中同期对比（yoy）和环比增长（mom）需在保持原始行粒度下计算，lag()适用于mom，而yoy需join自关联补全月份，避免直接lag(...,12)；须注意时间归一化、null处理及多维分区。
什么是窗口聚合函数中的同期对比和环比增长
同期对比（Year-over-Year, YoY）和环比增长（Month-over-Month, MoM）本质都是两个时间点的值做差值或比值，但必须在不丢失原始行粒度的前提下完成——这正是窗口函数的强项。LAG() 和 LEAD() 是最直接的工具，而 DATE_TRUNC() 或 EXTRACT() 配合分组则用于构造“同期”逻辑。别用 GROUP BY + 子查询硬连，容易漏数据、性能差，也难对齐时间维度。
用 LAG() 实现标准环比（MoM）计算
环比就是当前月减去上个月，关键在于按时间排序后取前一行。注意：必须显式指定 ORDER BY，且时间字段不能有重复值（否则 LAG() 行为不确定）；若存在多条同月记录，先用 SUM() 或 AVG() 聚合再开窗更稳妥。
-
LAG(amount, 1)取上一行的amount；第二个参数可改为2拿上上月 - 月份需提前归一化，例如
DATE_TRUNC('month', order_date)（PostgreSQL/BigQuery）或CONCAT(YEAR(order_date), '-', LPAD(MONTH(order_date), 2, '0'))（MySQL） - 结果中首月的环比值为
NULL，建议用COALESCE(lag_amount, 0)或保留原态，避免误算增长率
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(revenue) AS monthly_revenue,
LAG(SUM(revenue)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS prev_month_revenue,
ROUND(
(SUM(revenue) - LAG(SUM(revenue)) OVER (ORDER BY DATE_TRUNC('month', order_date)))
/ NULLIF(LAG(SUM(revenue)) OVER (ORDER BY DATE_TRUNC('month', order_date)), 0),
4
) AS mom_growth_rate
FROM sales
GROUP BY 1
ORDER BY 1;
用 JOIN + 窗口辅助实现严格同期（YoY）
纯粹靠 LAG() 做年同期不安全——比如 2023-03 取 LAG(..., 12)，前提是数据每月只有一行且连续。现实中常缺月或有多行，更可靠的方式是：先聚合到月粒度，再用 LEFT JOIN 自关联，匹配 month = month - INTERVAL '1 year'。窗口函数在这里只起辅助作用，比如补全缺失月份或标记是否匹配成功。
- 不要依赖
LAG(col, 12)直接跨年，除非你 100% 确认时间序列绝对稠密 - 用
GENERATE_SERIES()（PostgreSQL）或递归 CTE 补全月份，再左连实际数据，能暴露空档期 - MySQL 8.0+ 支持
DATE_SUB(month, INTERVAL 1 YEAR)做 join 条件；BigQuery 用DATE_SUB(month, INTERVAL 1 YEAR)
-- PostgreSQL 示例：带补全的 YoY
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS rev
FROM sales GROUP BY 1
),
full_months AS (
SELECT GENERATE_SERIES(
MIN(month), MAX(month), '1 month'::INTERVAL
) AS month FROM monthly
),
yoy_joined AS (
SELECT
f.month,
m1.rev AS rev_curr,
m2.rev AS rev_last_year
FROM full_months f
LEFT JOIN monthly m1 ON f.month = m1.month
LEFT JOIN monthly m2 ON f.month = m2.month + INTERVAL '1 year'
)
SELECT
month,
rev_curr,
rev_last_year,
ROUND((rev_curr - rev_last_year) / NULLIF(rev_last_year, 0), 4) AS yoy_growth
FROM yoy_joined
ORDER BY month;
常见错误与性能陷阱
写错一个 OVER 子句就可能让结果全偏：同比/环比不是简单加减，而是带时间语义的比较。最容易被忽略的是时区和日期截断精度——比如用 EXTRACT(YEAR FROM date) 分组会把不同月混在一起；用 LAG() 却没 PARTITION BY product_id 会导致跨产品错位。
- 在多维分析中（如按地区+产品），
OVER (PARTITION BY region, product ORDER BY month)必须完整，漏掉任意维度都会导致错行 -
NULLIF(x, 0)必须包裹除数，否则除零报错；ROUND(..., 4)比直接::NUMERIC更可控 - 大表慎用
GENERATE_SERIES或递归 CTE 补全，优先考虑业务侧已有的日历维表 - MySQL 5.7 不支持窗口函数，必须升级到 8.0+；SQLite 3.25+ 才支持
LAG()
真正难的从来不是写出公式，而是确认每一行的“上月”和“去年同期”在业务上是否真的可比——比如春节错位、财务关账延迟、数据上报滞后，这些都得在 SQL 外部对齐，窗口函数不会替你判断。
