填补时间序列数据中的空白
在处理时间序列数据时,由于缺失数据或不活动,数据中可能会出现空白。
通常情况下,我们不希望在查询数据时存在这些空白。在这种情况下,WITH FILL
子句可以派上用场。
本指南讨论了如何使用 WITH FILL
来填补时间序列数据中的空白。
设置
假设我们有以下表格,用于存储由 GenAI 图像服务生成的图像的元数据:
让我们导入一些记录:
按桶查询
我们将探索在 2023 年 3 月 24 日的 00:24:03
和 00:24:04
之间创建的图像,因此让我们为这些时间点创建一些参数:
接下来,我们将编写一个查询,将数据分组到 100 毫秒的桶中,并返回在该桶中创建的图像数量:
结果集仅包括在其中创建了图像的桶,但对于时间序列分析,我们可能希望返回每个 100 毫秒的桶,即使它没有任何条目。
WITH FILL
我们可以使用 WITH FILL
子句来填补这些空白。
我们还将指定 STEP
,即要填补的空白的大小。
对于 DateTime
类型,默认值是 1 秒,但我们希望填补 100 毫秒的空白,因此我们将步长值设置为 100 毫秒:
我们可以看到,空白在 count
列中已被 0 值填充。
WITH FILL...FROM
不过,时间范围的开始处仍有一个空白,我们可以通过指定 FROM
来解决这个问题:
从结果中可以看到,00:24:03.000
到 00:24:03.500
的桶现在都已出现。
WITH FILL...TO
不过,我们仍然缺少一些时间范围结束时的桶,我们可以通过提供 TO
值来填补这些空白。
TO
是不包含的,因此我们将结束时间加上一小段时间,以确保它被包括在内:
所有空白现在都已填补,我们从 00:24:03.000
到 00:24:05.000
的每 100 毫秒都有条目。
累计计数
假设我们现在想保持跨桶创建的图像数量的累计计数。
我们可以通过添加一个 cumulative
列来实现,如下所示:
累计列中的值并未按照我们希望的方式工作。
WITH FILL...INTERPOLATE
任何 count
列中为 0
的行,cumulative
列中也为 0
,而我们希望它使用 cumulative
列中的上一个值。
我们可以通过使用 INTERPOLATE
子句来实现,如下所示:
看起来好多了。
现在为了完成它,让我们使用 bar
函数添加一个条形图,别忘了将我们的新列添加到 INTERPOLATE
子句中。