Assign the same sequence elements a number of consecutive repetitions

Question

I am working with a SQL Server table that looks like this:

diff	nom	rec_num
-7	abc	9
-6	abc	9
-5	abc	7
-4	abc	6
-3	abc	6
-2	abc	6
-1	abc	13
-7	fav	7
-6	fav	9
-5	fav	9
-4	fav	13
-3	fav	7
-2	fav	7
-1	fav	7

Real table is so much larger, this is the sample.

I need to assign to each rec_num of the table the number of their consecutive repetitions. For example, there is a sequence 5 5 4 4 4 3 5 5 5 5 1, then the result of the work should be 2 2 3 3 3 1 4 4 4 4 1. I need exactly the number of repetitions in a row, not the total number of repeats.

Using the combination of ROW_NUMBER:

with cte as (
  select diff, nom, rec_num,
  row_number() over (partition by nom order by nom, diff desc) rn1,
  row_number() over (partition by nom, rec_num order by nom, diff desc) rn2
  from t)
  
 select diff, nom, rec_num,
   row_number() over (partition by nom, rn1 - rn2 order by nom, diff desc) as con_repeats
from cte

I got the following table

diff	nom	rec_num	con_repeats
-1	abc	13	1
-2	abc	6	1
-3	abc	6	2
-4	abc	6	3
-5	abc	7	1
-6	abc	9	1
-7	abc	9	2
-1	fav	7	1
-2	fav	7	2
-3	fav	7	3
-4	fav	13	1
-5	fav	9	1
-6	fav	9	2
-7	fav	7	1

But I need the table look like this:

diff	nom	rec_num	con_repeats
-1	abc	13	1
-2	abc	6	3
-3	abc	6	3
-4	abc	6	3
-5	abc	7	1
-6	abc	9	2
-7	abc	9	2
-1	fav	7	3
-2	fav	7	3
-3	fav	7	3
-4	fav	13	1
-5	fav	9	2
-6	fav	9	2
-7	fav	7	1

How can I do this?

Answer 1

You can accomplish this simply with group by

with x as (
    select nom, rec_num, Count(*) n
    from t
    group by nom,rec_num
)
update t set t.con_repeats=x.n
from x
join t on t.nom=x.nom and t.rec_num=x.rec_num

see Fiddle

Edit

After clarification of the problem, a different solution is required, this uses window functions to identify the islands of repeating values and updatable CTE to apply the max count of each island group to the source table:

with groups as (
    select t.*,
        Dense_Rank() over (partition by nom order by (rn - rn2), rec_num) as grp,
        Row_Number() over (partition by nom, (rn - rn2), rec_num order by diff) as c
    from(
        select t.*, row_number() over (partition by nom order by diff) as rn, Row_Number() over (partition by nom, rec_num order by diff) as rn2
        from t
    )t
),
cnt as (
    select *, Max(c) over (partition by nom,grp) maxc
    from groups
)
update cnt set con_repeats=maxc;

select * from t;

See this New fiddle