如何统计ClickHouse中用户购买行为发生前的广告记录行数

方案1：子查询关联（直观易懂） #

直接针对每个购买事件，查询同一用户在该购买时间之前的广告总数：

SELECT
    device_id,
    event_ts AS purchase_time,
    (
        SELECT COUNT(*)
        FROM event_table ad_events
        WHERE ad_events.device_id = main.device_id
          AND ad_events.event_name = 'ad'
          AND ad_events.event_ts < main.event_ts
    ) AS ads_before_purchase
FROM event_table main
WHERE main.event_name = 'purchase'
ORDER BY device_id, purchase_time;

• 逻辑：主查询筛选所有购买事件，子查询为每个购买事件匹配同用户、更早的广告并计数
• 适用场景：数据量不大时，写法简单易维护

方案2：窗口函数累计（高效批量计算） #

通过窗口函数提前累计每个用户的广告数量，再筛选出购买事件对应的累计值：

WITH user_event_sequence AS (
    SELECT
        device_id,
        event_name,
        event_ts,
        -- 按用户分组、时间排序，累计到当前事件为止的广告数量
        SUM(CASE WHEN event_name = 'ad' THEN 1 ELSE 0 END) OVER (
            PARTITION BY device_id
            ORDER BY event_ts
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_ads
    FROM event_table
)
SELECT
    device_id,
    event_ts AS purchase_time,
    cumulative_ads AS ads_before_purchase
FROM user_event_sequence
WHERE event_name = 'purchase'
ORDER BY device_id, purchase_time;

• 逻辑：先给所有事件按用户和时间排序，用窗口函数实时累计广告数；购买事件对应的累计值就是该次购买前的广告总数（当前购买事件不是广告，所以累计值不含当前行）
• 适用场景：数据量大时，批量计算性能更优

方案3：LATERAL JOIN关联（灵活扩展） #

利用ClickHouse的LATERAL JOIN特性，为每个购买事件关联对应的前置广告：

SELECT
    main.device_id,
    main.event_ts AS purchase_time,
    COUNT(ad_events.event_name) AS ads_before_purchase
FROM event_table main
LEFT JOIN LATERAL (
    SELECT *
    FROM event_table
    WHERE device_id = main.device_id
      AND event_name = 'ad'
      AND event_ts < main.event_ts
) ad_events ON 1=1
WHERE main.event_name = 'purchase'
GROUP BY main.device_id, main.event_ts
ORDER BY device_id, purchase_time;

• 逻辑：主查询的每个购买事件，通过LATERAL JOIN拉取符合条件的前置广告，最后分组计数
• 适用场景：需要对前置广告做额外过滤或处理时，扩展性更强

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