Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help: a difficult SQL request
> 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:
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:
drop table foo_;
Hth
Rene Nyffenegger
-- no sig todayReceived on Mon Feb 24 2003 - 14:58:47 CST