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; ```