Re: Can an analytic function do the following?

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Tue, 01 Jul 2003 02:26:45 GMT
Message-ID: <3F00F164.8080001@nospam_netscape.net>

hourman wrote:
> running Oracle 9i R2 on Sun Starfire server with Sun OS
>
> We have table A with the following structure
>
> Date hour tagname value
> 6/1/03 6 A 100
> 6/1/03 7 A 200
> 6/1/03 8 A 100
> ..
> 6/1/03 23 A 95
> 6/1/03 6 B 53
> 6/1/03 7 B 20
> 6/1/03 8 B 30
> ..
> 6/1/03 23 B 55
>
>
>
>
> We want to find tags where the value for 3 consective hours is the same
>
> for exampe
>
> 6/1/03 11 F 30
> 6/1/03 12 F 30
> 6/1/03 13 F 30
>
> The values have to be consectivie not just appearing 3 times in a day
>
> Can an analytical function do this? We want to avoid a stored procedure for a
> variety of reasons (we have 3,000,000 rows to go thru and loading each row and
> comparing it to the previous and so forth will be too slow, we hope an AF will
> be faster)

Try:

select date, hour, tagname, value
from
(select date, hour, tagname, value,
lead(hour, 2, null) - lead(hour, 1, null)

	over (	partition by date, value
		order by hour ) lead2,
lead(hour, 1, null) - hour
	over (	partition by date, value
		order by hour ) lead1,
hour - lag(hour, 1, null)
	over (	partition by date, value
		order by hour ) lag1,
lag(hour, 1, null) - lag(hour, 2, null)
	over (	partition by date, value
		order by hour ) lag2

from tableA)
where (lead2 = 1 and lead1 = 1)
or (lead1 = 1 and lag1 = 1)
or (lag1 = 1 and lag2 = 1)
order by date, hour, tagname

Cheers,
Dave Received on Mon Jun 30 2003 - 21:26:45 CDT