Discrete Derivative in SQL

Question

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

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.

Would make for an excellent interview question: Trivial to formulate but non-trivial to fix. — Zephyr was a Friend of Mine, Oct 21 '10 at 12:36
+1 for the fact that you are smart enough to not just do it the way that you already know and asking the question. — Kuberchaun, Oct 21 '10 at 18:43

score 4 · Answer 1 · answered Oct 21 '10 at 11:34

4

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)

answered Oct 21 '10 at 11:34

Andy

3,631
2
23
32

Tested this on my copy of the table and it also works. I like the addition of the time change as well as value change :) – Brendan Bullen Oct 21 '10 at 11:40
Also see my other answer, it avoids the subquery. – Andy Oct 21 '10 at 14:46

Andy · Accepted Answer · 2010-10-21T14:43:51.580

4

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

edited Oct 21 '10 at 14:43

answered Oct 21 '10 at 11:49

Andy

3,631
2
23
32

This seems a little inefficient to me. Does it make sense to do 2 joins when one join would be enough? See this answer http://stackoverflow.com/questions/6299950/sql-difference-between-rows – Mark Lakata Apr 02 '12 at 23:36

score 4 · Answer 3 · answered Jun 26 '20 at 05:46

4

you could use a SQL window function, below is an example based on BIGQUERY syntax.

SELECT 
  LAG(time, 1) OVER (BY time) AS start_time,
  time AS end_time, 
  (value - LAG(value, 1) OVER (BY time))/value AS Change
from data

answered Jun 26 '20 at 05:46

Hui Zheng

2,394
1
14
18

1

`OVER (ORDER BY time)` for PostgreSQL. – Anton Abrosimov Oct 04 '20 at 13:37

Brendan Bullen · Answer 4 · 2010-10-21T11:37:56.957

0

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

Then, I would 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 table to try this for myself so I don't know if it works yet but it's worth a shot!

Update Fixed with one minor issue (CHANGE is a protected word and had to be quoted) but tested it and it works! It produces exactly the results defined above.

edited Oct 21 '10 at 11:37

answered Oct 21 '10 at 11:31

Brendan Bullen

11,607
1
31
40

This will only work if rows are inserted in order(by startTime), AND no row will ever be deleted; deleting one row will result in a gap so t2.id - t1.id will be > 1 – Andy Oct 21 '10 at 11:56
Indeed, you're right, but given the nature of the data (i.e. coming from a sensor) I would expect the rows to be correctly sequential and a deleted row would be unlikely (I guess) since it would effectively be a gap in the data. – Brendan Bullen Oct 21 '10 at 12:24

score 0 · Answer 5 · answered Oct 21 '10 at 12:29

0

Does this work?

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

answered Oct 21 '10 at 12:29

Zephyr was a Friend of Mine

7,661
4
37
39

Would work for SQL Server, but I now realise that is not tagged. – Zephyr was a Friend of Mine Oct 21 '10 at 12:34

Discrete Derivative in SQL

5 Answers5

Linked