Crate数据库如何实现上午9点为日起始的自定义按天分组
CrateDB 实现9点起始的按天聚合方案
实现原理
常规date_trunc('day')以自然日0点为分组起点,要将统计起点改为当日9点,只需在分组前对时间戳做9小时的负偏移,再按自然日截断即可:偏移后,所有落在[当日9点, 次日9点)区间的数据会被归到同一个业务日分组,自然将凌晨0-9点的数据划入前一日统计区间,不会和当日9点后的数据混组。
修正后的聚合SQL
原语句存在两个适配问题:
- 存储的Unix时间戳为整数格式,需先用
FROM_UNIXTIME()转为CrateDB可处理的时间类型,不能直接传入date_trunc - 未做起点时间的偏移修正
可直接使用的完整语句:
SELECT date_trunc('day', FROM_UNIXTIME(v.timestamp) - INTERVAL '9 hours') AS stat_day, sensorid, sum(reading) AS total_reading FROM sensor1 v GROUP BY stat_day, sensorid ORDER BY stat_day;
示例数据验证效果
根据你给出的示例数据(实际列对应关系为sensorid | timestamp | reading,与数据值匹配),偏移后的分组结果完全符合预期:
- 时间戳为1616457600、1616461200的两条数据(reading为10、100),偏移9小时后落入2021-03-22的统计区间,聚合值为110
- 时间戳为1616493600、1616493601的两条数据(reading为1、10),偏移9小时后落入2021-03-23的统计区间,聚合值为11,不会混入前一日分组
特殊场景调整
- 如果你的Unix时间戳为UTC标准秒数(非北京时间对应秒数),需要先增加8小时的时区转换,再做9小时偏移:将时间计算部分改为
FROM_UNIXTIME(v.timestamp) + INTERVAL '8 hours' - INTERVAL '9 hours'即可 - 如果你不需要统计凌晨0-9点的数据,仅需聚合当日9点到当日23:59的数据,可以增加WHERE过滤条件直接排除凌晨数据:
WHERE FROM_UNIXTIME(v.timestamp) >= date_trunc('day', FROM_UNIXTIME(v.timestamp)) + INTERVAL '9 hours'
内容的提问来源于stack exchange,提问作者avancho marchos
相关产品推荐
相关产品推荐

