Dec 072011

Rows|Range enhances partition functionality of Over clause in SQL Server 2012. In the Over clause before SQL 2012, you can group rows with the same attributes together, which is called partition. You can also order the rows within each of partitions, which is implemented by Order By. The aggregation functions in front of Over clause will be are partition and order based. In SQL Server 2012, a lower level “partition”, which is called windows fram, range of rows, can be defined under the partition. A window frame can be static like partition clause but can also be dynamic – moving window. This provides great power on data analytics, for instance, you no longer need joins for calculating moving average, instead, one SQL with moving window can give you the result.

There 3 basic concepts in the window frame definition

    • UNBOUNDED PRECEDING: the first row in the partition
    • # PRECEDING: number of rows prior to current rows
    • UNBOUNDED FOLLOWING: the last row in the partition
    • # FOLLOWING: number of consequent rows to current rows
  • CURRENT ROW: Current row

You can use BETWEEN or just PRECEDING to define a window frame. When the FOLLOWING is absent from the window frame definition, CURRENT ROW is always used for the boundary.

--Use between
--This is a 10 row moving window in the partition Col1
Over(Partition By Col1 Order By Col2 Rows Between 9 PRECEDING and Current row) 

--An average over an moving window is called moving average
--example below is an 10 row moving everage
avg(Col3) Over(Partition By Col1 Order By Col2 Rows Between 9 PRECEDING and Current row) as Moving Average

--This is a static window which aligned to the partition
-- last_value() will always gives you the last value in the partition
Over(Partition By Col1 Order By Col2 Rows Between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) 

--This will always give you a dynamic window from the first value in the partition to the current row
Over(Partition By Col1 Order By Col2 Rows UNBOUNDED PRECEDING) 

There are many examples in SQL Server 2012 BOL

  6 Responses to “Rows|Range – Another Layer of Partition within Over Clause in SQL Server 2012”

  1. John, it is a nice article. I only wish the window frame bond can be parameterized In your examples, “Between 9 PRECEDING” if the number 9 can be parameterized that will be nice. -Joshua

  2. Can you explain whats the difference between ROWS and RANGE frame units?

  3. John, thanks for the helpful article. A question about “current row”: does “current row” refer to the last row in a partition, based on the ORDER BY clause? Thanks, Jack

  4. John, Thanks for this… really good information. However mine is a strange requirement. How to keep a windows frame that goes from 2nd row of partition to the end , irrespective of how many rows are there ?

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">



C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.