使用 SQL 回填缺失的时间序列

作者：Jessica Wachtel / 产品, 开发者
2023 年 5 月 3 日

时间序列数据流通常是嘈杂且不规则的。但无论不规则性的原因是什么——网络错误、不稳定的传感器还是断电——高级分析工具、机器学习和人工智能模型都要求其数据输入包含具有固定时间间隔的数据集。这使得填充所有缺失行和值的过程成为数据清理和基本分析过程的必要部分。

随着我们完全拥抱 Apache 生态系统，SQL 成为 InfluxDB 的主要查询语言，但我们必须构建一些功能来填补 SQL 的传统功能与时间序列数据工作负载需求之间的空白。我们构建的用于解决数据缺失和不一致时间窗口的自定义查询函数之一是 date_bin_gapfill。

添加行

date_bin_gapfill 根据用户确定的特定参数计算时间间隔，如果在某个时间间隔内不存在行，则该函数将插入一个新行，并将时间值设置为时间间隔开始，数据值设置为 null。

示例查询

SELECT
  date_bin_gapfill( '30 minutes', time) as _time,
  room,
  avg(temp) as temp
FROM home
WHERE
	time >= '2023-04-27T08:00:00Z'
	AND time <= '2023-04-27T10:00:00Z'
GROUP BY _time, room

结果

添加行和插入数据

插值

在 date_bin_gapfill 函数内部添加 interpolate 会将 null 数据值替换为最后一个值和下一个值之间的线性插值。请务必将其放在聚合函数（以下查询中的 avg）之前，以便它在运行时包含所有值。

示例查询

SELECT
  date_bin_gapfill('30 minutes', time) as _time,
  room,
  interpolate(avg(temp))
FROM home
WHERE
	time >= '2023-04-27T08:00:00Z'
	AND time <= '2023-04-27T10:00:00Z'
  GROUP BY _time, room

结果

LOCF (上次观测值结转)

locf 代表上次观测值结转。在聚合函数之前添加 locf 会将 null 值替换为最后一个值。

SELECT
  date_bin_gapfill( '30 minutes', time) as _time,
  room,
 locf(avg(temp))
FROM home
WHERE
	time >= '2023-04-27T08:00:00Z'
	AND time <= '2023-04-27T10:00:00Z'
GROUP BY _time, room

结果

结论

此演示视频展示了该强大功能的实际应用。

有关使用 SQL 和时间序列数据的更多详细信息，请查看我们的文档。

要开始使用 InfluxDB 和 SQL 构建一些很酷的东西，请立即注册您的免费云账户。