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
|
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;
|
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;
|
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;
|
- SQL - Stored Procedures
- SQL - Create Stored Procedure
- SQL - Execute Stored Procedure
- SQL - Alter Stored Procedure
- SQL - Delete Stored Procedure
- SQL - Views
- SQL - Stored Procedures Vs Ad-Hoc (In-Line) Queries
- SQL - Stored Procedures Vs Functions
- SQL - Stored Procedures Vs Triggers
- SQL - Merge Statement
- SQL - Functions
- SQL - Triggers
- SQL - Indexes
- SQL - Cursors
No comments:
Post a Comment