쿼리 작성 예제 - 연속된 날짜 집합 구하기(row_number)

업무를 하다보면 여러가지 쿼리를 만들어야 하는 상황이 발생하기 마련이다.

다음과 같이 일어날 수 있는 상황을 가정하여 쿼리를 작성해 보기로 한다.

문제.

A라는 사람이 어떤 곳에서 프리랜서로 일하게 되었다.

근무한 날자를 기록하기 위해 어떤 테이블에 근무일자만을 오름차순으로 기록해 두었다.

그런데 프로젝트 기간동안 일정치 않게 일을 하게 되었고

따라서 연속으로 출근하여 일한 날도 있고 출근하지 않은 날도 있었다.

이 때 연속으로 출근한 날짜만을 가져올 수 있을까?

예를 들면 이런 것이다.

work_day
20181201
20181202
20181205
20181206
20181207
20181210
20181211
20181221
20190101
20190102
20190104
20190105
20190107
20190108

 select *


into #test

from (     

select '20181201' as 'work_day' union all

select '20181202' as 'work_day' union all

select '20181205' as 'work_day' union all

select '20181206' as 'work_day' union all

select '20181207' as 'work_day' union all

select '20181210' as 'work_day' union all

select '20181211' as 'work_day' union all

select '20181221' as 'work_day' union all

select '20190101' as 'work_day' union all

select '20190102' as 'work_day' union all

select '20190104' as 'work_day' union all

select '20190105' as 'work_day' union all

select '20190107' as 'work_day' union all

select '20190108' as 'work_day') test

위와 같이 어떤 테이블에 work_day 라는 컬럼으로 날짜가 입력되어 있다.

이 날짜값은 일을 한 날짜만을 기록하였고 일하지 않은 날은 기록되어 있지 않다.

어떻게 하면 연속적으로 일한 날짜를 빠르게 구할 수 있을까?

물론

데이터의 양이 많지 않다면 그냥 눈으로 봐도 금방 찾을수 있다.(아니면 엑셀로 하든지...)

하지만 양이 많아지게 된다면 굉장한 노가다성 작업이 될 가능성이 높다.

이럴 경우 어떻게 쿼리로 빠르게 연속으로 근무한 날짜를 찾아낼 수 일을까?

물론 방법은 여러가지가 있을 수 있지만

순서함수인 "row_number() over"를 사용한 방법을 포스팅 해 보려고 한다.

===============================================

쿼리를 만드는 단계는 다음과 같다.

1. 일단 입력한 날짜 데이터를 row_number 함수를 사용해 순서를 매긴다.

2. 근무한 날짜와 변동이 없는 아무 날짜(여기서는 '20190131'로 정함)를 datediff하여 차이가 나는 일수를 구한다.

3. 순서값과 차이값을 더해서 같은 수가 나오면 연속적으로 일한 날짜.

4. 연속근무일수를 그룹화 하여 시작일과 종료일을 구한다.

-------------------- 결과

 work_day       idx    diff_day Consecutive_day
20181201	1	61	62
20181202	2	60	62
20181205	3	57	60
20181206	4	56	60
20181207	5	55	60
20181210	6	52	58
20181211	7	51	58
20181221	8	41	49
20190101	9	30	39
20190102	10	29	39
20190104	11	27	38
20190105	12	26	38
20190107	13	24	37
20190108	14	23	37

select work_day
, ROW_NUMBER() over(order by work_day) AS 'idx'
, DATEDIFF(day, work_day, '20190131') AS 'diff_day'
, (ROW_NUMBER() over(order by work_day) + DATEDIFF(day, work_day, '20190131')) AS 'Consecutive_day'
from #test

이렇게 하면 'Consecutive_day'라는 컬럼값이 동일한 것들은 연속된 날짜라고 볼 수 있을 것이다.

따라서 Consecutive_day컬럼값을 group으로 묶고

시작한 날짜와 종료한 날짜를 가져오는 쿼리를 추가하면 된다.

-------------------- 최종 결과

 시작일        종료일        연속근무일수

20181201	20181202	2일
20181205	20181207	3일
20181210	20181211	2일
20181221	20181221	1일
20190101	20190102	2일
20190104	20190105	2일
20190107	20190108	2일

 select MIN(a.work_day) AS '시작일', MAX(a.work_day) AS '종료일', cast(COUNT(*) as varchar(5)) + '일' AS '연속근무일수'
	from( 
		select work_day
		, ROW_NUMBER() over(order by work_day) AS 'idx'
		, DATEDIFF(day, work_day, '20191031') AS 'diff_day'
		, (ROW_NUMBER() over(order by work_day) + DATEDIFF(day, work_day, '20191031')) AS 'Consecutive_day'
		from #test
	) a
group by Consecutive_day
order by Consecutive_day desc

결과값을 먼저 보여주고 쿼리는 더보기 버튼을 눌러야 보이도록 포스팅 했다.

고민을 먼저 충분히 한 다음 비교해 본다면 더 재미있을 거라고 생각한다.