TimescaleDB入门-数据查询(翻译整理)

2022-03-25 00:00:00 序列 数据 分析 时间 时间段

使用 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_metricsWHERE 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_metricsWHERE 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_metricsWHERE time > now() - (12* INTERVAL '1 month')GROUP BY bucket, city_nameORDER BY bucket DESC;


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

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

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

time_bucket_gapfill()

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

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

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

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

-- non-gapfill querySELECT time_bucket('30 days', time) as bucket,   city_name, sum(snow_1h_mm) as sumFROM weather_metricsWHERE time > now() - INTERVAL '1 year' AND time < now()GROUP BY bucket, city_nameORDER 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 sumFROM weather_metricsWHERE time > now() - INTERVAL '1 year' AND time < now()GROUP BY bucket, city_nameORDER BY bucket DESC;


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

相关文章