ClickHouse字段分组聚合、按照任意时间段粒度查询SQL
需求
- 基础数据入库,需要根据字段做一些聚合
- 现有路口过车数据,有各种车型,需要统计总流量和各个车型流量,要一起返回
- 现有过车数据,需要按照时间不同粒度,统计车流量,实现不同时间粒度求和,5分钟、10分钟、半小时、一个月、一年都行
解决
使用if和sum
- 主要是使用
if
,符合条件为1,不符合为0,再使用sum
求和
select
count(1) as "总流量",
SUM(if(vehicle_type = 'PEDESTRIAN', 1, 0)) as "行人流量",
SUM(if(vehicle_type = 'NON_MOTORIZED', 1, 0)) as "非机动车流量",
SUM(if(vehicle_type = 'LIGHT_DUTY', 1, 0)) as "小型车流量",
SUM(if(vehicle_type = 'MEDIAN_SIZED', 1, 0)) as "中型车流量",
SUM(if(vehicle_type = 'OVERSIZE', 1, 0)) as "大车流量"
from
passing_vehicle
where
exist_time > 0
and time_stamp > toDateTime('2022-06-30 11:00:00')
and time_stamp < toDateTime('2022-06-30 15:00:00')
;
使用toStartOfInterval
select
toStartOfInterval(time_stamp , INTERVAL 30 minute) as half_hour,
count(1) as "总流量",
SUM(if(vehicle_type = 'PEDESTRIAN', 1, 0)) as "行人流量",
SUM(if(vehicle_type = 'NON_MOTORIZED', 1, 0)) as "非机动车流量",
SUM(if(vehicle_type = 'LIGHT_DUTY', 1, 0)) as "小型车流量",
SUM(if(vehicle_type = 'MEDIAN_SIZED', 1, 0)) as "中型车流量",
SUM(if(vehicle_type = 'OVERSIZE', 1, 0)) as "大车流量"
from
passing_vehicle
where
exist_time > 0
and time_stamp > toDateTime('2022-06-30 11:00:00')
and time_stamp < toDateTime('2022-06-30 15:00:00')
group by
half_hour,
order by
half_hour;