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
|
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;
|
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;
|
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