在数据分析工作中，经常需要计算当前月份数据与上个月数据的差异，比如月度销售额环比、用户量月度变化等。使用SQL的LAG窗口函数可以快速实现这类上个月数据的获取和对比，无需复杂的自连接操作。
LAG函数基本语法
LAG函数是SQL中的窗口函数，用于获取当前行之前指定偏移量的行的数据。获取上个月数据的核心语法如下：
-- LAG函数基本语法 LAG(目标列, 偏移量, 默认值) OVER (PARTITION BY 分组列 ORDER BY 排序列) AS 别名
参数说明：
- 目标列：需要获取的之前行的列值，比如上个月的销售额
- 偏移量：向前偏移的行数，获取上个月数据通常设置为1
- 默认值：当偏移行不存在时返回的值，比如第一个月没有上个月数据可以返回0或者NULL
- PARTITION BY：可选的分组条件，比如按不同产品分组计算各自的月度对比
- ORDER BY：排序条件，必须按时间升序排列才能正确获取上个月的对应数据
实际案例演示
假设我们有一张月度销售表sales_monthly，表结构如下：
| 列名 | 类型 | 说明 |
|---|---|---|
| product_id | INT | 产品ID |
| sale_month | DATE | 销售月份，格式为YYYY-MM-01 |
| sale_amount | DECIMAL(10,2) | 当月销售额 |
现在需要查询每个产品每个月的销售额，以及上个月的销售额和环比增长率，使用LAG函数的查询语句如下：
SELECT
product_id,
sale_month,
sale_amount AS current_month_amount,
-- 获取上个月的销售额，没有上个月数据时返回0
LAG(sale_amount, 1, 0) OVER (
PARTITION BY product_id
ORDER BY sale_month
) AS last_month_amount,
-- 计算环比增长率，避免除数为0的情况
CASE
WHEN LAG(sale_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_month) = 0 THEN NULL
ELSE ROUND(
(sale_amount - LAG(sale_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_month))
/ LAG(sale_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_month) * 100,
2
)
END AS month_on_month_growth_rate
FROM sales_monthly
ORDER BY product_id, sale_month;
使用注意事项
在使用LAG函数获取上个月对比数据时，需要注意以下几点：
- ORDER BY子句必须正确按时间字段升序排列，否则无法获取到正确的上个月数据
- 如果数据存在跨年的情况，只要时间字段是连续的日期类型，LAG函数依然可以正确工作，不需要额外处理年份逻辑
- PARTITION BY子句根据业务需求添加，如果不需要分组对比可以省略该部分
- 默认值的设置要符合业务逻辑，比如销售额对比可以返回0，增长率计算时返回NULL更合理
与传统自连接方式对比
传统获取上个月数据的方式通常需要使用自连接，查询语句如下：
SELECT
a.product_id,
a.sale_month,
a.sale_amount AS current_month_amount,
COALESCE(b.sale_amount, 0) AS last_month_amount,
CASE
WHEN COALESCE(b.sale_amount, 0) = 0 THEN NULL
ELSE ROUND((a.sale_amount - COALESCE(b.sale_amount, 0)) / COALESCE(b.sale_amount, 0) * 100, 2)
END AS month_on_month_growth_rate
FROM sales_monthly a
LEFT JOIN sales_monthly b
ON a.product_id = b.product_id
AND DATE_ADD(b.sale_month, INTERVAL 1 MONTH) = a.sale_month
ORDER BY a.product_id, a.sale_month;
对比可以看出，使用LAG函数的查询逻辑更简洁，不需要额外的表连接操作，在数据量较大的情况下执行效率也更高，是获取上个月对比数据的更优选择。