跳至主要内容

填补时间序列数据中的缺失值

在处理时间序列数据时,由于数据丢失或停机,数据中可能存在缺失值。 通常,我们不希望在查询数据时存在这些缺失值。 在这种情况下,WITH FILL 子句非常有用。 本指南讨论如何使用 WITH FILL 来填补时间序列数据中的缺失值。

设置

假设我们有一个存储 GenAI 图像服务生成的图像元数据的表

CREATE TABLE images
(
`id` String,
`timestamp` DateTime64(3),
`height` Int64,
`width` Int64,
`size` Int64
)
ENGINE = MergeTree
ORDER BY (size, height, width);

导入一些记录

INSERT INTO images VALUES (1088619203512250448, '2023-03-24 00:24:03.684', 1536, 1536, 2207289);
INSERT INTO images VALUES (1088619204040736859, '2023-03-24 00:24:03.810', 1024, 1024, 1928974);
INSERT INTO images VALUES (1088619204749561989, '2023-03-24 00:24:03.979', 1024, 1024, 1275619);
INSERT INTO images VALUES (1088619206431477862, '2023-03-24 00:24:04.380', 2048, 2048, 5985703);
INSERT INTO images VALUES (1088619206905434213, '2023-03-24 00:24:04.493', 1024, 1024, 1558455);
INSERT INTO images VALUES (1088619208524431510, '2023-03-24 00:24:04.879', 1024, 1024, 1494869);
INSERT INTO images VALUES (1088619208425437515, '2023-03-24 00:24:05.160', 1024, 1024, 1538451);

按桶查询

我们将探索 2023 年 3 月 24 日 00:24:0300:24:04 之间创建的图像,因此让我们为这些时间点创建一些参数

SET param_start = '2023-03-24 00:24:03',
param_end = '2023-03-24 00:24:04';

接下来,我们将编写一个查询,将数据分组到 100 毫秒的桶中,并返回在该桶中创建的图像数量

SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘

结果集仅包含创建了图像的桶,但对于时间序列分析,我们可能希望返回每个 100 毫秒的桶,即使它没有条目。

WITH FILL

我们可以使用 WITH FILL 子句来填补这些缺失值。 我们还将指定 STEP,它是要填补的缺失值的间隔大小。 这对于 DateTime 类型默认为 1 秒,但我们希望填补 100 毫秒间隔的缺失值,因此让我们将 100 毫秒的间隔作为我们的步长值

SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘

我们可以看到,缺失值已在 count 列中使用 0 填充。

WITH FILL...FROM

但是,时间范围的开头仍然存在一个缺失值,我们可以通过指定 FROM 来解决

SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.000 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
└─────────────────────────┴───────┘

我们可以从结果中看到,从 00:24:03.00000:24:03.500 的桶现在都出现了。

WITH FILL...TO

但是,我们仍然缺少时间范围末尾的一些桶,我们可以通过提供 TO 值来填充它们。 TO 不是包含性的,因此我们将向结束时间添加少量值以确保它被包含在内

SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 1 millisecond
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┐
│ 2023-03-24 00:24:03.000 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │
│ 2023-03-24 00:24:03.900 │ 1 │
│ 2023-03-24 00:24:04.000 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │
│ 2023-03-24 00:24:04.400 │ 1 │
│ 2023-03-24 00:24:04.500 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │
│ 2023-03-24 00:24:04.900 │ 0 │
│ 2023-03-24 00:24:05.000 │ 0 │
└─────────────────────────┴───────┘

现在所有缺失值都已填补,并且我们有从 00:24:03.00000:24:05.000 的每 100 毫秒的条目。

累积计数

假设我们现在想在桶中保留创建图像数量的累积计数。 我们可以通过添加 cumulative 列来做到这一点,如下所示

SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 1 millisecond
STEP toIntervalMillisecond(100);
┌──────────────────bucket─┬─count─┬─cumulative─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │ 0 │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │
│ 2023-03-24 00:24:04.000 │ 0 │ 0 │
│ 2023-03-24 00:24:04.100 │ 0 │ 0 │
│ 2023-03-24 00:24:04.200 │ 0 │ 0 │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │
│ 2023-03-24 00:24:04.500 │ 0 │ 0 │
│ 2023-03-24 00:24:04.600 │ 0 │ 0 │
│ 2023-03-24 00:24:04.700 │ 0 │ 0 │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │
│ 2023-03-24 00:24:04.900 │ 0 │ 0 │
│ 2023-03-24 00:24:05.000 │ 0 │ 0 │
└─────────────────────────┴───────┴────────────┘

cumulative 列中的值没有按我们想要的方式工作。

WITH FILL...INTERPOLATE

任何在 count 列中为 0 的行,在 cumulative 列中也为 0,而我们更希望它使用 cumulative 列中的先前值。 我们可以使用 INTERPOLATE 子句来做到这一点,如下所示

SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 100 millisecond
STEP toIntervalMillisecond(100)
INTERPOLATE (cumulative);
┌──────────────────bucket─┬─count─┬─cumulative─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │
│ 2023-03-24 00:24:03.700 │ 0 │ 1 │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │
│ 2023-03-24 00:24:04.000 │ 0 │ 3 │
│ 2023-03-24 00:24:04.100 │ 0 │ 3 │
│ 2023-03-24 00:24:04.200 │ 0 │ 3 │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │
│ 2023-03-24 00:24:04.500 │ 0 │ 5 │
│ 2023-03-24 00:24:04.600 │ 0 │ 5 │
│ 2023-03-24 00:24:04.700 │ 0 │ 5 │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │
│ 2023-03-24 00:24:04.900 │ 0 │ 6 │
│ 2023-03-24 00:24:05.000 │ 0 │ 6 │
└─────────────────────────┴───────┴────────────┘

看起来好多了。 现在,让我们使用 bar 函数添加一个条形图,不要忘记将新列添加到 INTERPPOLATE 子句中。

SELECT
toStartOfInterval(timestamp, toIntervalMillisecond(100)) AS bucket,
count() AS count,
sum(count) OVER (ORDER BY bucket) AS cumulative,
bar(cumulative, 0, 10, 10) AS barChart
FROM MidJourney.images
WHERE (timestamp >= {start:String}) AND (timestamp <= {end:String})
GROUP BY ALL
ORDER BY bucket ASC
WITH FILL
FROM toDateTime64({start:String}, 3)
TO toDateTime64({end:String}, 3) + INTERVAL 100 millisecond
STEP toIntervalMillisecond(100)
INTERPOLATE (cumulative, barChart);
┌──────────────────bucket─┬─count─┬─cumulative─┬─barChart─┐
│ 2023-03-24 00:24:03.000 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.100 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.200 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.300 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.400 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.500 │ 0 │ 0 │ │
│ 2023-03-24 00:24:03.600 │ 1 │ 1 │ █ │
│ 2023-03-24 00:24:03.700 │ 0 │ 1 │ █ │
│ 2023-03-24 00:24:03.800 │ 1 │ 2 │ ██ │
│ 2023-03-24 00:24:03.900 │ 1 │ 3 │ ███ │
│ 2023-03-24 00:24:04.000 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.100 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.200 │ 0 │ 3 │ ███ │
│ 2023-03-24 00:24:04.300 │ 1 │ 4 │ ████ │
│ 2023-03-24 00:24:04.400 │ 1 │ 5 │ █████ │
│ 2023-03-24 00:24:04.500 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.600 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.700 │ 0 │ 5 │ █████ │
│ 2023-03-24 00:24:04.800 │ 1 │ 6 │ ██████ │
│ 2023-03-24 00:24:04.900 │ 0 │ 6 │ ██████ │
│ 2023-03-24 00:24:05.000 │ 0 │ 6 │ ██████ │
└─────────────────────────┴───────┴────────────┴──────────┘