이번 퀴즈로 배워보는 SQL 시간에는 주어진 연월에 해당하는 달력을 만들어 보는 문제를 풀어본다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
문제
연월('201402') 조건을 이용해 [표 1] 형태의 달력 결과를 도출하는 쿼리를 작성하세요.
- [표 1] 2014년 2월 달력
-
일 월 화 수 목 금 토 ---- ---- ---- ---- ---- ---- ---- 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 1] 정답 리스트
SELECT 일, 월, 화, 수, 목, 금, 토
FROM (SELECT TRUNC (dt + LEVEL - 1, 'd') w
, TO_CHAR(dt + LEVEL - 1, 'd') d
, TO_CHAR(dt + LEVEL - 1, 'dd') dd
FROM (SELECT TO_DATE('201402','yyyymm') dt FROM dual)
CONNECT BY LEVEL < = LAST_DAY(dt) - dt + 1
)
PIVOT ( MIN(dd) FOR d IN
(1 일, 2 월, 3 화, 4 수, 5 목, 6 금, 7 토) )
ORDER BY w
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 주어진 연월 조건을 이용해 달력을 만드는 문제입니다. 정답 쿼리를 살펴보기 전에 차근차근 단계별로 문제에 접근해 보도록 합시다.
- [리스트 2] 원하는 행만큼 숫자 리스트
-
SELECT LEVEL lv FROM dual CONNECT BY LEVEL < = 28;
- [표 2] 숫자 리스트
-
LV -------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
<리스트 2>의 쿼리를 이용해 <표 2>의 결과를 얻었습니다. 계층 쿼리 구문인 CONNECT BY와 LEVEL을 이용하면 아주 간단하게 원하는 행만큼의 숫자 리스트를 얻을 수 있습니다.
여러모로 쓸모가 있는 구문이니 기억해 두면 좋습니다. 이번에는 주어진 문자열 형태(‘201402’)의 조건을 이용해 해당 월의 모든 일자를 출력해 봅시다.
- [리스트 3] 해당 연월 날짜 리스트
-
SELECT dt + LEVEL - 1 AS dt FROM (SELECT TO_DATE('201402', 'yyyymm') dt FROM dual) CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd') ;
- [표 3] 2014년 2월 날짜 리스트
-
DT ------------ 2014-02-01 2014-02-02 2014-02-03 2014-02-04 2014-02-05 2014-02-06 2014-02-07 2014-02-08 2014-02-09 2014-02-10 2014-02-11 2014-02-12 2014-02-13 2014-02-14 2014-02-15 2014-02-16 2014-02-17 2014-02-18 2014-02-19 2014-02-20 2014-02-21 2014-02-22 2014-02-23 2014-02-24 2014-02-25 2014-02-26 2014-02-27 2014-02-28
<리스트 3>의 쿼리를 이용해 <표 3>의 결과를 얻었습니다. 지금 다루는 문제는 연월 및 날짜, 요일, 달력 등 모두 날짜와 관련된 것으로 주로 날짜 함수를 사용하게 됩니다. 따라서 문자열인 ‘201402’를 TO_DATE 함수를 이용해 날짜형으로 변경했습니다.
연월(‘yyyymm’)만으로 날짜 변형을 하면 일자는 기본으로 1일이 됩니다(2014년 2월 1일). 해당 연월(2014년 2월)의 마지막 일자(28일)만큼의 조건을 주기 위해 LAST_DAY를 이용해 마지막 일자를 구하고 TO_CHAR 함수를 이용해 일자(28)만 뽑아 CONNECT BY LEVEL < = 28의 조건에 대입시켰습니다.
SELECT 절에서는 초기 날짜(2014년 2월 1일)에 LEVEL(1 ~ 28)을 더하고 1을 뺌으로써 2014년 2월 1일부터 2014년 2월 28일까지의 날짜를 구하게 됩니다.
2014년 2월의 날짜를 1열로 모두 구했다면, 이제 1열의 날짜를 7열(요일별)의 형태로 바꿔야 합니다. 이렇게 바꾸기 위해서는 행과 열을 구별하는 기준이 필요합니다.
- [리스트 4] 행과 열 구분
-
SELECT dt + LEVEL - 1 AS dt , LPAD(LEVEL, 2, '0') dd , TO_CHAR(dt + LEVEL - 1, 'd') d , TRUNC(dt + LEVEL - 1, 'd') w1 , TO_CHAR(dt + LEVEL, 'iw') w2 FROM (SELECT TO_DATE('201402','yyyymm') dt FROM dual) CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd') ;
- [표 4] 2014년 2월 날짜 리스트
-
DT DD D W1 W2 ------------ ---------------- -- ---------- ---- 2014-02-01 01 7 2014-01-26 05 2014-02-02 02 1 2014-02-02 06 2014-02-03 03 2 2014-02-02 06 2014-02-04 04 3 2014-02-02 06 2014-02-05 05 4 2014-02-02 06 2014-02-06 06 5 2014-02-02 06 2014-02-07 07 6 2014-02-02 06 2014-02-08 08 7 2014-02-02 06 2014-02-09 09 1 2014-02-09 07 2014-02-10 10 2 2014-02-09 07 2014-02-11 11 3 2014-02-09 07 2014-02-12 12 4 2014-02-09 07 2014-02-13 13 5 2014-02-09 07 2014-02-14 14 6 2014-02-09 07 2014-02-15 15 7 2014-02-09 07 2014-02-16 16 1 2014-02-16 08 2014-02-17 17 2 2014-02-16 08 2014-02-18 18 3 2014-02-16 08 2014-02-19 19 4 2014-02-16 08 2014-02-20 20 5 2014-02-16 08 2014-02-21 21 6 2014-02-16 08 2014-02-22 22 7 2014-02-16 08 2014-02-23 23 1 2014-02-23 09 2014-02-24 24 2 2014-02-23 09 2014-02-25 25 3 2014-02-23 09 2014-02-26 26 4 2014-02-23 09 2014-02-27 27 5 2014-02-23 09 2014-02-28 28 6 2014-02-23 09
<리스트 4>의 쿼리를 이용해 <표 4>의 결과를 얻었습니다. dd는 달력에 표시될 일자로, LPAD(LEVEL, 2, ‘0’)을 이용했습니다. d는 요일을 나타내는 숫자입니다. 이는 열을 구별하는 기준이 됩니다.
w1은 일주일의 시작 날짜로, TRUNC 함수와 요일포맷(‘d’)을 이용해 구했습니다. w2는 1년 중의 주차를 ISO 기준으로 표시합니다. 주차포맷(‘iw’)은 월요일이 일주일의 시작이므로 보정을 위해 1일을 더해줬습니다.
w1, w2 모두 행을 나누는 기준이 될 수 있습니다. 여기서는 w1을 선택하겠습니다.
- [리스트 5] 행열 전환
-
SELECT MIN(DECODE(d, '1', dd)) 일 , MIN(DECODE(d, '2', dd)) 월 , MIN(DECODE(d, '3', dd)) 화 , MIN(DECODE(d, '4', dd)) 수 , MIN(DECODE(d, '5', dd)) 목 , MIN(DECODE(d, '6', dd)) 금 , MIN(DECODE(d, '7', dd)) 토 FROM (SELECT TRUNC (dt + LEVEL - 1, 'd') w , TO_CHAR(dt + LEVEL - 1, 'd') d , LPAD(LEVEL, 2, '0') dd FROM (SELECT TO_DATE('201402','yyyymm') dt FROM dual) CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd') ) GROUP BY w ORDER BY w ;
<리스트 5>의 쿼리를 이용해 <표 1>의 정답 결과를 얻었습니다. 행의 기준인 w(한 주의 시작일)로 GROUP BY하고 MIN (DECODE()) 구문을 이용해 d(요일)의 값에 따라 열을 나눴습니다.
GROUP BY와 MIN(DECODE())를 이용해 행열 전환을 했습니다. 이는 PIVOT 구문을 이용해 좀더 간단하게 바꿀 수 있습니다.
- [리스트 6] PIVOT
-
SELECT 일, 월, 화, 수, 목, 금, 토 FROM (SELECT TRUNC (dt + LEVEL - 1, 'd') w , TO_CHAR(dt + LEVEL - 1, 'd') d , LPAD(LEVEL, 2, '0') dd FROM (SELECT TO_DATE('201402','yyyymm') dt FROM dual) CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd') ) PIVOT ( MIN(dd) FOR d IN (1 일, 2 월, 3 화, 4 수, 5 목, 6 금, 7 토) ) ORDER BY w ;
완성된 <리스트 6>의 쿼리가 맞는 쿼리인지 다른 데이터를 이용해 검증해보겠습니다.
- [리스트 7] 1582년 10월 데이터 검증1
-
SELECT 일, 월, 화, 수, 목, 금, 토 FROM (SELECT TRUNC (dt + LEVEL - 1, 'd') w , TO_CHAR(dt + LEVEL - 1, 'd') d , LPAD(LEVEL, 2, '0') dd FROM (SELECT TO_DATE('158210','yyyymm') dt FROM dual) CONNECT BY LEVEL < = TO_CHAR(LAST_DAY(dt), 'dd') ) PIVOT ( MIN(dd) FOR d IN (1 일, 2 월, 3 화, 4 수, 5 목, 6 금, 7 토) ) ORDER BY w ;
- [리스트 8] 1582년 10월 데이터 검증2
-
SELECT 일, 월, 화, 수, 목, 금, 토 FROM (SELECT TRUNC (dt + LEVEL - 1, 'd') w , TO_CHAR(dt + LEVEL - 1, 'd') d , TO_CHAR(dt + LEVEL - 1, 'dd') dd FROM (SELECT TO_DATE('158210','yyyymm') dt FROM dual) CONNECT BY LEVEL < = LAST_DAY(dt) - dt + 1 ) PIVOT ( MIN(dd) FOR d IN (1 일, 2 월, 3 화, 4 수, 5 목, 6 금, 7 토) ) ORDER BY w ;
- [표 5] 1582년 10월 달력 1
-
일 월 화 수 목 금 토 ---- ---- ---- ---- ---- ---- ---- 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
- [표 6] 1582년 10월 달력 2
-
일 월 화 수 목 금 토 ---- ---- ---- ---- ---- ---- ---- 01 02 03 04 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
582년 10월을 검증용 데이터로, <리스트 7>과 <리스트 8>의 쿼리를 실행해 서로 다른 <표 5>, <표 6>의 결과를 얻었습니다. 왜 결과가 다를까요?
그리고 어떤 결과가 맞는 결과일까요? 언뜻 보면 <표 6>의 결과가 이상하게 보이므로 틀린 결과인 것 같습니다만, <표 6>이 맞는 결과입니다.
이를 확인하기 위해서는 지금 사용하고 있는 달력의 역사적, 과학적 배경을 알아야 합니다. 1년은 365일이지만, 태양의 공전주기는 365.2422일입니다.
이를 보전하기 위해 4년에 한번은 윤년(366일)으로 한 율리우스력이 사용됐습니다. 이 달력의 문제는 365.2422일이 아닌 365.25일이 기준이라는 것이었습니다. 수백 년의 세월이 지나면서 오차가 누적됐고 10일의 차이가 발생하게 됐습니다.
1582년 교황 그레고리는 이 오차를 보정하기 위해 1582년 10월 5일을 1582년 10월 15일로 정하고 새로운 그레고리력을 사용하게 됩니다. 즉, 달력에서 1582년 10월 5일부터 1582년 10월 14일까지의 날짜가 사라지게 된 것입니다.
그레고리력에서는 율리우스력에서의 오차를 보정하기 위해 4년에 한번 366일이 되는 것은 유지하되, 100년 단위로는 366일이 아닌 365일이 되며, 400년에 한 번씩만 366일이 됩니다.
결과적으로 <리스트 7>에는 두 가지 오류가 있었습니다. <리스트 8>의 정답 쿼리와 비교하면서 무엇이 오류인지 직접 고민해 보시기 바랍니다.
- 강좌 URL : http://www.gurubee.net/lecture/2863
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.