直接用LAG无法准确获取上月数据,因它按行序而非自然月偏移;必须先用generate_series或递归CTE补全连续月份,再对归一化月字段(如DATE_TRUNC('month', date))开窗计算LAG(sales) OVER (ORDER BY month_start)。

LAG函数怎么写才能拿到上个月的数据
直接用 LAG() 不加参数,默认取前1行,但前提是数据必须严格按时间排序且不能缺月。如果原始表里有2024-01、2024-03但缺2024-02,LAG(sales) 会把3月值和1月值强行配对,结果完全错误。
正确做法是先生成连续的月份序列(比如用递归CTE或日历表),再左连接业务数据,确保每行代表一个完整自然月;之后再对补零/补NULL后的结果用 LAG():
SELECT ym, COALESCE(sales, 0) AS sales, LAG(COALESCE(sales, 0)) OVER (ORDER BY ym) AS last_month_sales FROM full_month_list LEFT JOIN monthly_sales USING (ym);
-
ym必须是DATE或可排序的年月字符串(如'2024-01'),不能是整数202401,否则排序会出错(202401、202402、202410 会被当成数字排成 202401、202410、202402) - 一定要在
OVER子句里显式写ORDER BY ym,不依赖表默认顺序 - 如果原始数据粒度是“天”,别在明细层用
LAG,先GROUP BY YEAR(date), MONTH(date)聚合好再算环比
计算增长率时怎么处理除零和NULL
上个月销售额为0,或者 LAG() 返回NULL(比如首月),直接除会导致 NULL 或报错(不同数据库行为不同)。不能只靠 WHERE last_month_sales != 0 过滤——这会丢掉首月和所有零值月份的分析维度。
稳妥写法是用 NULLIF 配合 COALESCE:
COALESCE( (sales - LAG(sales) OVER (ORDER BY ym)) * 1.0 / NULLIF(LAG(sales) OVER (ORDER BY ym), 0), 0 ) AS mom_rate
-
NULLIF(x, 0)在分母为0时返回NULL,避免除零错误 - 乘
1.0强制转浮点,防止整数除法截断(例如5/10 = 0) - 外层
COALESCE(..., 0)把首月的NULL统一设为0,比留空更利于下游展示
为什么用窗口函数比自连接更可靠
有人用 LEFT JOIN 自连接来匹配“本月 vs 上月”,例如 ON a.ym = DATE_ADD(b.ym, INTERVAL 1 MONTH)。问题在于:日期函数跨年/跨库兼容性差(MySQL用 DATE_ADD,PostgreSQL用 a.ym + INTERVAL '1 month',SQL Server用 DATEADD),而且一旦某月无数据,连接就失效,还得额外 FULL OUTER JOIN 补全——复杂度陡增。
LAG() 的优势是逻辑清晰、语法统一、不依赖JOIN性能:
- 只要排序字段确定,
LAG就严格按物理行序取值,不受日期间隔是否均匀影响 - 所有主流数据库(PostgreSQL、SQL Server、Oracle、BigQuery、Doris)都支持标准
LAG(expr) OVER (ORDER BY ...)语法 - 执行计划通常比多表JOIN更轻量,尤其数据量大时
实际跑出来结果不对?重点检查这三个地方
环比率数值异常,90%的情况不是函数写错,而是底层数据或排序没对齐:
- 确认
ORDER BY字段是唯一且无重复的,如果多行共用同一个ym(比如按天聚合但没去重),LAG取哪一行不确定 - 检查是否漏了
PARTITION BY——如果要分别计算每个产品的环比,必须写LAG(sales) OVER (PARTITION BY product_id ORDER BY ym),否则所有产品混在一起排序 - 验证原始
sales字段有没有意外的负值或异常极大值(比如退款记为负、测试数据灌入百万级脏数),这些会直接污染增长率结果
真正麻烦的是“数据缺月”和“多源拼接后时间字段类型不一致”——这两个问题不会报错,但会让 LAG 的结果看起来合理实则全错。










