Re: Searching for big differences between values

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