sqloracle-database

PL SQL calculate delta and transpose the result


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))

enter image description here

I want to achieve two things:

  1. calculating the daily value (added here as a refference as a 4th column)
  2. transposing the daily value for the combination of ID and reference date (today) the result would look something like that:

enter image description here

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.)


Solution

  • 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

    fiddle