SQL 한 문장으로 달력 만들기
재미있는 SQL Quiz
저는 데이터에 대한 관심이 많습니다. 데이터와 관련된 페이스북의 그룹에 가입이 많이 되어 있는데, 재미있는 이벤트를 보게 되었습니다. 간단한 퀴즈를 해결하는 문제인데, 상품이 국내 오픈소스의 대표 주자인 올챙이 커뮤니티에서 제작한 티셔츠였습니다.
간단하게 올챙이에 대해 알아보면, 올챙이(Tadpole DB Hub)는 웹 브라우저에서 Apache Hive, Apache Tajo, MongoDB, Cubrid, MariaDB, Oracle , PostgreSQL 등의 다양한 DBMS를 관리하는 도구입니다.
페이스북에서 제가 본 문제는 다음과 같습니다. 주요 요건은 문제를 SQL 한 문장으로 해결해야 합니다.
문제: 특정 연월(예: 201507)을 입력받아 해당 연월의 달력을 표시하시오.
아주 간단한 문제는 아니지만, 조금만 생각해보면 쉽게 해결할 수 있는 문제입니다.
Oracle
제가 가장 익숙한 DBMS가 오라클이기 때문에, 가장 먼저 오라클을 활용하여 해결했습니다. 이 문제를 해결하는 방법은 여러 가지가 있겠지만, 저는 오라클에서 주로 활용하는 DECODE를 활용한 방법과 SQL:2003 표준인 PIVOT을 활용하여 해결해봤습니다. DECODE와 PIVOT에 관한 내용은 다음 링크에서 확인하실 수 있습니다.
먼저 콘솔 모드에서 데이터를 입력 받는 방법은 다음과 같습니다. Oracle에서는 Accept 명령을 활용하여 사용자 입력을 읽어 변수에 저장할 수 있습니다.
ACCEPT v_input PROMPT 'Input DATE(YYYYMM) : '
이 명령을 수행하면 다음과 같이 프롬프트에 Input DATE(YYYYMM) : 메시지가 보여집니다. 그리고 뒷 부분에 다음과 같이 연월을 입력하면 됩니다. 입력 창에 다음과 같이 201507을 입력하면, ACCEPT 명령에서 지정한 v_input 변수에 값이 저장됩니다.
사용자에게 연월(예: 201507)을 입력받는 명령과 실행 예시이제 사용자로부터 연월 정보를 입력받았으니 해당 연월에 알맞은 달력을 출력해보겠습니다.
SQL 문제를 해결할 때 가장 중요한 점은 문제를 해결하는 데 필요한 원천 데이터를 생성 및 가공하는 것입니다. 다음 두 개의 질의를 살펴보면, 데이터를 생성하는 부분이 같다는 것을 알 수 있습니다. Oracle이나 Cubrid를 자주 사용하신 분들은 CONNECT BY 절을 사용한 질의가 어색하지 않게 느껴질 것이고, 다른 DBMS를 사용하신 분들에게는 CONNECT BY 구문이 생소하게 느껴질 수도 있을 것입니다.
이 글에서 CONNECT BY에 대해 자세한 설명을 하는 것은 논점을 벗어나는 것 같아 생략하고 링크로 대체하겠습니다. 다음에 기회가 된다면 CONNECT BY 구문에 관해 알아보는 시간을 만들어보겠습니다.
DECODE 활용 방법
SELECT WEEK 주차
, MIN(DECODE(WEEKDAY,'1', DAY)) 일
, MIN(DECODE(WEEKDAY,'2', DAY)) 월
, MIN(DECODE(WEEKDAY,'3', DAY)) 화
, MIN(DECODE(WEEKDAY,'4', DAY)) 수
, MIN(DECODE(WEEKDAY,'5', DAY)) 목
, MIN(DECODE(WEEKDAY,'6', DAY)) 금
, MIN(DECODE(WEEKDAY,'7', DAY)) 토
FROM (
SELECT TO_CHAR(YM+LEVEL, 'IW') WEEK
, TO_CHAR(YM+LEVEL-1,'d') WEEKDAY
, LPAD(LEVEL,2,'0') DAY
FROM (SELECT TO_DATE(&v_input, 'YYYYMM') YM FROM DUAL)
CONNECT BY LEVEL <= LAST_DAY(YM)-YM+1
)
GROUP BY WEEK
ORDER BY WEEK
/
PIVOT 활용 방법
SELECT 주차, 일, 월, 화, 수, 목, 금, 토
FROM (
SELECT TO_CHAR(YM+LEVEL,'IW') 주차
, TO_CHAR(YM+LEVEL-1,'d') WEEKDAY
, TO_CHAR(YM+LEVEL-1,'dd') DAY
FROM (SELECT TO_DATE('&v_input', 'YYYYMM') YM FROM DUAL)
CONNECT BY LEVEL <= LAST_DAY(YM)-YM+1
)
PIVOT (MIN(DAY) FOR WEEKDAY IN (1 일, 2 월, 3 화, 4 수, 5 목, 6 금, 7 토))
ORDER BY 1
/
MariaDB
다음으로 MariaDB에서 이 문제를 해결하는 방법입니다. 해결 방법은 Oracle에서 풀이했던 방법 중 첫 번째 방법과 동일합니다. 다만 각 DBMS에서 제공하는 함수의 명칭이 다르며, Oracle과 비교할 때 질의가 더 복잡해 보이는 이유는 가상 집합을 생성하는 부분에서 CONNECT BY 절을 지원하지 않기 때문입니다. 만약 더 편리한 방법을 찾게 되면 갱신하겠습니다.
SET @input_date := ?;
SELECT WEEK AS '주'
, MIN(CASE WHEN WEEKDAY = '2' THEN DAY END) AS '월'
, MIN(CASE WHEN WEEKDAY = '3' THEN DAY END) AS '화'
, MIN(CASE WHEN WEEKDAY = '4' THEN DAY END) AS '수'
, MIN(CASE WHEN WEEKDAY = '5' THEN DAY END) AS '목'
, MIN(CASE WHEN WEEKDAY = '6' THEN DAY END) AS '금'
, MIN(CASE WHEN WEEKDAY = '7' THEN DAY END) AS '토'
, MIN(CASE WHEN WEEKDAY = '1' THEN DAY END) AS '일'
FROM (
SELECT WEEKOFYEAR(CDATE) AS WEEK
, DAYOFWEEK(CDATE) AS WEEKDAY
, DAYOFMONTH(CDATE) AS DAY
FROM (SELECT D1+D2 AS CDATE
FROM (SELECT STR_TO_DATE(@input_date, '%Y-%m-%d') D1) D1,
(SELECT 1 D2 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION
SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION
SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION
SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION
SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION
SELECT 29 UNION SELECT 30 UNION SELECT 31) AS D2 ) AS TEMP
WHERE TEMP.CDATE <= LAST_DAY(TEMP.CDATE)
) AS TEMP2
GROUP BY WEEK;
마치면서
지금까지 Oracle과 MariaDB에서 사용자로부터 특정 연월을 입력받아, 해당 연월의 달력을 출력하는 질의에 대해 알아봤습니다. 다른 DBMS 환경(Cubrid, PostgreSQL)에서의 결과는 틈틈이 작성해서 갱신하겠습니다.
결과에서도 확인할 수 있듯이 간단한 SQL 한 문장으로 이 문제를 해결할 수 있었습니다. CONNECT BY 구문과 PIVOT 절이 익숙하지 않을 수도 있습니다만, 아주 유용한 구문이므로 이 기회에 학습해 두시면 좋을 것 같습니다.
