archiver archiver - 2 months ago 19
SQL Question

Oracle analytical query

All, I need some help with writing a query, here is the snipped of data...

TABLE_NAME SIZE_BYTES DATE
---------------------------------------- --------------- ---------
TABLE_A 346,817,560,576 17-SEP-16
TABLE_A 346,817,560,576 18-SEP-16
TABLE_A 369,046,323,200 19-SEP-16
TABLE_A 413,623,386,112 20-SEP-16
TABLE_A 466,840,715,264 21-SEP-16
TABLE_B 42895409152 17-SEP-16
TABLE_B 42962518016 18-SEP-16
TABLE_B 43163844608 19-SEP-16
TABLE_B 43365171200 20-SEP-16
TABLE_B 43566497792 21-SEP-16


17-SEP-2016 is the baseline and growth is calculated per day, so on 19-SEP-16 data grew by taking 19-SEP-2016 size_bytes less 18-SEP-16 size_bytes.

What I'm hoping to achieve is to show data in this format

TABLE_NAME DATE SIZE_DELTA
------------- ----------- --------------
TABLE_A 17-SEP-16 346,817,560,576
18-SEP-16 0
19-SEP-16 22,228,762,624
20-SEP-16 44,577,062,912
21-Sep-16 53,217,329,152
TABLE_B 17-Sep-16 42895409152
18-Sep-16 67108864
19-Sep-16 201326592
20-Sep-16 201326592
21-Sep-16 201326592

Answer

Use LAG function: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions082.htm#SQLRF00652

SELECT table_name, date,
       size_bytes - lag( size_bytes, 1, 0 ) 
                    OVER ( partition by table_name order by date ) As SIZE_DELTA
FROM tablename;