跳到主要内容
跳到主要内容

基本时间序列操作

ClickHouse 提供了多种方法来处理时间序列数据,使您能够在不同时间段内聚合、分组和分析数据点。 本节涵盖了在处理基于时间的数据时常用的基本操作。

常见的操作包括按时间间隔对数据进行分组、处理时间序列数据中的间隙,以及计算时间段间的变化。 这些操作可以使用标准 SQL 语法结合 ClickHouse 的内置时间函数来执行。

我们将使用 Wikistat(Wikipedia 页面浏览数据)数据集来探索 ClickHouse 的时间序列查询能力:

让我们用 10 亿条记录填充这个表:

按时间桶聚合

最常见的需求是基于时间段聚合数据,例如获取每天的总点击量:

我们在这里使用了 toDate() 函数,它将指定的时间转换为日期类型。或者,我们可以按小时分组并筛选特定日期:

这里使用的 toStartOfHour() 函数将给定的时间转换为小时的开始。 您还可以按年份、季度、月份或天进行分组。

自定义分组间隔

我们甚至可以按任意时间间隔分组,例如,使用 toStartOfInterval() 函数分组为 5 分钟。

假设我们想按 4 小时的间隔分组。我们可以使用 INTERVAL 子句来指定分组间隔:

或者,我们可以使用 toIntervalHour() 函数

无论哪种方式,我们都可以得到以下结果:

填充空组

在许多情况下,我们处理稀疏数据,某些时间段缺失。这会导致出现空桶。让我们来看一个示例,其中我们按 1 小时的间隔分组数据。这将输出以下统计数据,其中某些小时缺失值:

ClickHouse 提供了 WITH FILL 修饰符来解决此问题。这样可以将所有空缺的小时填充为零,从而更好地理解随时间的分布:

滚动时间窗口

有时,我们不想处理间隔的开始(例如,天或小时的开始),而是窗口间隔。 假设我们想了解一个窗口的总点击量,而不是基于天,而是基于从下午 6 点开始的 24 小时周期。

我们可以使用 date_diff() 函数来计算参考时间与每条记录时间之间的差值。 在这种情况下,day 列将表示天数差(例如,1 天前、2 天前等):