Re: Help: a difficult SQL request

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 25 Feb 2003 08:59:09 -0800
Message-ID: <336da121.0302250859.6ca083ef@posting.google.com>

"charlie cs" <cs3526(no-spam)@yahoo.com> wrote in message news:<jvP5a.9872$_J5.3067_at_nwrddc01.gnilink.net>...
> we have a huge table, with data coming in every minute, table will be like
> this
> TIME VOLULME
> --------------------
> 1:01 12
> 1:02 13
> 1:04 15
> 1:05 3
> .......
>
> but sometimes the data collector went bad, so the volume will inherit the
> privious one, the data will be like
>
> TIME VOLULME
> --------------------
> 1:01 12
> 1:02 13
> 1:04 15
> 1:05 3
> .......
> 1.10 3
> 1.11 3
> 1.12 3
> 1.13 3
> 1.14 3
> 1.15 3
> 1.16 3
> ...............
>
> How could we find those records which did change for at least 8 minutes?
>
> Thanks for your help

Use connect by:

select t.time, t.volume
from your_table t,

     (select level ll, time, volume
      from your_table
      connect by time = prior time + .01
             and volume = prior volume) c

where c.ll >= 8
  and t.time between c.time - c.ll * .01 and c.time

If time column is indexed, it should run pretty fast. You can restrict time range using START WITH clause. Received on Tue Feb 25 2003 - 10:59:09 CST