在Postgress中查询时间序列

Yaron Naveh 发表于 Dev

亚伦·纳夫（Yaron Naveh）

我正在使用Postgres来跟踪时间序列数据。

---------
|ts|value|
|--------|
|1 |5    |
|--------|
|2 |2    |
|--------|
|5 |10   |
----------

值字段的含义是“自上一个时间戳以来发生的事件数”。例如在3-5秒内，有10个事件，如ts 5的值所示。

我们的用户可以提出类似的要求：“向我显示0-6秒的折线图，其中数据点的间隔为4秒”。因此，我们将显示两个数据点，分别是秒0和4，每个数据点汇总到下一个点的事件数。因此，折线图将使用以下数据：

---------
|ts|value|
|--------|
|0 |7    |
|--------|
|4 |10   |
----------

（因为5 + 2 = 7和10 = 10）

我们使用此查询来获取此数据。用户可以询问任意的开始（时间），结束（时间）和间隔值。ts的类型也是整数（但可以调整为时间戳）。

SELECT start+round((ts-start)/interval)*interval as ts1, sum(value)
FROM events
WHERE ts >= start AND ts <= end
GROUP BY ts1

这是正确的查询，更重要的是正确的方法吗？
我认为ts上的索引将被忽略，对此我能做些什么？
无论如何，是否要向查询中添加线性插值逻辑，以使在第3-5秒（在原始表中）发生的10个事件不会完全累加到（结果集的）第4个事件，因为它们中的第三个实际上发生在第3个事件（原始数据）？

您可以使用此sqlfiddle。

有害的

当前操作方式的一个问题是，它不会在没有任何样本数据的任何间隔中生成数据点。例如，如果用户想要从0到10秒以1为步长的图表，则您的图表在5点之后将没有任何点。不过，这在您的用例中并不重要。

如您所指出的，另一个问题是，如果请求的图的分辨率大于可用数据，则能够使用某种线性插值来归因于测量是很好的。

为了解决其中的第一个问题，我们可以从纯粹的样本表中选择数据，而无需将数据与生成的符合用户要求的系列数据结合在一起。后者可以使用以下方式生成：

SELECT int4range(rstart, rstart+1) AS srange 
FROM generate_series(0,10,1) AS seq(rstart)

上面的查询将生成一系列范围，从0到10，步长为1。输出如下所示：

 srange
---------
 [0,1)
 [1,2)
 [2,3)
 [3,4)
 [4,5)
 [5,6)
 [6,7)
 [7,8)
 [8,9)
 [9,10)
 [10,11)
(11 rows)

我们可以使用&&运算符（对重叠进行过滤）将其连接到数据表。

第二点可以通过计算落入每个样本窗口中的每个数据行的比例来解决。

这是完整的查询：

SELECT lower(srange) AS t,
    sum (CASE 
        -- when data range is fully contained in sample range
        WHEN drange <@ srange THEN value
        -- when data range and sample range overlap, calculate the ratio of the intersection
        -- and use that to apportion the value
        ELSE CAST (value AS DOUBLE PRECISION) * (upper(drange*srange) - lower(drange*srange)) / (upper(drange)-lower(drange))
    END) AS value
FROM (
    -- Generate the range to be plotted (the sample ranges).
    -- To change the start / end of the range, change the 1st 2 arguments
    -- of the generate_series. To change the step size change BOTH the 3rd
    -- argument and the amount added to rstart (they must be equal).
    SELECT int4range(rstart, rstart+1) AS srange FROM generate_series(0,10,1) AS seq(rstart)
) AS s
LEFT JOIN (
    -- Note the use of the lag window function so that for each row, we get
    -- a range from the previous timestamp up to the current timestamp
    SELECT int4range(coalesce(lag(ts) OVER (order by ts), 0), ts) AS drange, value FROM data
) AS d ON srange && drange
GROUP BY lower(srange)
ORDER BY lower(srange)

结果：

 t  |      value
----+------------------
  0 |                5
  1 |                2
  2 | 3.33333333333333
  3 | 3.33333333333333
  4 | 3.33333333333333
  5 |
  6 |
  7 |
  8 |
  9 |
 10 |
(11 rows)

就目前而言，此查询不太可能在ts上使用任何索引，并且如果数据表很大，那么性能将很糟糕。

您可以尝试一些方法来帮助解决此问题。一个建议可能是重新设计数据表，使第一列包含数据样本的时间范围，而不仅仅是结束时间，然后可以添加范围索引。然后，您可以从第二个子查询中删除窗口功能，并希望可以使用索引。

在这里阅读范围类型。

请注意：除了您提供的微小数据样本外，我还没有进行过其他测试。我出于某种相似的目的使用了类似的东西。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。