value

)

-- see then end of this note for a helper

-- script that will fill the table with

-- some random data.

/*

Now we look at some solutions using CTE's

*/

-- this calculates the financial median

with

as

(

select

from

),

as

(

from

)

select

/*

The first CTE, positions, calculates the a couple of aggregates on our sample data.

position.mid is the number of the middle row if the number of rows is odd, otherwise it is the number of the first middle row.

position.even is a marker that is one if the number of rows is even and zero otherwise.

The second CTE, rows, calculates the row number of each value in our sample data when ordered by value. The ROW_NUMBER() is aliased as rows.rn. You might be tempted use this alias in a predicate of this CTE to shortcut the calculation of the median, but an alias for the ROW_NUMBER() function cannot be used in a predicate of the expression that defines it.

The final expression selects the average value of the values from the positions CTE whose row number is either position.mid or position.mid + position.even. Note that when the number of rows in data is odd both position.mid and position.mid + position.even will be the same value.

The result of averaging one or two rows will be the financial median for the values in the data table.

Another way to calculate the median, which can be used in versions of SQL Server prior to 2005, is to move the sorted values into a temporary table that has an identity column. The identity column is, in effect, the same as the ROW_NUMBER(). Then, as the ROW_NUMBER based solution does, pick the "middle" row

*/

-- temp table median calculation of median

declare

insert

select

/*

Is there any advantage to using the ROW_NUMBER based solution? If you compare the actual execution plan of the two calculations run in a single batch, the ROW_NUMBER based one has an insignificant advantage, 48% vs. 52% of the overall plan on my test system.

The ROW_NUMBER solution does have one nice feature though, it is a SQL expression and the temporary table solution is a batch, not an expression. You can use this fact to improve the relative performance of the ROW_NUMBER based calculation. If you know you are going to do this median calculation very often you can add an index to the value column of the data table. This, of course, is not for free, you are making a space/time tradeoff by spending space to cut down time.

*/

-- index to speed up ROW_NUMBER

-- based median calculation

create

/*

With this index in place you will find a notable difference between the execution plans for the ROW_NUMBER and temporary table calculations, the ROW_NUMBER calculation takes up only 37% of the overall plan on my test system.

The reason for this is that when there is no index that sorts the values the ROW_NUMBER calculation must build a temporary table itself and sort it, just like temporary table calculation does. But the temporary table solution always builds the temporary table, even if there is a index available to sort it. The ROW_NUMBER solution mearly has to walk over the index to find the middle value.

Calculating the statistical mean, the one that always uses one of the values from the table, takes a bit more work, but not much.

*/

-- calculation of statistical mean

with

as

(

from

),

as

(

select

from

)

select

ORDER

/*

For the statistical mean you must sort the results by row number and pick the first one. If you want the last one then do an DESC sort.

This calculation isn't a real aggregate, but you can get some of the effect of an aggregate by wrapping it inside of an inline table valued user defined function. Note that because the ROW_NUMBER calculation is a SQL expression you can do this. The temporary table solution can only be wrapped into a multi-line user defined function.

*/

-- median TVF

-- note that this function

-- always returns a table

-- with a single row

CREATE

returns

return

with

as

(

from

),

as

(

select

from

)

select

/*

So if you now want to get all the values in the data table that are greater than the median you can do something like:

*/

/*

So that is yet another median calculation for SQL Server. In some cases it will may provide better performance than using a temporary table, but at the cost of space. It not worse that using a temporary table and gives you the option of making the space/time tradeoff whenever you feel it is necessary.

*/

-- helper script to fill sample TABLE data

set

declare

set

while

BEGIN

Insert

set

END