Calculating the difference between subsequent values in SQL (SQL)

It's sometimes desirable to caculate the difference between subsequent points.

This might be necessary, for example, if a record's value is drawn from a cumulative total (such as packet counter on a switch) - we're not really interested in the total value so much as the change over time.

The LAG function can be used to look at the previous value, whilst LEAD can be used to look at the next

If the source is something like a packet counter, it might also be desirable to generate a non-negative difference so that a counter reset doesn't lead to large negative figures being reported.

Similar To

• InfluxQL difference
• Flux difference()

Details

• Language: SQL

Snippet

-- Compare to the previous value
SELECT
   time
   _value - LAG (_value) OVER (order by time) AS _value,
FROM foo

-- Compare to the next value
SELECT
   time
   _value - LEAD (_value) OVER (order by time) AS _value,
FROM foo

-- Non-negative difference
SELECT
   time
   CASE
     WHEN _value - LAG (_value) OVER (order by time) < 0 THEN NULL,
     ELSE _value - LAG (_value) OVER (order by time)
   END as _value
FROM foo