postgresql - TimescaleDB 查询以选择列值从上一行更改的行

Question

最近才开始使用 TimescaleDB 和 Postgres 来处理大多数数据请求。

但是我遇到了一个问题，我对时间序列数据的请求非常低效。

它是一个可以是任意时间长度的数据系列，具有特定的整数值。

大多数情况下，除非出现异常，否则该值将是相同的。因此，而不是获取 +10,000 行数据。我想将其汇总为“时间 block ”。

假设连续 97 个项目的值为 100(每 5 分钟新项目)#98 连续 5 个项目的值为 48，然后在另外 2,900 行中返回到 100。

我不想获取 3002 项来显示此数据。我应该只需要获取 3 个项目。

• 1 项表示从 startDate 开始值为 100
• 1 项表示从 #1 之后的开始日期开始值为 48
• 1 项从 #2 之后的 startDate 起再次表示值为 100

但我在弄清楚如何使用 timescaledb 做到这一点时遇到了一些麻烦。

基本上，如果该值与上一个值相同，则将其聚合。这就是我需要它做的。

有谁知道如何使用连续聚合(或者如果有更快的方法)来为 timescaleDB 中的这种情况构造一个 View 来获取它？

Answer 1

您可以使用窗口函数和子选择实现所需的结果:

SELECT time, value FROM (
  SELECT 
    time,
    value,
    value - LAG(value) OVER (ORDER BY time) as diff
  FROM hypertable) ht 
WHERE diff IS NULL OR diff != 0;

您使用窗口函数计算与前一行的差异，然后在外部查询中过滤所有差异为 0 的行。