Re: Can an analytic function do the following? find consective days where a value was over a threshold?

From: jonwat <jonwaterhouse_at_mail.gov.nf.ca>
Date: 11 May 2006 09:52:26 -0700
Message-ID: <1147366346.517915.153600@i40g2000cwc.googlegroups.com>

kyleman wrote:
> We have table A with the following structure
>
> ID Date value
> 1 6/1/03 10
> 1 6/2/03 20
> 1 6/3/03 10
> 1 6/4/03 4
> 1 6/5/03 45
> 1 6/6/03 32
> 1 6/7/03 22
>
> 2 6/1/03 70
> 2 6/2/03 20
> 2 6/3/03 5
> 2 6/4/03 5
> 2 6/5/03 5
> 2 6/6/03 20
> 2 6/7/03 30
>
> We want to find, starting from 6/7/06 number of consecutive days prior to
> that date that the value exceeded 10.
>
> for exampe
>
> ID 1 3 days 6/7-6-5
>
> ID 2 2 days 6/7-6
>
>
>
> Can an analytical function do this? We want to avoid a stored procedure for a
> variety of reasons (we have 125,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)

Yes. This version is maybe a bit more general than you need (i.e. it will give you all periods, not just those that end on a particular date)

create table test
(id number(2),
 d date,
 v number);

 insert into test
 values (1,sysdate,20);

 insert into test
 values (1,sysdate-1,20);

 insert into test
 values (1,sysdate-2,15);

 insert into test
 values (1,sysdate-3,15);

 insert into test
 values (1,sysdate-4,5);

 insert into test
 values (1,sysdate-5,20);

select id,max(d),count(*)
FROM
(select id,d,v,CASE WHEN v >= 10 THEN max(grp) OVER (partition by ID order by d desc)

	   		ELSE NULL
			END	 grp

FROM
(select id,d,v,CASE WHEN row_number() OVER (Partition by ID order by d desc)=1

  				or lag(v) OVER (Partition by ID order by d desc) < 10
			    THEN row_number() OVER (Partition by ID order by d desc)
				ELSE NULL
			END grp

FROM test))
WHERE grp IS NOT NULL
GROUP BY ID,grp

Jon Received on Thu May 11 2006 - 11:52:26 CDT