大象时序系列9之TimescaleDB入门-数据查询(翻译整理)

使用 TimescaleDB，无需学习自定义查询语言。TimescaleDB 支持完整的 SQL。这意味着您可以充分利用您的 SQL 知识，并使用您熟悉和喜爱的丰富的 PostgreSQL 工具生态系统。

该示例中的数据可以从以下网址获取:

https://docs.timescale.com/timescaledb/latest/getting-started/add-data/

例如，以下是查找每个城市过去 2 年的平均气温的方法：

--------------------------------
-- Average temperature per city
-- in past 2 years
--------------------------------
SELECT city_name, avg(temp_c)
FROM weather_metrics
WHERE time > now() - INTERVAL '2 years'
GROUP BY city_name;
复制

以下是查找过去 5 年每个城市的总降雪量的方法：

--------------------------------
-- Total snowfall per city
-- in past 5 years
--------------------------------
SELECT city_name, sum(snow_1h_mm)
FROM weather_metrics
WHERE time > now() - INTERVAL '5 years'
GROUP BY city_name;
复制

注：有趣的事实：TimescaleDB 为 PostgreSQL 查询计划器添加了重要的增强功能，提高了 INTERVAL 谓词的查询可重用性，这是 PostgreSQL 所没有的。

用于时间序列数据的高级 SQL 函数

Timescale 有许多定制的 SQL 函数，可帮助您以更少的代码行执行时间序列分析。

这些功能的示例包括：

• time_bucket()
  - 用于分析任意时间间隔的数据

• first()
  - 用于根据聚合组中的时间查找最早的值

• last()
  - 用于在聚合组中根据时间查找最新值

• time_bucket_gapfill()
  - 用于分析任意时间间隔内的数据并填补数据中的任何空白

• locf()
  - 用于通过将最后观察到的值向前推进来填补数据中的空白

• interpolate()
  - 用于通过线性插值已知数据点之间的缺失值来填补空白

让我们仔细看看time_bucket
。

时间桶（）

下面是一个示例，说明如何使用time_bucket()
过去 6 个月来查找每个城市每 15 天的平均温度：

-----------------------------------
-- time_bucket
-- Average temp per 15 day period
-- for past 6 months, per city
-----------------------------------
SELECT time_bucket('15 days', time) as "bucket"
   ,city_name, avg(temp_c)
   FROM weather_metrics
   WHERE time > now() - (12* INTERVAL '1 month')
   GROUP BY bucket, city_name
   ORDER BY bucket DESC;
复制

使用time_bucket
，您可以在对您的用例最重要的时间间隔（例如，10 秒、15 分钟、6 小时 - 无论您感兴趣的时间段是什么）中监控、分析和可视化时间序列数据。这是因为time_bucket
使您能够将数据分段为任意时间间隔。在分析时间序列数据时，通常需要这样的间隔，但有时可能会因为您使用的数据库、查询语言或多合一工具的限制而变得笨拙。

对于熟悉 PostgreSQL 的读者，可以认为是PostgreSQL功能time_bucket
更强大的版本。允许任意时间间隔，而不是由.date_trunc
time_bucket
date_trunc

time_bucket
只是众多 TimescaleDB 自定义构建的 SQL 函数之一，可帮助您以更少的代码行执行更深入的时间序列分析。时间序列分析的另一个强大功能是time_bucket_gapfill
.

time_bucket_gapfill()

时间序列分析中的另一个常见问题是处理不完美的数据集。一些时间序列分析或可视化希望显示每个选定时间段的记录，即使在该时间段内没有记录数据。这通常被称为“间隙填充”，并且可能涉及执行诸如为任何缺失数据记录“0”、插入缺失值或向前携带最后观察到的值直到记录新数据为止的操作。

Timescale 提供time_bucket_gapfill()
、 locf()
和interpolate()
帮助对有差距的数据进行分析。

在样本数据集中，某些城市有没有下雨或下雪的日子。但是，您可能仍希望对特定时间段的雨或雪进行分析或绘制趋势线图。

例如，下面的查询计算每个城市在过去一年的 30 天时间段内的总降雪量：

-- non-gapfill query
SELECT time_bucket('30 days', time) as bucket,
   city_name, sum(snow_1h_mm) as sum
   FROM weather_metrics
   WHERE time > now() - INTERVAL '1 year' AND time < now()
   GROUP BY bucket, city_name
   ORDER BY bucket DESC;
复制

请注意，结果仅包括城市降雪的时间段，而不是我们分析的特定时间段，即一年。

要为我们分析期间的所有时间桶生成数据，我们可以使用 time_bucket_gapfill 代替：

-----------------------------------------
-- time_bucket_gapfill
-- total snow fall per city
-- in 30-day buckets for past 1 year
-----------------------------------------
SELECT time_bucket_gapfill('30 days', time) as bucket,
   city_name, sum(snow_1h_mm) as sum
   FROM weather_metrics
   WHERE time > now() - INTERVAL '1 year' AND time < now()
   GROUP BY bucket, city_name
   ORDER BY bucket DESC;
复制

time_bucket 和 time_bucket_gapfill 等 TimescaleDB SQL 函数有助于对数据进行历史分析并创建具有特定时间段的视觉效果。