SQL

Access data from previous record


Problem

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?

Year Revenue Change %
2009 210 000 ?
2010 235 000 ?

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!


Solution

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:

Year Revenue Previous_Revenue
2009 210000 -
2010 235000 210000

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:

Year Revenue Change
2009 210000 -
2010 235000 .119

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.

Comments




Please sign-in to post a comment