Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> median function
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 ?