Friday, August 7, 2009

The Transact-SQL OVER Clause: Not Just For Ranking Functions

If you’ve been following this blog for a bit, you may have seen the post I made back in March on Ranking Functions. In it I introduced ranking functions: RANK(), DENSE_RANK(), and ROW_NUMBER(). I also introduced the OVER clause, and how it is used to order and partition the ranking logic.

Until now, I didn’t realize that the OVER clause could be applied to aggregate functions as well. Earlier this week, I dug up this little nugget from BOL.

SELECT SalesOrderID, ProductID, OrderQty

,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'

,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'

,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'

,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'

,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'

FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);

Holy cow, Batman! How did I miss that? The partitioned aggregates very efficiently perform subtotals based upon the OVER clause that you define. Prior to the OVER clause, we would have needed to create a derived table which GROUPed the query by our partition columns, then joined said table back to our parent query. This method is much cleaner, and much more efficient, as you aren’t running through your results twice (one for the aggregate, and again to fetch back the “rows” themselves).

This opens up a lot of possibilities, so it’s a good tool to have in your belt, so to speak.

Tune in later this week, as we return to our regularly scheduled BI series.


Uri Dimant said...

Hi Aaron
One more is COUNT(*) OVER () FROM tbl

Jeremiah said...

Aggregations with OVER are my favorite use of them. I don't know how you missed it before, but I'm glad you found it now, it's such an awesome tool to have in your toolbox.

Kent Waldrop said...

I am sure hoping that the next version of SQL Server will also include the LAG, LEAD and the complement of the analytic OVER() clause that is available in Oracle.

Kent Waldrop

John Sansom said...

Well I never, something that I actually did not know ;-) It’s true I’m an arse.

Seriously though, this is a hidden gem. I'm definitely going to be able to put this to good use in quite a few BI solutions. Thanks for the tip.

Aaron Alton said...

Woohoo! Somebody other than me didn't know about this! Thanks for the comment, John.

@Uri - I didn't notice count. It would also be very useful.

@Kent - I'll keep my fingers crossed with you. It pains me greatly to use cursors for running aggregates, so I'd love to see all of the windowing functions fleshed out.

dledwards said...

"Aaron Alton, The HOBT, informs us that the Transact-SQL OVER clause is not just for ranking functions. [...]"

Log Buffer #158

Tim Mitchell said...

Good tip... I didn't know about this functionality. Thanks for sharing this little nugget.

Bob Folkerts said...

Look at how PostgreSQL or Oracle implement window partition functions. SQL Server is missing some useful functions like lead and lag. These can be very useful when reporting on business processes, e.g., tracking tickets in a service organization.