使用clickhouse的ORDER BY WITH FILL方法插值补全缺失的值

需求

• 要聚合查询一些数据，按照时间15分钟聚合数据量。但是由于数据缺失，一些中间值没有，需要自己插值解决
• 其中一种实现方式是，在查询出来后，在服务端使用代码补全这些数据。但是觉得这样有点low，应该有更好的方法
• 在网上搜索了一番，发现WITH FILL符合我的需求，按照一些网上的文章试了下，效果不太好
• 于是去看了下clickhouse的官方文档，发现确实可以，只不过多字段使用时，要特别注意

WITH FILL介绍

• WITH FILL修饰符配合ORDER BY使用，就是为了按照指定顺序填值，有值跳过，无值插入
• 可以在ORDER BY expr之后用可选的FROM expr，TO expr和STEP expr参数来设置WITH FILL修饰符
• from to确定补值的范围，step是步进，没有则使用默认值
• 所有expr列的缺失值将被顺序填充，而其他列将被填充为默认值
• 使用以下语法填充多列，在ORDER BY部分的每个字段名称后添加带有可选参数的WITH FILL修饰符。

ORDER BY expr [WITH FILL] [FROM const_expr] [TO const_expr] [STEP const_numeric_expr], ... exprN [WITH FILL] [FROM expr] [TO expr] [STEP numeric_expr]

• WITH FILL可以针对单字段使用，也可以多字段使用，但是仅适用于具有数字（所有类型的浮点，小数，整数）或日期/日期时间类型的字段
• 当未定义 FROM const_expr 填充顺序时，则使用 ORDER BY 中的最小expr字段值
• 当定义了 STEP const_numeric_expr 时，不同类型（就3种）表示不一样。对于数字类型，const_numeric_expr 为具体数值；当作为日期类型，const_numeric_expr 为多少天；当作为日期时间（DateTime）类型时，const_numeric_expr 为多少秒。
• 如果省略了 STEP const_numeric_expr，则填充顺序使用1.0表示数字类型，1 day表示日期类型，1 second 表示日期时间类型。

实际应用SQL

单字段补全

• 此处是对表passing_vehicle 进行数据统计，把一天按照15分钟间隔，获取96条过车数据
• toStartOfInterval(time_stamp, INTERVAL 15 minute) as time_stamp2是对时间戳进行聚合，按照15分钟间隔
• GROUP by time_stamp2,approach是按照15分钟和进口聚合
• 一开始的SQL是 GROUP by time_stamp2,approach order by time_stamp2,approach，查询条件也没有and approach = 'SB'，结果插值补全有问题，多字段聚合排序，只补值一个字段，得到的不是自己的想要的结果

SELECT 
	toStartOfInterval(time_stamp, INTERVAL 15 minute) as time_stamp2 ,
	approach,lane_nbr, 
	count() as totalVolume
from passing_vehicle pv 
WHERE 
	intersection_number = 1687001
	and time_stamp > '2023-05-08 00:00:00'
	and time_stamp < '2023-05-09 00:00:00'
	and approach = 'SB'
	and status = 1
GROUP by time_stamp2
order by time_stamp2
WITH FILL 
FROM toDateTime('2023-05-08 00:00:00')  TO toDateTime('2023-05-09 00:00:00') 
STEP 15*60

• 现在这条SQL能正常运行和补值，它查询的是，某路口南进口（SB）的15分钟过车流量统计
• FROM toDateTime('2023-05-08 00:00:00') TO toDateTime('2023-05-09 00:00:00') 是时间范围，这个和我的查询时间是对应的，这个范围内补全。注意这个类型，一定要是时间日期，即使用toDateTime函数，将字符串转成日期时间类型
• STEP 15*60，是将插值补全，步进为15分钟，日期时间类型，默认是1s，15分钟乘以对应秒数即可
  注： 如果是要按照一天（或n天）去做时间聚合统计，就需要使用toDate函数，相应步进为n天

多字段补全

• 进阶，按照进口查询多次，多个字段补全

SELECT toStartOfInterval(time_stamp, INTERVAL 15 minute) as time_stamp2,
lane_nbr,
sum(status) as totalVolume
from passing_vehicle pv 
WHERE 
	intersection_number = 1687001
	and time_stamp >= '2023-05-08 00:00:00'
	and time_stamp <= '2023-05-08 01:59:59'
	and approach = 'NB'
GROUP by time_stamp2,lane_nbr 
order by time_stamp2 WITH FILL FROM toDateTime('2023-05-08 00:00:00') TO toDateTime('2023-05-08 01:59:59') STEP 15 * 60,
		-- 注意：这里的6，是传参进来的，因为是5车道，lane_nbr需要补全1-5（最大是5，范围要是6才行）
		lane_nbr WITH FILL FROM 1 TO 6 STEP 1

• 这条SQL查询的是，某路口北进口（NB）各个车道15分钟过车数量统计
• GROUP by time_stamp2,lane_nbr，聚合条件是15分钟，和车道编号
• time_stamp2 WITH FILL与上面一样，按照15分钟补全
• lane_nbr WITH FILL FROM 1 TO 6 STEP 1是按照车道编号补全，注意to的值要比最大值大1
• 这2个 WITH FILL一起使用，就会把时间和车道编号都补全，得到我们想要的结果
• 对于非数值/日期/日期时间类型的字段，如果是枚举类型字符串类型字段，也可以使用补全，将该字段的枚举值，存成连续新增的数值即可