在SQL数据分析工作中，经常需要获取当前记录的前一行或多行对应字段的数值，比如计算相邻时间段的指标变化、判断用户行为的连续性等。传统的自连接写法逻辑复杂且执行效率较低，而LAG函数作为窗口函数的核心成员，能够简洁高效地实现跨行取值的需求。
LAG函数基础语法
LAG函数的核心作用是从当前行向上回溯指定行数，获取对应字段的值，基础语法如下：
-- LAG函数基础语法
LAG(column_name, offset, default_value) OVER (
[PARTITION BY partition_column]
ORDER BY sort_column [ASC|DESC]
)
各参数含义说明：
- column_name：需要获取值的字段名称，必填参数
- offset：回溯的行数，默认值为1，即获取上一行的值，可选参数
- default_value：当回溯的行不存在时返回的默认值，可选参数，不填则返回NULL
- PARTITION BY：可选的分组子句，用于将数据集按指定字段分组，在每个分组内独立计算
- ORDER BY：必填的排序子句，用于指定分组内行的排序规则，排序结果决定了行的先后顺序
基础使用示例
假设我们有一张用户每日消费记录表user_daily_cost，表结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| user_id | INT | 用户ID |
| cost_date | DATE | 消费日期 |
| cost_amount | DECIMAL | 当日消费金额 |
现在需要查询每个用户每日的消费金额，以及该用户上一次的消费金额，使用LAG函数的实现如下：
-- 查询用户每日消费及上一次消费金额
SELECT
user_id,
cost_date,
cost_amount,
-- 获取同一用户上一行的消费金额，没有上一行则返回0
LAG(cost_amount, 1, 0) OVER (
PARTITION BY user_id
ORDER BY cost_date ASC
) AS last_cost_amount
FROM user_daily_cost
ORDER BY user_id, cost_date;
上述查询中，首先按user_id分组，保证每个用户的计算相互独立，然后按cost_date升序排序，确定日期的先后顺序，最后通过LAG函数获取每个用户上一次的消费金额，若用户没有之前的消费记录，则返回默认值0。
进阶应用场景
计算相邻周期指标差值
基于上面的消费表，我们可以计算每个用户相邻两次消费的间隔天数，以及消费金额的变化值：
-- 计算相邻消费间隔和金额变化
SELECT
user_id,
cost_date,
cost_amount,
last_cost_date,
-- 计算两次消费间隔天数
DATEDIFF(cost_date, last_cost_date) AS cost_interval_days,
-- 计算消费金额变化
cost_amount - last_cost_amount AS cost_change
FROM (
SELECT
user_id,
cost_date,
cost_amount,
LAG(cost_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY cost_date ASC
) AS last_cost_date,
LAG(cost_amount, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY cost_date ASC
) AS last_cost_amount
FROM user_daily_cost
) t
WHERE last_cost_date IS NOT NULL
ORDER BY user_id, cost_date;
判断用户连续行为
如果需要判断用户是否连续登录，我们可以结合LAG函数获取用户上一次的登录日期，再判断日期差是否为1：
-- 判断用户是否连续登录
SELECT
user_id,
login_date,
-- 判断是否为连续登录，是则返回1，否则返回0
CASE
WHEN DATEDIFF(login_date, last_login_date) = 1 THEN 1
ELSE 0
END AS is_continuous_login
FROM (
SELECT
user_id,
login_date,
LAG(login_date, 1, NULL) OVER (
PARTITION BY user_id
ORDER BY login_date ASC
) AS last_login_date
FROM user_login_record
) t
ORDER BY user_id, login_date;
使用注意事项
- LAG函数的
ORDER BY子句是必填项，没有排序规则的话无法确定行的先后顺序，会导致取值结果不符合预期 - 如果使用了
PARTITION BY分组，回溯操作只会在当前分组内进行，不会跨分组取值 - 当回溯的行数超过分组内的总行数时，会返回设置的默认值，若未设置默认值则返回NULL
- LAG函数和LEAD函数是对应的，LEAD函数用于获取当前行向下指定行数的字段值，二者可以结合使用满足不同的跨行取值需求
需要注意的是，不同数据库对窗口函数的支持程度略有差异，部分低版本的MySQL、SQLite可能不支持LAG函数，使用前需要确认数据库的版本和兼容性。