Access data from previous record in a SELECT query
When you work with lots of numbers you might want to compare two values from different records. Look at the table below, what if you wanted to calculate the increase of the revenue per year?
You could sure do that by using some PL/SQL code, an ugly subselect or some logic in your interface. Before you start doing something like that, have a look at the following example, it might make things a lot simpler and cleaner!
Recipe #1 - oracle analytics functions lag and lead
We start by creating a table which represents the output printed above:
CREATE TABLE revenues (YEAR NUMBER(4), revenue NUMBER(11)); INSERT INTO revenues (YEAR, revenue) VALUES (2009,210000); INSERT INTO revenues (YEAR, revenue) VALUES (2010,235000);
Instead of just selected the field, we add a virtual column which uses the function LAG. Let's have a look at the first step:
SELECT YEAR, revenue, LAG(revenue,1) over(ORDER BY YEAR) previous_revenue FROM revenues ORDER BY YEAR;
If you run the query above, you'll get a result like this:
Once you've got this result it should be pretty easy to come up with the query to calculate the change in percentages. A possible solution is the query below which uses a sub-select:
SELECT YEAR, revenue, ROUND((revenue-previous_revenue)/previous_revenue,3) change FROM ( SELECT YEAR, revenue, LAG(revenue,1) over(ORDER BY YEAR) previous_revenue FROM revenues ORDER BY YEAR );
Which is going to print this result:
0.119 means of course 11.9%.
Just make sure you don't have a revenue of 0 ;-)
Background Information #1
The functions lead and lag were introduced in Oracle 8.1.6.
Both functions have the same parameters:
- expression: A column or expression
- offset [optional]: The number of rows you want to go backwards/forward
- default [optional]: If you're on the first row, there's no previous value. By default you'll get NULL, if that doesn't suit your needs, specify the value by using this parameter.