漏斗分析怎么写？从埋点到转化率一条SQL搞定

SQL手册第 9 题约 11 分钟读完

本文目录

总结
面试原题
数据准备
写法一：简单COUNT（不考虑顺序）
写法二：严格有序漏斗
写法三：带时间窗口限制的漏斗
算转化率
实战经验：短剧出海的漏斗分析
面试追问：OLAP引擎的漏斗函数

总结

写法	特点	适用场景
------	------	----------
简单COUNT	不保证顺序	粗略估算，快速验证
严格有序	t2 > t1保证顺序	标准漏斗分析
时间窗口	加时间限制	单次转化分析
OLAP内置函数	windowFunnel等	生产环境推荐

面试答题思路：先说简单版，再主动优化到有序版，最后提时间窗口和OLAP函数。层层递进，展示你的思考深度。

明天 DAY 10——用SQL实现RFM用户分层模型。 这道综合题会把窗口函数、分组聚合、CASE WHEN、子查询全部用上，相当于一次小型SQL面试。

你们公司的漏斗分析是用SQL手写还是用现成的分析工具？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

漏斗分析是互联网数据分析最核心的方法论之一，面试经常考。但这道题的难度不在SQL本身，而在于你对"漏斗"这个概念的理解深度——什么算有效转化？步骤之间有没有时间限制？要不要严格有序？今天把这些都讲清楚。

面试原题

给定用户行为日志表，计算以下漏斗的各步转化率：

首页曝光 → 商品详情页 → 加入购物车 → 提交订单 → 支付成功

数据准备

-- 用户行为日志表
user_event(
  user_id     STRING,
  event_name  STRING,   -- 事件名
  event_time  TIMESTAMP,
  dt          STRING    -- 日期分区
)
 
-- 漏斗涉及的事件：
-- 'page_home'      首页曝光
-- 'page_detail'    商品详情页
-- 'add_cart'       加入购物车
-- 'submit_order'   提交订单
-- 'pay_success'    支付成功

写法一：简单COUNT（不考虑顺序）

最基础的漏斗，只看每一步有多少人，不管步骤之间的先后顺序：

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'page_home'    THEN user_id END) AS step1_uv,
  COUNT(DISTINCT CASE WHEN event_name = 'page_detail'  THEN user_id END) AS step2_uv,
  COUNT(DISTINCT CASE WHEN event_name = 'add_cart'     THEN user_id END) AS step3_uv,
  COUNT(DISTINCT CASE WHEN event_name = 'submit_order' THEN user_id END) AS step4_uv,
  COUNT(DISTINCT CASE WHEN event_name = 'pay_success'  THEN user_id END) AS step5_uv
FROM user_event
WHERE dt = '2025-01-01'
  AND event_name IN ('page_home','page_detail','add_cart','submit_order','pay_success')

这个写法面试能拿及格分，但有个明显问题：一个用户可能直接从首页跳到支付（通过缓存订单），他会同时出现在step1和step5里，但中间步骤可能没走过。这不是一个"真实"的漏斗。

写法二：严格有序漏斗

要求用户必须按顺序走完每一步，后一步的时间必须晚于前一步。

WITH funnel AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'page_home'    THEN event_time END) AS t1,
    MIN(CASE WHEN event_name = 'page_detail'  THEN event_time END) AS t2,
    MIN(CASE WHEN event_name = 'add_cart'     THEN event_time END) AS t3,
    MIN(CASE WHEN event_name = 'submit_order' THEN event_time END) AS t4,
    MIN(CASE WHEN event_name = 'pay_success'  THEN event_time END) AS t5
  FROM user_event
  WHERE dt = '2025-01-01'
    AND event_name IN ('page_home','page_detail','add_cart','submit_order','pay_success')
  GROUP BY user_id
)
SELECT
  COUNT(DISTINCT CASE WHEN t1 IS NOT NULL
    THEN user_id END) AS step1_uv,
  COUNT(DISTINCT CASE WHEN t1 IS NOT NULL AND t2 > t1
    THEN user_id END) AS step2_uv,
  COUNT(DISTINCT CASE WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2
    THEN user_id END) AS step3_uv,
  COUNT(DISTINCT CASE WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2 AND t4 > t3
    THEN user_id END) AS step4_uv,
  COUNT(DISTINCT CASE WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2 AND t4 > t3 AND t5 > t4
    THEN user_id END) AS step5_uv
FROM funnel

核心逻辑： 先把每个用户每个事件的最早发生时间拿出来，然后用 t2 > t1 AND t3 > t2 ... 保证步骤是有序的。

这个写法就合理多了。用户必须先看了首页，然后才去看详情页，然后才加购...每一步都要在前一步之后。

写法三：带时间窗口限制的漏斗

实际业务中，漏斗通常有时间窗口限制——用户必须在N分钟内完成整个转化流程，否则不算有效转化。

比如：用户从首页曝光到支付成功，必须在30分钟内完成。

WITH funnel AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'page_home'    THEN event_time END) AS t1,
    MIN(CASE WHEN event_name = 'page_detail'  THEN event_time END) AS t2,
    MIN(CASE WHEN event_name = 'add_cart'     THEN event_time END) AS t3,
    MIN(CASE WHEN event_name = 'submit_order' THEN event_time END) AS t4,
    MIN(CASE WHEN event_name = 'pay_success'  THEN event_time END) AS t5
  FROM user_event
  WHERE dt = '2025-01-01'
    AND event_name IN ('page_home','page_detail','add_cart','submit_order','pay_success')
  GROUP BY user_id
)
SELECT
  COUNT(DISTINCT CASE
    WHEN t1 IS NOT NULL
    THEN user_id END) AS step1_uv,
  COUNT(DISTINCT CASE
    WHEN t1 IS NOT NULL AND t2 > t1
      AND TIMESTAMPDIFF(MINUTE, t1, t2) <= 30
    THEN user_id END) AS step2_uv,
  COUNT(DISTINCT CASE
    WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2
      AND TIMESTAMPDIFF(MINUTE, t1, t3) <= 30
    THEN user_id END) AS step3_uv,
  COUNT(DISTINCT CASE
    WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2 AND t4 > t3
      AND TIMESTAMPDIFF(MINUTE, t1, t4) <= 30
    THEN user_id END) AS step4_uv,
  COUNT(DISTINCT CASE
    WHEN t1 IS NOT NULL AND t2 > t1 AND t3 > t2 AND t4 > t3 AND t5 > t4
      AND TIMESTAMPDIFF(MINUTE, t1, t5) <= 30
    THEN user_id END) AS step5_uv
FROM funnel

注意时间限制是从第一步到当前步的总时间，不是每两步之间的时间。当然口径可以根据业务需求调整。

算转化率

在上面任一写法的基础上，加上转化率计算：

WITH funnel_result AS (
  -- 上面的任一写法，得到step1_uv到step5_uv
)
SELECT
  step1_uv,
  step2_uv,
  step3_uv,
  step4_uv,
  step5_uv,
  -- 整体转化率（相对第一步）
  ROUND(step2_uv * 100.0 / NULLIF(step1_uv, 0), 2) AS overall_rate_2,
  ROUND(step5_uv * 100.0 / NULLIF(step1_uv, 0), 2) AS overall_rate_5,
  -- 逐步转化率（相对上一步）
  ROUND(step2_uv * 100.0 / NULLIF(step1_uv, 0), 2) AS step_rate_1to2,
  ROUND(step3_uv * 100.0 / NULLIF(step2_uv, 0), 2) AS step_rate_2to3,
  ROUND(step4_uv * 100.0 / NULLIF(step3_uv, 0), 2) AS step_rate_3to4,
  ROUND(step5_uv * 100.0 / NULLIF(step4_uv, 0), 2) AS step_rate_4to5
FROM funnel_result

整体转化率看的是从头到尾有多少人留下来，逐步转化率看的是哪个环节流失最严重。业务上两个都要看。

实战经验：短剧出海的漏斗分析

我现在做短剧出海，我们的核心漏斗是：

广告曝光 → 广告点击 → App下载 → 注册 → 观看第一集 → 付费解锁

这个漏斗跨越了多个数据源：广告平台数据（Facebook/TikTok）、应用商店数据、App内埋点数据。最大的挑战不是SQL写法，而是数据打通——怎么把广告平台的click_id和App内的user_id关联起来。

这就是广告归因要解决的问题，我做的归因体系本质上就是在给这个漏斗的每一步"穿针引线"。

另外一个实战经验：MIN(event_time)不一定是你想要的。

比如用户今天看了详情页但没买，明天又从首页重新进来买了。如果用MIN取最早时间，会把今天的详情页浏览和明天的下单关联起来——这算一个有效转化吗？

答案取决于业务定义。如果是"单次访问漏斗"，应该用Session来切分；如果是"日级别漏斗"，按天取MIN就行。面试的时候主动说出这个口径区别，面试官会觉得你有业务sense。

面试追问：OLAP引擎的漏斗函数

ClickHouse和Doris都有内置的漏斗函数，比手写SQL方便得多：

-- ClickHouse的windowFunnel
SELECT
  user_id,
  windowFunnel(1800)(  -- 1800秒窗口
    event_time,
    event_name = 'page_home',
    event_name = 'page_detail',
    event_name = 'add_cart',
    event_name = 'submit_order',
    event_name = 'pay_success'
  ) AS funnel_level
FROM user_event
WHERE dt = '2025-01-01'
GROUP BY user_id

windowFunnel直接返回用户在时间窗口内走到了漏斗的第几步，底层帮你处理了有序性和时间窗口，不用手写那一堆CASE WHEN。

面试的时候如果能提到这个，说明你不仅会手写SQL，还了解OLAP引擎的特性，视野更宽。

刷题进度保存在当前浏览器