Re: Searching for big differences between values

[Laurenz Albe <laurenz.albe@xxxxxxxxxxx>]

Durumdara wrote:
> Somewhere the users made mistakes on prices (stock).
> 
> I need to search for big differences between values.
> For example:
> 
> 20
> 21
> 21,5
> 30
> 28
> ..
> 46392 <-
> 46392 <-
> 
> But it could be:
> 
> 42300
> 43100
> 44000
> 43800
> 65000 <-
> 42100
> 
> Human eye could locate these values, but there we need to check 30.000 articles and 450.000 values.
> 
> Do you have any idea, how to this with SQL?

You could use some variant of

  SELECT id, price
  FROM (SELECT id, price,
               avg(price) OVER (ORDER BY id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS av
        FROM stock) q
  WHERE NOT (price BETWEEN 0.8 * av AND 1.25 * av);

Yours,
Laurenz Albe