Tip: OVER and PARTITION BY
For the month of December, I’ll be taking a little holiday from blogging. In the meantime, enjoy a few of my more popular golden oldies, like this one (original article: http://wp.me/pZM1Z-gO). And be sure to drop by our live weekly webshow, DBAs@Midnight, at 11pm Central time on Fridays!
Here’s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful…
OVER
OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:
SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders
Will return something like this:
Cost OrderNum
10.00 345
10.00 346
10.00 347
10.00 348
Quick translation:
- SUM(cost) – get me the sum of the COST column
- OVER – for the set of rows….
- () – …that encompasses the entire result set.
OVER(PARTITION BY)
OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost] in the resultset. We can break up that resultset into partitions with the use of PARTITION BY:
SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo
FROM Orders
My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:
Cost OrderNum CustomerNo
8.00 345 1
8.00 346 1
8.00 347 1
2.00 348 2
The translation here is:
- SUM(cost) – get me the sum of the COST column
- OVER – for the set of rows….
- (PARTITION BY CustomerNo) – …that have the same CustomerNo.
Further Reading: BOL: OVER Clause
June 2012 edit: We highly, highly recommend Itzik Ben-Gan’s brand new book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions for an outstanding and thorough explanation of windowing functions (including OVER / PARTITION BY).
Enjoy, and happy days!
-Jen
http://www.MidnightDBA.com/Jen
Recent Comments