표에서 값이 연속적으로 발생한 횟수

표에서 값이 연속적으로 발생한 횟수

아래 테이블이 있습니다.

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

하지만 그것은 제게 기대되는 결과를 주지 못합니다.

한 가지 접근 방식은 행 번호의 차이입니다.

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;

하위 쿼리를 실행하고 각 행 번호의 값을 따로 살펴본 다음 차이를 보면 논리가 가장 쉽게 이해됩니다.

윈도우 기능을 사용하면 다음과 같이LAG실행 합계:

WITH cte AS (
 SELECT Id, Name, grp = SUM(CASE WHEN Name = prev THEN 0 ELSE 1 END) OVER(ORDER BY id)
 FROM (SELECT *, prev = LAG(Name) OVER(ORDER BY id) FROM t) s
)
SELECT name, cnt = COUNT(*)
FROM cte
GROUP BY grp,name
ORDER BY grp;

db<> fid 데모

첫번째 cte는 그룹 번호를 반환합니다.

+-----+-------+-----+
| Id  | Name  | grp |
+-----+-------+-----+
|  1  | A     |   1 |
|  2  | A     |   1 |
|  3  | B     |   2 |
|  4  | B     |   2 |
|  5  | B     |   2 |
|  6  | B     |   2 |
|  7  | C     |   3 |
|  8  | B     |   4 |
|  9  | B     |   4 |
+-----+-------+-----+

그리고 주요 쿼리는 이를 기반으로 그룹화합니다.grp앞에서 계산한 열:

+-------+-----+
| name  | cnt |
+-------+-----+
| A     |   2 |
| B     |   4 |
| C     |   1 |
| B     |   2 |
+-------+-----+

재귀 CTE를 사용하고 row_number의 사용을 최소화하며 카운트(*)를 피합니다.

성능이 더 좋을 것 같지만 실제로는 영향을 받는 행의 수를 최소화하기 위해 어떤 필터를 넣느냐에 따라 다릅니다.

ID가 신중한 값을 가지면 연속 ID를 생성하기 위해 하나의 추가 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

언급URL : https://stackoverflow.com/questions/36927685/count-number-of-consecutive-occurrence-of-values-in-table