留存率、漏斗转化——报表里最容易算错的两类指标怎么实现？

-- 次日留存的标准写法：新增集合 LEFT JOIN 回访集合
SELECT n.reg_date,
       COUNT(DISTINCT n.user_id)               AS new_cnt,   -- 分母
       COUNT(DISTINCT a.user_id)               AS d1_cnt,    -- 分子
       ROUND(COUNT(DISTINCT a.user_id) * 1.0
             / COUNT(DISTINCT n.user_id), 4)   AS d1_rate
FROM dwd_user_new_di n            -- 当日新增用户表（分母集合）
LEFT JOIN dwd_user_active_di a    -- 活跃明细（分子集合）
  ON  a.user_id = n.user_id
  AND a.active_date = DATE_ADD(n.reg_date, 1)  -- D+1自然日口径
WHERE n.reg_date = '${bizdate}'
GROUP BY n.reg_date;
-- 要点：分母只来自新增表，分子必须从分母集合JOIN出去
-- 两个集合各自COUNT再相除，是留存率>100%事故的根源

-- Doris/Hologres的Bitmap留存（以Doris为例）
CREATE TABLE dws_user_bitmap_1d (
  stat_date DATE,
  country   VARCHAR(32),
  user_type VARCHAR(16),          -- new / active
  user_set  BITMAP BITMAP_UNION   -- 用户ID集合
) AGGREGATE KEY (stat_date, country, user_type)
DISTRIBUTED BY HASH (country);
 
-- 任意维度的次留 = 新增Bitmap ∩ 次日活跃Bitmap
SELECT BITMAP_COUNT(BITMAP_AND(n.user_set, a.user_set))
       * 1.0 / BITMAP_COUNT(n.user_set) AS d1_rate
FROM (SELECT user_set FROM dws_user_bitmap_1d
      WHERE stat_date='2025-03-10' AND user_type='new') n,
     (SELECT user_set FROM dws_user_bitmap_1d
      WHERE stat_date='2025-03-11' AND user_type='active') a;
-- 我在元宇宙社交App用这套方案支撑运营自助查任意国家的留存

不适用：Bitmap要求用户ID是整型（字符串ID要先做全局字典映射），且只能回答“多少人”，回答不了“是哪些人”——要捞名单还得回明细。

漏斗：三个口径参数决定一切

以“浏览→加购→支付”为例，先钉三个参数：窗口（浏览后24小时内）、有序（必须先浏览再加购再支付，跳步不算）、去重（按用户算转化率）。然后用窗口函数实现有序匹配：

-- 有序漏斗的SQL骨架：每一步都要求发生在前一步之后、窗口之内
WITH step1 AS (  -- 第一步：取每个用户最早的浏览时间
  SELECT user_id, MIN(event_time) AS t1
  FROM dwd_event_di WHERE dt='${bizdate}' AND event='view'
  GROUP BY user_id),
step2 AS (       -- 第二步：浏览之后24h内的最早加购
  SELECT e.user_id, MIN(e.event_time) AS t2
  FROM dwd_event_di e JOIN step1 s ON e.user_id = s.user_id
  WHERE e.event='add_cart'
    AND e.event_time BETWEEN s.t1 AND s.t1 + INTERVAL '24' HOUR
  GROUP BY e.user_id),
step3 AS (       -- 第三步：加购之后、窗口截止前的最早支付
  SELECT e.user_id
  FROM dwd_event_di e JOIN step2 s ON e.user_id = s.user_id
  WHERE e.event='pay'
    AND e.event_time > s.t2
  GROUP BY e.user_id)
SELECT (SELECT COUNT(*) FROM step1) AS uv_1,
       (SELECT COUNT(*) FROM step2) AS uv_2,
       (SELECT COUNT(*) FROM step3) AS uv_3;
-- 每步的时间必须严格大于前一步：这是“有序”的实现关键

很多引擎有原生漏斗函数（Doris的window_funnel、ClickHouse的windowFunnel），一行搞定且性能好得多，生产里优先用；但面试官通常要求你手写SQL版本，证明你理解“有序+窗口”是怎么落到JOIN条件上的。

面试高频追问

追问一：留存率算出来超过100%，可能是什么原因？

本质都是分子集合越出了分母集合。常见四种：分子分母来自两张口径不同的表（分母用去重后的新增表、分子直接count活跃明细，活跃里混进了非当日新增的用户）；用户ID口径不一致（分母按设备、分子按账号，一设备多账号就超）；分母表本身少算（新增判定漏了某注册渠道）；重复JOIN导致分子翻倍（活跃明细一天多行，没去重就count）。排查方法就一条：把分子集合EXCEPT分母集合，捞出那些“凭空多出来的用户”看明细，原因立刻现形。

追问二：漏斗后一步的人数比前一步还多，怎么回事？

三个高频原因。一是没做有序校验：用户先支付后浏览（比如从推送直接拉起支付页），无序统计时支付步人数就可能超过浏览步。二是窗口不一致：第二步统计了全天，第一步只统计了半天。三是去重主体不一致：一步按用户去重、另一步按事件计数。所以我做漏斗报表有个铁律：漏斗的所有步骤必须出自同一个SQL、同一套参数，绝不允许各步骤分别取数再拼图表——分开算的漏斗迟早出现倒挂。

追问三：运营要看“任意自定义事件组成的漏斗”，怎么支撑？

这就从写SQL升级成做产品了。方案分三档：低配是参数化SQL模板，运营填事件名和窗口，数据组代跑；中配是BI+引擎漏斗函数，把事件表接进支持window_funnel的引擎，配置化生成查询；高配是自研或采购行为分析平台（神策类），事件模型标准化之后漏斗、留存、路径分析全部自助。我在元宇宙社交App的演进路径就是低配到中配：先用模板顶着，等需求量证明了价值，再立项把事件宽表接进Doris做自助漏斗。给面试官讲清楚“按需求量分档投入”的思路，比直接答“上平台”更显成熟。

实战踩坑：礼物漏斗虚高，差点误导运营策略

在元宇宙社交应用做虚拟礼物的送礼漏斗（进房→点开礼物面板→送礼）时踩过“无序”的坑。第一版没做时序校验，三步分别count用户数再相除，算出“面板→送礼”转化率高得离谱。运营据此判断礼物面板体验很好，准备把优化资源全投到进房环节。上线前我抽了几十个用户的明细做校验，发现大量用户是通过“快捷送礼”按钮直接送礼的，根本没打开过面板——他们被无序统计错误地计入了面板步骤的转化。修正为有序漏斗后，“面板→送礼”转化率降回正常水平，同时暴露出快捷送礼才是主路径这个真相，运营策略整个调转了方向。这次之后，我们给所有漏斗报表立了上线前抽样验证的规矩：随机抽30个转化用户，人工核对事件序列是否符合漏斗定义。

教训：漏斗不是三个数字相除，是用户行为序列的还原。上线前抽明细人工核对一遍事件顺序，半小时的成本，挡住的是一次战略级误判。

总结

留存记住“两个集合求交集”：分母分子同源，JOIN出去算，大数据量上Bitmap；漏斗记住“窗口+有序+去重”三参数：所有步骤一个SQL算完，优先用引擎的漏斗函数，上线前抽明细验证时序。这两类指标的事故几乎全是口径事故而不是SQL事故——面试时先把口径参数摆出来再动笔写SQL，这个动作本身就是高分信号。

下一篇进入实时战场：实时大屏和离线日报数对不上，业务方该信哪个——实时报表怎么做才靠谱？

你们的次留是自然日口径还是滚动24小时口径？评论区聊聊～

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

数据报表 面试通关手册

第11-15篇：技术实现（下）与治理

Wood聊大数据

9年大数据老兵，聊技术、聊职场、聊成长