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 :
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 :
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.