GROUP BY 不能直接统计漏斗转化率，因其无法保证各步骤用户池一致；正确做法是先按 user_id 分组标记用户是否完成各步骤，再聚合计算转化率。
GROUP BY 本身不能直接解决漏斗转化率统计——它只能分组，不能跨步对齐用户池。强行只用 GROUP BY + COUNT(DISTINCT user_id) 按 step 分组，会导致各步骤人数基于不同用户子集，除法结果完全失真。
为什么 GROUP BY step 会算错转化率
常见错误写法：SELECT step, COUNT(DISTINCT user_id) FROM events GROUP BY step。这看似能拿到每步人数，但问题在于：
- step=1 的用户集合和 step=2 的用户集合没有交集约束，比如某用户只触发了 step=2 日志（漏发 step=1），也会被计入 step=2 分母
- 同一用户在 step=1 有 5 次曝光、step=2 有 1 次点击，
COUNT(DISTINCT user_id)仍只算 1，但你无法知道他是否从 step=1 走到了 step=2 - 后续做
step2_cnt / step1_cnt时，分子分母根本不是同一个用户池，转化率可能 >100% 或剧烈波动
正确做法：先按 user_id 去重，再用 CASE WHEN 多列标记
核心是把“每个用户是否走到某步”变成一行里的多个布尔字段，再用 SUM() 统计人数。必须用子查询或 CTE 先完成用户级去重：
SELECT
SUM(CASE WHEN has_view = 1 THEN 1 ELSE 0 END) AS uv_view,
SUM(CASE WHEN has_cart = 1 THEN 1 ELSE 0 END) AS uv_cart,
SUM(CASE WHEN has_purchase = 1 THEN 1 ELSE 0 END) AS uv_purchase,
ROUND(100.0 * uv_cart / NULLIF(uv_view, 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 user_id IS NOT NULL
AND event_type IN ('view', 'add_to_cart', 'purchase')
GROUP BY user_id
) t;
关键点：
-
GROUP BY user_id是必须的，它让每个用户只占一行 -
MAX(CASE ...)比SUM(CASE ...)更安全：避免重复事件导致标记翻倍（如用户点了 3 次加购，SUM会得 3，MAX仍是 1） -
NULLIF(uv_view, 0)防止除零报错，不能省 - 若原始
event_type含空格或大小写不一致，CASE WHEN会完全不匹配，务必提前TRIM(LOWER(event_type))
Hive 等不支持 DISTINCT ON 的场景怎么处理
Hive 不支持 SELECT DISTINCT user_id, event_type 直接去重多列，也不能用 DISTINCT ON。此时必须用窗口函数兜底：
SELECT
SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS uv_view,
SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS uv_cart,
SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS uv_purchase
FROM (
SELECT user_id, event_type
FROM (
SELECT
user_id,
event_type,
ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY event_time) AS rn
FROM events
WHERE user_id IS NOT NULL
AND event_type IN ('view', 'add_to_cart', 'purchase')
) t
WHERE rn = 1
) t2;
注意：
- 必须
PARTITION BY user_id, event_type，否则一个用户多次加购会被全保留 -
ORDER BY event_time确保取最早一次行为（业务上通常更合理） - 别在最外层再套一层
GROUP BY user_id—— 这里已经靠rn = 1保证了每个(user_id, event_type)最多一行
真正容易被忽略的是：漏斗各步骤的定义必须与业务口径强绑定。比如“加购”是否包含删除后重新加购、“支付”是否要求状态为 success 而非 pending——这些过滤条件必须写死在子查询的 WHERE 里，而不是靠后期 CASE WHEN 补救。
