基于时间戳差异的SQL查询数据

Question

我有一个与sql数据库相关的问题。

也就是说，我想创建一个查询，获取某个measurment列的平均值，但只针对那些时间差小于60秒的后续行。

一个示例表将如下所示：

| ID | Measurement | Timestamp | 
| 1  | 0.564       | 1535648400|
| 2  | 0.456       | 1535648459|
| 3  | 0.785       | 1535648501|
| 4  | 0.321       | 1535648620|

时间戳之间的差异对于所有行都是可以的，除了3和4之间，它们之间的差异超过60秒。在这种情况下，average将不接受ID为4的行，而只接受前3行。

我主要计划为InfluxDB数据库编写此查询，但通常也可以为结构SQL编写查询。

Answer 1

使用inner和timestamp条件将表连接到自身，以包含60秒内的所有记录。下面是标准SQL (需要针对InfluxDB进行调整)：

SELECT t1.ID, t1.measurement, t1.timestamp,  avg(t2.measurement) 
FROM table t1, table t2
WHERE t1.timestamp <= t2.timestamp
  AND t1.timestamp + 60*1000 > t2.timestamp
GROUP BY t1.ID, t1.measurement, t1.timestamp

Answer 2

我想在V.G.上面添加一个我认为非常好的答案。出于性能原因，您可能希望重新编写查询并添加一个好的索引，如下所示：

create index ix1 on table (timestamp);

那么查询可以是：

SELECT t1.ID, t1.measurement, t1.timestamp,  avg(t2.measurement) 
FROM table t1, table t2
WHERE t2.timestamp >= t1.timestamp
  AND t2.timestamp < t1.timestamp + 60*1000
GROUP BY t1.ID, t1.measurement, t1.timestamp

有时，一个简单的“查询重述”可以产生很大的性能差异。