在SQL中如何优雅地用窗口函数计算转化漏斗每步留存-SQL-PHP中文网

核心是先用ROW_NUMBER()标记用户行为序号，再用COUNT() OVER配合条件聚合分步统计用户数、路径数和首次行为时间，而非直接算比例。

在sql中如何优雅地用窗口函数计算转化漏斗每步留存

用 `ROW_NUMBER()` 和 `COUNT() OVER` 搭配实现分步计数

漏斗留存的核心不是“算比例”，而是先稳准狠地把每一步的用户数、路径数、首次行为时间等基础量抓出来。窗口函数里最常用的是 COUNT() OVER，但它不能直接按漏斗阶段聚合——得先用 ROW_NUMBER() 标记每个用户的首步、第二步……再用条件聚合统计。

典型做法是：对每个用户按事件时间排序，打上 step_seq 序号；再用 COUNT(*) FILTER (WHERE step_seq = 1)（PostgreSQL）或 SUM(CASE WHEN step_seq = 1 THEN 1 ELSE 0 END)（MySQL/SQL Server）做分步计数。

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) 必须加 PARTITION BY user_id，否则跨用户错乱
时间字段必须能严格排序，如果存在毫秒级重复，要加 event_id 作为二级排序，否则 ROW_NUMBER() 结果不稳定
MySQL 8.0+ 才支持窗口函数，5.7 及以前只能用自连接或变量模拟，性能差且易出错

用 `FIRST_VALUE()` 定义漏斗起点，避免“伪进入”

很多漏斗计算错误源于把任意一步当起点。比如用户先看详情页再搜关键词，结果把“搜关键词”误判为第一步。真正该用的是 FIRST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY event_time)，配合 MIN(event_time) 确定每个用户的首个事件类型和时间。

这步之后，再过滤出以目标入口（如 'search'）开始的用户子集，后续所有步骤才具备可比性。

别直接 WHERE event_type = 'search'，那会漏掉“搜完才注册”的用户——他们没在搜索事件里带注册字段
FIRST_VALUE() 默认不忽略 NULL，如果某些事件缺失 event_type，得加 IGNORE NULLS（PostgreSQL/Oracle 支持，MySQL 不支持）
起点时间建议存成 first_event_time 字段，后续所有步骤都用 event_time - first_event_time 控制时间窗，而非固定日期范围

用 `LAG()` 判断连续路径，过滤掉跳步用户

留存率失真的常见原因是把“搜索 → 支付”这种跳过下单环节的用户也算进第二步。要用 LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time) 拿到前序事件，再判断是否满足漏斗顺序。

例如：只保留 LAG(event_type) = 'search' AND event_type = 'list_view' 的记录，才能算作有效第二步。

LAG() 返回 NULL 是正常行为，必须用 IS NOT NULL 显式过滤，否则 NULL = 'search' 结果为 UNKNOWN，整行被丢弃
如果漏斗有 4 步，别写三层嵌套 LAG，改用 ARRAY_AGG(event_type) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)（PostgreSQL），然后用 @> 判断前缀数组
SQL Server 的 LAG() 不支持 IGNORE NULLS，若中间事件可能为空，需先用 COALESCE() 填充占位符

最后算留存时，分母必须是“上一步的去重用户数”，不是原始 UV

很多人写成 COUNT(DISTINCT user_id) / COUNT(DISTINCT FIRST_VALUE(user_id) OVER (...))，这是错的——分母应该是上一步中满足路径连续性的用户数，不是所有起点用户。

正确做法是：用 CTE 或子查询先生成每步的有效用户集合（含 user_id 和 step_num），再用自连接或窗口聚合算比率：

SELECT
  step_num,
  COUNT(DISTINCT user_id) AS step_uv,
  ROUND(
    100.0 * COUNT(DISTINCT user_id) / 
    LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY step_num), 2
  ) AS retention_rate
FROM funnel_steps
GROUP BY step_num
ORDER BY step_num;