꿈꾸는 개발자, DBA 커뮤니티 구루비

이번 퀴즈로 배워보는 SQL 시간에는 조건으로 주어진 월에 해당하는 달력을 만들어 보는 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.

진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.

문제

2015년 3월의 달력을 <표 1> 결과로 도출하는 MS SQL 쿼리를 작성하세요.

[표 1] 2015년 3월 달력

정답

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

[리스트 1] 정답 리스트

WITH t AS
(
SELECT DATEADD(d, 0, '201503'+'01') dt
 UNION ALL
SELECT DATEADD(d, 1, dt) dt
  FROM t
 WHERE dt + 1 < DATEADD(m, 1, '201503'+'01')
)
SELECT [1] 일
     , [2] 월
     , [3] 화
     , [4] 수
     , [5] 목
     , [6] 금
     , [7] 토
  FROM (SELECT DATEPART(d, dt) d
             , DATEPART(w, dt) w
             , DATEPART(ww, dt) ww
          FROM t
        ) a
 PIVOT( MIN(d) FOR w IN
        ([1], [2], [3], [4], [5], [6], [7]) ) a
;

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

해설

이번 문제는 주어진 연월 조건을 이용해 달력을 만들어 내는 문제입니다. 오라클 쿼리에 익숙한 필자는 MS SQL 쿼리로도 달력을 만들어 보고자 했습니다

MS SQL 정답 쿼리를 살펴보기 전에 오라클 쿼리를 먼저 살펴보면서 MS SQL과 비교해 보는 방식으로 접근해 보겠습니다.

[리스트 2] Oracle 달력 쿼리

WITH t AS
(
SELECT dt + LEVEL - 1 dt
  FROM (SELECT TO_DATE('201503', 'yyyymm') dt
          FROM dual)
 CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd')
)
SELECT 일, 월, 화, 수, 목, 금, 토
  FROM (SELECT TO_CHAR(dt, 'dd') d
             , TO_CHAR(dt, 'd' ) w
             , TRUNC(dt, 'd') ww
          FROM t
        )
 PIVOT (MIN(d) FOR w IN
 (1 일, 2 월, 3 화, 4 수, 5 목, 6 금, 7 토))
 ORDER BY ww
;

[표 2] 2015년 3월 달력

<리스트 2>의 쿼리를 이용해 <표 2>의 달력 결과를 얻었습니다. <리스트 2>의 오라클 쿼리를 단계별로 분석해 보면서 MS SQL 쿼리로 변경해 보도록 하겠습니다.

우선 WITH 구문안의 쿼리를 살펴보겠습니다. 주어진 월(201503)의 조건을 이용해 해당 월의 모든 일자를 출력하는 구문입니다.

[리스트 3] 해당 연월 날짜 리스트

SELECT dt + LEVEL - 1 dt
  FROM (SELECT TO_DATE('201503', 'yyyymm') dt
          FROM dual)
 CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd')
;

[표 3] 2015년 3월 날짜 리스트

<리스트 3>의 쿼리를 이용해 <표 3>의 결과를 얻었습니다. 주어진 연월 문자열을 날짜타입으로 바꾸고

(SELECT TO_DATE('201503', 'yyyymm') dt FROM dual)

해당 월의 마지막 일자까지 만큼의 행을 생성합니다.

CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd')

그리고 생성된 행의 LEVEL을 더하여 일자 리스트를 완성합니다.

SELECT dt + LEVEL - 1 dt

오라클에서의 행을 생성해주는 “CONNECT BY LEVEL < = n”구문을 대체할 MS SQL 구문이 마땅히 떠오르질 않습니다.

오라클 11G의 Recursive SQL 구문을 이용한다면 MS SQL 로 대체가 가능할 듯 합니다. “CONNECT BY LEVEL < = n” 구문을 Recursive SQL 구문으로 바꿔보겠습니다.

[리스트 4] n행 생성 구문

-- 1. Connect By LEVEL < = n
SELECT LEVEL lv FROM dual
 CONNECT BY LEVEL < = 31
;
-- 2. Recursive SQL(재귀쿼리)로 대체
WITH t(lv) AS
(
SELECT 1 lv FROM dual
 UNION ALL
SELECT lv + 1
  FROM t
 WHERE lv + 1 < = 31
)
SELECT * FROM t
;

[표 4] n행 생성 결과

<리스트 4>의 쿼리를 이용해 <표 4>의 결과를 얻었습니다. 재귀쿼리 구문을 이용해 동일한 행 생성 결과를 얻었습니다.

Recursive SQL 구문은 WITH 구문으로 정의한 집합을 WITH 구문 안에서 재귀적으로 다시 참조해 UNION ALL로 결과를 붙여나가는 형태입니다.

이는 MS SQL에서도 그대로 적용이 가능한 구문입니다. 이 구문을 이용해 날짜리스트를 생성해 보겠습니다.

[리스트 5] MS SQL에서 날짜 리스트 생성

WITH t AS
(
SELECT DATEADD(d, 0, '201503'+'01') dt
 UNION ALL
SELECT DATEADD(d, 1, dt) dt
  FROM t
 WHERE dt + 1 < DATEADD(m, 1, '201503'+'01')
)
SELECT *
  FROM t
;

[표 5] 2015년 3월 날짜 리스트

<리스트 5>의 쿼리를 이용해 <표 5>의 결과를 얻었습니다. 주어진 연월 문자열을 날짜타입으로 바꿨습니다.

SELECT DATEADD(d, 0, '201503'+'01') dt

MS SQL에서는 오라클과 달리 FROM dual이 필요 없습니다. DATEADD는 주어진 타입으로 주어진 수만큼 날짜를 더하는 구문입니다. 앞에서는 0일을 더하는 구문입니다. 일부러 0일을 더해준 이유는 문자열을 날짜타입으로 변경하기 위함입니다. 이 방법 외에도 명시적인 형변환 구문을 이용해도 됩니다.

SELECT CONVERT(DATETIME, '201503'+'01', 120) dt
SELECT CAST('201503'+'01' AS DATETIME) dt

다음은 재귀쿼리 구문을 이용해 하루씩을 더해 나갑니다.

SELECT DATEADD(d, 1, dt) dt FROM t

이렇게만 재귀쿼리를 사용한다면 이 쿼리는 끝이 없이 반복하여 돌게 됩니다. 마지막 일자까지만 더하고 끝내도록 조건을 추가해야만 합니다.

WHERE dt + 1 < DATEADD(m, 1, '201503'+'01')

이전 일자에 하루 더해진 날짜가 2015년3월1일에 한달을 더한 일자보다 작아야 한다는 조건입니다. 재귀쿼리를 이용해 일자 리스트를 완성했습니다. 이번에는 일자 리스트를 이용해 달력을 만드는 과정을 살펴보겠습니다. 우선 오라클 쿼리를 보겠습니다.

[리스트 6] 달력 생성을 위한 날짜자료 변환

WITH t AS
(
SELECT dt + LEVEL - 1 dt
  FROM (SELECT TO_DATE('201503', 'yyyymm') dt
          FROM dual)
 CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd')
)
SELECT TO_CHAR(dt, 'dd') d
     , TO_CHAR(dt, 'd' ) w
     , TRUNC(dt, 'd') ww
  FROM t
;

[표 6] 달력 생성을 위한 날짜자료 변환 리스트

<리스트 6>의 쿼리를 이용해 <표 6>의 결과를 얻었습니다. TO_CHAR를 이용해 일자로부터 필요 자료를 추출합니다.

TO_CHAR(dt, 'dd') 를 이용해 일자 d를 추출합니다. TO_CHAR(dt, 'd')를 이용해 요일정보 w를 추출합니다.

TRUNC(dt, 'd')를 이용해 주의 시작일 ww를 구합니다. 이렇게 구한 3개의 값을 이용해 달력을 완성할 수 있습니다.

ww는 일주일 기간을 구룹으로 묶어주는 기준 역할을 합니다. w는 일주일 기간을 요일별 컬럼으로 나누는 역할을 합니다. d는 최종 출력값의 역할을 하게 됩니다.

이 3개 컬럼의 값을 이용해 행을 열로 바꾸는 과정을 거치면 달력이 완성됩니다.

[리스트 7] Oracle 달력 쿼리

WITH t AS
(
SELECT dt + LEVEL - 1 dt
  FROM (SELECT TO_DATE('201503', 'yyyymm') dt
          FROM dual)
 CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd')
)
SELECT MIN(DECODE(w, 1, d)) 일
     , MIN(DECODE(w, 2, d)) 월
     , MIN(DECODE(w, 3, d)) 화
     , MIN(DECODE(w, 4, d)) 수
     , MIN(DECODE(w, 5, d)) 목
     , MIN(DECODE(w, 6, d)) 금
     , MIN(DECODE(w, 7, d)) 토
  FROM (SELECT TO_CHAR(dt, 'dd') d
             , TO_CHAR(dt, 'd' ) w
             , TRUNC(dt, 'd') ww
          FROM t
        )
 GROUP BY ww
 ORDER BY ww
;

<리스트 7>의 쿼리는 GROUP BY 와 MIN(DECODE()) 를 이용해 행을 열로 바꾸는 구문입니다. 이 구문은 간단하게 PIVOT 구문을 이용해 표현할 수도 있습니다.

PIVOT (MIN(d) FOR w IN
(1 일, 2 월, 3 화, 4 수, 5 목, 6 금, 7 토))

같은 원리로 MS SQL에서 d, w, ww 이 3개 값을 뽑는 쿼리를 만들어 보면 다음과 같습니다.

[리스트 8] MS SQL 에서의 날짜 자료 변환

WITH t AS
(
SELECT DATEADD(d, 0, '201503'+'01') dt
 UNION ALL
SELECT DATEADD(d, 1, dt) dt
  FROM t
 WHERE dt + 1 < DATEADD(m, 1, '201503'+'01')
)
SELECT *
  FROM (SELECT DATEPART(d, dt) d
             , DATEPART(w, dt) w
             , DATEPART(ww, dt) ww
          FROM t
        ) a
;

[표 7] MS SQL 에서의 날짜자료 변환 리스트

<리스트 8>의 쿼리를 이용해 <표 8>의 결과를 얻었습니다. 오라클에서 TO_CHAR를 이용한것과 마찬가지로 MS SQL에서는 DATEPART 함수를 이용해 일자로부터 필요 자료를 추출합니다.

DATEPART(d, dt)를 이용해 일자 d를 추출합니다. DATEPART (w, dt)를 이용해 요일정보 w를 추출합니다. TRUNC(ww, dt)를 이용해 주차 ww를 구합니다.

이 3개 값을 이용해 마지막으로 PIVOT 구문을 완성하면 <리스트 1>의 MS SQL 정답쿼리가 완성됩니다.

이번 퀴즈로 배우는 SQL 시간에는 오라클 달력 쿼리와 MS SQL 달력쿼리를 서로 비교해 보며 완성해 보았습니다.