09-29 03:07 阅读 285

关注

Clickhouse 实现分时统计

利用clickhouse中的toStartOf*()函数，将时间归属到相同点，然后利用分组统计即可。

toStartOf*()函数：

• toStartOfInterval()

• toStartOfHour()

• toStartOfFifteenMinutes()

• toStartOfFiveMinute()

• toStartOfDay()

• toStartOfMonth()

• toStartOfQuarter()

• toStartOfWeek()

主要讲一下toStartOfInterval(datetime, interval)函数,可以利用后面interval参数，任意指定时间间隔，如需要完成间隔2分钟的分时统计：

select toStartOfInterval(trade_time, INTERVAL 2  minute) as minute,count() as volumefrom table_allgroup by minute order by volume desc;

增加一个例子：
需求是统计某一天内的指标数量，按照15分钟分时进行汇总。
如果用传统数据库，则一般做法会把原始数据按照15分钟结存到另一张表(table_15)里，业务对table_15进行查询。如果需求明天改成20、30分钟，则改动需要改动结存逻辑，代价很大。
使用CK的分组高性能结合toStartOfInterval()函数，可以很方便将统计时间变成参数，实时查询。

原始表数据结构：

SQL：

select toStartOfInterval(trade_time, INTERVAL 15  minute ) as minute,count() as volumefrom table_allwhere trade_time >= '2020-11-30 00:00:00'and trade_time <= '2020-11-31 00:00:00'group by minuteorder by minute;

汇总结果：
minute字段值代表从该时刻开始的15分钟的时间窗，volume代表时间窗内数据的累加之和。改变时间窗，只需要改变INTERVAL参数即可。

汇总结果

性能参考：
ReplacingMergeTree引擎，单机
分区toYYYYMMDD(trade_time)
测试表总数据量：216836575（2.1亿）
耗时：500ms左右
满足业务时效要求

作者：EdgeE
链接：https://www.jianshu.com/p/7a62f578c608