/*
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 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, as an aggregate
function like SUM is.

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
a values, 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 to the predicate a clause that
makes the row and positions have the same state. And at the
end it groups the AVG() agregate 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.

*/

-- 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

