oracle lag函数

项目中有这么个需求   求出增长率 如：

2001     10    --

2002     20   100%

2003     30   50%

 

每个时间点对前一个时间的数量相减求出增长率(20-10)/10，(30-20)/20。第一条记录没有则显示'--'

 

[TEST2@orcl#17-4月 -11] SQL>select * from t3;

        ID        NUM
---------- ----------
         1          1
         2          2
         3          4
         4          5

 

[TEST2@orcl#17-4月 -11] SQL>select id,num,lag(num,1) over (order by id) as bai from t3;

        ID        NUM        BAI
---------- ---------- ----------
         1          1
         2          2          1
         3          4          2
         4          5          4

 

[TEST2@orcl#17-4月 -11] SQL>select id,num, case when bai is null then '--' else to_char(round((num-bai)/bai,2)*100,'990.99') end as bai from (select id,num,lag(num,1) over (order by id) as bai from t3);

        ID        NUM BAI
---------- ---------- -------
         1          1 --
         2          2  100.00
         3          4  100.00
         4          5   25.00

 

类似的函数还有lead