CREATE TABLE default.test_2_all
(
`id` Int64 DEFAULT 0 COMMENT '序号',
.....
name, phone, idcard,code
)
这里创建一张表里面有多个字段,其中有个id类型为Int64
下面建立一个物化视图,引擎使用aggregatingMergeTree,使用POPULATE隐式创建,这里select中聚合的方法要加-state后缀:
create materialized view mv_tbl_mv
(
`id` Int64,
`name` String,
`phone` String,
`idcard` String,
`code` String,
`cost` AggregateFunction(sum, Int64),
`max_dwell_time` AggregateFunction(max, Int64),
`min_dwell_time` AggregateFunction(min,Int64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY tuple() ORDER BY (id) POPULATE
AS SELECT
id, name, phone, idcard, code ,
sumState(id) AS c,
maxState(id) AS max_c,
minState(id) AS min_c
FROM test_2_all ta
GROUP BY id,name,phone,idcard,code
在视图表中看到其他三个字段的类型为AggregateFunction,也就是查询时需要使用聚合方法 加 -Merge:
select sumMerge(cost) from mv_tbl_mv mtm
Reference: