Saturday, October 24, 2015

SQL – LAG function

SQL – LAG function
LAG function was introduced in SQL Server 2012 as an analytics function which is very helpful to make analytics in T-SQL a possibility and would add some value from a BI perspective. LAG function is one of them an analytical function which could make certain operations which done in a multi-step fashion be more efficient.

The basic fundamental of LAG function, accesses data from a previous 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 LAG function         
By using below syntax, we can use this function where want to use.
LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Arguments
scalar_expression
The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value.
scalar_expression cannot be an analytic function.

offset
The number of rows back from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default
The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

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.

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 previous day’s value as shown below:

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

Now, we need to get the previous day’s value and LAG 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 LAG 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 Previous Day's Stock Value
;WITH CTE AS
(
SELECT StockId, StockName, StockDate, StockValue,
---- LAG Function to get previous Day's Value
LastDayValue=LAG(StockValue,1,0) Over (ORDER BY StockDate)
FROM @TableStock
)

----- Values in CTE table
SELECT StockId,
StockName, StockDate,
CurrentValue=StockValue,
LastDayValue
FROM CTE
ORDER BY StockDate DESC;
StockId
StockName
StockDate
CurrentValue
LastDayValue
101
StockName S1
10/23/2015
546.56
544.22
101
StockName S1
10/22/2015
544.22
543.50
101
StockName S1
10/21/2015
543.50
544.75
101
StockName S1
10/20/2015
544.75
543.78
101
StockName S1
10/19/2015
543.78
0.00


Get the Daily performance now as shown below: 
---- USE CTE to get the Previous Day's Stock Value
;WITH CTE AS
(
SELECT StockId, StockName, StockDate, StockValue,
---- LAG Function to get previous Day's Value
LastDayValue=LAG(StockValue,1,0) Over (ORDER BY StockDate)
FROM @TableStock
)

----- Performance Calculation in CTE table
SELECT StockId,
StockName,
StockDate,
CurrentValue=StockValue,
LastDayValue,
---- If previous day value is 0 then set current value
Performance=(StockValue/ (Case when LastDayValue=0 then StockValue else LastDayValue end) )-1

FROM CTE
ORDER BY StockDate DESC;
StockId
StockName
StockDate
CurrentValue
LastDayValue
Performance
101
StockName S1
10/23/2015
546.56
544.22
0.004300
101
StockName S1
10/22/2015
544.22
543.50
0.001325
101
StockName S1
10/21/2015
543.50
544.75
-0.002295
101
StockName S1
10/20/2015
544.75
543.78
0.001784
101
StockName S1
10/19/2015
543.78
0.00
0.000000



With the help of the LAG function, we can get any previous 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 Previous Day's Stock Value
;WITH CTE AS
(
SELECT StockId, StockName, StockDate, StockValue,
---- LAG Function to get previous Day's Value
LastDayValue=LAG(StockValue,1,0) Over (ORDER BY StockDate)
FROM @TableStock
)
----- Performance Calculation in CTE table
SELECT StockId,
StockName, StockDate,
CurrentValue=StockValue,
LastDayValue,
---- If previous day value is 0 then set current value
Performance=(StockValue/ (Case when LastDayValue=0 then StockValue else LastDayValue end) )-1
FROM CTE
ORDER BY StockDate DESC;
So, LAG 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:


No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands