Oracle Analytic Functions – Part II

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

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *