clickhouse聚合

原指标数据表

CREATE TABLE metrics.samples (
`date` Date DEFAULT toDate(0),
`name` String,
`tags` Array(String),
`val` Float64,
`ts` DateTime,
`updated` DateTime DEFAULT now()
) ENGINE = MergeTree(date, (name, tags, ts), 8192)

创建表

CREATE TABLE metrics.samplesnew (
`date` Date,
name String,
tags Array(String),
ts DateTime,
updated DateTime,
avg Float64,
max Float64,
min Float64
)
ENGINE = MergeTree(date, (name, tags, ts), 8192)

查询数据

select name,tags,avg(val),max(val),min(val) from samples where val>=0 group by name,tags

查询分组聚合数据

select `date`,name,tags,avg(val) as avg,max(val) as max,min(val) as min, `date` as ts,`date` as updated from samples where val>=0 group by `date`,name,tags

删除表

DROP TABLE metrics.samplesnew;

删除表中数据
ALTER TABLE samplesnew delete where 1=1;

将统计查询结果导入创建的新表中

insert into samplesnew(`date`,name,tags,avg,max,min,ts,updated) select `date`,name,tags,avg(val) as avg,max(val) as max,min(val) as min, `date` as ts,`date` as updated from samples where val>=0 group by `date`,name,tags

clickhouse聚合

原文：https://www.cnblogs.com/yaoyu1983/p/12318028.html