Evaluating the mean absolute deviation of a set of numbers in Oracle

Question

I'm trying to implement a procedure to evaluate the median absolute deviation of a set of numbers (usually obtained via a GROUP BY clause).

An example of a query where I'd like to use this is:

select id, mad(values) from mytable group by id;

I'm going by the aggregate function example but am a little confused since the function needs to know the median of all the numbers before all the iterations are done.

Any pointers to how such a function could be implemented would be much appreciated.

Answer 1

In Oracle 10g+:

SELECT  MEDIAN(ABS(value - med))
FROM    (
        SELECT  value, MEDIAN(value) OVER() AS med
        FROM    mytable
        )

, or the same with the GROUP BY:

SELECT  id, MEDIAN(ABS(value - med))
FROM    (
        SELECT  id, value, MEDIAN(value) OVER(PARTITION BY id) AS med
        FROM    mytable
        )
GROUP BY
        id