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

使用时间函数查询

select date,name,tags,avg,max,min,ts,updated from samplesnew where toUnixTimestamp(ts)>1578561978 and toUnixTimestamp(ts)<1580680800 order by ts desc limit 10

查询按照ts排序:

SELECT * FROM
metrics.samples
where name = 'ecs_cpu_util'
order by ts desc
LIMIT 100

 

prometheus查询远端库

select avg,ts,tags from metrics.samplesnew where ts>=toDateTime(1577068260) and ts<=toDateTime(1580524260) and arrayExists(x -> x IN ('instance=pushgateway'), tags) = 1 and arrayExists(x -> x IN ('job=pushgateway'), tags) = 1 and name='go_memstats_frees_total' order by ts

时间戳转换

select toUnixTimestamp('2020-01-16 19:00:00'),toUnixTimestamp('2020-01-19 08:00:00');
select toDateTime(1578561978),toDateTime(1580680800);