SQL Server 2012 Analytical Functions: Lead and Lag

SQL Server 2012 Analytical Functions: Lead and Lag

SQL Server 2012 supports many T-SQL analytic functions which are listed below:

• LEAD: Accesses data from a subsequent row in the same result set without the use of a self-join.
• LAG: Accesses data from a previous row in the same result set without the use of a self-join.
• CUME_DIST: Calculates the cumulative distribution of a value in a group of values. In other words, CUME_DIST computes the relative position of a specified value in a group of values
• FIRST_VALUE: Returns the first value in an ordered set of values.
• LAST_VALUE: Returns the last value in an ordered set of values.
• PERCENTILE_CONT: Calculates a percentile based on a continuous distribution of the column.
• PERCENTILE_DISC: Computes a specific percentile for sorted values in an entire rowset or within distinct partitions of a rowset.
• PERCENT_RANK: Calculates the relative rank of a row within a group of rows.

Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, they can return multiple rows for each group. You can use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

In this article, we are going to focus only on Lead and Lag functions.

Lead function accesses data from a subsequent row and Lag function accesses from previous row in the same result set without the use of a self-join.

Syntax:

LEAD | Lag ( scalar_expression [ ,offset ] , [ default ] )
    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression is the value to be returned based on the specified offset. Offset specify the number of rows forward/backward from the current row from which to obtain a value. Default is the value to return when scalar_expression at offset is NULL.

Now lets explorer Lead function with an example:

Problem: Compare values between current year, previors year, and next year.

---------------------

-- Prepare Test Data

---------------------

DECLARE @TestData TABLE (Period DATE, Amount INT

INSERT @TestData

SELECT '2006-12-31' Period, 1000 Amount UNION

SELECT '2007-12-31' Period, 3000 Amount UNION

SELECT '2008-12-31' Period, 5000 Amount UNION

SELECT '2009-12-31' Period, 4000 Amount UNION

SELECT '2010-12-31' Period, 7000 Amount UNION

SELECT '2011-12-31' Period, 6000 Amount UNION

SELECT '2012-12-31' Period, 8000 Amount

 

----------------------------------------------------------

-- Fetch current year, previous year, and next year data

----------------------------------------------------------

SELECT Period
,Amount AS CurrentAmount

,LAG(Amount, 1, 0) OVER (ORDER BY Period) AS PreviousAmount

,LEAD(Amount, 1, 0) OVER (ORDER BY Period) AS NextAmount

FROM @TestData

 

 

 

 

 

 

Note that in the above we have set the default vaule 0 for Lead and Lag functions so we are getting zero (0) instead of NULL. Also we have set the offset to 1 for lead and lag function so we are getting only subsequent  and previous values. Offset can be set as per requirement but in most of the cases we use 1.

For more information on Analytical Functions, please click here.