Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help: a difficult SQL request

Re: Help: a difficult SQL request

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 24 Feb 2003 20:58:47 GMT
Message-ID: <b3e126$1i16um$1@ID-82536.news.dfncis.de>

> 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
 

Hello

You can achieve what you need with analytical functions. There are four steps involved:

  1. finding rows with the same volume as the previous one,
  2. filtering rows that have the same volume
  3. determining how much the gap is
  4. selecting those with a gap of 8 minutes

This example only works correctly, if data is recorded exactly once for each minute.  

set feedback off

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

create table foo_ (
  time_ date,
  volulme number
);

insert into foo_ values ('01.01.2000 01:01:00', 12);
insert into foo_ values ('01.01.2000 01:02:00',  8);
insert into foo_ values ('01.01.2000 01:03:00', 14);
insert into foo_ values ('01.01.2000 01:04:00',  6);
insert into foo_ values ('01.01.2000 01:05:00', 24);
insert into foo_ values ('01.01.2000 01:06:00',  5);
insert into foo_ values ('01.01.2000 01:07:00', 29);
insert into foo_ values ('01.01.2000 01:08:00',  2);
insert into foo_ values ('01.01.2000 01:09:00', 37);
insert into foo_ values ('01.01.2000 01:10:00',  3);
insert into foo_ values ('01.01.2000 01:11:00',  3);
insert into foo_ values ('01.01.2000 01:12:00',  3);
insert into foo_ values ('01.01.2000 01:13:00',  3);
insert into foo_ values ('01.01.2000 01:14:00',  3);
insert into foo_ values ('01.01.2000 01:15:00',  3);
insert into foo_ values ('01.01.2000 01:16:00',  3);
insert into foo_ values ('01.01.2000 01:17:00',  3);
insert into foo_ values ('01.01.2000 01:18:00', 18);
insert into foo_ values ('01.01.2000 01:19:00',  7);
insert into foo_ values ('01.01.2000 01:20:00',  7);
insert into foo_ values ('01.01.2000 01:21:00',  7);
insert into foo_ values ('01.01.2000 01:22:00',  7);
insert into foo_ values ('01.01.2000 01:23:00',  7);
insert into foo_ values ('01.01.2000 01:24:00',  7);
insert into foo_ values ('01.01.2000 01:25:00',  7);
insert into foo_ values ('01.01.2000 01:26:00', 11);



-- fourth select:
-- The interesting rows are those that have
-- a difference of at least 8 between the -- previous row and the current row:
select
  time_
from (

drop table foo_;

Hth

Rene Nyffenegger

-- 
  no sig today
Received on Mon Feb 24 2003 - 14:58:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US