SELECT EVENT_TYPE, VALUE - LATEST_VALUE "Value"
FROM (SELECT ROWNUMBER,
EVENT_TYPE,
VALUE,
NVL (
LAG (VALUE, 1)
OVER (PARTITION BY EVENT_TYPE ORDER BY EVENT_TYPE, TIME),
0)
AS LATEST_VALUE,
TIME
FROM ( SELECT ROW_NUMBER ()
OVER (PARTITION BY EVENT_TYPE
ORDER BY EVENT_TYPE, TIME DESC)
AS ROWNUMBER,
EVENT_TYPE,
VALUE,
TIME
FROM EVENTS
ORDER BY EVENT_TYPE, TIME DESC)
WHERE ROWNUMBER < 3)
WHERE LATEST_VALUE > 0;
Data Preparation
CREATE TABLE EVENTS
(
EVENT_TYPE INTEGER NOT NULL,
VALUE INTEGER NOT NULL,
TIME TIMESTAMP(6) NOT NULL
);
SET DEFINE OFF;
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(2, 5, TO_TIMESTAMP('7/25/2016 4:27:27.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(2, 13, TO_TIMESTAMP('7/25/2016 4:28:44.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(2, 34, TO_TIMESTAMP('7/25/2016 4:28:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(4, 42, TO_TIMESTAMP('7/25/2016 4:27:44.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(5, 4, TO_TIMESTAMP('7/25/2016 4:31:56.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(5, 12, TO_TIMESTAMP('7/25/2016 4:31:53.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(5, 34, TO_TIMESTAMP('7/25/2016 4:31:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(5, 12, TO_TIMESTAMP('7/25/2016 4:27:50.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
INSERT INTO EVENTS
(EVENT_TYPE, VALUE, TIME)
VALUES
(7, 13, TO_TIMESTAMP('7/25/2016 4:27:56.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;
