如何利用SQL窗口函数进行漏斗模型分析_时序数据处理技巧-SQL-PHP中文网

漏斗分析必须按时间顺序建模，纯case when+sum无法判断步骤先后与时间窗口；需用row_number()按user_id和event_time排序打序号，并结合lag()计算时间差以验证是否在指定窗口内完成。

如何利用sql窗口函数进行漏斗模型分析_时序数据处理技巧

漏斗分析必须处理时间顺序，否则 `CASE WHEN` 会算错

纯 CASE WHEN + SUM 的写法只统计“有没有发生”，完全忽略“谁先谁后”和“间隔多久”。比如用户先支付再浏览，SUM(CASE WHEN step = 1 THEN 1 END) 和 SUM(CASE WHEN step = 4 THEN 1 END) 都会加 1，但实际漏斗根本没走通。

真实漏斗要求：第二步必须发生在第一步之后、且在指定时间窗口内。这只能靠窗口函数的 ORDER BY timestamp + PARTITION BY user_id 实现序列建模。

必须用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) 给每个用户的事件打序号，这是后续判断顺序的基础
不能直接对原始日志表做漏斗，要先按 user_id 和 event_time 排序，否则 LAG() 或自连接匹配会错位
如果业务要求“10 分钟内完成下一步”，就得用 LAG(event_time, 1) OVER (...) AS prev_time，再加 WHERE event_time - prev_time

`LAG()` 和 `LEAD()` 是构建步骤依赖最轻量的方式

比起写多层子查询或自连接，LAG() 能在单次扫描中拿到上一行（比如上一个事件）的时间和类型，适合判断“是否从浏览进入加购”这类相邻步骤关系。

示例：标记某用户是否完成「浏览→加购」连续动作

SELECT user_id,
       event_type,
       event_time,
       LAG(event_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_type,
       LAG(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
FROM events
WHERE event_type IN ('view', 'cart');

结果中每行都带前一次事件类型和时间，可直接 WHERE event_type = 'cart' AND prev_type = 'view' 筛出有效转化
注意 LAG() 默认返回 NULL，首行无前序事件，无需额外过滤
若漏斗含 3 步以上（如 view → cart → buy），需嵌套两次 LAG() 或改用 ROW_NUMBER() + 自连接

ClickHouse 的 `windowFunnel()` 不是语法糖，它绕过了传统窗口函数的性能瓶颈

PostgreSQL 或 MySQL 里用 LAG() + 多重条件只能跑小数据量。一旦用户行为超 10 万行，OVER (PARTITION BY user_id ORDER BY time) 的排序开销会指数上升。而 windowFunnel() 是 ClickHouse 内置的向量化漏斗引擎，底层不做全排序，而是用状态机逐行推进匹配。

典型调用：

SELECT
  windowFunnel(86400)(event_time, event_type = 'view', event_type = 'cart', event_type = 'buy') AS level
FROM events
GROUP BY user_id;

86400 是窗口秒数（1 天），不是固定值，得根据业务节奏设（如秒杀场景可能用 300）
返回值 level 是整数：0=没触发第一步，1=只完成 view，2=view+cart，3=全路径
该函数不支持非等值条件（如 event_type LIKE '%pay%'），必须用布尔表达式
如果事件表没建 ORDER BY (user_id, event_time) 排序键，windowFunnel() 可能漏匹配——它依赖物理存储顺序

别在 Athena / Presto 上硬套 `LAG()` 做多步漏斗

Athena 底层是 Presto，LAG() 支持有限：不支持 ROWS BETWEEN 的精确帧控制，且大分区（如千万级 user_id）下 PARTITION BY 容易 OOM。更麻烦的是，Presto 的 LAG() 在 shuffle 阶段不保证严格时序，尤其当数据源是 Parquet 分区表且未按时间排序时。