|
Guest
|
Given a table of values and the ROW_NUMBER() function it is pretty
easy to compute the traditional Median. Example:
CREATE TABLE Foobar(x INTEGER NOT NULL);
INSERT INTO Foobar VALUES (1), (2), (2), (3), (3), (3);
The traditional median would be AVG(2, 3) = 2.5. Take the two middle
values and average them. The weighted median would return AVG (2, 2,
3, 3, 3) = 13/5 = 2.6 instead. Take the subsets of the two middle
values so that the results skew in the direction of the most frequent
middle value. Likewise, AVG (2, 2, 2, 3, 3) = 12/5 = 2.4.
The traditional median is pretty easy with ROW_NUMBER(); here are two
ways:
SELECT AVG(x) AS median
FROM (SELECT x,
ROW_NUMBER()
OVER(ORDER BY x) AS row_nbr,
COUNT(*) OVER() AS row_cnt
FROM Foobar)
AS OrderedFoobar
WHERE row_nbr IN((row_cnt + 1)/2, (row_cnt + 2)/2);
or:
SELECT AVG(x) AS median
FROM (SELECT x,
ROW_NUMBER() OVER(ORDER BY x ASC),
ROW_NUMBER() OVER(ORDER BY x DESC)
FROM Foobar)
AS SortedFoobar (x, high, low)
WHERE high IN (low, low+1, low-1);
I have been trying to do a weighted median along the same lines, but I
keep getting ugly code. Anyone see an elegant way to do this with
COUNT(), RANK(), DENSE_RANK() and/ ROW_NUMBER()? I have a feeling it
exists and I am not seeing it. |
|
|
