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.

Ormond

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

Viscus

Answer 2

If you have to group by and compute your value, here's what worked for me:

SELECT id, 
       ABS(ROUND(MEDIAN(dur - med))) as stmed
FROM   (
       SELECT  id,
               (end - start)*86400 as dur, 
               MEDIAN((end - start)*86400 ) OVER() AS med
       FROM    mytable
       WHERE   STATUS = 'SUCCESS'
       )
GROUP  BY id;

I used @Quassnoi's answer, but had to tweak it to get the calculation of the Median Absolute Deviation (MAD) of time elapsed.

The table I queried contained instances of processes that ran. The start and end columns tell how long a process took to run. I wanted to know if a process was taking too long, or if it didn't run long enough but still was successful (false positive).

However, the durations are not in a normal distribution, so Standard Deviation was not giving me reliable info.

The next step would be to remove the outliers from consideration to get an even more accurate normal run time, and I will update this answer once I figure that out.

Bridewell