TimescaleDB 查询以选择列值从前一行更改的行 [英] TimescaleDB query to select rows where column value changed from previous row

问题描述

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

Just recently started using TimescaleDB with postgrest to handle most requests for data.

但是，我遇到了一个问题，即我对时间序列数据的请求效率极低.

However I'm running into an issue where I have a horribly inefficient request for time series of data.

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

It's a data series that can be any length of time, with specific Integer values.

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

Most of the time the value will be the same unless there's an anomaly. So rather than fetching +10,000 rows of data. I would like to aggregate this into "time blocks".

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

Let's say there 97 items in a row where the value is 100 (new item for every 5 minutes) #98 the value is 48 for 5 items in a row and then it goes back up to 100 for another 2,900 rows.

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

I don't want to fetch 3002 items to display this data. I should only need to fetch 3 items.

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

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

But I'm having some trouble figuring out how I can do this with timescaledb.

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

basically, if the value is the same as the last value, aggregate it. That's all I need it to do.

有谁知道如何使用连续聚合(或者如果有更快的方法)在 timescaleDB 中为这种情况构建 VIEW 来获取它?

Does anyone know how to construct a VIEW for this kind of situation in timescaleDB using continuous aggregation (or if there's a faster way) to fetch this?

推荐答案

你可以用窗口函数和一个子选择来达到想要的结果:

You can achieve the desired result with window functions and a subselect:

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 的所有行.

You use a window function to calculate the diff to the previous row and then filter all the rows where the diff is 0 in the outer query.

这篇关于TimescaleDB 查询以选择列值从前一行更改的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！