【无标题】clickhouse 物化视图 materialized view

-- 创建一个明细表
use test1;
drop table if exists orders;
create table orders
(
    uid UInt64,
    money UInt64,
    ctime Date,
    Sign Int8
)
engine = MergeTree()
order by uid;

insert into orders values(1, 100, toDate(now()), 1);
insert into orders values(1, 100, toDate(now()), 1);
insert into orders values(1, 100, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);
insert into orders values(2, 200, toDate(now()), 1);

select * from orders;

--创建物化视图(聚合同步)

drop view if exists orders_agg_view;
create materialized view orders_agg_view
engine = AggregatingMergeTree()
partition by toDate(ctime)
order by uid
populate
as select uid,
          ctime,
          sumState(money) as ms
from orders
group by uid, ctime;


desc orders_agg_view;

select uid, ctime, sumMerge(ms) from orders_agg_view group by uid, ctime;

--向明细表中插入数据,物化视图会同步数据 提前聚合

insert into orders values(1, 700, toDate(now()), 1);
--再次查询聚合数据
select uid, ctime, sumMerge(ms) from orders_agg_view group by uid, ctime;