Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

What is a Window Aggregate Function?

  • 22 June 2013
  • Author: Kathi Kellenberger
  • Number of views: 15986
  • 0 Comments

As I have been presenting at SQL Saturdays and user group events on the window functions and enhancements with SQL Server 2012, I have learned that many SQL Server professionals have not heard about window aggregate functions that have been around since SQL Server 2005. Window aggregate functions allow you to return summary values like SUM, MIN, MAX, COUNT and AVG along with details. In other words, window aggregate functions let you add aggregate calculations to non-aggregate queries.

So, how does this work? Imagine that all the rows are ready to be returned and there is one last chance to perform calculations on these rows before the data is returned to the client.  We can also define the window or set of rows that the aggregate function will perform the calculation on.

The simplest example is to return a grand total over all the rows along with the details. One non-window function method is to use a sub-query within the SELECT clause as shown in the next example. NOTE: These examples use the AdventureWorks database.

SELECT  SalesOrderID, CustomerID,OrderDate,  TotalDue,
 (SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader) AS GrandTotal
FROM Sales.SalesOrderHeader;

When using the window aggregate method, the OVER clause allows us to define the window that the function will operate on. When using the OVER clause, empty parentheses mean the entire result set. Using the OVER clause to define a window for the SUM function to work on, the query can be written like this to get the same results:

SELECT  SalesOrderID, CustomerID,OrderDate,  TotalDue,
 SUM(TotalDue) OVER()AS GrandTotal
FROM Sales.SalesOrderHeader)

 

All window functions require the OVER clause. The OVER clause contains the definition of the window for the function to operate on, and empty parentheses mean the window consists of the entire result set. The next thing you can do is to create smaller windows by partitioning on one or more of the columns. For example, you may want to get a sub-total of each customer’s orders. To do this without window functions, you could write a correlated sub-query as shown in the next example:

SELECT  SalesOrderID, CustomerID,OrderDate,  TotalDue,
     (SELECT SUM(TotalDue) 
      FROM Sales.SalesOrderHeader InnerQuery 
      WHERE  InnerQuery.CustomerID = OuterQuery.CustomerID)AS CustomerTotal
FROM Sales.SalesOrderHeader OuterQuery
ORDER BY CustomerID;

 

To accomplish the same thing with window aggregates, you add the PARTITION BY option to the OVER clause. Now, instead of one window for the calculation, you have multiple, smaller windows defined. It’s not the official terminology, but I like to think of these as panes within the larger window. The following query demonstrates how to use the PARTITION BY clause.

 

SELECT  SalesOrderID, CustomerID,OrderDate,  TotalDue,
 SUM(TotalDue) OVER(PARTITION BY CustomerID) AS CustomerTotal
FROM Sales.SalesOrderHeader OuterQuery
ORDER BY CustomerID;

Window aggregate functions can be used in calculations like any other function and you can have multiple window aggregate functions that operate on differently defined windows in the same query. For example, you can write a query that contains both the Grand Total and the Customer Total. The following is a more complex example:

SELECT  SalesOrderID, CustomerID,OrderDate,  TotalDue,
 SUM(TotalDue) OVER(PARTITION BY CustomerID) AS CustomerTotal,
 SUM(TotalDue) OVER() AS GrandTotal,
 AVG(TotalDue) OVER(PARTITION BY CustomerID) AS AvgCustSale
FROM Sales.SalesOrderHeader OuterQuery
ORDER BY CustomerID;

 

Another thing you can do is apply a window function to the record set of an aggregate query. For example, say you wanted to get a list of CustomerIDs with the total for each customer but you also need to see the grand total. This is a bit trickier, though. My first attempt at writing this query resulted in an error:

SELECT CustomerID, SUM(TotalDue) AS CustomerTotal,
 SUM(TotalDue) OVER() AS GrandTotal
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

 

This seems odd since the query does have the TotalDue column within an aggregate function, but that is not what we have asked the window aggregate function to operate on. We really want a sum of all the sums from each CustomerID. To fix this, change the window aggregate function to calculate a sum of the sums:

SELECT CustomerID, SUM(TotalDue) AS CustomerTotal,
 SUM(SUM(TotalDue)) OVER() AS GrandTotal
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

 

Window aggregate functions allow you to write queries in a much simpler way. They can only appear in the SELECT and ORDER BY clauses and operate after the FROM, WHERE, GROUP BY and HAVING clauses.  All functionality demonstrated in this post was introduced in 2005. So, what are you waiting for? Start thinking about using window aggregate functions in your queries today!

Print
Categories: SQL Server
Tags:
Rate this article:
4.0

Please login or register to post comments.