ClickHouse日期窗口函数应用：填充无UTM标签的有机会话渠道

需求背景 #

现有带UTM标签的Web会话数据，流量渠道包含cpc、smm、push三类：

• 部分会话带有明确渠道标签
• 部分有机来源会话无UTM标签（utm_channel为NULL）
  需要将无标签会话替换为符合规则的历史渠道标签。

核心规则 #

• 规则1：push渠道仅作用于其自身所在的会话，不覆盖其他无标签会话
• 规则2：cpc、smm这类非空渠道，需覆盖当前会话及次日所有无标签会话
• 规则3：渠道覆盖有先后优先级——当日若先出现cpc渠道，后续出现smm渠道，则先以cpc覆盖对应范围，之后出现的smm会覆盖当前及次日的无标签会话（后出现的非push渠道优先覆盖同时间段内未被标记的会话）

假设表结构 #

假设会话表定义如下：

CREATE TABLE web_sessions (
    user_id String,
    session_time DateTime,
    utm_channel Nullable(String) -- 取值为'cpc'/'smm'/'push'或NULL
) ENGINE = MergeTree()
ORDER BY (user_id, session_time);

实现方案（ClickHouse 22.8.10.29） #

方案1：子查询匹配（适合中小数据量） #

WITH 
-- 预处理渠道事件，生成覆盖时间区间
channel_events AS (
    SELECT 
        user_id,
        session_time AS event_time,
        utm_channel,
        -- 非push渠道覆盖至次日23:59:59，push仅覆盖自身会话时间
        CASE 
            WHEN utm_channel != 'push' THEN toDateTime(toDate(session_time) + INTERVAL 1 DAY) - INTERVAL 1 SECOND
            ELSE session_time
        END AS coverage_end_time
    FROM web_sessions
    WHERE utm_channel IS NOT NULL
),
-- 为每个会话匹配符合条件的最新渠道标签
filled_sessions AS (
    SELECT 
        s.user_id,
        s.session_time,
        -- 优先取覆盖当前会话的最新渠道，无匹配则保留原标签
        coalesce(
            (SELECT utm_channel FROM channel_events ce 
             WHERE ce.user_id = s.user_id 
               AND ce.event_time <= s.session_time 
               AND ce.coverage_end_time >= s.session_time
             ORDER BY ce.event_time DESC LIMIT 1),
            s.utm_channel
        ) AS filled_utm_channel
    FROM web_sessions s
)
SELECT * FROM filled_sessions ORDER BY user_id, session_time;

方案2：ASOF JOIN（适合大数据量，性能更优） #

ClickHouse的ASOF JOIN适合按时间区间关联数据，性能优于子查询：

WITH 
channel_events AS (
    SELECT 
        user_id,
        session_time AS event_time,
        utm_channel,
        CASE 
            WHEN utm_channel != 'push' THEN toDateTime(toDate(session_time) + INTERVAL 1 DAY) - INTERVAL 1 SECOND
            ELSE session_time
        END AS coverage_end_time
    FROM web_sessions
    WHERE utm_channel IS NOT NULL
)
SELECT 
    s.user_id,
    s.session_time,
    coalesce(ce.utm_channel, s.utm_channel) AS filled_utm_channel
FROM web_sessions s
LEFT ASOF JOIN channel_events ce
ON s.user_id = ce.user_id
AND s.session_time >= ce.event_time
AND s.session_time <= ce.coverage_end_time
-- 按事件时间倒序，确保取最新的渠道标签
ORDER BY s.user_id, s.session_time, ce.event_time DESC;

逻辑说明 #

1. channel_events CTE筛选所有带标签的会话，为不同渠道生成对应的覆盖时间区间：
   • cpc/smm渠道覆盖从当前会话时间到次日23:59:59
   • push渠道仅覆盖自身会话时间
2. 通过关联查询，为每个无标签会话匹配用户名下最新的、覆盖时间包含当前会话的渠道标签，完全符合规则要求。

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