퀴즈로 배우는 SQL

시작일, 종료일 사이의 휴일을 제외한 일수 구하기

퀴즈로 배우는 SQL에서는 시작일자와 종료일자가 주어졌을 때 해당 기간에서 휴일을 뺀 일수를 계산하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음, 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

[문제]

Date Type의 시작일자, 종료일자 항목을 가진 원본 테이블 ‘<표 1>’로부터 두 날짜 사이의 기간에서 휴일을 빼고 실제로 근무한 일수(<표 2>)를 출력하는 SQL을 작성하세요.

<리스트 1> 원본 리스트WITH t AS
(
SELECT TO_DATE(sdt, 'yyyymmdd') sdt
, TO_DATE(edt, 'yyyymmdd') edt
FROM (
SELECT '20151029' sdt, '20151105' edt FROM dual
UNION ALL SELECT '20151101', '20151117' FROM dual
UNION ALL SELECT '20151103', '20151107' FROM dual
UNION ALL SELECT '20151114', '20151115' FROM dual
)
)
SELECT * FROM t
;

[문제설명]

<리스트 1>은 기간정보 테이블입니다. 이 테이블에는 시작일자, 종료일자 정보가 저장돼 있습니다. 이 기간 동안에 일수를 구하는 문제네요. 단 휴일인 토요일, 일요일을 제외한 일수를 구해야 합니다.

[정답]

문제를 스스로 해결해 보셨나요 이제 정답을 알아보겠습니다.

<리스트 2> 정답 리스트
SELECT sdt, edt
, NVL(
LENGTH(
REPLACE(
RPAD(
SUBSTR('~m^O^m~', TO_CHAR(sdt, 'd'))
, edt - sdt + 1, '~m^O^m~')
, '~')
)
, 0)
cnt
FROM t
;

어떤가요 여러분이 만들어본 답과 같은가요 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

[해설]

이번 문제는 특정 기간 동안 실제로 근무한 일수를 구하는 문제입니다. 특이한 점은 휴일을 제외해야 한다는 것입니다. 두 일자 사이에 토요일과 일요일이 몇 번 나올지를 알아야만 풀 수 있는 문제인데요. 정답 쿼리를 살펴보기 전에 다른 방법부터 살펴보겠습니다. 우선 기간정보를 날짜별로 분리한 뒤 휴일을 제거하는 방법을 함께 해볼까요.

<리스트 3> 기간을 일자로 분리하는 방법
SELECT sdt, edt
, sdt + lv - 1 dt
FROM t
, (SELECT LEVEL lv FROM dual
CONNECT BY LEVEL < = 99)
WHERE lv < = edt - sdt + 1
ORDER BY sdt, edt, dt
;

<리스트 3>의 쿼리를 통해 <표 3>의 결과를 얻었습니다. 1개 행을 여러 행으로 분리하기 위한 더미 테이블이 조인에 사용됐습니다.

, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < = 99)

이 구문은 1부터 99까지 숫자를 가진 행을 만듭니다. 이 집합이 조인에 이용됐습니다. 조인 조건은 다음과 같습니다.

WHERE lv < = edt - sdt + 1

두 날짜 사이의 일수는 edt - sdt + 1로 구할 수 있습니다. 이 조건은 일수보다 숫자 lv가 작거나 같다는 의미인데요. 이렇게 조인을 하게 되면 1개의 행이 일수만큼 복제돼 생성됩니다. 마지막으로 복제된 데이터를 표시할 때에는 다음과 같이 표현하면 됩니다.

SELECT sdt + lv - 1 dt

시작일자에 증가되는 수를 더해줌으로써 일자를 구합니다. 결국 2015-10-29부터 2015-11-05까지의 일자가 생성된 것입니다. 이제 이렇게 분리된 일자를 이용해 결과를 도출해 보겠습니다.

<리스트 4> 분리된 일자로부터 결과 도출SELECT sdt, edt
, COUNT(*) cnt
FROM (SELECT sdt, edt
, sdt + lv - 1 dt
FROM t
, (SELECT LEVEL lv FROM dual
CONNECT BY LEVEL < = 99)
WHERE lv < = edt - sdt + 1
)
WHERE TO_CHAR(dt, 'd') NOT IN (1, 7)
GROUP BY sdt, edt
ORDER BY sdt, edt
;

<리스트 4> 쿼리를 실행한 결과는 <표 4>와 같습니다.

WHERE TO_CHAR(dt, 'd') NOT IN (1, 7)

이 조건은 토요일과 일요일을 제거하는 조건입니다. 날짜를 요일의 숫자를 의미하는 포맷으로 변경한 뒤 일요일과 토요일에 해당하는 1과 7을 제외시킵니다. 1개의 행을 일자만큼 분리시킨 후 휴일을 제거했으니 이제 다시 하나의 행으로 합쳐서 카운트하면 되겠네요. GROUP BY와 COUNT를 이용해 결과를 도출했습니다.

그런데 이렇게 행을 분리하고 다시 합치는 과정은 그닦 효율적이지 않습니다. 이러한 과정을 거치지 않고 바로 계산할 수 있다면 좀 더 효율적이겠죠 이번에는 정답 쿼리로 제시했던 재미있는 방법을 설명 드리겠습니다. <리스트 2>의 정답 쿼리를 보면 다양한 함수들이 여러 번에 걸쳐서 중첩 사용됐는데요. 가장 안쪽 함수부터 차례로 적용시켜며 결과를 확인하겠습니다.

<리스트 5> 단계별 함수 적용 결과
SELECT sdt, edt
, TO_CHAR(sdt, 'd') x1
, SUBSTR('~m^O^m~', TO_CHAR(sdt, 'd')) x2
, RPAD(
SUBSTR('~m^O^m~', TO_CHAR(sdt, 'd'))
, edt - sdt + 1, '~m^O^m~') x3
, REPLACE(
RPAD(
SUBSTR('~m^O^m~', TO_CHAR(sdt, 'd'))
, edt - sdt + 1, '~m^O^m~')
, '~') x4
, LENGTH(
REPLACE(
RPAD(
SUBSTR('~m^O^m~', TO_CHAR(sdt, 'd'))
, edt - sdt + 1, '~m^O^m~')
, '~')
) x5
, NVL(
LENGTH(
REPLACE(
RPAD(
SUBSTR('~m^O^m~', TO_CHAR(sdt, 'd'))
, edt - sdt + 1, '~m^O^m~')
, '~')
)
, 0)
x6
FROM t
;

<리스트 5> 쿼리를 실행한 결과는 <표 5>와 같습니다. 1단계는 TO_CHAR 함수를 이용해 요일을 가져옵니다.

, TO_CHAR(sdt, 'd') x1

요일은 숫자로 표현합니다. 즉, ‘일월화수목금토’가 ‘1234567’가 되는 것입니다. 2단계에서는 SUBSTR을 통해 문자열을 1단계에서 가져온 요일부터 잘라냅니다.

, SUBSTR('~m^O^m~', x1) x2

이때 사용한 문자열 ‘~m^O^m~’ 은 호빵만 이모티콘을 사용했습니다. 7개의 문자로 이루어졌는데, 일주일인 ‘일월화수목금토’와 매칭됩니다. 일요일과 토요일에 해당하는 문자는 일부러 같은 문자로 배치한 것입니다. 이 7개 문자를 요일에 해당하는 수의 자리부터 잘라냅니다. 예를 들면 10월29일은 목요일부터 잘라내어 ‘목금토’에 해당하는 ‘^m~’가 됩니다. 3단계에서는 LPAD를 이용했습니다.

, RPAD(x2, edt - sdt + 1, '~m^O^m~') x3

2단계의 결과물에 전체 일수인 edt sdt + 1 만큼의 자리를 다시 또 호빵맨 문자열로 채워 넣습니다. 10월29일부터 11월5일까지는 총 8일이므로 결과는 ‘^m~~m^O^’가 됩니다. 요일의 의미를 부여하면 ‘목금토일월화수목’이 되겠네요. 4단계에서는 REPLACE를 이용합니다.

, REPLACE(x3, '~')

토요일과 일요일에 해당하는 문자인 ‘~’을 제거하는 것입니다. 결과는 ‘^mm^O^’ 즉, ‘목금월화수목’입니다. 5단계에서는 LENGTH를 이용해 문자길이를 구합니다.

, LENGTH(x4) x5

6단계에서는 NVL을 이용했습니다.

, NVL(x5, 0) x6

11월 14일부터 15일은 토요일과 일요일이므로 문자열을 제거하고 남은 문자가 없습니다. NULL에 대해서 LENGTH 함수는 0이 아닌 NULL을 반환합니다. 따라서 NVL 함수로 NULL을 보정해 줍니다.

<리스트 6> 수식 계산을 통한 일수 구하기SELECT sdt, edt
, edt - sdt + 1
- CEIL((edt - NEXT_DAY(sdt - 1, '토') + 1) / 7)
- CEIL((edt - NEXT_DAY(sdt - 1, '일') + 1) / 7)
AS cnt
FROM t
;

이해가 되셨나요 복잡하게 여러 번의 함수를 중첩해 사용했지만 원리는 간단합니다. 요일을 의미하는 문자열 ‘일월화수목금토’가 시작일과 종료일 사이의 일수만큼 반복되는 형태로 나열한 뒤에 휴일인 ‘토’, ‘일’의 문자를 제거한 뒤 남은 요일문자의 길이를 측정하면 이 길이가 곧 일수가 되는 원리입니다.

앞서 CONNECT BY LEVEL의 계층 쿼리를 이용해 하나의 행을 여러 행으로 분리한 뒤 휴일을 제외시킨 다음 GROUP BY해 COUNT로 집계하는 방식을 살펴봤습니다. 계층쿼리, 조인, 집계쿼리까지 사용되는 쿼리를 이러한 복잡한 과정을 거치지 않고 바로 함수로 문제를 해결하는 방법도 살펴봤습니다. 일수를 구하는 문제를 카운트 문제가 아닌 문자열의 길이 계산 방식으로 접근해 보는 새로운 시도였죠. 독자 여러분도 다양한 방법으로 문제를 해결해 보시기 바랍니다.

출처 : 마이크로소프트웨어 11월호

제공 : 데이터 전문가 지식포털 DBguide.net