Htykuut

I tried Tabibitosan method but no help. Can you please suggest some other solution for below scenario.

Using Oracle 11g:

Please find the below table format ,

Region  Date      Value

East    1/1/2018    1

East    1/2/2018    1

East    1/3/2018    0

East    1/4/2018    1

East    1/5/2018    1

East    1/7/2018    1

West    1/9/2018    0

West    1/10/2018   0

West    2/3/2018    1

West    2/4/2018    1

East    2/5/2018    1

West    2/8/2018    0

West    2/9/2018    0

West    2/10/2018   0

From the above table I should return the rows that has value 1 occurred 3 or more times with respect date(order by) and Region.

**My Output:**



    Region  Date       Value

    East    1/4/2018    1

    East    1/5/2018    1

    East    1/7/2018    1

Note : The date in the Date column may not contain all the days. Say, in the above 1/6/2018 is missing which is fine. I need to look for 'Value' column that has 1 consecutive for 3 or more rows when ordered by 'Date'.

Try the following if you wish to get all the rows which match the condition.

with data

  as (

select x.region,x.date1,x.value,x.pattern_start,x.rnk

 from (

select region

       ,date1

       ,value

       ,row_number() over(order by region,date1) as rnk

       ,case when value=1 

                    and lead(value,1) over(partition by region order by date1) = 1

                    and lead(value,2) over(partition by region order by date1) = 1

                   then row_number() over(order by region,date1)

        end  as pattern_start   

       ,lead(value,2) over(partition by region order by date1) as next_val_2

       ,lead(value,3) over(partition by region order by date1) as next_val_3

 from t)x

     )

select *

  from data y

 where y.rnk in (select pattern_start from data union all

                 select pattern_start+1 from data union all

                 select pattern_start+2 from data

                  )

order by 1,2     

Demo Link
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=1aa6d5de2b0ec375f659d0243aba350a

Just use lead():

select t.*

from (select t.*,

             lead(value) over (partition by region order by date) as value_1,

             lead(value, 2) over (partition by region order by date) as value_2

      from t

     ) t

where value = 1 and value_1 = 1 and value_2 = 1;

In the event that you have 4 or more in a row and only want the first one, you can add a lag():

select t.*

from (select t.*,

             lag(value) over (partition by region order by date) as prev_value,

             lead(value) over (partition by region order by date) as value_1,

             lead(value, 2) over (partition by region order by date) as value_2

      from t

     ) t

where value = 1 and value_1 = 1 and value_2 = 1 and

      (prev_value is null or prev_value <> 1);