[SQL] 연속된 년도 구하기

연속된 년도 구하기

DB에 다음과 같은 데이터가 있다고 가정했을 때, 연속된 년도(혹은 어떤 숫자든)가 얼마나 있는지 확인하는 쿼리

ex ) [인원별 수상내역]테이블이 아래와 같을때 2년이상 연속으로 수상한 사람을 구하시오

수상년도	성명
2014	A
2013	A
2016	B
2020	B
2020	C
2021	C
2019	C

=> A(2013, 2014 2년연속 수상), B(2019, 2020, 2021 3년 연속 수상)를 출력해야 한다.

 

방법

핵심 아이디어는 "년도를 오름차순으로 정리하고 순서대로 인덱스를 매기는것" 그 후 현재년도(혹은 수상년도 컬럼에서 가장 max값)에서 수상년도값을 뺀값과 index를 더하는것

1. 수상년도와 이름을 오름차순으로 정렬

수상년도	성명
2013	A
2014	A
2016	B
2020	B
2019	C
2020	C
2021	C

 

2. 순서대로 index 부여

index	수상년도	성명
1	2013	A
2	2014	A
3	2016	B
4	2020	B
5	2019	C
6	2020	C
7	2021	C

 

3. 현재년도 - 수상년도 컬럼을 생성

index	수상년도	현재년도-수상년도(2024-수상년도)	성명
1	2013	11	A
2	2014	10	A
3	2016	8	B
4	2020	4	B
5	2019	5	C
6	2020	4	C
7	2021	3	C

 

4. {(현재년도 - 수상년도 컬럼값)과 index값의 합} 컬럼을 생성

index	수상년도	현재년도-수상년도(2024-수상년도)	index + (2024-수상년도)	성명
1	2013	11	12	A
2	2014	10	12	A
3	2016	8	11	B
4	2019	5	9	C
5	2020	4	10	B
6	2020	4	10	C
7	2021	3	10	C

 

5. 여기서 {(현재년도 - 수상년도 컬럼값)과 index값의 합}  값이 같은것들을 묶으면 연속된 숫자들임이 보장된다.

index	수상년도	현재년도-수상년도(2024-수상년도)	index + (2024-수상년도)	성명
1	2013	11	12	A
2	2014	10	12	A
3	2016	8	11	B
4	2019	5	9	C
5	2020	4	10	B
6	2020	4	10	C
7	2021	3	10	C

 

쿼리

select 성명,cnt
from (select 성명, count(*) cnt
       from (SELECT 성명, 수상년도, row_number() over (partition by 성명 order by 수상년도 ) indx , 2024-수상년도 year_diff
             from 수상테이블)
       group by 성명, idx+year_diff)
where cnt>=2;

성명	cnt
A	2
B	3