"ClickHouse 物化视图与实时汇总实践"

ClickHouse 物化视图与实时汇总实践明细与聚合表CREATE TABLE events ( ts DateTime, user_id UInt64, amount Float64 ) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (ts, user_id); CREATE TABLE events_agg ( user_id UInt64, total_amount Float64 ) ENGINE = AggregatingMergeTree() ORDER BY user_id; 物化视图CREATE MATERIALIZED VIEW mv_events_agg TO events_agg AS SELECT user_id, sum(amount) AS total_amount FROM events GROUP BY user_id; 查询SELECT user_id, total_amount FROM events_agg ORDER BY total_amount DESC LIMIT 10; 总结物化视图可在写入时完成聚合，显著降低读时开销，适合报表与排行榜场景。