漏斗分析必须用PARTITION BY user_id分用户计算序号,否则ROW_NUMBER()或LEAD()会跨用户错误关联;需配合ORDER BY event_time, event_id保证时序,并在窗口前WHERE过滤、最后聚合。

漏斗分析必须用PARTITION BY分用户计算序号
不加 PARTITION BY user_id 的 ROW_NUMBER() 会把所有用户行为混在一起排序,根本没法判断“谁先做了什么”。漏斗本质是单用户路径问题,窗口必须按人切片。
常见错误现象:ROW_NUMBER() OVER (ORDER BY event_time) 导致用户A的view和用户B的cart被连成“view → cart”,结果全是假路径。
- 必须写成
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) -
event_id是二级排序关键:避免同一秒多事件时序错乱(Hive/MySQL都可能因时间精度丢失顺序) - WHERE 过滤要放在窗口函数之前:先
WHERE event_name IN ('view','cart','pay'),再OVER,否则排序开销翻倍
用LEAD()匹配相邻步骤时PARTITION BY不能少
LEAD(event_name, 1) 本身不带分组逻辑,如果漏掉 PARTITION BY user_id,就会跨用户取下一行——比如用户A的view后跟着用户C的pay,直接算作有效转化。
正确写法必须同时满足三个条件:
-
PARTITION BY user_id—— 确保只在同一个人内找下一步 -
ORDER BY event_time, event_id—— 保证时间先后真实可比 - 外层
WHERE event_name = 'view' AND next_event = 'cart'—— 过滤出明确的 step1→step2
示例:
SELECT user_id FROM ( SELECT user_id, event_name, LEAD(event_name, 1) OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS next_event FROM events WHERE event_name IN ('view','cart','pay') ) t WHERE event_name = 'view' AND next_event = 'cart';
PARTITION BY和GROUP BY混用时要注意行粒度
漏斗中间结果仍是明细行(每人每步一条),不能直接 GROUP BY user_id 统计人数——那样会丢失路径信息。真正聚合要放到最后一步。
典型链路是:PARTITION BY 打序号 → LEAD/LAG 提取路径对 → 外层 COUNT(DISTINCT user_id) 算各环节人数。
- 错误:在打序号子查询里就
GROUP BY user_id,导致无法判断 step1 是否真有 step2 - 正确:保留所有原始行为行,用布尔条件筛选出完整路径用户,再聚合
- Hive 用户注意:
DISTINCT多列去重不生效,得靠ROW_NUMBER() = 1替代
时间窗口控制必须嵌套在PARTITION BY内部
业务常要求“30分钟内完成 view → cart”,这个约束不能靠外层 WHERE 实现,必须在窗口定义里用 FRAME 或二次计算。
因为 PARTITION BY user_id ORDER BY event_time 只排了序,没限定时间跨度。要验证间隔,得用 LAG(event_time) 算差值:
-
LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time, event_id)取上一步时间 - 外层用
TIMESTAMPDIFF(MINUTE, prev_time, event_time) 过滤 - 别用
BETWEEN ... AND ...做窗口帧——多数引擎不支持动态时间边界,老实用LAG/LEAD
真正难的不是写对语法,而是想清楚:你是在统计“用户有没有做过A”,还是在确认“同一个用户是否在规则时间内走通A→B→C”。前者用 CASE WHEN 就够,后者非 PARTITION BY 不可。

















