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

# ClickHouse 物化视图与实时汇总实践 ## 明细与聚合表 ```sql 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; ``` ## 物化视图 ```sql CREATE MATERIALIZED VIEW mv_events_agg TO events_agg AS SELECT user_id, sum(amount) AS total_amount FROM events GROUP BY user_id; ``` ## 查询 ```sql SELECT user_id, total_amount FROM events_agg ORDER BY total_amount DESC LIMIT 10; ``` ## 总结 物化视图可在写入时完成聚合，显著降低读时开销，适合报表与排行榜场景。