Window Functions in SQL Server 2012

A window function in SQL allows an aggregate value to be calculated on a defined sub set of rows.  Window functions were introduced into Transact-SQL with the release of SQL Server 2005. To use a window function you must use the OVER() clause to define the window.

Assume we have a table containing sales data.  It contains columns for order_id , salesman id, sale amount .  We could write a SQL statement such as

SELECT
salesman_id
,sale_amount
,MAX(sale_amount)  OVER(partition BY salesman_id) AS [MAX Sale]
FROM
sales

The statement will return one row for each sale in the sales table.  On each row would be the maximum sales_amount for the salesman_id.  Conceptually, while processing for each row, the rows for the current row’s salesman_id are examined and their maximum sale_amount is calculated.  The PARTITION BY clause defines the windows of rows to be examined.  It is possible to leave out the PARTITION BY clause entirely, in which case the window of rows the aggregate is applied to is the entire result set.

With SQL 2012, the OVER() clause now supports an ORDER BY clause.  This allows you to specify the order of the rows in the window.

Also, the ability to define the rows in the window has been expanded.  The new ROWS and RANGE clause allows you to be more selective in choosing which rows to include in your window.  This is referred to as framing.  The require the use of the ORDEER BY clause.  An example using our sales table would be

SELECT
order_id
,salesman_id
,order_amount
,SUM(order_amount)  OVER( ORDER BY order_id ROWS BETWEEN 3 PRECEDING  AND CURRENT ROW)
FROM
sales

In this example, the window consists of 4 rows: the current row and the previous three rows.  The clause can define PRECEDING and FOLLOWING rows or both.  The keyword CURRENTT ROW means just that, the current row.  There is also the UNBOUND keyword which can be used to indicate the top or the bottom of the result set.  Here are some examples of framing phrases:

SUM(order_amount)  OVER( ORDER BY order_id  ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)

SUM(order_amount)  OVER( ORDER BY order_id  ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)

SUM(order_amount)  OVER( ORDER BY order_id  ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)

SUM(order_amount)  OVER( ORDER BY order_id  ROWS BETWEEN 3 PRECEDING AND 2 FOLLOWING)
SUM(order_amount)  OVER( ORDER BY order_id  ROWS UNBOUND PRECEDING)

In the above examples only the ROWS clause was used, but the RANGE clause could have been used.  In the ANSI standard, the RANGE has functionality which is not yet implemented in transact-SQL.  I am sure this will change with future releases.  However, there is one import difference between ROWS and RANGE.  This occurs when the ORDER BY clause does not have an expression which returns unique values for each row.  If there are ties in the ORDER BY clause, the ROWS clause will ignor those rows, while the RANGE clause will not.

Using window functions allows you to more easily include aggregates in your query.  The code is more compact and easier for others to follow than using either subqueries or derived tables.  These new clauses make creating running totals and averages much easier.  However, these are not the only improvements to the OVER() clause in SQL 2012.  Consult Books on Line to read about the others.

Comments are closed.