ClickHouse按时间日期补全数据

ClickHouse数据查询:https://cookcode.blog.csdn.net/article/details/108886972
ClickHouse的官网https://clickhouse.tech/docs/en/sql-reference/statements/select/

--补全SQL
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]

按天查询数据补全

每天都出现一条数据STEP 1,如果每3天出现一条数据可修改成 STEP 3

SELECT 
toDate(create_day) create_day, -- 日期字符串记得要转成时间
sum(value) AS sumValue -- 补0
FROM product
WHERE product_id = #{ID}
GROUP BY create_day
ORDER BY create_day ASC
WITH FILL -- 填充
FROM toDate('2022-08-20') TO toDate('2022-11-29') -- 补全没有数据的时间段
STEP 1 --每2秒填充一条数据

按秒查询数据补全

SELECT
	product_id,
	toStartOfInterval(create_time,INTERVAL 2 SECOND) AS create_time,
	sum(value) AS sumValue
FROM
	product
WHERE
	product_id = #{ID}
	AND create_time >= '2022-08-27 12:00:00'
	AND create_time <= '2022-09-29 12:00:00'
GROUP BY
	create_time
ORDER BY
	create_time ASC 
WITH FILL 
FROM toDateTime('2022-08-27 12:00:00') TO toDateTime('2022-09-29 12:00:00') 
STEP 2