## Re: how to identify outliers

From: "Chris Spotts" Re: how to identify outliers 2009-10-28 12:34:02 00d601ca57ca\$ef5229b0\$cdf67d10\$@com (view raw, whole thread or download thread mbox) 2009-10-27 21:36:12 from "Rhys A(dot)D(dot) Stewart"  2009-10-27 22:37:10 from "Rhys A(dot)D(dot) Stewart"   2009-10-27 22:56:04 from Alvaro Herrera   2009-10-27 23:04:47 from Scott Bailey  2009-10-27 23:58:23 from John R Pierce   2009-10-28 11:00:39 from Sam Mason    2009-10-28 12:34:02 from "Chris Spotts" 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
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

```

### pgsql-general by date

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