SQL Server LAG function is a Analytics Functions that provides access to a row at a given physical offset that comes before the current row.
Basically, It is used in a SELECT statement to compare values in the current row with values in a previous row.
SYNTAX
LAG ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression
Value to be returned based on the specified offset. Itย cannot be an analytic function.
offset
The number of rows back from the current row from which to obtain a value.
By default it is 1. offset should be a positive value of type integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.
default
Value to return when offset is beyond the scope of the partition. If a default value is not specified, NULL is returned. 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. Whenย partition_by_clauseย is specified, it determines the order of the data in each partition. Theย order_by_clause is required.
Lets see, how does it works in SQL Server, To demonstrate this we create a sample table – SalesTrack which keeps a monthly sales records of products.
Create table SalesTrack ( SalesId Int identity(1,1) , ProductId Int , SalesAmount Numeric(9,2), SalesDate Date)
Now we insert some sales records into SalesTrack table.
Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,56743,'2018/01/15'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,67463,'2018/02/18'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,40000,'2018/03/19'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,89000,'2018/04/30'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,78000,'2018/05/22'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,23500,'2018/06/19'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,58800,'2018/07/19'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5002,78743,'2018/01/15'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5002,37793,'2018/03/14'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5002,40570,'2018/04/19'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5002,45870,'2018/05/20'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5001,90890,'2018/06/22'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5002,46570,'2018/07/29'); Insert into SalesTrack(ProductId, SalesAmount,SalesDate) Values (5002,87800,'2018/08/15');
Using Lag Function to compare values between months
Following query uses the Lag function to return the difference in sales amounts for a specific products over previous months.
SELECT ProductId,Month(SalesDate) AS SalesMonth, Year(SalesDate)AS SalesYear, SalesAmount AS SalesAmount, Lag( SalesAmount , 1)OVER (ORDER BY Month(SalesDate)) AS PreviousMonthSalesAmount FROM SalesTrack WHERE ProductId =5001
As we see, there is no lag value available for the first row so the default value null is returned.
We can provide custom value if no lag value available by passing third argument value into Lag() function as given below.
SELECT ProductId,Month(SalesDate) AS SalesMonth, Year(SalesDate)AS SalesYear, SalesAmount AS SalesAmount, Lag( SalesAmount , 1,0)OVER (ORDER BY Month(SalesDate)) AS PreviousMonthSalesAmount FROM SalesTrack WHERE ProductId =5001
Using Lead() Function to compare values within Partition By clause
The following query uses the Lag function to compareย month wise sales between products.
SELECT ProductId,Month(SalesDate) AS SalesMonth, Year(SalesDate)AS SalesYear, SalesAmount AS SalesAmount, Lag(SalesAmount, 1) OVER(PARTITION BY ProductId ORDER BY Month(SalesDate)) AS NextMonthSalesAmount FROM SalesTrack
Also Read..
1,751 total views, 1 views today