开发者

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

开发者 https://www.devze.com 2023-01-23 09:53 出处:网络
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).

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 o开发者_运维技巧f 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.


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
0

精彩评论

暂无评论...
验证码 换一张
取 消