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
- PRECEDING:
- UNBOUNDED PRECEDING: the first row in the partition
- # PRECEDING: number of rows prior to current rows
- FOLLOWING:
- 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