As this is the second part of my series about Oracle’s analytic functions I would like to step ahead with two more functions:
- LEAD-function
- LAG-function
Sometimes you need to access the preceding or following data row. In these cases you can use the above functions. Let’s use the following table to build some examples:
| MONTH | QTY | TURNOVER | |
| 1 | 201211 | 3 | 125 |
| 2 | 201212 | 5 | 500 |
| 3 | 201301 | 1 | 100 |
| 4 | 201302 | 2 | 150 |
| 5 | 201303 | 3 | 250 |
| 6 | 201304 | 4 | 450 |
Fig.1: TBL_SALES
If you want to show the turnover of every month compared with the turnover of the month before you can use the Lag-Function. The general syntax for both functions is the following:
Lead/Lag (<Value>, <No of Rows>, <Default>) Over (Partition by … Order by ….)
Please note that you have to use the order by clause for these two functions. Lead returns the following row and Lag the preceding row. <Value> describes the Column of which the following or preceding value is returned. <No of rows> sets how many rows in front or back the system should look for and return a value. If there isn’t a value available (within the partition) then the <default> value is returned.
Let’s develop the first example:
SELECT MONTH,
TURNOVER,
LAG(TURNOVER,1,-1) OVER (ORDER BY MONTH ASC) AS TURNOVER_2
FROM TBL_SALES
The result should look like this:
MONTH TURNOVER TURNOVER_2
——————————————————-
201211 125 -1
201212 500 125
201301 100 500
201302 150 100
201303 250 150
201304 450 250
As you can see the TURNOVER_2 column contains the value of the turnover column of the row before. The very first row of our little example TURNOVER_2 is -1 cause there is no row before and because of this a value can’t be found and the -1 is returned instead. If you wouldn’t define a default value then NULL is returned.
Now we change our statement a little:
SELECT MONTH,
TURNOVER,
LAG(TURNOVER,1,-1) OVER (PARTITION BY substr(MONTH,1,4)
ORDER BY MONTH ASC)
AS TURNOVER_2
FROM TBL_SALES
The result looks this way:
MONTH TURNOVER TURNOVER_2
——————————————————
201211 125 -1
201212 500 125
201301 100 -1
201302 150 100
…
The difference is the partition by clause. That means that the function is only used within the partitions we have defined. In our case we have partitioned our data by years. To do so we use the function substr on the column MONTH. Now the DBMS uses the LAG function only within the corresponding year. That means for 2012 the first row in our table is the 201211. We have a -1 there. The next row is the second row within the partition. So we see the value of the row before there. The third row in our table is the 201301. It’s a new partition because substr(201301,1,4)=2013 and that is different from 2012. So it starts again and the first row of this partition (i.e. the third row in the table) is -1 again.
I hope the concept of partitioning became clearer with this issue too. Have fun experimenting with the two new functions.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!
