morethan 3 consecutive rows

[cjard]

ok i finally whacked this one but for one little bug:

create table tmp_test as
select '3/1/2006 7:00a.m' as d,'a' as t,'3' as v from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:00a.m','a','1' from dual union all
select '3/1/2006 7:03a.m','a','3' from dual union all
select '3/1/2006 7:03a.m','a','4' from dual union all
select '3/1/2006 7:03a.m','a','5' from dual union all
select '3/1/2006 7:06a.m','a','6' from dual union all
select '3/1/2006 7:06a.m','a','1' from dual union all
select '3/1/2006 7:06a.m','a','2' from dual union all
select '3/1/2006 7:09a.m','a','3' from dual union all
select '3/1/2006 7:09a.m','a','5' from dual union all
select '3/1/2006 7:09a.m','a','4' from dual union all
select '3/1/2006 7:12a.m','b','3' from dual union all
select '3/1/2006 7:12a.m','b','2' from dual union all
select '3/1/2006 7:12a.m','b','1' from dual;

following my own advice (in english) given last post i made this query:

  select
    d,
    t,
    v,
    case when
      SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 and
      MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
      1
    end as consec
   from tmp_test

it reliably identifies consecutives and for run length of N, the last N-2 rows are flagged with a 1. i called this column CONSEC:

3/1/2006 7:00a.m	a	1	
3/1/2006 7:00a.m	a	1	
3/1/2006 7:03a.m	a	3	
3/1/2006 7:03a.m	a	4	
3/1/2006 7:03a.m	a	5	1
3/1/2006 7:06a.m	a	6	1
3/1/2006 7:06a.m	a	1	
3/1/2006 7:06a.m	a	2

so now we can use a lookahead to see if the CONSEC column holds a 1 character, and because our CONSEC currently shows all but the first 2 of a consecutive range, we look ahead 2, or 1, or look at the current position:

select
  d,
  t,
  v,
  consec,
  case when
    consec = 1 or
    lead(consec,1) OVER (ORDER BY d, rowid) = 1 or
    lead(consec,2) OVER (ORDER BY d, rowid) = 1
    then 'Y'
  end as flag

from
(
  select
    d,
    t,
    v,
    case when
      SUM(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 9 and
      MIN(v) OVER (ORDER BY d, rowid ROWS 2 PRECEDING)>= 3 then
      1
    end as consec
   from tmp_test
 );

results:

D			T	V	CONSEC	FLAG
3/1/2006 7:00a.m	a	3	 
3/1/2006 7:00a.m	a	1		
3/1/2006 7:00a.m	a	1		
3/1/2006 7:03a.m	a	3		Y
3/1/2006 7:03a.m	a	4		Y
3/1/2006 7:03a.m	a	5	1	Y
3/1/2006 7:06a.m	a	6	1	Y
3/1/2006 7:06a.m	a	1		
3/1/2006 7:06a.m	a	2		
3/1/2006 7:09a.m	a	3		Y
3/1/2006 7:09a.m	a	5		Y
3/1/2006 7:09a.m	a	4	1	Y
3/1/2006 7:12a.m	b	3	1	Y
3/1/2006 7:12a.m	b	2		
3/1/2006 7:12a.m	b	1		
3/1/2006 7:13a.m	c	4		Y
3/1/2006 7:14a.m	d	4		Y
3/1/2006 7:14a.m	d	5	1	Y
3/1/2006 7:14a.m	d	1		
3/1/2006 7:14a.m	d	3		
3/1/2006 7:14a.m	d	3		
3/1/2006 7:14a.m	d	1

(i added some more values to the table after the initial create table sql)


stomped! and we have learned some interesting things about analytic functions too