ClickHouse物化视图：无需元组的UTM参数分组聚合方案咨询

要实现按会话聚合并保留首次UTM参数组，同时避免元组带来的过滤不便，你可以对每个UTM字段单独使用anySimpleState聚合函数，直接在物化视图中存储独立的UTM字段状态，而非打包成元组。

1. 源数据UTM字段提取（与原逻辑一致） #

从JSON负载中提取UTM字段并统一空值为字符串：

SELECT
    session_id,
    -- 其他业务字段...
    coalesce(JSONExtractString(sessionPayload, 'utm_source'), '') as utm_source,
    coalesce(JSONExtractString(sessionPayload, 'utm_medium'), '') as utm_medium,
    coalesce(JSONExtractString(sessionPayload, 'utm_campaign'), '') as utm_campaign,
    coalesce(JSONExtractString(sessionPayload, 'utm_content'), '') as utm_content,
    coalesce(JSONExtractString(sessionPayload, 'utm_term'), '') as utm_term
FROM your_source_table

2. 物化视图聚合逻辑（无元组版本） #

对每个UTM字段单独使用anySimpleState，确保每个字段保留会话内首次出现的值：

CREATE MATERIALIZED VIEW session_utm_agg
ENGINE = AggregatingMergeTree()
ORDER BY session_id
AS
SELECT
    session_id,
    -- 其他聚合字段...
    anySimpleState(utm_source) AS utm_source,
    anySimpleState(utm_medium) AS utm_medium,
    anySimpleState(utm_campaign) AS utm_campaign,
    anySimpleState(utm_content) AS utm_content,
    anySimpleState(utm_term) AS utm_term
FROM (
    -- 嵌入上述UTM提取逻辑
    SELECT
        session_id,
        -- 其他业务字段...
        coalesce(JSONExtractString(sessionPayload, 'utm_source'), '') as utm_source,
        coalesce(JSONExtractString(sessionPayload, 'utm_medium'), '') as utm_medium,
        coalesce(JSONExtractString(sessionPayload, 'utm_campaign'), '') as utm_campaign,
        coalesce(JSONExtractString(sessionPayload, 'utm_content'), '') as utm_content,
        coalesce(JSONExtractString(sessionPayload, 'utm_term'), '') as utm_term
    FROM your_source_table
)
GROUP BY session_id

3. 物化视图Schema #

每个UTM字段独立存储为SimpleAggregateFunction类型：

SCHEMA
  `session_id` String, -- 根据实际session_id类型调整
  -- 其他聚合字段...
  `utm_source` SimpleAggregateFunction(any, String),
  `utm_medium` SimpleAggregateFunction(any, String),
  `utm_campaign` SimpleAggregateFunction(any, String),
  `utm_content` SimpleAggregateFunction(any, String),
  `utm_term` SimpleAggregateFunction(any, String)

4. 查询与动态过滤 #

查询时用any函数还原字段值，可直接对单个UTM字段做过滤：

SELECT
    session_id,
    any(utm_source) AS utm_source,
    any(utm_medium) AS utm_medium,
    any(utm_campaign) AS utm_campaign,
    any(utm_content) AS utm_content,
    any(utm_term) AS utm_term
FROM session_utm_agg
WHERE utm_source = 'google' -- 直接基于单个字段过滤
GROUP BY session_id

可选优化：按时间严格取首次UTM组 #

如果会话事件的写入顺序与时间顺序不一致，可改用argMinSimpleState结合事件时间字段，确保取时间最早的UTM参数组：

-- 聚合语句调整
SELECT
    session_id,
    argMinSimpleState(utm_source, event_time) AS utm_source,
    argMinSimpleState(utm_medium, event_time) AS utm_medium,
    argMinSimpleState(utm_campaign, event_time) AS utm_campaign,
    argMinSimpleState(utm_content, event_time) AS utm_content,
    argMinSimpleState(utm_term, event_time) AS utm_term
FROM ...
GROUP BY session_id

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