SQL Server – Window Offset Functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE)

SQL Server 2012 has introduced Window offset functions. These functions can be divided into two groups:

  • Functions that allow us to return a value from a row that is in a specific offset from the current row (LAG and LEAD functions). NOTE: The default offset is 1. In other words, the default offset is the previous row when using LAG or the next row when using LEAD.
  • Functions that allow us to return a value from the first or last row in a certain window frame (FIRST_VALUE and LAST_VALUE functions).

EXAMPLES

DECLARE @AnnualSales table (
state CHAR(2),
year varchar(4),
total money);

INSERT INTO @AnnualSales (state,year,total)
VALUES
('CA','2010',20540),
('CA','2011',15700),
('CA','2012',5980),
('CA','2013',12800),
('CA','2014',13600),
('CA','2015',14200),
('NY','2010',7850),
('NY','2011',11830),
('NY','2012',18150),
('NY','2013',21430),
('NY','2014',19250),
('NY','2015',16300);
 
LAG – default arguments
 
SELECT
state,
year,
total,
LAG(total) OVER (PARTITION BY state ORDER BY year) AS 'LAG (year-1)'
FROM @AnnualSales
ORDER BY state, year;
 
window_functions_lag_01

The LAG offset is set to 1 by default. When there is not value for the lag, the default value is NULL.

LAG – defining arguments
 
SELECT
state,
year,
total,
LAG(total,2,0) OVER (PARTITION BY state ORDER BY year) AS 'LAG (year-2)'
FROM @AnnualSales
ORDER BY state, year;
 
 window_functions_lag_02

The LAG offset is set to 2. When there is not value for the lag, NULL is replaced with 0.

LEAD – default arguments
 
SELECT
state,
year,
total,
LEAD(total) OVER (PARTITION BY state ORDER BY year) AS 'LEAD (year+1)'
FROM @AnnualSales
ORDER BY state, year;
 
window_functions_lead_01

The LEAD offset is set to 1 by default. When there is not value for the lead, the default value is NULL.

LEAD – defining arguments
 
SELECT
state,
year,
total,
LEAD(total,2,0) OVER (PARTITION BY state ORDER BY year) AS 'LEAD (year+2)'
FROM @AnnualSales
ORDER BY state, year;
 
window_functions_lead_02

The LEAD offset is set to 2. When there is not value for the lead, NULL is replaced with 0.

FIRST_VALUE and LAST_VALUE

SELECT
state,
year,
total,
FIRST_VALUE(total) OVER (PARTITION BY state ORDER BY year
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'FIRST_VALUE',
LAST_VALUE(total) OVER (PARTITION BY state ORDER BY year
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 'LAST_VALUE'
FROM @AnnualSales
ORDER BY state, year;

window_functions_first_value_last_value

The first value found in CA is 20540,00. The last value found in CA is 14200,00. The first value found in NY is 7850,00. The last value found in NY is 16300,00.

 

Advertisements

2 thoughts on “SQL Server – Window Offset Functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s