| ProfileDeferred ProcessingBlog |  Tools  Help |
|
August 09 Psudo Median Aggregate/*
Psuedo Median Aggregrate
In an item I previously blogged, YAMC, I showed how to use the ROW_NUMBER() function in SQL Server 2005 to do a pretty efficient median calculation. Being able to calculate the median in an expression can be useful, but a median aggregate function would be much more useful.
Well T-SQL, even in SQL Server 2005, doesn't provide a median aggregate function and the is no way to write one using user defined aggregates written in a CLR language either.
Remember that an aggregate is set in, scalar out. YAMC was an aggregate calculation, but not an aggregate function. One of the useful things about aggregate function, like SUM(), is that they can be used in conjunction with GROUP BY phrases, so you can get an aggregate per some kind of value.
We are going to look at how to extend the YAMC to use a GROUP BY phrase so that it can be used, efficiently, to produce the same results as a MEDIAN() aggregate function would.
We will use the sales table that follows as example data. A script that will initialize it with some sample data is at the end of this blog item.
*/
Create table Sales
(
PONumber int Primary Key,
Value Money,
State NCHAR(2)
)
/*
A simple aggregate calculation could be the SUM of the values by state.
*/
SELECT State, SUM(value) FROM Sales GROUP BY State
/*
Here is a little bit of the output...
NB 891760.7953
WI 1001671.6347
PA 962849.0728
IL 1055766.3988
What we would like to do thougth is:
SELECT State, MEDIAN(Value) FROM Sales GROUP BY State
but that won't work because T-SQL has no MEDIAN aggregate function.
We could calculate the median of the whole table or a specific state using YAMC by modifying it a bit to just use rows from the state we are interest in.
*/
with positions
as
(
select (1 + Count(*)) / 2 as mid,
1-(count(*) % 2) as even
from Sales where State = 'NB'
),
rows
as
(
select value,
ROW_NUMBER() over (order by value) as rn
from Sales where State = 'NB'
)
select 'NB' as State, AVG(value) from rows JOIN positions on
rn in (positions.mid, positions.mid + positions.even)
/*
And we get...
NB 4218.464
It turns out ROW_NUMBER() has a friend named PARTITION BY, whose purpose in spirit is the same as GROUP BY. In fact by using PARTITION BY and GROUP BY together we can do an aggregate calculation grouped by a value, just as you can using the built in aggregate functions.
Here is a "psuedo" median aggregate that calculates the median sales value by state.
*/
-- psuedo median aggregate
WITH
positions
AS
(
SELECT (1 + count(*)) / 2 AS mid,
1-(count(*) % 2) AS even,
state
FROM Sales GROUP BY state
),
rows AS
(
SELECT value, state, row_number() OVER
(PARTITION BY state order by value) AS rn FROM sales
)
SELECT rows.state, AVG(rows.value) AS median
FROM rows JOIN positions
ON rows.state = positions.state
AND rows.rn IN
( positions.mid, positions.mid + positions.even)
GROUP BY rows.state
/*
This explaination assumes you have already read the YAMC blog item.
Similar as in YAMC, positions calculates the mid and even values, but this time groups the values by state.
Again, similar to YAMC, rows associates a row number with each value, but this time partitions the row numbers by state. This means that each state will have its own set of row numbers starting with one.
The SELECT that follows the CTE's is a bit more complex than the one in YAMC. It joins together positions and rows just as YAMC did. However it adds to the predicate a clause that makes the row and positions have the same state. And at the end it groups the AVG() aggregate by state.
Here is a little bit of what it produces...
AK 5158.6132
AL 5222.3433
AR 5347.351
AZ 5190.8084
CA 5015.9836
NB 4218.464
It would be much easier, and a lot more flexible, if T-SQL just gave us a MEDIAN() aggregate function, but for now at least we can do a real Median grouped by value if we want to.
Since this idiom accomplises pretty much what an aggregate function does, I wonder if the SQL team could cook up a syntax that used PARTITION BY and GROUP BY so that we could write our own user defined aggregates in T-SQL? One can dream...
*/
-- genrates test data
DECLARE @index int
SET @index = 10000
WHILE @index > 0
BEGIN
DECLARE @state NVARCHAR(2)
DECLARE @rand float
SET @rand =RAND() * 50
SET @state = CASE
WHEN @rand < 1 THEN N'AL'
WHEN @rand < 2 THEN N'AK'
WHEN @rand < 3 THEN N'AZ'
WHEN @rand < 4 THEN N'CA'
WHEN @rand < 5 THEN N'CO'
WHEN @rand < 6 THEN N'CT'
WHEN @rand < 7 THEN N'DE'
WHEN @rand < 8 THEN N'FL'
WHEN @rand < 9 THEN N'GA'
WHEN @rand < 10 THEN N'HI'
WHEN @rand < 11 THEN N'ID'
WHEN @rand < 12 THEN N'IL'
WHEN @rand < 13 THEN N'IA'
WHEN @rand < 14 THEN N'IN'
WHEN @rand < 15 THEN N'KS'
WHEN @rand < 16 THEN N'KY'
WHEN @rand < 17 THEN N'LA'
WHEN @rand < 18 THEN N'MA'
WHEN @rand < 19 THEN N'MD'
WHEN @rand < 20 THEN N'MN'
WHEN @rand < 21 THEN N'MI'
WHEN @rand < 22 THEN N'MS'
WHEN @rand < 23 THEN N'MO'
WHEN @rand < 24 THEN N'MT'
WHEN @rand < 25 THEN N'NB'
WHEN @rand < 26 THEN N'NV'
WHEN @rand < 27 THEN N'NH'
WHEN @rand < 28 THEN N'NJ'
WHEN @rand < 29 THEN N'NM'
WHEN @rand < 30 THEN N'NY'
WHEN @rand < 31 THEN N'NC'
WHEN @rand < 32 THEN N'ND'
WHEN @rand < 33 THEN N'OH'
WHEN @rand < 34 THEN N'OK'
WHEN @rand < 35 THEN N'OR'
WHEN @rand < 36 THEN N'PA'
WHEN @rand < 37 THEN N'RI'
WHEN @rand < 38 THEN N'SC'
WHEN @rand < 39 THEN N'SD'
WHEN @rand < 40 THEN N'TN'
WHEN @rand < 41 THEN N'TX'
WHEN @rand < 42 THEN N'UT'
WHEN @rand < 43 THEN N'VT'
WHEN @rand < 44 THEN N'VA'
WHEN @rand < 45 THEN N'WA'
WHEN @rand < 46 THEN N'WV'
WHEN @rand < 47 THEN N'WI'
WHEN @rand < 48 THEN N'WY'
WHEN @rand < 49 THEN N'AR'
WHEN @rand < 50 THEN N'AL'
END
DECLARE @value MONEY
SET @value = RAND() * 10000
INSERT INTO SALES VALUES (@index, @value, @state)
SET @index = @index - 1
END Comments (8)
TrackbacksThe trackback URL for this entry is: http://dsullivan.spaces.live.com/blog/cns!F253C6CDC8EC1EAC!117.trak Weblogs that reference this entry
|
|
|
