Skip site navigation (1) Skip section navigation (2)

Re: how to identify outliers

From: "Chris Spotts" <rfusca(at)gmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re: how to identify outliers
Date: 2009-10-28 12:34:02
Message-ID: 00d601ca57ca$ef5229b0$cdf67d10$@com (view raw or flat)
Thread:
Lists: pgsql-general
> 
> I'd agree, stddev is probably best and the following should do
> something
> reasonable for what the OP was asking:
> 
>   SELECT d.*
>   FROM data d, (
>     SELECT avg(distance), stddev(distance) FROM data) x
>   WHERE abs(d.distance - x.avg) < x.stddev * 2;
> 
[Spotts, Christopher] 
Statistically speaking if you dataset is of a fairly normal distribution the
following works "well" and is a *fairly* standard outlier definition.

First get a median function (there's a million of them on the net, doogle a
google).
You'll need one pass to get the median.
Divide your data set in half based on that median.  
Get the median of the first half (this is Q1).
Get the median of the second half (this is Q3).
Then your range for your good data should be from (Q1 - (Q3-Q1)*1.5) TO (Q3
+ (Q3-Q1)*1.5). 
Anything outside that range is an outlier.  Adjust the 1.5 up or down to be
more or less aggressive.

Using the "avg" formula for outliers is bad news.

I HIGHLY suggest installing PL/R for this, it makes it trivial.

Chris


In response to

pgsql-general by date

Next:From: fox7Date: 2009-10-28 12:42:40
Subject: Re: Slow running query with views...how to increase efficiency? with index?
Previous:From: Sam MasonDate: 2009-10-28 11:00:39
Subject: Re: how to identify outliers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group