漏斗分析必须为用户行为打步骤序号(如ROW_NUMBER())后按序号分组并用COUNT(DISTINCT user_id)计算,否则GROUP BY事件类型仅得并列统计;转化率需用窗口函数(如FIRST_VALUE或LAG)拉取上层人数作分母,并将所有过滤条件置于子查询内确保路径完整。

漏斗各层数据必须带有序号字段才能用 GROUP BY 计算转化率
直接对原始行为日志 GROUP BY 事件类型(如 'view'、'click'、'submit')只能统计每层绝对量,无法体现用户路径顺序。漏斗本质是「同一用户在时间序列上的多步行为」,所以必须先给每个用户的行为打上步骤序号(比如用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time)),再按序号分组,否则 GROUP BY 出来的结果不是漏斗,只是并列统计。
常见错误现象:
— 把 SELECT event_type, COUNT(*) FROM logs GROUP BY event_type 当作漏斗,结果各层人数加起来远超总用户数
— 忽略去重,用 COUNT(*) 而非 COUNT(DISTINCT user_id),导致单用户多次触发被重复计入
- 真正可用的漏斗分层字段通常是:步骤编号(
step_num)、或预定义阶段名(stage = 'view' / 'click' / 'submit') - 必须配合
COUNT(DISTINCT user_id),否则转化率分母失真 - 如果原始表没步骤序号,得先用 CTE 或子查询生成,不能跳过这步
用 GROUP BY + 窗口函数组合算各层转化率更稳
单纯 GROUP BY step_num 只能拿到每层用户数,要算转化率(本层人数 ÷ 上层人数),就得把上层人数“拉下来”。这时候别硬写自连接,用窗口函数更简洁可靠:
WITH funnel AS (
SELECT
step_num,
COUNT(DISTINCT user_id) AS users
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS step_num
FROM events
WHERE event_type IN ('view', 'click', 'submit')
) t
GROUP BY step_num
)
SELECT
step_num,
users,
ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_num), 2) AS rate_pct
FROM funnel;
关键点:
— FIRST_VALUE(users) OVER (ORDER BY step_num) 固定取第一步人数作分母,避免除零或错位
— 如果想看逐级转化(比如 click / view、submit / click),就用 LAG(users) OVER (ORDER BY step_num) 替换分母
- MySQL 8.0+、PostgreSQL、SQL Server 都支持;旧版 MySQL 需用变量模拟,易出错
- 注意
ORDER BY step_num必须严格升序,漏掉某步(比如没人到 submit)会导致LAG返回 NULL - 若某步人数为 0,
LAG分母为 0 会报错,需加NULLIF保护:users / NULLIF(LAG(users) OVER (...), 0)
WHERE 条件必须放在子查询里,不能丢在 GROUP BY 外层
漏斗分析对数据范围极其敏感。比如只看当天完成全流程的用户,很多人会这样写:
-- ❌ 错误:外层 WHERE 会砍掉中间步骤,漏斗断裂 SELECT step_num, COUNT(DISTINCT user_id) FROM events WHERE event_date = '2026-11-03' GROUP BY step_num;
正确做法是把时间、用户属性等过滤条件压到生成步骤序号的子查询里:
-- ✅ 正确:所有步骤都在同一时间窗口内提取
SELECT step_num, COUNT(DISTINCT user_id)
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS step_num
FROM events
WHERE event_date BETWEEN '2026-11-03' AND '2026-11-03'
AND event_type IN ('view', 'click', 'submit')
) t
GROUP BY step_num;
- 漏斗断裂常因外层
WHERE导致——比如用户在 11/02 看了 page,在 11/03 提交表单,外层限定 11/03 就只剩 submit,漏掉前两步 - 若需跨天漏斗(如注册→次日首单),得用日期差逻辑,不能简单靠
event_date过滤 - 用户维度过滤(如只看 VIP)也必须放子查询里,否则同理失真
实际跑漏斗时最易忽略的是「用户行为完整性校验」:有没有人跳步(比如直接 submit 没 view)?有没有人重复触发同一层?这些不会报错,但会让转化率虚高或失真,得靠 COUNT(*) vs COUNT(DISTINCT user_id) 的比值先快速探查。










