在后题中查询时间序列
我使用Postgres跟踪时间序列数据。
---------
|ts|value|
|--------|
|1 |5 |
|--------|
|2 |2 |
|--------|
|5 |10 |
----------value字段的含义是“自上次时间戳以来发生的事件数量”。例如，在秒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(原始数据)中？
您可以使用这个木琴。
回答 1
Stack Overflow用户
发布于 2014-02-24 06:32:13
您当前所做工作的一个问题是，它不会在没有任何样本数据的任何反演中生成数据点。例如，如果用户想要一张从秒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使用任何索引，如果数据表很大，那么性能将非常糟糕。
在这件事上你可以尝试帮助一些事情。一种建议是重新设计数据表，以便第一列包含数据示例的时间范围，而不仅仅是结束时间，然后可以添加范围索引。然后，您可以从第二个子查询中删除窗口函数，希望索引能够被使用。
阅读范围类型这里。
注意:除了你提供的微小数据样本之外，我还没有测试过这一点。不过，我用了一些类似的东西来达到类似的目的。
https://stackoverflow.com/questions/21977220
复制相似问题
腾讯云开发者
