Advertisement Space

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 some revenue data:

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 selecting the field, we add a virtual column which uses the function LAG:

SELECT YEAR, revenue,
       LAG(revenue, 1) OVER (ORDER BY YEAR) previous_revenue
FROM revenues
ORDER BY YEAR;

Once you've got this result it should be pretty easy to come up with the query to calculate the change in percentages:

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
);

0.119 means of course 11.9%. Just make sure you don't have a revenue of 0 ;-)

Background Information

The functions LAG and LEAD were introduced in Oracle 8.1.6. Both functions have the same parameters: