使用 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

结果

SQL - Adding Rows

添加行和插入数据

插值

在 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

结果

Adding rows and inserting data

LOCF

locf 代表向前推移最后观测值。在聚合函数（如以下查询中的 avg）之前添加 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

结果

LOCF

结论

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

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

要开始使用 InfluxDB 和 SQL 构建酷炫的应用，请今天免费注册您的云账户。