ClickHouse Funnel SQL：顺序、窗口与身份

最小可运行测试

WITH events AS (
  SELECT * FROM VALUES(
    'distinct_id String, event String, time DateTime64(3)',
    ('u1','ProductView','2026-09-19 10:00:00.000'),
    ('u1','AddToCart', '2026-09-19 10:05:00.000'),
    ('u1','Purchase',  '2026-09-19 10:09:00.000'),
    ('u2','ProductView','2026-09-19 11:00:00.000'),
    ('u2','Purchase',  '2026-09-19 11:03:00.000'),
    ('u3','AddToCart', '2026-09-19 12:00:00.000'),
    ('u3','ProductView','2026-09-19 12:01:00.000')
  )
)
SELECT distinct_id,
  windowFunnel(3600)(
    toDateTime(time),
    event = 'ProductView',
    event = 'AddToCart',
    event = 'Purchase'
  ) AS level
FROM events
GROUP BY distinct_id
ORDER BY distinct_id;

预期：u1=3、u2=1、u3=1。购买不能跳过第二步，倒序事件也不会补成正序漏斗。

生产表查询

WITH per_user AS (
  SELECT
    distinct_id,
    windowFunnel(86400)(
      toDateTime(time),
      event = 'ProductView',
      event = 'AddToCart',
      event = 'Purchase'
    ) AS level
  FROM sensors.event
  WHERE time >= toDateTime('2026-09-01 00:00:00')
    AND time <  toDateTime('2026-10-01 00:00:00')
    AND event IN ('ProductView','AddToCart','Purchase')
    AND distinct_id != ''
  GROUP BY distinct_id
)
SELECT
  countIf(level >= 1) AS viewed,
  countIf(level >= 2) AS added,
  countIf(level >= 3) AS purchased,
  round(100 * added / nullIf(viewed, 0), 2) AS view_to_cart_pct,
  round(100 * purchased / nullIf(added, 0), 2) AS cart_to_purchase_pct
FROM per_user;

语义必须先定清楚

• 时间窗：86400 是从匹配到的第一步开始的 24 小时，不是自然日。
• 身份：示例按 distinct_id。若登录会改变它，需先构造统一 analysis_id；简单 coalesce(user_id, distinct_id) 不能自动合并历史匿名事件。
• 重复：同一步重复通常不会增加步骤，但可能改变可匹配路径。需要严格一次事件时，应先按业务 event id 去重。
• 同一时间：多个步骤时间戳完全相同会让顺序依赖模式和执行语义。需要严格递增时使用 windowFunnel(86400)('strict_increase')。
• 迟到事件：查询基于事件 time，迟到但仍在查询范围内的数据会重算历史漏斗；物化日报必须设置重算窗口。

按天输出漏斗

若产品定义是“以第一步发生日归因”，先按用户和日期分组。跨午夜但仍在 24 小时内的后续步骤会被日期过滤掉，所以这与滚动 24 小时漏斗不是同一个指标。

SELECT cohort_day,
  countIf(level >= 1) AS step_1,
  countIf(level >= 2) AS step_2,
  countIf(level >= 3) AS step_3
FROM (
  SELECT toDate(time) AS cohort_day, distinct_id,
    windowFunnel(86400)(toDateTime(time),
      event='ProductView', event='AddToCart', event='Purchase') AS level
  FROM sensors.event
  WHERE ds BETWEEN '2026-09-01' AND '2026-09-30'
    AND event IN ('ProductView','AddToCart','Purchase')
  GROUP BY cohort_day, distinct_id
)
GROUP BY cohort_day ORDER BY cohort_day;

相关阅读