Estimating Average Absolute Deviation of a Number Set in Oracle

Question

I am trying to perform a procedure to estimate the median absolute deviation of a set of numbers (usually obtained using the GROUP BY clause),

An example query in which I would like to use this:

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

I turn to the aggregate function example , but a little confused, since the function must know the median of all numbers before all iterations are performed.

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

Answer 1

In Oracle 10g+ :

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

same with 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