Count Количество последовательных событий значений в таблице

У меня ниже таблицы

create table #t (Id int, Name char)

insert into #t values
(1, 'A'),
(2, 'A'),
(3, 'B'),
(4, 'B'),
(5, 'B'),
(6, 'B'),
(7, 'C'),
(8, 'B'),
(9, 'B')

Я хочу подсчитать последовательные значения в столбце имен

+------+------------+
| Name | Repetition |
+------+------------+
| A    |          2 |
| B    |          4 |
| C    |          1 |
| B    |          2 |
+------+------------+

Лучшее, что я пробовал:

select Name
, COUNT(*) over (partition by Name order by Id) AS Repetition
from #t
order by Id

но это не дает ожидаемого результата

Ответ 1

Одним из подходов является различие номеров строк:

select name, count(*) 
from (select t.*,
             (row_number() over (order by id) -
              row_number() over (partition by name order by id)
             ) as grp
      from t
     ) t
group by grp, name;

Логику проще всего понять, если вы запустите подзапрос и посмотрите на значения каждого номера строки отдельно, а затем посмотрите на разницу.

Ответ 2

Я использую рекурсивный CTE и минимизирую использование row_number, также избегаю count (*).

Я думаю, что он будет работать лучше, но в реальном мире это зависит от того, какой еще фильтр вы помещаете, чтобы минимизировать количество затронутых строк.

Если идентификатор имеет дискретные значения, тогда один дополнительный CTE будет использоваться для генерации непрерывного идентификатора.

 ;With CTE2 as
(
select ROW_NUMBER()over(order by id) id, name,1 Repetition ,1 Marker  from @t
)
, CTE as
(
select top 1 cast(id as int) id, name,1 Repetition ,1 Marker  from CTE2 order by id

union all

select a.id, a.name
, case when a.name=c.name then Repetition +1 else 1 end  
, case when a.name=c.name then c.Marker else  Marker+1 end
from @t a
inner join CTE c on a.id=c.id+1

)
,CTE1 as
(select *,ROW_NUMBER()over(partition by marker order by id desc)rn from cte c
)
select Name,Repetition from cte1 where rn=1