Discrete Derivative in SQL

Parker_42562 0
Viewed 6270

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.

time-series postgresql sql
5 Answers

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
user_64717430
Related