SQL Server LAG() function Overview
At many instances, user would like to access data of the previous row or any row before the previous row from the current row. To solve this problem SQL Server’s LAG() window function can be used. LAG() : SQL Server provides LAG() function which is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. The previous value can be returned on the same record without the use of self join making it straightforward to compare.
Syntax:
LAG (scalar_expression [, offset [, default ]])
OVER ( [ partition_by_clause ] order_by_clause )
Where:
- scalar_expression – The value to be returned based on the specified offset.
- offset – The number of rows back from the current row from which to obtain a value. If not specified, the default is 1.
- default – default is the value to be returned if offset goes beyond the scope of the partition. If a default value is not specified, NULL is returned.
- partition_by_clause: An optional clause that divides the result set into partitions. The LAG() function is applied to each partition separately.
- order_by_clause: The order of the rows within each partition. This is mandatory and must be specified.
Example-1:
SELECT Organisation, [Year], Revenue, LAG (Revenue, 1, 0) OVER (PARTITION BY Organisation ORDER BY [Year]) AS PrevYearRevenue FROM Org ORDER BY Organisation, [Year];
Output:
| Organisation | Year | Revenue | PrevYearRevenue |
|---|---|---|---|
| ABCD News | 2013 | 440000 | 0 |
| ABCD News | 2014 | 480000 | 440000 |
| ABCD News | 2015 | 490000 | 480000 |
| ABCD News | 2016 | 500000 | 490000 |
| ABCD News | 2017 | 520000 | 500000 |
| ABCD News | 2018 | 525000 | 520000 |
| ABCD News | 2019 | 540000 | 525000 |
| ABCD News | 2020 | 550000 | 540000 |
| Z News | 2016 | 720000 | 0 |
| Z News | 2017 | 750000 | 720000 |
| Z News | 2018 | 780000 | 750000 |
| Z News | 2019 | 880000 | 780000 |
| Z News | 2020 | 910000 | 880000 |
In the above example, We have 2 TV News Channel whose Current and Previous Year’s Revenue is presented on the same row using the LAG() function. As You can see that the very first record for each of the TV News channels don’t have previous year revenues so it shows the default value of 0. This function can be very useful in yielding data for BI reports when you want to compare values in consecutive periods, for e.g. Year on Year or Quarter on Quarter or Daily Comparisons.
Example-2:
SELECT Z.*, (Z.Revenue - z.PrevYearRevenue) as YearonYearGrowth
from (SELECT Organisation, [Year], Revenue,
LAG (Revenue, 1)
OVER (PARTITION BY Organisation ORDER BY [Year] ) AS PrevYearRevenue
FROM Org) Z ORDER BY Organisation, [Year];
Output:
| Organisation | Year | Revenue | PrevYearRevenue | YearOnYearGrowth |
|---|---|---|---|---|
| ABCD News | 2013 | 440000 | NULL | NULL |
| ABCD News | 2014 | 480000 | 440000 | 40000 |
| ABCD News | 2015 | 490000 | 480000 | 10000 |
| ABCD News | 2016 | 500000 | 490000 | 10000 |
| ABCD News | 2017 | 520000 | 500000 | 20000 |
| ABCD News | 2018 | 525000 | 520000 | 5000 |
| ABCD News | 2019 | 540000 | 525000 | 15000 |
| ABCD News | 2020 | 550000 | 540000 | 10000 |
| Z News | 2016 | 720000 | NULL | NULL |
| Z News | 2017 | 750000 | 720000 | 30000 |
| Z News | 2018 | 780000 | 750000 | 30000 |
| Z News | 2019 | 880000 | 780000 | 100000 |
| Z News | 2020 | 910000 | 880000 | 30000 |
In the above example, We can similarly calculate Year On Year Growth for the TV News Channel. Also, one thing to notice in this example is we haven’t supplied any default parameter to LAG(), and hence the LAG() function returns NULL in case there are no previous values. The LAG() function can be implemented at the database level and BI Reporting solutions like Power BI and Tableau can avoid using the cumbersome measures at the reporting layer.
Please Login to comment...