How toCalculate percentage increase/ decrease in Oracle Database?

Question

I have a table with 3 rows contain values :

Row1 : 5
Row2 : 8
Row3 : 9

I wish to calculate the percentage of increase or decrease of : Row2 compared to Row1 , Row3 to Row2 so i would have this table : The formula to calculate % is : [Row(n+1) - Row(n)] / Row(2)

     Value   Percentage 
Row1 : 5         -
Row2 : 8        60% (increase compared to Row1) | (8-5)/5
Row3 : 9        12.5%(increase compared to Row2)| (9-8)/8

Please suggest the way or a solution .

Thanks all.

Answer 1

You need to use the Oracle Analytical function LAG:

Your query will look something like:

SELECT ((value - lagv)/lagv) * 100
  FROM (
        SELECT value,
               LAG(value) OVER(ORDER BY <ordering_column>) as lagv
          FROM table1
);

To test I ran:

WITH t AS (SELECT 1 as rnum,
                  5 AS value FROM DUAL
           UNION
           SELECT 2 as rnum,
                  8 AS value FROM DUAL
           UNION
           SELECT 3 as rnum,
                  9 AS value FROM DUAL
          ) 
SELECT ((value - lagv)/lagv) * 100 AS Percentage
  FROM (
        SELECT value,
               LAG(value) OVER(ORDER BY rnum) as lagv
          FROM t
);

It returned:

Row  Percentage
  1           
  2          60
  3        12.5

As you need to specify an order for the LAG function I have created the rnum column, I assume your table has some sort of ordering column you can use, (otherwise how do you know which rows to compare?).

Hope this helps...

EDIT: This link is pretty useful for learning about Oracle's LEAD and LAG functions. http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php