Adtech场景ClickHouse按inventory_id合并多插入行的库表设计咨询

你当前选用的ClickHouse SummingMergeTree引擎完全可以满足你的需求，只需要调整表结构设计和查询逻辑即可实现同inventory_id下三类指标的自动汇总，无需更换其他数据库。以下是具体落地步骤：

方案1：优化现有SummingMergeTree使用逻辑 #

你当前的方案存在两个核心问题：

1. SummingMergeTree的合并是后台异步执行的，未触发合并前查询会返回分散的多行数据
2. impression、views的插入语句未携带country、city参数，导致对应行的维度字段为NULL，合并时维度值可能出现异常

调整方法： #

1. 插入impression、views数据时，补全对应inventory_id关联的country、city字段，保证同id的维度字段值统一
2. 如需实时查询汇总结果，查询时主动加聚合逻辑即可，性能无明显损耗：

SELECT 
    inventory_id,
    max(country) AS country,
    max(city) AS city,
    sum(inventory) AS inventory,
    sum(impression) AS impression,
    sum(views) AS views
FROM report.empty_summing
GROUP BY inventory_id;

3. 如需后台合并后直接查原表得到汇总结果，可手动执行合并命令（生产环境不建议频繁执行，会占用IO资源）：

OPTIMIZE TABLE report.empty_summing FINAL;

方案2：搭配物化视图实现自动聚合（更符合你的业务规划） #

你原本就计划使用物化视图生成业务表，该方案是生产环境的标准玩法，稳定性和性能最优：

步骤1：建原始明细表存储全量插入日志 #

CREATE TABLE report.ad_events_raw (
    times DateTime64,
    inventory_id String,
    city Nullable(String),
    country Nullable(String),
    inventory Int32 DEFAULT 0,
    impression Int32 DEFAULT 0,
    views Int32 DEFAULT 0
) ENGINE = MergeTree()
ORDER BY (times, inventory_id);

所有Inventory、impression、views的插入请求都直接写入该表，无需调整插入逻辑。

步骤2：建SummingMergeTree引擎的物化视图自动聚合数据 #

CREATE MATERIALIZED VIEW report.ad_stats_mv
ENGINE = SummingMergeTree()
PRIMARY KEY (inventory_id)
ORDER BY (inventory_id)
AS SELECT
    inventory_id,
    max(country) AS country, -- 自动取非空的维度值
    max(city) AS city,
    sum(inventory) AS inventory,
    sum(impression) AS impression,
    sum(views) AS views
FROM report.ad_events_raw
GROUP BY inventory_id;

步骤3：业务查询直接访问物化视图 #

无需额外加聚合逻辑，即可直接拿到你需要的单条汇总结果：

SELECT * FROM report.ad_stats_mv;

如果需要按时间、地域等多维度生成报表，只需要调整物化视图的主键和GROUP BY维度即可，扩展性极强。

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