sql - Group two rows and all rows in between on condition -

a table defined through following query gives output shown in screenshot below:

select id, value, case when value = 'foo' , random() <= 0.5 't' else null end to_group   (select generate_series(1,100) id, case when random() <= 0.2 'foo' else 'bar' end value )t1 

how can group rows tagged 'foo' , 't' preceding 'foo' row (no matter if hast to_group = 't' or not) , enclosed 'bar' rows?
in given example rows 33 - 37.

the special difficulty need of rows ('foo', true) in two groups - @ end of 1 , @ start of next. have add instance of those.

to make simpler understand, , work stable set of rows, put sample values temporary table:

create temp table t select id, value       ,case when value = 'foo' , random() < 0.5          true else null        end to_group  (    select id, case when random() < 0.2 'foo' else 'bar' end value      generate_series(1,100) id    ) sub; 

using boolean data type flag make bit simpler.
query reduced to:

with cte (    select *, count(value = 'foo' or null) on (order id) grp     t    ) select grp, min(id) min_id, max(id) max_id  (    select id, value, to_group, grp     cte    union     select id, value, to_group, grp - 1 cte to_group    ) sub group  grp having count(value = 'foo' or null) = 2 order  grp; 

explain

• in cte cte add running count of rows grp value = 'foo'. other rows in between same number:

  • the expression value = 'foo' or null null values not 'foo'.
  • count() counts non-null values.
  • members of groups have same grp number, plus next row to_group.
  • detailed explanation technique:
    compute percents sum() in same select sql query

• as mentioned, special difficulty need rows twice. add instance union all in subquery sub. while being @ it, decrease grp number of copies 1 groups complete.

• the final select can group grp.
  valid groups have two rows value = 'foo'.