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 http://msdn.microsoft.com/en-us/library/ms189461(v=sql.110).aspx
6 thoughts on “Rows|Range – Another Layer of Partition within Over Clause in SQL Server 2012”
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
Unfortunately it can’t be replaced by a variable. It must be literal.
Can you explain whats the difference between ROWS and RANGE frame units?
Martin, Range can only be used by unbounded preceding/following and current row.
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
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 ?