In Oracle, how to do Rank() (or any other ways that work) down to group level instead of row level?

Question

Let's say we have this set of code:

NAM T   F   
A   10  Y   
A   11  N   
A   12  N   
A   13  Y
B   10  Y
B   11  Y

How can we use Rank() (or any other ways that work) to transform the above to:

NAM F   ID  MNT CNT
A   Y   1   10  1
A   N   2   11  2
A   Y   3   13  1
B   Y   1   11  2

(NAM and F is grouped, but for F they can be grouped only when the rows are next to each other - in other words, for F to be grouped togather, the rows must have the value of T = 0,1,2,3,4... the difference of each T must be exactly 1)

The new columns are ID and CNT - the main point is column ID, where the 2nd and 3rd row have to be considered to be in the same rank (ID=2) because both rows have the F flag as false/0.

The source data can be got from:

select 'A' NAM, 10 t, 'Y' f FROM dual union all
select 'A' NAM, 11 t, 'N' f FROM dual union all
select 'A' NAM, 12 t, 'N' f FROM dual union all
select 'A' NAM, 13 t, 'Y' f FROM dual union all
select 'B' NAM, 10 t, 'Y' f FROM dual union all
select 'B' NAM, 11 t, 'Y' f FROM dual

The order of the time field T has to be considered, in other words the following result should not result:

NAM F   ID  MNT CNT
A   Y   1   10  2
A   N   2   11  2
B   Y   1   10  2

One more example:

NAM T   F
A   10  Y
A   11  N
A   12  Y
A   13  Y
A   14  N
A   15  N
A   16  N
A   17  Y
B   10  Y
B   11  Y

Result should be:

NAM F   ID  MNT CNT
A   Y   1   10  1
A   N   2   11  1
A   Y   3   12  2
A   N   4   14  3
A   Y   5   17  1
B   Y   1   10  2

The source data set:

select 'A' NAM, 0 t, 'Y' f FROM dual union all
select 'A' NAM, 1 t, 'N' f FROM dual union all
select 'A' NAM, 2 t, 'Y' f FROM dual union all
select 'A' NAM, 3 t, 'Y' f FROM dual union all
select 'A' NAM, 4 t, 'N' f FROM dual union all
select 'A' NAM, 5 t, 'N' f FROM dual union all
select 'A' NAM, 6 t, 'N' f FROM dual union all
select 'A' NAM, 7 t, 'Y' f FROM dual union all
select 'B' NAM, 0 t, 'Y' f FROM dual union all
select 'B' NAM, 1 t, 'Y' f FROM dual

Answer 1

Using the well known two step approach to identify contignous groups (LAG / LAST_VALUE ignore nulls) here the query (updated for the new setup)

with tab1 as (
select nam,t,f,
 nvl(lag(f) over (partition by nam  order by t),-1) lag_f,
 case when (nvl(lag(f) over (partition by nam  order by t),-1) <> f) then 
 row_number() over (partition by nam  order by t) end grp
from tst
), tab2 as (
select nam,t,f,
last_value(grp ignore nulls) over (partition by nam order by t) as grp2
from tab1
), tab3 as (
select 
nam, f, count(*) cnt, min(t) mnt
from tab2
group by nam, f, grp2
)
select nam,f,
rank() over (partition by nam order by mnt) r,
mnt, cnt
from tab3
order by nam,4; 

gives

NAM, F, R, MNT, CNT
A   Y   1   0   1
A   N   2   1   1
A   Y   3   2   2
A   N   4   4   3
A   Y   5   7   1
B   Y   1   0   2

Answer 2

If you need to count consecutive rows in partitions by column A you could use this technique:

select a, min(f) f, rank() over (partition by a order by diff) i, count(1) cnt 
  from (
    select test.*, 
           row_number() over (partition by a order by t)
           - count(f) over (partition by a, f order by t) diff
      from test)
  group by a, diff order by a, diff

^SQLFiddle

---

Edit: for updated part of question use these modifications

select nam, mnt, cnt,
       row_number() over (partition by nam, diff order by mnt) id
  from (
    select nam, min(t) mnt, count(1) cnt, diff
      from (
        select nam, t, f, 
               row_number() over (partition by nam order by t) 
               - count(1) over (partition by nam, f order by t) diff
          from test )
      group by nam, diff, f )
  order by nam, diff

This query gave me expected result, please test it.

Answer 3

Can be solved with "stacked" analytics:

1. Identify the "leader" of each group to be aggregated.
2. Propagate the "leader" to the rest of the elements in the group.
3. Aggregate the data via the "leader".

The query would be:

with source_data$ as (
/*
... your source data here ...
*/
),
find_the_leader$ as (
    select X.*,
        case when lag(t) over (partition by nam, f order by t asc) is null
                or t != 1 + lag(t) over (partition by nam, f order by t asc)
            then t
        end as mnt_leader
    from source_data$ X
),
propagage_the_leader$ as (
    select X.*,
        last_value(mnt_leader) ignore nulls over (partition by nam, f order by t asc) as mnt
    from find_the_leader$ X
)
select nam, f,
    row_number() over (partition by nam order by mnt asc) as id,
    mnt, count(1) as cnt
from propagage_the_leader$
group by nam, f, mnt
order by nam, id, f
;

On my PC, with your source data no.1 it yields:

NAM F   ID  MNT CNT
A   Y   1   10  1
A   N   2   11  2
A   Y   3   13  1
B   Y   1   10  2

And with your source data no.2 (with values of t increased by 10 in the "union-all-from-dual" select) it yields:

NAM F   ID  MNT CNT
A   Y   1   10  1
A   N   2   11  1
A   Y   3   12  2
A   N   4   14  3
A   Y   5   17  1
B   Y   1   10  2

I hope that you don't have any additional constraints over how your results should look like, because I don't have more time to adjust the query to answer a different problem.