---

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

keywords:

• ClickHouse
• 物化视图
• AggregatingMergeTree
• SUM
• 实时汇总

description: 使用物化视图将明细表实时汇总到聚合表，提供建表与视图示例，提升查询性能。

tags:

• AggregatingMergeTree
• ClickHouse
• SUM
• 实时汇总
• 性能优化
• 数据库
• 物化视图

categories:

• 文章资讯
• 编程技术

---

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;

总结

物化视图可在写入时完成聚合，显著降低读时开销，适合报表与排行榜场景。