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;

总结

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