甲骨文分析查询

Question

好，我需要一些帮助编写查询，这里是剪断数据...

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是基线和成长是每天计算，等等19-SEP-16数据增长了19 SEP-2016 size_bytes 18-SEP-16 size_bytes。

就是我希望做到的，是这种格式

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 1

使用LAG函数来显示数据：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;