Discrete Derivative in SQL

Question

I have sensor data in a table in the form:

Time Value 10 100 20 200 36 330 46 440 

I would like to pull a change in values ​​for each period of time. Ideally, I would like to get:

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

My SQL skills are pretty rudimentary, so I tend to pull all the data into a script that processes it and then return it back to the new table, but I thought I would ask if there is a spot way to do it all in the database.

Answer 1

 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 

Answer 2

 Select a.Time as StartTime, b.time as EndTime, b.time-a.time as TimeChange, b.value-a.value as ValueChange FROM YourTable a, YourTable b WHERE b.time = (Select MIN(c.time) FROM YourTable c WHERE c.time>a.time) 

Answer 3

First, I would add an id column to the table so that you have something that grows predictably from row to row.

Then I will try the following query:

 SELECT t1.Time AS 'Starttime', t2.Time AS 'Endtime', (t2.Value - t1.Value) AS 'Change' FROM SensorData t1 INNER JOIN SensorData t2 ON (t2.id - 1) = t1.id ORDER BY t1.Time ASC 

I'm going to create a test pattern to try it for myself, so I don’t know if it works yet, but it's worth it!

Update Fixed with one minor problem (CHANGE is a protected word and should have been indicated), but verified and it works! It gives exactly the results defined above.

Answer 4

It works?

 WITH T AS ( SELECT [Time] , Value , RN1 = ROW_NUMBER() OVER (ORDER BY [Time]) , RN2 = ROW_NUMBER() OVER (ORDER BY [Time]) - 1 FROM SensorData ) SELECT StartTime = ISNULL(t1.[time], t2.[time]) , EndTime = ISNULL(t2.[time], 0) , Change = t2.value - t1.value FROM T t1 LEFT OUTER JOIN T t2 ON t1.RN1 = t2.RN2