I'm using PL SQL and I have the following sample data:
(I have an ID and an accumulated daily counter, assuming I need to check only recent 7 days (Nov 26th till Dec 3rd in this case))
I want to achieve two things:
the same would be for more IDs and more dates, but let's assume that the 7 days range remains, but the process can be executed daily of course (tomorrow, for example, it will examine the dates - Nov 27th till Dec 4th etc.)
Convert the date to an offset from the current day and use LAG
to find the delta
and then PIVOT
:
SELECT *
FROM (
SELECT id,
TRUNC(SYSDATE) - dt AS day,
value - LAG(value) OVER (PARTITION BY id ORDER BY dt) AS delta
FROM table_name
)
PIVOT (
SUM(delta) FOR day IN (
0 AS value_0,
1 AS value_1,
2 AS value_2,
3 AS value_3,
4 AS value_4,
5 AS value_5,
6 AS value_6,
7 AS value_7
)
)
Which, for the sample data:
CREATE TABLE table_name (id, dt, value) AS
SELECT 10, TRUNC(SYSDATE) - 7, 74 FROM DUAL UNION ALL
SELECT 10, TRUNC(SYSDATE) - 6, 651 FROM DUAL UNION ALL
SELECT 10, TRUNC(SYSDATE) - 5, 12416 FROM DUAL UNION ALL
SELECT 10, TRUNC(SYSDATE) - 4, 12800 FROM DUAL UNION ALL
SELECT 10, TRUNC(SYSDATE) - 3, 12802 FROM DUAL UNION ALL
SELECT 10, TRUNC(SYSDATE) - 2, 12804 FROM DUAL UNION ALL
SELECT 10, TRUNC(SYSDATE) - 1, 13624 FROM DUAL UNION ALL
SELECT 10, TRUNC(SYSDATE) - 0, 13752 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 7, 88 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 6, 95 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 5, 96 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 4, 1111 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 3, 1111 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 2, 1200 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 1, 1350 FROM DUAL UNION ALL
SELECT 20, TRUNC(SYSDATE) - 0, 1350 FROM DUAL;
Outputs:
ID | VALUE_0 | VALUE_1 | VALUE_2 | VALUE_3 | VALUE_4 | VALUE_5 | VALUE_6 | VALUE_7 |
---|---|---|---|---|---|---|---|---|
10 | 128 | 820 | 2 | 2 | 384 | 11765 | 577 | null |
20 | 0 | 150 | 89 | 0 | 1015 | 1 | 7 | null |