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

使用带有时间戳条件的内部连接将表连接到自身,以包含60秒的所有记录。下面是标准SQL(需要针对XDB进行调整):

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

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

有时,一个简单的“查询重述”会带来很大的性能差异。