python - 如何使用移动时间窗口计算 PostgreSQL 时间序列中的条目率

Question

所以我有这样的数据:

index                      price
2015-01-08 07:01:18.601318  10
2015-01-08 07:01:19.100645  10
2015-01-08 07:01:19.600494  24
2015-01-08 07:01:20.101432  22
2015-01-08 07:01:20.102579  235
2015-01-08 07:01:20.600370  1050
2015-01-08 07:01:20.603521  100
2015-01-08 07:01:20.800408  50
2015-01-08 07:01:21.100629  10
2015-01-08 07:01:21.601491  12
2015-01-08 07:01:21.601663  97

我想要的是在每个条目后面有一个指定时间长度的移动窗口，比如 1 秒，并计算该窗口内有多少条目。

结果应该是这样的:

index                      price    rate
2015-01-08 07:01:18.601318  10       1
2015-01-08 07:01:19.100645  10       2
2015-01-08 07:01:19.600494  24       3
2015-01-08 07:01:20.101432  22       2
2015-01-08 07:01:20.102579  235      3
2015-01-08 07:01:20.600370  1050     4
2015-01-08 07:01:20.603521  100      4
2015-01-08 07:01:20.800408  50       5
2015-01-08 07:01:21.100629  10       6
2015-01-08 07:01:21.601491  12       4
2015-01-08 07:01:21.601663  97       5

这是为了计算这些条目输入数据库的速率。

看起来我需要某种滑动扩展窗口，但我无法让它工作。

如果这对于 pandas DataFrame 可行，那也可以。我知道我可以使用 pandas 中的 TimeGrouper 按秒对它们进行分组并计算每个组的计数，但是我在索引重复方面遇到了其他问题，这也删除了信息:

df['rate'] = df.groupby(pd.TimeGrouper('1s'))['price'].transform('count')

Answer 1

我不认为你可以有不同行大小的滑动窗口/框架。

这适用于示例(但非常可怕):

SELECT t1.index, count(*) AS rate
FROM the_table t1
JOIN the_table t2 ON (t2.index BETWEEN t1.index - interval '1s' AND t1.index)
GROUP BY 1 ORDER BY 1;

当然，按秒(而不是 前 秒)进行分区会很容易。