如何在SQL中利用LEAD()和LAG()重构复杂的事件漏斗模型？-SQL-PHP中文网

lead() 和 lag() 不能用于重构事件漏斗模型，因其仅支持单行位移，无法判断同一用户是否连续完成多阶段事件；正确做法是先按 user_id 聚合各事件存在性，再用 case when 统计转化率。

如何在sql中利用lead()和lag()重构复杂的事件漏斗模型？

不能用 LEAD() 和 LAG() 重构事件漏斗模型 —— 它们根本不处理用户路径或阶段包含关系，只做单行位移。 漏斗的核心是“同一用户是否连续完成多个事件”，而 LEAD/LAG 只能告诉你“这个用户下一条记录是不是 purchase”，无法确认“他之前有没有 view”。强行套用会把跨用户的乱序事件误判为有效路径。

为什么 WHERE 或 JOIN 里直接用 LAG/LEAD 判定漏斗必然出错

常见错误写法：WHERE event_type = 'view' AND LEAD(event_type) OVER (PARTITION BY user_id ORDER BY event_time) = 'add_to_cart'。这看似在找“view 后紧跟 add_to_cart”，但问题极多：

没过滤重复事件：一个用户一天内多次 view，LEAD 可能取到他自己第二次的 view，而非真正的 add_to_cart
时间排序不唯一：若 event_time 有毫秒缺失或并发写入，ORDER BY event_time 无法保证稳定顺序，LEAD 取到的“下一行”可能是任意同秒事件
漏斗要求“view 用户池 ⊇ add_to_cart 用户池”，但该写法只筛出部分 view 行，根本没统计 add_to_cart 的总人数，转化率分母错位
无法处理跳步（如用户 view → purchase，跳过 cart），LEAD 只能看紧邻下一行，不是逻辑意义上的“后续任意环节”

真正需要漏斗时，必须先做用户级聚合再判断

正确路径是：先按 user_id 聚合各事件是否存在，再用 CASE WHEN 判断组合条件。例如三阶漏斗：

SELECT
  COUNT(*) AS uv_view,
  COUNT(CASE WHEN has_cart = 1 THEN 1 END) AS uv_cart,
  COUNT(CASE WHEN has_purchase = 1 THEN 1 END) AS uv_purchase,
  ROUND(100.0 * COUNT(CASE WHEN has_cart = 1 THEN 1 END) / NULLIF(COUNT(*), 0), 2) AS rate_view_to_cart
FROM (
  SELECT
    user_id,
    MAX(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS has_view,
    MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS has_cart,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS has_purchase
  FROM events
  WHERE event_type IN ('view', 'add_to_cart', 'purchase')
  GROUP BY user_id
) t;

关键点：

GROUP BY user_id 确保每个用户只算一次，消除重复事件干扰
MAX(CASE ...) 是布尔聚合：只要该用户有过此事件，就标为 1
所有漏斗阶段共享同一 user_id 集合，分子天然是分母子集
若需时间约束（如 view 后 30 分钟内 cart），得先用子查询对每个用户取最早 view 和最早 cart 时间，再用 CASE 做差值判断 —— 这仍不是 LAG 的场景

LAG/LEAD 唯一可用的漏斗辅助场景：单用户序列中的相邻行为检测

仅当业务明确要求“严格紧邻、无跳过、且已按用户+时间去重”时，LAG/LEAD 才可辅助验证。例如风控中检测“登录后 5 秒内连续两次输错密码”：

SELECT user_id, event_time, event_type
FROM (
  SELECT 
    user_id, event_time, event_type,
    LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_type,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM events 
  WHERE event_type IN ('login_fail')
) t
WHERE event_type = 'login_fail' 
  AND prev_type = 'login_fail' 
  AND event_time - prev_time <p>注意限制：</p>