ROWS PRECEDING and FOLLOWING in TSQL 2012
March 25, 2012 3 Comments
First for this example, and a few to follow we need to create the database that we are going to play around in.
USE [Master]; CREATE DATABASE [tsql2012]; GO USE [tsql2012]; -- Table to be used by Over Clause Rows/Range CREATE TABLE REVENUE ( [DepartmentID] int, [Revenue] int, [Year] int ); insert into REVENUE values (1,10030,1998),(2,20000,1998),(3,40000,1998), (1,20000,1999),(2,60000,1999),(3,50000,1999), (1,40000,2000),(2,40000,2000),(3,60000,2000), (1,30000,2001),(2,30000,2001),(3,70000,2001), (1,90000,2002),(2,20000,2002),(3,80000,2002), (1,10300,2003),(2,1000,2003), (3,90000,2003), (1,10000,2004),(2,10000,2004),(3,10000,2004), (1,20000,2005),(2,20000,2005),(3,20000,2005), (1,40000,2006),(2,30000,2006),(3,30000,2006), (1,70000,2007),(2,40000,2007),(3,40000,2007), (1,50000,2008),(2,50000,2008),(3,50000,2008), (1,20000,2009),(2,60000,2009),(3,60000,2009), (1,30000,2010),(2,70000,2010),(3,70000,2010), (1,80000,2011),(2,80000,2011),(3,80000,2011), (1,10000,2012),(2,90000,2012),(3,90000,2012);
Next a review of the OVER clause as it is support prior to SQL Server 2012.
--First OVER Clause pre SQL 2012 -- http://stevestedman.com/?p=1454 select *, avg(Revenue) OVER (PARTITION by DepartmentID) as AverageRevenue, sum(Revenue) OVER (PARTITION by DepartmentID) as TotalRevenue from REVENUE order by departmentID, year;
Which produces the following output. Which gives you the average revenue and sum or the revenue for each department. In the output you will see that the AverageRevenue and TotalRevenue are the same for the entire range of the departmentID.
Introducing ROWS in the OVER clause which has been called Windowing Functions, or running or sliding aggregates.
With the addition of ROWS in the over clause, you can either specify to look at ROWS before (PRECEDING) or ROWS after (FOLLOWING), and you can specify the number of ROWS PRECEDING or ROWS FOLLOWING as shown in the following TSQL.
--ROWS PRECEDING select Year, DepartmentID, Revenue, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as Prev3 from REVENUE order by departmentID, year;
In this example as shown in the output below, you can see the in row 1, the prev3 column shows 10030, or the just the same thing as the current row. In the second row it shows the sum of Revenue from row 1 and row 2. For row 3 Prev3 shows the total of 70030 which is the sum of revenue from rows 1 to 3. Finally Row4 Prev3 shows the current row (4) plus the sum of the previous 3, or a total of 100030. Now on to row five. Row 5, prev3 shows 180000 which is the revenue from row 5, row 4, row 3, row2, but not row1.
Now for following, very similar to preceding, but in the other direction.
</pre> -- ROWS FOLLOWING select Year, DepartmentID, Revenue, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) as Next3 from REVENUE order by departmentID, year; <pre>
The ROWS PRECEDING and ROWS FOLLOWING options in the OVER clause in TSQL 2012 give an easy way to calculate sliding window aggregates in your TSQL code.