Rows and Range, Preceding and Following

Download PDF

SQL Server 2012 adds many new features to Transact SQL (T-SQL).  One of my favorites is the Rows/Range enhancements to the over clause. These enhancements are often times referred to as the windowing functions.

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;

rows_range_preceding_following1
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;

rows_range_preceding_following2

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_range_preceding_following3

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_range_preceding_following4

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;

rows_range_preceding_following5

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;

rows_range_preceding_following6

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;

rows_range_preceding_following7

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_range_preceding_following8

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;

rows_range_preceding_following9

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;

rows_range_preceding_following10

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.

Tagged with: , , , , , , , , , , ,
6 comments on “Rows and Range, Preceding and Following
  1. Matt Trangmar says:

    Best article yet on ROWS and RANGE with SQL Windowing functions

  2. Rutuja Vamanrao Nawale says:

    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}}

  3. want the solution for SUM(units) over(order by units ROWS BETWEEN unbounded preceding and unbounded following in sql server 2008

  4. Nasmi says:

    So are Lead/Lag functions a syntactic sugar over these window functions. They seem to be achieving the same result?

    • SteveStedman SteveStedman says:

      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

  5. Radek Krizka says:

    Thank you Steve for the article, it helped me to easily solve a problem I had.

1 Pings/Trackbacks for "Rows and Range, Preceding and Following"
  1. […]  Bert’s post doesn’t cover window ranges and sizes, as the defaults work for him, but Steve Stedman has a good post on the topic if you want more […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.