Arun Arun - 1 year ago 66
SQL Question

SQL to calculate difference between 2 latest recent values by event_types

The events table looks like

event_type value timestamp
2 2 06-06-2016 14:00:00
2 7 06-06-2016 13:00:00
2 2 06-06-2016 12:00:00
3 3 06-06-2016 14:00:00
3 9 06-06-2016 13:00:00
4 9 06-06-2016 13:00:00


My goal is to filter event types that occur more than twice and subtract most two recent values and shows BY event_type.

The end result would be

event_type value
2 -5
3 -6


I was able to get filter events occurred more than twice and order by event_type based on timestamp desc.
The difficult part for me is to subtract most two recent values and shows BY event_type.

DB / SQL experts , please help

Answer Source

You can use a query like this:

SELECT event_type, diff
FROM (
  SELECT event_type, value, "timestamp", rn,
         value - LEAD(value) OVER (PARTITION BY event_type 
                                  ORDER BY "timestamp" DESC) AS diff
  FROM (
    SELECT event_type, value, "timestamp",
           COUNT(*) OVER (PARTITION BY event_type) AS cnt,
           ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY "timestamp" DESC) AS rn
    FROM mytable) AS t
  WHERE cnt >=2 AND rn <= 2 ) AS s
WHERE rn = 1  

The innermost subquery uses:

  • Window function COUNT with PARTITION BY clause, so as to calculate the population of each event_type slice.
  • Window function ROW_NUMBER so as to get the two latest records within each event_type slice.

The mid-level query uses LEAD window function, so as to calculate the difference between the first and the second records. The outermost query simply returns this difference.

Demo here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download