SQL中的离散导数 [英] Discrete Derivative in SQL

问题描述

我在表格中获得了传感器数据:

I've got sensor data in a table in the form:

Time      Value
10        100
20        200
36        330
46        440

我想提取每个时间段的值变化.理想情况下，我想得到:

I'd like to pull the change in values for each time period. Ideally, I'd like to get:

Starttime Endtime   Change
10        20        100
20        36        130
36        46        110

我的 SQL 技能相当初级，所以我倾向于将所有数据拉出到一个脚本中来处理它，然后将其推回到新表中，但我想我会问是否有一种巧妙的方法来处理它这一切都在数据库中完成.

My SQL skills are pretty rudimentary, so my inclination is to pull all the data out to a script that processes it and then push it back to the new table, but I thought I'd ask if there was a slick way to do this all in the database.

推荐答案

Select a.Time as StartTime
     , b.time as EndTime
     , b.time-a.time as TimeChange
     , b.value-a.value as ValueChange
FROM YourTable a 
Left outer Join YourTable b ON b.time>a.time
Left outer Join YourTable c ON c.time<b.time AND c.time > a.time
Where c.time is null
Order By a.time

这篇关于SQL中的离散导数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！