对表中的增量求和的SQL查询

Question

我有存储在表中的传入传感器数据。传感器的每条记录都有几个"计数器"列。计数器只不过是时间的快照。示例记录如下所示。

<>之前|id |传感器|计数器1 |计数器2 |时间戳15 100 200上午10时25 125 210上午10:01…6051000800上午11时之前

我有数千个这样的传感器及时发送它们计数器值的快照。我需要做的是，给定一个时间段，比如在上午10点到11点之间，将记录之间的delta映射到图表。总是取前一个记录。
什么是SQL查询，以获得连续记录之间的增量?
我的第二个问题是，什么是一个好的表设计来存储这样的数据，这是自我引用。可能是一个链表，每个记录指向前一个记录?

Answer 1

泛型SQL为:

select t.*,
       (select counter1 from t t2 where t2.sensor = t.sensor and t2.timestamp < t.timestamp
        order by timestamp desc
        limit 1
       ) prevCounter1
from t

limit 1依赖于数据库。可能是top 1 (SQL Server, Sybase)或where rownum = 1 (Oracle)。

您需要为每个计数器执行此操作。

如果您使用的是Postgres, Oracle或SQL Server 2012，您可以使用lag():

select t.*,
       lag(counter1) over (partition by sensor order by timestamp) as prevCounter1
from t

在MySQL中，如果你放入一个prevID，你将会得到最好的结果。如果没有，在sensor, timestamp上建立索引应该可以提供合理的性能。