ClickHouse MergeTree 与物化视图预聚合实践

创建明细与聚合表：CREATE TABLE events ( ts DateTime, uid UInt64, category LowCardinality(String) ) ENGINE = MergeTree() PARTITION BY toYYYYMM(ts) ORDER BY (ts, uid); CREATE TABLE events_agg ( month Date, category LowCardinality(String), cnt AggregateFunction(count) ) ENGINE = AggregatingMergeTree() PARTITION BY month ORDER BY (month, category); 创建物化视图做预聚合：CREATE MATERIALIZED VIEW mv_events_agg TO events_agg AS SELECT toStartOfMonth(ts) AS month, category, countState() AS cnt FROM events GROUP BY month, category; 查询聚合结果：SELECT month, category, finalizeAggregation(cnt) AS total FROM events_agg ORDER BY month DESC, category LIMIT 10;