Sunday, October 25, 2015

SQL – LEAD function

LEAD function was introduced in SQL Server 2012 in the group of analytics functions which is very helpful to make analytics in T-SQL a possibility and would add some value from a BI perspective. LEAD function is used for accessing subsequent (or columns from the subsequent row) rows along with the current row which could make certain operations which done in a multi-step fashion be more efficient.
The basic fundamental of LEAD function, accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2012. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

How to use LEAD function
Lead function supports non-deterministic nature. Non-deterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. By using below syntax, we can use this function where want to use.
LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Arguments
scalar_expression
Within this parameter, we can specify a scalar expression or column name whose value from the subsequent row is to be returned.
Scalar_expression cannot be an analytic function.

Offset, default
Within this parameter, we can specify an offset to access not only the next immediate row but any row after the current row. Its default value of 1 accesses the next immediate row whereas a value of 3 accesses the third row from the current row.

OVER ( [ partition_by_clause ] order_by_clause)
Partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. Order_by_clause determines the order of the data before the function is applied.

Return Types
The data type of the specified scalar_expression. NULL is returned if scalar_expression is nullable or default is set to NULL.

LEAD functions are flexible to let us specify the number of rows to move forward from the current row with the offset parameter. To better understand this analytical function, we can take an example to calculation daily performance of any stock where we need to calculate the current value divided by Next day’s value as shown below:

Performance= (Current Day Value/Next Day’s Value)-1

Now, we need to get the Next day’s value and LEAD function is capable to fulfill this requirement but first of all we need to know about our data table and position of the actual data into the table before using LEAD Function.

USE TEMPDB
GO

---- Declare Table variable for Daily Stock Values
DECLARE @TableStock Table
(
StockId int,
StockName Varchar(20),
StockDate Date,
StockValue real
)
---- Insert Values in the stock Table variable
INSERT INTO @TableStock (StockId, StockName, StockDate, StockValue)
VALUES
(101, 'StockName S1', '2015-10-23', '546.56'),
(101, 'StockName S1', '2015-10-22', '544.22'),
(101, 'StockName S1', '2015-10-21', '543.50'),
(101, 'StockName S1', '2015-10-20', '544.75'),
(101, 'StockName S1', '2015-10-19', '543.78')

---- Values in the Table Variable
SELECT StockId, StockName, StockDate, StockValue FROM @TableStock
StockId
StockName
StockDate
StockValue
101
StockName S1
10/23/2015
546.56
101
StockName S1
10/22/2015
544.22
101
StockName S1
10/21/2015
543.50
101
StockName S1
10/20/2015
544.75
101
StockName S1
10/19/2015
543.78



Get previous day’s value with the help of LAG Function as given below:

---- USE CTE to get the Next Day's Stock Value
;WITH CTE AS
(
SELECT StockId, StockName, StockDate, StockValue,
---- LEAD Function to get Next Day's Value
NextDayValue=LEAD(StockValue,1,0) Over (ORDER BY StockDate)
FROM @TableStock
)

----- Values in CTE table
SELECT StockId,
StockName, StockDate,
CurrentValue=StockValue,
NextDayValue FROM CTE
ORDER BY StockDate DESC;

StockId
StockName
StockDate
CurrentValue
NextDayValue
101
StockName S1
10/23/2015
546.56
0.00
101
StockName S1
10/22/2015
544.22
546.56
101
StockName S1
10/21/2015
543.50
544.22
101
StockName S1
10/20/2015
544.75
543.50
101
StockName S1
10/19/2015
543.78
544.75


Get the Daily performance now as shown below:

---- USE CTE to get the Next Day's Stock Value
;WITH CTE AS
(
SELECT StockId, StockName, StockDate, StockValue,
---- LEAD Function to get Next Day's Value
NextDayValue=LEAD(StockValue,1,0) Over (ORDER BY StockDate)
FROM @TableStock
)

----- Performance Calculation in CTE table
SELECT StockId,
StockName, StockDate,
CurrentValue=StockValue,
NextDayValue,
---- If Next day value is 0 then set current value
Performance=(StockValue/ (Case when NextDayValue=0 then StockValue else NextDayValue end) )-1
FROM CTE
ORDER BY StockDate DESC;

StockId
StockName
StockDate
CurrentValue
NextDayValue
Performance
101
StockName S1
10/23/2015
546.56
0.00
0.000000
101
StockName S1
10/22/2015
544.22
546.56
-0.004281
101
StockName S1
10/21/2015
543.50
544.22
-0.001323
101
StockName S1
10/20/2015
544.75
543.50
0.002300
101
StockName S1
10/19/2015
543.78
544.75
-0.001781

With the help of the LEAD function, we can get any next values for day, month, year or anything which you want to use in you analytics calculations.
Query at a Glance

USE TEMPDB
GO
---- Declare Table variable for Daily Stock Values
DECLARE @TableStock Table
(
StockId int,
StockName Varchar(20),
StockDate Date,
StockValue real
)

---- Insert Values in the stock Table variable
INSERT INTO @TableStock (StockId, StockName, StockDate, StockValue)
VALUES
(101, 'StockName S1', '2015-10-23', '546.56'),
(101, 'StockName S1', '2015-10-22', '544.22'),
(101, 'StockName S1', '2015-10-21', '543.50'),
(101, 'StockName S1', '2015-10-20', '544.75'),
(101, 'StockName S1', '2015-10-19', '543.78')

---- Values in the Table Variable
SELECT StockId, StockName, StockDate, StockValue FROM @TableStock

---- USE CTE to get the Next Day's Stock Value
;WITH CTE AS
(
SELECT StockId, StockName, StockDate, StockValue,
---- LEAD Function to get Next Day's Value
NextDayValue=LEAD(StockValue,1,0) Over (ORDER BY StockDate)
FROM @TableStock
)

----- Performance Calculation in CTE table
SELECT StockId,
StockName, StockDate,
CurrentValue=StockValue,
NextDayValue,
---- If Next day value is 0 then set current value
Performance=(StockValue/ (Case when NextDayValue=0 then StockValue else NextDayValue end) )-1
FROM CTE
ORDER BY StockDate DESC;



So, LEAD function can really help in looking at after records to compute records differences from a single T-SQL statement that is more readable and logical than previous methods for gathering this information. Learn more on another features of SQL as:

Popular Posts