Using the SQL Server 2012 Lead and Lag Functions

Category : Blogs Published : March 1, 2014 User Rating : 5 Stars      Views : 2.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




The LEAD and LAG windows functions were introduced in SQL Server 2012 and have added functionality that would previously have been achieved with a self join.

LEAD and LAG allow you to use values from a previous (or following) row in a resultset. You can use this, for instance, to calculate the difference between the current and previous value of a column. The order of the rows is determined using the 'OVER' clause, so how the previous or following rows are selected can be defined, which typically might be based on a date or ID column. You can also specify an offset to step forwards or backwards by more than one row (the default is one).

By way of example I'll set up a table containing share price values by date, and use the LAG function to obtain the share price for the previous day. This is then used to calculate the day by day change in share price.

The table creation script and SQL to populate the table are below :
CREATE TABLE SharePriceTracker ( ValuationDate DATETIME, SharePrice INT)
GO

INSERT INTO SharePriceTracker (ValuationDate, SharePrice)
SELECT '20140101',23 UNION ALL
SELECT '20140102',26 UNION ALL
SELECT '20140103',25 UNION ALL
SELECT '20140104',30 UNION ALL
SELECT '20140105',30 UNION ALL
SELECT '20140106',31 UNION ALL
SELECT '20140107',25 UNION ALL
SELECT '20140108',24 UNION ALL
SELECT '20140109',25 UNION ALL
SELECT '20140110',28
The table then contains the following data :
Lead and Lag results 1

The following SQL will return both columns in the table, along with two new values, for yesterday's share price and the increase (or decrease) in the share price since yesterday.
SELECT   ValuationDate
        ,SharePrice
        ,LAG(SharePrice) OVER (ORDER BY ValuationDate) AS YesterdaysSharePrice
        ,SharePrice - LAG(SharePrice) OVER (ORDER BY ValuationDate) AS PriceDifference
FROM     SharePriceTracker
ORDER BY ValuationDate
As I mentioned, prior to SQL 2012 this would typically have been implemented with a self join, such as the following SQL, which produces the same resultset :
SELECT    Today.ValuationDate
         ,Today.SharePrice
         ,Yesterday.SharePrice AS YesterdaysSharePrice
         ,Today.SharePrice - Yesterday.SharePrice AS PriceDifference
FROM      SharePriceTracker Today
LEFT JOIN SharePriceTracker Yesterday
ON        Today.ValuationDate = DATEADD(dd,1, Yesterday.ValuationDate)
ORDER BY  Today.ValuationDate
Both queries return the resultset below :
Lead and Lag results 2
As you can see the value of 'YesterdaysSharePrice' is copied from the 'SharePrice' column from the previous day as indicated in the image above.

To extend this a little, if we now need the PriceDifference compared to the previous week rather than the previous day we can use offset clause to step back 7 days rather than 1 day, as follows (note that this does assume that there is one row per day in the table) :
SELECT   ValuationDate
        ,SharePrice
        ,LAG(SharePrice,7) OVER (ORDER BY ValuationDate) AS LastWeeksSharePrice
        ,SharePrice - LAG(SharePrice ,7) OVER (ORDER BY ValuationDate) AS PriceDifference
FROM     SharePriceTracker
ORDER BY ValuationDate

Partition By

The LEAD and LAG functions also include a PARTITION BY clause which mean that the functions can be used on subsets of the data in a record set. I won’t demonstrate that here but it is useful if you want to calculate the LEAD and LAG values on a subset of the data split by some criteria. For instance in the example above if the table contained daily share prices of more than one company (perhaps using a CompanyID column), the data could be partitioned by CompanyID, to show the daily difference in share price for each company.

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Using the SQL Server 2012 Lead and Lag Functions.aspx

Keywords

SQL 2012, Windows Functions, blogs


Comments
Post by anvesh on Wed 08 Jun 2016 21:06. Report Inappropriate Post

Nice Article !
This is my pleasure to read your article.
Really this will help to people of SQL Server Community.

I have also prepared one article about, New LEAD() and LAG() analytics function of SQL Server 2012.
You can also visit my article, your comments and reviews are most welcome.

http://www.dbrnd.com/2016/02/sql-server-2012-introduced-lead-function-the-best-alternative-of-self-join-to-compare-next-row/

http://www.dbrnd.com/2016/02/sql-server-2012-introduced-lag-function-the-best-alternative-of-self-join-to-compare-previous-row/

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered