基于Clickhouse的Analytics表漏斗分析查询修正与优化

问题分析 #

你的核心问题在于原方案无法跟踪同一用户的连续访问路径，且JOIN查询因逻辑缺陷（未关联用户、无时间顺序）导致数据爆炸触发内存溢出，同时存在语法错误。

关键前提说明 #

漏斗转化统计必须依赖用户唯一标识（如uid列），否则无法区分不同用户的跳转行为，你的表中若缺少该列，必须先添加才能准确统计。以下方案均假设表中包含uid列。

最优方案：使用数组+窗口函数（Clickhouse原生高效实现） #

Clickhouse对序列类场景支持极佳，通过数组函数可以避免JOIN带来的笛卡尔积问题，性能远优于关联查询：

WITH 
  -- 定义目标漏斗路径
  ['pageA', 'pageB', 'pageC'] AS funnel_path
SELECT
  pid,
  -- 各步骤转化用户数
  countIf(has(page_sequence, funnel_path[1])) AS step1_visitors, -- 访问A的用户
  countIf(has_subsequence(page_sequence, funnel_path[:2])) AS step2_converted, -- 完成A→B的用户
  countIf(has_subsequence(page_sequence, funnel_path)) AS step3_converted -- 完成A→B→C的用户
FROM (
  SELECT
    pid,
    uid,
    -- 按时间排序用户的访问页面，生成有序页面序列
    arrayMap(x -> x.2, arraySort((x) -> x.1, arrayMap((pg, created) -> (created, pg), pg, created))) AS page_sequence
  FROM analytics
  WHERE
    pid = 'example.com'
    AND created BETWEEN '2023-05-13' AND '2023-07-13'
    -- 只保留目标路径相关页面，减少数据量
    AND pg IN ('pageA', 'pageB', 'pageC')
  GROUP BY pid, uid
)
GROUP BY pid;

逻辑说明：

1. 内层查询按pid+uid分组，将用户的访问页面与时间绑定后排序，生成按时间顺序排列的用户页面访问序列。
2. 外层使用has_subsequence函数（Clickhouse 21.8+支持）检查序列中是否包含目标路径的连续子序列，直接统计各步骤的转化用户数。

修复版JOIN方案（不推荐，仅作参考） #

如果必须使用JOIN，需强制关联用户和时间顺序，避免无效关联：

SELECT
  COUNT(DISTINCT t0.uid) AS step1_visitors,
  COUNT(DISTINCT t1.uid) AS step2_converted,
  COUNT(DISTINCT t2.uid) AS step3_converted
FROM analytics t0
LEFT JOIN analytics t1 
  ON t0.pid = t1.pid 
  AND t0.uid = t1.uid 
  AND t1.prev = t0.pg 
  AND t1.created > t0.created -- 保证B在A之后访问
  AND t1.pg = 'pageB'
LEFT JOIN analytics t2 
  ON t1.pid = t2.pid 
  AND t1.uid = t2.uid 
  AND t2.prev = t1.pg 
  AND t2.created > t1.created -- 保证C在B之后访问
  AND t2.pg = 'pageC'
WHERE
  t0.pid = 'example.com'
  AND t0.created BETWEEN '2023-05-13' AND '2023-07-13'
  AND t0.pg = 'pageA';

修复点：

1. 添加uid关联，确保是同一用户的跳转行为。
2. 增加created时间条件，保证访问顺序符合漏斗路径。
3. 使用COUNT(DISTINCT uid)统计用户数，避免重复计数。
4. 改用LEFT JOIN，可同时统计各步骤的转化数据。

原JOIN查询的错误原因 #

1. 语法错误：count(t0)写法不合法，Clickhouse中需使用count(t0.*)或COUNT(DISTINCT t0.uid)。
2. 逻辑错误：未关联用户ID和时间顺序，导致不同用户的记录被错误关联，产生海量笛卡尔积，触发内存溢出。
3. 缺少用户标识：无uid列时无法区分用户，根本无法准确统计连续路径。

性能优化建议 #

1. 提前过滤数据：WHERE子句只保留目标路径页面和时间范围，减少处理数据量。
2. 利用分区/索引：表按pid+created分区，或对uid、pg列建索引，提升查询速度。
3. 优先使用数组函数：Clickhouse的数组和窗口函数在序列场景下性能远优于JOIN，避免内存溢出风险。

内容的提问来源于stack exchange，提问作者Andrew