怎样在SQL中利用COUNT CASE统计不同渠道转化效果-SQL-PHP中文网

转化率必须基于用户级去重计算，分母为各渠道独立访问用户数，分子为该渠道下单的独立用户数，不可用事件数或全站用户数替代。

怎样在sql中利用count case统计不同渠道转化效果

直接用 COUNT(CASE WHEN) 统计各渠道订单数没问题，但转化率不能这么算

如果你只写 COUNT(CASE WHEN channel = 'wechat' THEN 1 END) 统计微信渠道订单数，没问题；但若想算“微信渠道访问用户里有多少人下单”，就不能直接拿这个值除以总访问数——分母必须是「微信渠道的去重访问用户」，否则分子分母压根不属于同一集合。

常见错误现象：wechat_order_count / total_visit_uv 算出来的数字看起来像转化率，实则是“微信订单占全站访问比例”，业务上毫无意义。真正要的是：在微信来的用户中，有多少人完成了下单动作。

渠道字段必须参与用户去重逻辑，不能只在事件行上做条件计数
若原始表是行为日志（含 user_id, channel, event_type），需先按 user_id + channel 去重得到“各渠道独立用户池”
再对每个用户判断其是否在该渠道下发生过下单行为，而不是统计所有带 channel='wechat' 的订单行

正确做法：先取渠道用户池，再用 CASE 判定行为归属

核心是把“渠道”和“用户”绑定为最小分析单元，避免事件级误计。例如统计微信、抖音、自然搜索三个渠道的访问→下单转化：

SELECT 
  channel,
  COUNT(DISTINCT user_id) AS uv_visit,
  COUNT(DISTINCT CASE WHEN event_type = 'order_create' THEN user_id END) AS uv_order,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN event_type = 'order_create' THEN user_id END) / NULLIF(COUNT(DISTINCT user_id), 0), 2) AS cvr
FROM events 
WHERE channel IN ('wechat', 'douyin', 'organic')
  AND event_time >= '2026-09-01'
GROUP BY channel;

注意点：

COUNT(DISTINCT user_id) 是分母，代表该渠道当日访问过的独立用户数
分子的 CASE WHEN event_type = 'order_create' THEN user_id END 必须和分母共用同一 user_id 上下文，才能保证“下单用户 ⊆ 访问用户”
漏掉 NULLIF(..., 0) 会导致某渠道无访问时整条 SQL 报错（除零异常）
不要写成 COUNT(CASE WHEN channel='wechat' AND event_type='order_create' THEN 1 END)——这统计的是事件数，不是用户数

当渠道信息不在主日志表里，得先关联补全

如果 events 表没有 channel 字段，而渠道信息存在 visit_log 表中（含 user_id, channel, visit_time），就不能直接在 events 上加 WHERE channel = ... 过滤。

此时必须先通过 user_id 关联，把渠道打到行为事件上，再聚合：

SELECT 
  v.channel,
  COUNT(DISTINCT v.user_id) AS uv_visit,
  COUNT(DISTINCT CASE WHEN e.event_type = 'order_create' THEN e.user_id END) AS uv_order,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN e.event_type = 'order_create' THEN e.user_id END) / NULLIF(COUNT(DISTINCT v.user_id), 0), 2) AS cvr
FROM visit_log v
LEFT JOIN events e ON v.user_id = e.user_id 
  AND e.event_time BETWEEN v.visit_time AND v.visit_time + INTERVAL 7 DAY
WHERE v.visit_time >= '2026-09-01'
GROUP BY v.channel;

关键细节：