数据位移玩出花，SQL窗口函数:LAG/LEAD/FIRST/LAST案例解析

前些天有粉丝留言，多些高级查询案例总结分享，也有粉丝私信问了查询的案例问题，这里就一起安排上

粉丝问题

埋点表，想按照某个事件，把发生这个事件的时间到下次这个事件发生的时间中间所有的事件分成一个组应该怎么做?

解决思路

1.先获取到相邻两个特定事件之间的时间段;

2.再把所有事件与上面的结果1关联，时间段内的进行统一标识特定事件的开始发生时间;

最终就能通过开始发生时间完成分组了

Mysql 8.0+的话可以直接窗口函数来解决

WITH specific_events AS (
SELECT     
user_id,   
event_time AS session_start,   
LEAD(event_time)OVER (PARTITIONBY user_id ORDERBY event_time) AS next_session_start 
FROM events  WHERE event_name ='特定事件')
SELECT   
e.user_id, 
e.event_time, 
e.event_name, 
s.session_start AS session_id
FROM events e 
LEFTJOIN specific_events s 
ON e.user_id = s.user_id 
AND e.event_time >= s.session_start 
AND (e.event_time < s.next_session_start OR s.next_session_start ISNULL) 

要是mysql5.7+的话，就是用变量来了

-- 初始化变量
SET @group_id = 0;
SET @prev_user = NULL;

-- 执行分组查询
SELECT   
user_id,  event_time,  event_name,  group_id
FROM (  
SELECT     user_id,    event_time,    event_name,    
@group_id := IF(event_name = '特定事件', IF(user_id = @prev_user, @group_id + 1, 1), @group_id) AS group_id,    
@prev_user := user_id  
FROM     events  
ORDER BY     user_id, event_time) tmp
WHERE group_id > 0;  
-- 可选：排除首个分组前的事件

下面是LAG/LEAD/FIRST/LAST的介绍及案例

一、LAG(col, n, default)

核心价值：不用自连接！轻松获取历史数据

经典场景：计算环比增长、用户行为路径分析

案例：电商GMV周环比暴增分析

SELECT
  统计周,
  GMV,
  LAG(GMV, 1, 0) OVER (ORDER BY 统计周) AS 上周GMV, -- 默认0避免NULL
  ROUND((GMV - LAG(GMV) OVER (ORDER BY 统计周))/LAG(GMV) OVER (ORDER BY 统计周)*100,2) AS 环比增长率
FROM 周销售报表

避坑指南：

1 必加ORDER BY：无排序的LAG就像乱序时空穿越！

2 处理NULL值：用default参数给历史数据兜底

3 性能优化：相同OVER()子句可复用，减少重复计算

二、LEAD(col, n, default)

核心价值：提前获取未来数据，轻松计算差值

经典场景：用户下一步行为预测、库存消耗预警

案例：用户行为路径分析

SELECT
  user_id,
  event_time,
  current_action,
  LEAD(current_action, 1, '退出') OVER (PARTITION BY user_id ORDER BY event_time) AS 下一步动作
FROM 用户行为日志
WHERE event_date = '2023-06-18'

避坑指南：

1 分区陷阱：一定要用PARTITION BY隔离不同用户/业务

2 数据边界：末尾数据默认返回NULL，用default参数给明确状态

3 性能杀器：避免在十亿级日志表全量使用，先过滤时间分区！

三、FIRST_VALUE(col)

核心价值：快速定位窗口起点，计算累计值神器

经典场景：用户首单金额分析、累计销售额计算

案例：用户生命周期价值分析

SELECT
  user_id,
  order_date,
  amount,
  FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS 首单日期,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS 累计消费
FROM 订单表

避坑指南：

1 排序陷阱：不加ORDER BY会导致取值为窗口随机第一行！

2 窗口帧控制：默认RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

3 去重技巧：结合DISTINCT快速获取用户首单信息

四、LAST_VALUE(col)

核心价值：捕捉窗口终点数据，动态跟踪最新值

经典场景：库存最新状态跟踪、用户最近一次登录时间

案例：动态库存监控

SELECT
  product_id,
  stock_time,
  current_stock,
  LAST_VALUE(current_stock) OVER (
    PARTITION BY product_id 
    ORDER BY stock_time 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 关键！
  ) AS 最新库存
FROM 库存流水表

避坑指南：

1 窗口帧必改：默认只看到当前行！必须扩展窗口范围

2 性能警报：UNBOUNDED FOLLOWING在大数据量时慎用

3 替代方案：用LEAD(0)或反向排序的FIRST_VALUE更高效

四大函数通用避坑指南：

1 索引优化：窗口函数依赖排序，提前在ORDER BY字段建索引

2 内存控制：一次处理10万+数据时，调大数据库的sort_buffer_size

3 版本陷阱：MySQL 8.0+才能用，MariaDB 10.2+语法略有不同

4 执行顺序：窗口函数在WHERE之后执行，不能作为过滤条件

高阶组合技：四大函数合体

案例：用户复购间隔分析

WITH 用户订单 AS (
  SELECT
    user_id,
    order_date,
    LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS 上次购买日,
    FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date DESC) AS 最近购买日
  FROM 订单表
)
SELECT 
  user_id,
  DATEDIFF(order_date, 上次购买日) AS 购买间隔,
  DATEDIFF(CURRENT_DATE(), 最近购买日) AS 沉默天数 
FROM 用户订单

粉丝福利

你在工作中还遇到过哪些数据处理难题？ 评论区告诉我，后面专题解决！