如何使用SQL对电商订单进行多维度的漏斗分析统计？-SQL-PHP中文网

电商漏斗分析必须按用户粒度建模时间序列，而非仅用case when+count distinct；需用row_number()、lag()标记步骤顺序与时间间隔，或clickhouse的windowfunnel()函数实现高效路径匹配，并以用户首步时间为锚点定义滑动窗口确保时间约束准确。

如何使用sql对电商订单进行多维度的漏斗分析统计？

SQL 做电商漏斗分析，不能只靠 CASE WHEN + COUNT DISTINCT 算 UV——那样只统计“有没有做过”，完全不管“谁先谁后”“间隔多久”，结果全是假转化。

真正能反映用户行为路径的漏斗，必须按用户粒度建模时间序列。下面直说关键怎么做。

用 `ROW_NUMBER()` 和 `LAG()` 标记有效步骤链路

漏斗不是静态计数，是动态路径匹配。比如「浏览 → 加购 → 下单 → 支付」，要求每个用户的行为必须按顺序发生，且相邻步骤不能跨太长时间（如加购必须在浏览后 30 分钟内）。

正确做法是先对每个 user_id 按 event_time 排序，再用窗口函数抓上下文：

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 user_events
WHERE event_type IN ('view', 'add_to_cart', 'place_order', 'pay');

之后就能写条件筛出真实链路：

WHERE event_type = 'add_to_cart' AND prev_type = 'view' AND event_time - prev_time
同理可扩展到三步：再套一层 LAG(..., 2) 或用 ROW_NUMBER() + 自连接
注意：首行 prev_type 是 NULL，无需额外 IS NOT NULL 过滤

避免用多层子查询硬 JOIN，改用 CTE + 条件聚合

传统写法把每一步都写成子查询再 LEFT JOIN，不仅难读，还容易因 JOIN 逻辑错位导致 UV 膨胀（一个用户多个事件被笛卡尔积）。

更稳的写法是用 CTE 预先打标，再用条件聚合一次性出各步 UV：

WITH step_flag AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS has_view,
    MAX(CASE WHEN event_type = 'add_to_cart' AND 
                  LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time) = 'view'
             THEN 1 ELSE 0 END) AS has_cart_after_view,
    MAX(CASE WHEN event_type = 'place_order' AND 
                  LAG(event_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) = 'add_to_cart'
             THEN 1 ELSE 0 END) AS has_order_after_cart,
    MAX(CASE WHEN event_type = 'pay' AND 
                  LAG(event_type, 1) OVER (PARTITION BY user_id ORDER BY event_time) = 'place_order'
             THEN 1 ELSE 0 END) AS has_pay_after_order
  FROM user_events
  GROUP BY user_id
)
SELECT
  COUNT(*) AS step1_uv,
  COUNT(CASE WHEN has_cart_after_view = 1 THEN 1 END) AS step2_uv,
  COUNT(CASE WHEN has_order_after_cart = 1 THEN 1 END) AS step3_uv,
  COUNT(CASE WHEN has_pay_after_order = 1 THEN 1 END) AS step4_uv
FROM step_flag;

这种写法不依赖事件时间戳全局排序，也不怕用户重复触发同一事件，UV 统计更干净。

BGM猫

BGM猫是一款AI音频处理工具，灵动音科技推出的AI智能生成BGM音乐。

下载

ClickHouse 用户直接用 `windowFunnel()`，别硬卷窗口函数

如果你的数据平台是 ClickHouse，windowFunnel() 是专为漏斗设计的向量化函数，性能碾压手写窗口逻辑：

SELECT
  count(*) AS users,
  windowFunnel(86400)(
    event_time,
    event_type = 'view',
    event_type = 'add_to_cart',
    event_type = 'place_order',
    event_type = 'pay'
  ) AS funnel_level
FROM user_events
GROUP BY funnel_level;

它底层用状态机逐行推进，不排序、不生成中间行，百万级日志也能秒出结果。而 PostgreSQL/MySQL 里硬套 LAG() + 多重 WHERE，数据量一过 10 万行，查询就明显变慢。

注意：windowFunnel() 的第一个参数是时间窗口（单位秒），86400 表示“所有步骤必须在 24 小时内完成”，超出即断链。