I'm attempting to write a query to calculate the median
of a column of numbers.
The first solution I came across was
Select avg(col1) MEDIAN from
( select rownum row1, col1 from a where col1 in (select col1 from a )) a
where a.row1 in ( select floor(count(*)/2 +.5) from a )
or a.row1 in ( select ceil(count(*)/2+.5) from a )
This does too many FT scans (4) of table a, so I tried to write
a simpler version using the analytical functions.
I have gotten as far as
SELECT col1
FROM
(
SELECT col1
, row_number() OVER (ORDER BY col1) AS r
, CEIL(COUNT(col1) OVER () /2) m
FROM a
)
WHERE r = m
However, this only works for an odd number of values.
IIRC, if an even number of values is present, the median
is defined as the average of the two middle-most numbers.
Before I spend much more time on this, has anybody already written
one ?
----
Matt Adams - GE Appliances - [EMAIL PROTECTED]
If carpenters built buildings the way programmers write
programs, the first woodpecker to come along would destroy
civilization. - author unknown
