如何在ClickHouse物化视图中预计算聚合最终值而非查询时计算

你当前的物化视图定义存在冗余语法，末尾的FROM test_sessions GROUP BY session_id;为无效内容，需要先删除。

要实现数据摄入/预计算阶段完成聚合计算、避免查询时执行uniqMerge的需求，核心是将去重聚合的最终结果直接固化到存储层，不再存储AggregateFunction类型的中间聚合状态，可选两种落地方案：

方案一：近实时聚合，支持灵活查询 #

该方案保留按event_name聚合的结构，适合需要灵活查询不同事件指标的场景。

步骤1：创建聚合状态基础表（增量自动更新） #

该层接收基表的增量数据，自动维护聚合状态，后台会定期合并同维度的状态：

-- 存储聚合状态的基础表
CREATE TABLE IF NOT EXISTS inventory_state
(
    `post_id` String,
    `event_name` LowCardinality(String),
    `unique_users_state` AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree
ORDER BY (event_name, post_id);

-- 绑定增量写入的物化视图
CREATE MATERIALIZED VIEW IF NOT EXISTS inventory_state_mv
TO inventory_state
POPULATE AS
SELECT
    post_id,
    event_name,
    uniqState(user_id) unique_users_state
FROM active_events
GROUP BY post_id, event_name;

步骤2：创建最终结果存储表 #

CREATE TABLE IF NOT EXISTS inventory_final
(
    `post_id` String,
    `event_name` LowCardinality(String),
    `unique_users` UInt64,
    `version` DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (event_name, post_id);

步骤3：定时刷新最终结果 #

根据你能接受的延迟（比如5分钟/15分钟），定时执行以下语句完成最终值预计算：

TRUNCATE TABLE inventory_final;
INSERT INTO inventory_final
SELECT 
    post_id,
    event_name,
    uniqMerge(unique_users_state) as unique_users
FROM inventory_state
GROUP BY post_id, event_name;

业务查询语句（无需任何聚合计算） #

SELECT post_id, sumIf(unique_users, event_name = 'click') / sumIf(unique_users, event_name = 'impression') as ctr
FROM inventory_final
WHERE event_name IN ('click', 'impression')
GROUP BY post_id
HAVING ctr > 0.1
ORDER BY ctr DESC

方案二：宽表预计算CTR（查询性能最优） #

如果你的业务场景仅需要查询帖子CTR，直接预计算宽表可以做到查询时无任何计算开销，性能最高：

预计算宽表定义 #

CREATE TABLE IF NOT EXISTS post_ctr_final
(
    `post_id` String,
    `click_uv` UInt64,
    `impression_uv` UInt64,
    `ctr` Float64,
    `version` DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(version)
ORDER BY post_id;

定时刷新语句 #

TRUNCATE TABLE post_ctr_final;
INSERT INTO post_ctr_final
SELECT
    post_id,
    sumIf(uv, event_name = 'click') as click_uv,
    sumIf(uv, event_name = 'impression') as impression_uv,
    click_uv / impression_uv as ctr
FROM (
    SELECT 
        post_id,
        event_name,
        uniqMerge(unique_users_state) as uv
    FROM inventory_state
    WHERE event_name IN ('click', 'impression')
    GROUP BY post_id, event_name
)
GROUP BY post_id
HAVING ctr > 0;

业务查询语句（无任何聚合计算，毫秒级返回） #

SELECT post_id, ctr
FROM post_ctr_final
WHERE ctr > 0.1
ORDER BY ctr DESC

优化建议 #

如果你的数据规模较大，可以给所有表增加时间分区，每次刷新仅更新最近的分区，大幅降低预计算的开销。

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