ClickHouse分组查询仅对首次出现的唯一组合计数实现方案

本问题是此前关于物化视图性能问题的延伸，核心需求为：统计指定维度组合的唯一值时，仅对全局首次出现的行计数，后续时间窗口重复出现的相同组合不再计数。

原有实现使用uniqState是按分组窗口独立去重，无法感知其他窗口是否已经出现过相同的(user_id, status, message_id)组合，因此会重复计数，可通过以下两种方案实现需求：

方案1：查询时实时计算（无需修改现有物化视图） #

适合user_id维度下message_id量级不大、查询频率较低的场景，无需调整现有表结构：

小时粒度查询代码 #

WITH
    -- 先计算每个(user_id, status, message_id)的首次出现小时
    first_occur AS (
        SELECT
            user_id,
            multiIf(event_type_id IN (1,2), 10, 20) AS status,
            message_id,
            min(toStartOfHour(event_datetime)) AS first_hour
        FROM events
        WHERE user_id = 1
        GROUP BY user_id, status, message_id
    )
SELECT
    h.event_day_hour,
    h.status,
    h.total_count,
    -- 仅统计首次出现在当前小时的message_id数量
    count(f.message_id) AS unique_count
FROM (
    -- 原有小时聚合逻辑，先算每个窗口的总计数
    SELECT
        event_day_hour,
        multiIf(event_type_id IN (1,2), 10, 20) AS status,
        countMerge(count) AS total_count
    FROM events_hourly
    WHERE user_id = 1
    GROUP BY event_day_hour, status
) h
LEFT JOIN first_occur f ON h.status = f.status AND h.event_day_hour = f.first_hour
GROUP BY h.event_day_hour, h.status, h.total_count
ORDER BY h.event_day_hour, h.status;

天粒度查询代码 #

WITH
    -- 先计算每个(user_id, status, message_id)的首次出现天
    first_occur AS (
        SELECT
            user_id,
            multiIf(event_type_id IN (1,2), 10, 20) AS status,
            message_id,
            min(toYYYYMMDD(event_datetime)) AS first_day
        FROM events
        WHERE user_id = 1
        GROUP BY user_id, status, message_id
    )
SELECT
    h.day,
    h.status,
    h.total_count,
    count(f.message_id) AS unique_count
FROM (
    SELECT
        toYYYYMMDD(event_day_hour) AS day,
        multiIf(event_type_id IN (1,2), 10, 20) AS status,
        countMerge(count) AS total_count
    FROM events_hourly
    WHERE user_id = 1
    GROUP BY day, status
) h
LEFT JOIN first_occur f ON h.status = f.status AND h.day = f.first_day
GROUP BY h.day, h.status, h.total_count
ORDER BY h.day, h.status;

方案2：新增预聚合物化视图（高性能方案） #

适合高频率查询场景，通过额外物化视图预存每个组合的首次出现时间，查询时无需扫描源表：

第一步：创建首次出现记录物化视图 #

CREATE MATERIALIZED VIEW events_first_occur
ENGINE = ReplacingMergeTree(event_datetime)
ORDER BY (user_id, status, message_id)
AS SELECT
    user_id,
    multiIf(event_type_id IN (1,2), 10, 20) AS status,
    message_id,
    min(event_datetime) AS event_datetime
FROM events
GROUP BY user_id, status, message_id;

使用ReplacingMergeTree自动保留每个组合最小的event_datetime，自动去重重复插入的相同组合

第二步：小时粒度查询代码 #

WITH
    first_occur AS (
        SELECT
            status,
            message_id,
            toStartOfHour(event_datetime) AS first_hour
        FROM events_first_occur
        WHERE user_id = 1
        GROUP BY status, message_id, first_hour
    )
SELECT
    h.event_day_hour,
    h.status,
    h.total_count,
    count(f.message_id) AS unique_count
FROM (
    SELECT
        event_day_hour,
        multiIf(event_type_id IN (1,2), 10, 20) AS status,
        countMerge(count) AS total_count
    FROM events_hourly
    WHERE user_id = 1
    GROUP BY event_day_hour, status
) h
LEFT JOIN first_occur f ON h.status = f.status AND h.event_day_hour = f.first_hour
GROUP BY h.event_day_hour, h.status, h.total_count
ORDER BY h.event_day_hour, h.status;

第三步：天粒度查询代码 #

WITH
    first_occur AS (
        SELECT
            status,
            message_id,
            toYYYYMMDD(event_datetime) AS first_day
        FROM events_first_occur
        WHERE user_id = 1
        GROUP BY status, message_id, first_day
    )
SELECT
    h.day,
    h.status,
    h.total_count,
    count(f.message_id) AS unique_count
FROM (
    SELECT
        toYYYYMMDD(event_day_hour) AS day,
        multiIf(event_type_id IN (1,2), 10, 20) AS status,
        countMerge(count) AS total_count
    FROM events_hourly
    WHERE user_id = 1
    GROUP BY day, status
) h
LEFT JOIN first_occur f ON h.status = f.status AND h.day = f.first_day
GROUP BY h.day, h.status, h.total_count
ORDER BY h.day, h.status;

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