同比是今年某期与去年同期比较，环比是本期与上期比较；lag()等窗口函数可按时间排序后错位取值，避免自连接，但需确保时间字段规范且已排序。
什么是同比和环比，窗口函数怎么帮上忙
同比是跟去年同期比（比如今年3月 vs 去年3月），环比是跟上一期比（比如今年3月 vs 今年2月）。直接用 JOIN 自关联容易漏数据、写法冗长，而 LAG() 和 LEAD() 这类窗口函数天然适合按时间排序后“错位取值”，不用自连接就能拿到上期/同期值。
关键前提是：时间字段必须能精确到周期单位（如 year_month 字符串或 DATE 类型），且数据已按时间升序排列；否则 LAG() 取出来的就不是你要的“上个月”。
用 LAG() 计算环比：只差一个 ORDER BY
环比本质就是当前行和前一行的对比。只要把数据按时间排序，LAG() 就能取出上期数值：
SELECT dt, revenue, LAG(revenue) OVER (ORDER BY dt) AS prev_revenue, ROUND((revenue - LAG(revenue) OVER (ORDER BY dt)) / NULLIF(LAG(revenue) OVER (ORDER BY dt), 0), 4) AS mom_rate FROM sales;
-
dt必须是可排序的时间列（DATE、TEXT格式如'2024-03'都行，但不能是乱序字符串） -
NULLIF(..., 0)防止除零错误——这是实际跑起来最常报错的地方 - 如果想按月份环比（忽略日），先用
DATE_TRUNC('month', dt)或TO_CHAR(dt, 'YYYY-MM')统一粒度，再ORDER BY
用 LAG() + 时间偏移算同比：别硬写固定 offset
同比不是简单 LAG(revenue, 12) —— 如果数据里有缺失月份（比如2023年2月没记录），LAG(revenue, 12) 会跳到更早的月份，结果错乱。
正确做法是先构造“年份+月份”作为分组键，再按该键排序取前12行：
SELECT
year_month,
revenue,
LAG(revenue, 12) OVER (ORDER BY year_month) AS last_year_revenue,
ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY year_month)) / NULLIF(LAG(revenue, 12) OVER (ORDER BY year_month), 0), 4) AS yoy_rate
FROM (
SELECT
TO_CHAR(order_date, 'YYYY-MM') AS year_month,
SUM(amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
) t;
-
year_month必须是格式统一的字符串（如'2023-01'），不能是202301数字——否则排序会出错（202310排在20232前面） - 如果原始数据是每日粒度，务必先
GROUP BY汇总到月，再套窗口函数；否则LAG()是按天取，同比就变成“今天 vs 去年今天”，不是“本月 vs 去年本月” - 某些数据库（如 Presto）不支持
LAG(..., N)的N动态参数，只能写死数字，这时更要确保输入数据是连续无缺的月度汇总
遇到空值、跨年断档、非标准周期怎么办
真实业务中，LAG() 返回 NULL 不少见——但你得区分是“真没数据”还是“计算逻辑崩了”。几个硬核检查点：
- 查下
SELECT COUNT(*) FROM sales GROUP BY year_month，确认有没有月份缺失；缺了就得补全（用GENERATE_SERIES或日期维表LEFT JOIN） - 如果用的是周同比（比如周一到周日），别用
LAG(..., 52)，周数可能跨年（2023年第52周 ≠ 2024年第52周），得用ISOYEAR-WEEK格式对齐 - 有些场景要“滚动同比”（比如最近7天 vs 前7天），那就得用
SUM() OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)先聚合，再套LAG()
窗口函数本身不解决数据质量问题，它只是把“取上期值”这件事变简单了；但前期的数据清洗和周期对齐，一步都不能省。
