Calculating the difference between subsequent values in SQL



Published: 2024-06-15 22:14:18 +0000
Categories: SQL,

Language

SQL

Description

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

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

Keywords

lag, lead, non-negative, difference,

Latest Posts


Copyright © 2024 Ben Tasker | Sitemap | Privacy Policy
Available at snippets.bentasker.co.uk, http://phecoopwm6x7azx26ctuqcp6673bbqkrqfeoiz2wwk36sady5tqbdpqd.onion and http://snippets.bentasker.i2p
hit counter github.com/bentasker