Saturday, September 24, 2016

Oracle SQL - LEAD and LAG - Analytical Functions



Introduction

Both LAG and LEAD functions have the same usage, as shown below.

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

value_expression :- Can be a column or a built-in function, except for other analytic functions.
offset :- The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
default :- The value returned if the offset is outside the scope of the window. The default value is NULL.


Looking at the EMP table, we query the data in salary (SAL) order.

SELECT empno,
       ename,
       job,
       sal
FROM   emp
ORDER BY sal;




SQL>

LAG

The LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;
























SQL>

LEAD

The LEAD function is used to return data from the next row. The following query returns the salary from the next row to calulate the difference between the salary of the current row and the following row.

SELECT empno,
       ename,
       job,
       sal,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM   emp;























SQL>

2 comments :

  1. Hi There,


    Hip Hip Hooray! I was always told that slightly slow in the head, a slow learner. Not anymore! It’s like you have my back. I can’t tell you how much I’ve learnt here and how easily! Thank you for blessing me with this effortlessly ingestible digestible content.

    Is there a way to create attribute under Date Time dimension to control only top 15 weeks?
    My requirement is to allow user to see the selected measures value for only 15 weeks by selecting Top15Weeks attribute from Date Time dimension.
    Please let me know the approach to create attribute under Date Time dimension.


    I am so grateful for your blog. Really looking forward to read more.


    Kind Regards,
    Ishika

    ReplyDelete
  2. Hi There,

    Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on Oracle SQL - LEAD and LAG - Analytical Functions .

    I have data in a column
    #abcd#efghi#jlmn#opqrs

    I need to retrieve values between '#'. I need to get output as below

    var1 = abcd
    var2 = efghi
    var3 = jlmn
    var4 = opqrs

    Similary if column value is

    #abcd#efghi#jlmnopqrs

    var1 = abcd
    var2 = efghi
    var3 = jlmnopqrs
    var4 = null

    But nice Article Mate! Great Information! Keep up the good work!

    Thanks and Regards
    Manju

    ReplyDelete