Rows and Range, Preceding and Following
SQL Server 2012 adds many new features to Transact SQL (T-SQL). One of my favorites is the Rows and Range enhancements to the over clause. These enhancements are often times referred to as the windowing functions.
Want to learn more about the windowing functions? Check out this aggregation class that I have created:
Overview:
ROWS PRECEDING, FOLLOWING, UNBOUNDED, refers to the current row and those before or after based on preceding or following.
RANGE PRECEDING, FOLLOWING, UNBOUNDED, means all values in the current range and those before or after.
An example of a need for rows preceding would be to calculate a 3 year trailing average, which needs to look at the current year and the three previous years in the calculation.
Terminology:
ROWS or RANGE- specifying rows or range.
PRECEDING – get rows before the current one.
FOLLOWING – get rows after the current one.
UNBOUNDED – when used with PRECEDING or FOLLOWING, it returns all before or after.
CURRENT ROW
To start out we need a database to work on, so we will create a tsql2012 database. you can use your own database if you wish.
CREATE DATABASE [tsql2012]; GO USE [tsql2012];
Next we create a table to use for the over clause enhancments of rows and range preceding and following.
-- 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);
Take a look at the revenue table to see whats there before we start the demo.
USE [tsql2012]; -- first lets look at the REVENUE table SELECT * FROM Revenue;
Contents of the REVENUE table.
Then a quick review of sum and avg.
-- first simple sum and avg aggregates SELECT sum(Revenue) as TotalRevenue, avg(Revenue) as AverageRevenue, count(*) as NumRows FROM Revenue;
OVER Clause Pre SQL Server 2012
The OVER clause before SQL Server 2012 is really handy to perform aggregates over a different range than your standard grouping.
--First OVER Clause pre SQL 2012 SELECT *, avg(Revenue) OVER (PARTITION by DepartmentID) as AverageDeptRevenue, sum(Revenue) OVER (PARTITION by DepartmentID) as TotalDeptRevenue FROM REVENUE ORDER BY departmentID, year;
ROWS PRECEDING
Now the new features, ROWS PRECEDING specifies the the aggregate functions in the current partition in the OVER clause will consider the current row, and a specific number of rows before the current row.
--ROWS PRECEDING -- look at the sum of revenue over a trailing 3 year period SELECT Year, DepartmentID, Revenue, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as CurrentAndPrev3 FROM REVENUE ORDER BY departmentID, year;
ROWS FOLLOWING
Now rows following… The ROWS FOLLOWING option specifies a specific number of rows in the current partition to use after the current row.
-- ROWS FOLLOWING SELECT Year, DepartmentID, Revenue, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) as CurrentAndNext3 FROM REVENUE ORDER BY departmentID, year;
Both PRECEDING and FOLLOWING
Using both ROWS PRECEDING and ROWS FOLLOWING allows you to do things like calculate an average including the current year and years both before and after the current year.
--ROWS PRECEDING AND FOLLOWING SELECT Year, DepartmentID, Revenue, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as BeforeAndAfter FROM REVENUE ORDER BY departmentID, year;
UNBOUNDED
UNBOUNDED PRECEDING tells the windowing function and aggregrates to use the current value, and all values in the partition before the current value.
-- ROWS UNBOUNDED PRECEDING SELECT Year, DepartmentID, Revenue, min(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS UNBOUNDED PRECEDING) as MinRevenueToDate FROM REVENUE ORDER BY departmentID, year;
When using UNBOUNDED FOLLOWING as follows, this means the use the current row, and consider all rows after the current row in the current partition.
-- ROWS UNBOUNDED FOLLOWING -- http://stevestedman.com/?p=1485 SELECT Year, DepartmentID, Revenue, min(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as MinRevenueBeyond FROM REVENUE ORDER BY departmentID, year;
ROWS vs RANGE
ROWS vs RANGE appears to cause much confusion.
ROWS means the specific row or rows specified, and RANGE refers to those same rows plus any others that have the same matching values.
Here is an example of ROWS and RANGE producing the exact same results because the years and department IDs don’t have any duplicates. In this case the ROWS and RANGE are identical.
-- ROWS vs RANGE UNBOUNDED PRECEDING SELECT Year, DepartmentID, Revenue, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS UNBOUNDED PRECEDING) as RowsCumulative, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] RANGE UNBOUNDED PRECEDING) as RangeCumulative FROM REVENUE WHERE year between 2003 and 2008 ORDER BY departmentID, year;
Now if we consider the same query of ROWS vs RANGE with duplicate values. Here we insert a duplicate set of values for 2005, and the results below show that for the year 2005 there are two rows in the result set, and the RowsCumulative column is different form the RangeCumulative. The rows counts the specific rows it the aggregate calculation, the range counts the current row, plus the other 2005 rows for that department id.
-- INSERT A DUPLICATE VALUE FOR RANGE UNBOUNDED PRECEEDING INSERT INTO REVENUE VALUES (1,10000,2005),(2,20000,2005),(3,30000,2005); -- same query as above SELECT Year, DepartmentID, Revenue, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] ROWS UNBOUNDED PRECEDING) as RowsCumulative, sum(Revenue) OVER (PARTITION by DepartmentID ORDER BY [YEAR] RANGE UNBOUNDED PRECEDING) as RangeCumulative FROM REVENUE WHERE year between 2003 and 2008 ORDER BY departmentID, year;
Summary
With all the great new features in SQL Server 2012, the windowing functions, ROWS and RANGE, PRECEDING and FOLLOWING updates to the OVER clause are a great addition.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Best article yet on ROWS and RANGE with SQL Windowing functions
actually i am working with SQL server 2008.So some analytical functions aren’t working.Can you please suggest me the another solution for calculating total sum related to {{SUM(units) over(order by units ROWS BETWEEN unbounded preceding and unbounded following}}
want the solution for SUM(units) over(order by units ROWS BETWEEN unbounded preceding and unbounded following in sql server 2008
Rutuja –
You could do that without the windowing function, just using a normal sum.
select sum([Col1])
from [MyTable]
When you use an aggregate with no windowing functions and no group buy it covers the entire data set.
That should work fine in SQL Server 2008, even SQL Server 2005 or 2000 also.
So are Lead/Lag functions a syntactic sugar over these window functions. They seem to be achieving the same result?
I wouldn’t go that far as to as to call lead and lag them syntactic sugar. Sure you could do the equivalent of lead and lag with some very complex queries prior to the feature being added to SQL Server. If you want to call it syntactic sugar, then yes please, pass the sugar.
-Steve
Thank you Steve for the article, it helped me to easily solve a problem I had.
Thanks Steve. Wonderful expo.
Great expo. Thanks!
Great expo Steve. Thanks!
how to sum two value in column like
# purchase
1 : 175
2 : 0
3 : 0
4 : 170
5 : 0
6 : 0
i want sum like
1:175
2:175
3:175
4:170
5:170
6:170
how to get it
you could do that with a LAG function, and an IIF statement. IF IT IS zero, then look back at the previous rows.
I have a question on the rows between current row and 2 following. This 2 is actually dynamic for me based on the value in the table.
SUM(“DMDTO”) OVER (ORDER BY WEEK4 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS DAFETY
I want to actually give it as
SUM(“DMDTO”) OVER (ORDER BY WEEK4 ROWS BETWEEN CURRENT ROW AND S1 FOLLOWING) AS DAFETY
S1 is actually a field in the table and its an integer.
Will I be able to use that variable?
To do what you are asking, I believe you would need to build it as dynamic sql then execute that dynamic sql.
this has been fantastic. thank you very much.
Amazing Article!
Hi Steve,
I want to find min over selected window like from current row to preceding 4 rows. Any alternate solution as min only supports unbounded proceedings.
Ramesh – the MIN function should work just fine. I tried the following example which worked to show the minimum value over a range.
min(len(first_name)) OVER (PARTITION BY [state]
ORDER BY id
ROWS BETWEEN 4 PRECEDING and CURRENT ROW
) as minLen
Good luck.
Hi Steve,
I am using the following ROWS clause in order to calculate the sum of 30 rows before the last 30 rows, and although it does not throw an error, it returns only NULL values:
OVER(PARTITION BY a, b, c ORDER BY a, b, c
ROWS BETWEEN 60 PRECEDING AND 30 PRECEDING)
When I use just ROWS 30 PRECEDING everything works well.
Is this a syntax that is not supported?
Kostas –
I just tried the example similar to what you are doing, with
sum([columnName]) OVER (PARTITION BY [state] ORDER BY [id] ROWS BETWEEN 60 PRECEDING AND 30 PRECEDING)
and it did what close to what you were expecting.
The first 30 rows returned null because for the first 30 there is nothing between 60 and 30 before.
I did not ice that the ROWS BETWEEN 60 PRECEDING AND 30 PRECEDING returns 31 rows, not the 30, so you may need to do between 60 and 31.
Is it possible that your partitioning on a,b,c is causing groups that have less than the 30 rows needed to start the range of 60 to 30 preceeding?
One thing that is really helpful is to ORDER your query by the same results as you are ordering your windowing function. That helped me visualize what I was doing.
Great article Steve!
I tried to implement it Percent Rank() but I am getting an error.
SELECT *,
PERCENT_RANK() OVER (PARTITION BY SYMBOL ORDER BY QUOTE_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS PCT_RANK
FROM DBO.Stock
The error message is
Msg 10752, Level 15, State 3, Line 7
The function ‘PERCENT_RANK’ may not have a window frame.
Completion time: 2021-11-16T13:19:11.5543028-08:00
Is there a way to implement Percent Rank () so it looks back at the previous X rows?
Thanks in advance
that clarifies “rows and range” really well…thank you!