MySQL 쿼리로 인자화된 달력 표현하기

1 What’s this?

국산 오픈소스 멀티플랫폼 DB 클라이언트 올챙이 에서 진행한 이벤트에 제출한 답안과 풀이 입니다. 문제를 풀면서 배운점들이 있었기 때문에 개인적인 기록 차원에서 정리합니다.

2 문제

특정 년월의 달력을 표시하세요. 아래는 2015년 7월을 표시한 예시입니다. 입력을 201507 을 했을 경우 아래와 같이 나옵니다. 문제를 내신 부자형님은 MySQL로 순수한 쿼리로만 답을 내었다고합니다. (create table, insert into 문이 없습니다)

week 일 월 화 수 목 금 토

26 1 2 3 4

27 5 6 7 8 9 10 11

28 12 13 14 15 16 17 18

29 19 20 21 22 23 24 25

30 26 27 28 29 30 31

3 제출한 답안

SELECT
        woy                                                     AS 'week'
        ,MAX(CASE WHEN `dow` = '2' THEN `dom_f` END)            AS 'Mo'
        ,MAX(CASE WHEN `dow` = '3' THEN `dom_f` END)            AS 'Tu'
        ,MAX(CASE WHEN `dow` = '4' THEN `dom_f` END)            AS 'We'
        ,MAX(CASE WHEN `dow` = '5' THEN `dom_f` END)            AS 'Th'
        ,MAX(CASE WHEN `dow` = '6' THEN `dom_f` END)            AS 'Fr'
        ,MAX(CASE WHEN `dow` = '7' THEN `dom_f` END)            AS 'Sa'
        ,MAX(CASE WHEN `dow` = '1' THEN `dom_f` END)            AS 'Su'
FROM
(
    SELECT
            *
    FROM
    (
        SELECT
                `date_string`
                ,DAYOFMONTH(`date_string`)             AS dom           -- day of month
                ,DAYOFWEEK(`date_string`)              AS dow           -- day of week( 1 - sunday, 2 - monday ... )
                ,WEEKOFYEAR(`date_string`)             AS woy           -- week of year
                ,LPAD(DAYOFMONTH(`date_string`),2,'0') AS dom_f         -- left paded day of month string( 1 -> 01 )
        FROM
        (
            SELECT      CONCAT(?,'-',n) AS date_string                  -- parameterize
            FROM
            (
                SELECT '1' AS n                                         -- Make dynamic table in foolish way due to MySQL dosn't have generator
                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' UNION SELECT '32' UNION SELECT '33' UNION SELECT '34' UNION SELECT '35'
            ) AS a
        ) AS b
    ) AS c
    WHERE
        `dom` IS NOT NULL
) AS d
GROUP BY `woy`

4 결과

SQL 중간 ? 문 부분을 원하는 년과 달을 표현하는 문자열(ex: 2012-07, 2013-02 …)로 치환해서 실행시키면 됩니다.

cat query.sql | sed s/\?/\'2011-07\'/ | mysql

week	Mo	Tu	We	Th	Fr	Sa	Su
27	NULL	NULL	01	02	03	04	05
28	06	07	08	09	10	11	12
29	13	14	15	16	17	18	19
30	20	21	22	23	24	25	26
31	27	28	29	30	31	NULL	NULL

5 풀이

결과를 만들어내는 과정을 순서대로 살펴보겠습니다.

5.1 동적 테이블 생성

모든 달은 적게는 28일에서 많게는 31일까지의 ‘일’을 갖습니다. 우리는 이 숫자들을 달력에 표현해야 하기 때문에 필연적으로 1부터 28~31 까지의 숫자가 필요합니다.

각 달마다 가지는 ‘일’의 갯수가 다르다는 점은 차처 하더라도, 우리는 1부터 n 까지의 숫자를 행으로 가진 임의의 테이블이 필요합니다. 하지만 MySQL은 이런 ‘임의의 테이블’을 동적으로 만들 수 있는 우아한 방법이 없습니다.

따라서 일반적으로 사용하는 방법들을 활용해서 1부터 35까지의 숫자를 만들어보면 아래와 같습니다.

로컬 변수를 이용하는 방법

SELECT  @rownum := @rownum + 1 AS n
FROM    `mysql`.`help_relation` JOIN (SELECT @rownum:=0) r
WHERE   @rownum < 35

UNION을 이용하는 방법

SELECT '1' AS n
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' UNION SELECT '32' UNION SELECT '33' UNION SELECT '34' UNION SELECT '35'

이 두가지 방법중에서 첫번째 방법은 FROM 절에 생성하고자 하는 숫자보다 행이 더 많은 테이블이 필요하기 때문에 외부 테이블에 대한 의존성이 생깁니다. 따라서 좀 내용이 길긴 하지만 방어적으로 외부의존성이 없는 UNION을 이용하는 방법을 선택해서 임의의 테이블을 생성했습니다. (만약 생성해야 되는 수가 큰 경우 아래와 같이 카티전 곱(cartesian product)을 활용해서 테이블을 만드는 창의적인 방법도 있습니다)

SELECT ( H * 100 + T * 10 + U + 1) X
FROM
    (SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
    (SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
    (SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
ORDER BY X;

5.2 내장 함수를 이용한 2차 자료 생성

SELECT
        *
FROM
(
    SELECT
            `date_string`
            ,DAYOFMONTH(`date_string`)             AS dom           -- day of month
            ,DAYOFWEEK(`date_string`)              AS dow           -- day of week( 1 - sunday, 2 - monday ... )
            ,WEEKOFYEAR(`date_string`)             AS woy           -- week of year
            ,LPAD(DAYOFMONTH(`date_string`),2,'0') AS dom_f         -- left paded day of month string( 1 -> 01 )
    FROM
    (
        SELECT      CONCAT('2015-07','-',n) AS date_string                  -- parameterize
        FROM
        (
            SELECT '1' AS n                                         -- Make dynamic table in foolish way due to MySQL dosn't have generator
            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' UNION SELECT '32' UNION SELECT '33' UNION SELECT '34' UNION SELECT '35'
        ) AS a
    ) AS b
) AS c
WHERE
    `dom` IS NOT NULL

먼저 위에서 생성한 동적 테이블을 CONCAT 내장함수를 이용해서 우리가 만들고자 하는 날짜형식으로 문자열로 변환합니다. 즉 만들고자 하는 달력이 2015년 7월이고 사용자로 부터 받은 요청이 ‘2015-07’이라면 위에서 만든 1부터 35까지의 숫자는 ‘2015-07-1’ 부터 ‘2015-07-35’ 문자열로 치환되는 방식입니다.

그리고 MySQL의 내장 날짜와 시간 함수를 활용해서 다음의 값들을 얻어냅니다.

DAYOFMONTH : 해당 문자열에서 ‘일’에 해당하는 숫자를 구합니다. 잘못된 날짜인 경우 NULL을 반환합니다. (ex: 2015-07-33) // dom
DAYOFWEEK : 해당 문자열의 ‘요일’에 해당하는 색인 숫자를 구합니다. 1부터 7까지의 숫자가 ‘일요일’ 부터 ‘토요일’에 매칭됩니다. // dow
WEEKOFYEAR : 해당 문자열의 ‘주차’에 해당하는 숫자를 구합니다. (‘주차’ 개념은 생각보다 복잡합니다. 조금 뒤에서 다룹니다.) // woy
LPAD : 달력에 표시할 ‘일’에 해당하는 문자열이 한자리인 경우 왼쪽에 0으로 자리체움을(left padding)한 문자열을 구합니다. // dom_f

이때 WHERE 절 조건으로 dom(day of month)가 NULL 인 값을 걸러줍니다. 각 월마다 유동적인 숫자의 날짜가 존재할 수 있기때문에 앞서 살펴본 동적 쿼리를 통해 넉넉한 수의 ‘일’을 생성한 뒤 DAYOFMONTH 내장함수를 통해 날짜가 올바른지 검사하는 방법입니다.

요청이 2015-07일때 여기까지 진행된 결과는 아래와 같습니다.

date_string	dom	dow	woy	dom_f
2015-07-1	1	4	27	01
2015-07-2	2	5	27	02
2015-07-3	3	6	27	03
2015-07-4	4	7	27	04
2015-07-5	5	1	27	05
2015-07-6	6	2	28	06
2015-07-7	7	3	28	07
2015-07-8	8	4	28	08
2015-07-9	9	5	28	09
2015-07-10	10	6	28	10
2015-07-11	11	7	28	11
2015-07-12	12	1	28	12
2015-07-13	13	2	29	13
2015-07-14	14	3	29	14
2015-07-15	15	4	29	15
2015-07-16	16	5	29	16
2015-07-17	17	6	29	17
2015-07-18	18	7	29	18
2015-07-19	19	1	29	19
2015-07-20	20	2	30	20
2015-07-21	21	3	30	21
2015-07-22	22	4	30	22
2015-07-23	23	5	30	23
2015-07-24	24	6	30	24
2015-07-25	25	7	30	25
2015-07-26	26	1	30	26
2015-07-27	27	2	31	27
2015-07-28	28	3	31	28
2015-07-29	29	4	31	29
2015-07-30	30	5	31	30
2015-07-31	31	6	31	31

이 결과는 제가 예상했던 결과와 거의 비슷합니다. 하지만 자세히 살펴보면 출제된 예시결과에서 2015년 7월 1일이 2015년의 26주라고 표현되고 있었던 반면, 위 결과는 27주라고 표현된것을 볼 수 있습니다. 문제를 풀던 시점에는 제 결과가 MySQL 내장함수를 통해 나온 결과이며, 달력을 눈으로 확인했을때도 2015년 7월은 27주가 맞는것 처럼 보였기 때문에 단순히 출제하신분의 실수라고 생각하고 넘어갔습니다.

하지만 이 문제를 좀 더 확인해본 결과 이 이슈는 출제하신 분의 실수가 아니라, 년에서 주차를 표현하는 방식이 여러가지가 존재하기 때문에 생긴 이슈였습니다. 이 이슈는 위키피디아의 Week 페이지 의 ‘Week numbering’ 섹션에 자세하게 설명되어 있습니다. 간략하게 요약하면, 년에서 주차를 계산하는 방법식은 대표적으로 유럽에서 주로 사용하는 ISO8601 방식과 북미에서 주로 사용하는 방식이 존재하며 아래와 같은 차이점이 있다는 내용입니다.

ISO8601
- EU와 대부분의 유럽국가에서 사용
- 각각의 주는 ‘월요일’ 부터 시작함
- 새해의 첫번째 ‘목요일’을 새해의 첫 주로 계산함
- 따라서 새해의 첫주는 최소 4일부터 최대 7일이 될 수 있음
- 1년은 1주부터 53주로 구성됨
- 새해의 첫주는 작년 마지막주의 일부가 될 수 없음
북미방식
- Canada, USA, China, Japan, Israel, most of Latin America 에서 사용
- 각각의 주는 ‘일요일’ 부터 시작함
- 새해의 첫번째 ‘토요일’을 새해의 첫 주로 계산함
- 1년은 0주부터 53주로 구성됨
- 새해의 첫주는 작년 마지막주의 일부가 될 수 있음(0주)

이 두 방식은 MySQL의 내장 환경변수인 default_week_format 에 영향을 받으며, 내장함수인 WEEK 는 위 환경변수를 인자로 받아 적절하게 지역로케일에 맞는 날짜형식을 표현하는 구조로 되어있습니다. 즉 제가 작업한 환경은 ISO8601 방식의 로케일을 사용하고 있으며, 출제하신 분이 작업하신 환경은 북미방식의 로케일을 사용하고 있음을 추측해 볼 수 있습니다.

5.3 Column Row Transformation

SELECT
        woy                                                     AS 'week'
        ,MAX(CASE WHEN `dow` = '2' THEN `dom_f` END)            AS 'Mo'
        ,MAX(CASE WHEN `dow` = '3' THEN `dom_f` END)            AS 'Tu'
        ,MAX(CASE WHEN `dow` = '4' THEN `dom_f` END)            AS 'We'
        ,MAX(CASE WHEN `dow` = '5' THEN `dom_f` END)            AS 'Th'
        ,MAX(CASE WHEN `dow` = '6' THEN `dom_f` END)            AS 'Fr'
        ,MAX(CASE WHEN `dow` = '7' THEN `dom_f` END)            AS 'Sa'
        ,MAX(CASE WHEN `dow` = '1' THEN `dom_f` END)            AS 'Su'
FROM
(
    SELECT
            *
    FROM
    (
        SELECT
                `date_string`
                ,DAYOFMONTH(`date_string`)             AS dom           -- day of month
                ,DAYOFWEEK(`date_string`)              AS dow           -- day of week( 1 - sunday, 2 - monday ... )
                ,WEEKOFYEAR(`date_string`)             AS woy           -- week of year
                ,LPAD(DAYOFMONTH(`date_string`),2,'0') AS dom_f         -- left paded day of month string( 1 -> 01 )
        FROM
        (
            SELECT      CONCAT(?,'-',n) AS date_string                  -- parameterize
            FROM
            (
                SELECT '1' AS n                                         -- Make dynamic table in foolish way due to MySQL dosn't have generator
                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' UNION SELECT '32' UNION SELECT '33' UNION SELECT '34' UNION SELECT '35'
            ) AS a
        ) AS b
    ) AS c
    WHERE
        `dom` IS NOT NULL
) AS d
GROUP BY `woy`

지금까지 진행한 결과를 달력형식으로 보여주는것은 행(row) 형태로 표현된 자료(narrow, stacked, tall skinny)를 고정된 열(column) 형태로 표현된 자료(wide, unstacked, short fat) 형태로 표현하는 변환작업 입니다.

역시 MySQL은 이 작업을 위한 우아한 방법 을 제공하지않기 떄문에 일반적으로 사용하는 GROUP BY와 CASE WHEN 을 사용하는 방식으로 문제를 해결합니다. 이 기법은 GROUP BY를 사용한 뒤 집합(aggregate)함수 내부에서 CASE WHEN 문을 조건분기처럼 사용할수 있는 특성을 이용한 방법으로, MySQL에서 행형태로 표현된 자료를 열형태로 변환(pivot) 할때 자주 사용하는 방법입니다.

이 방법을 통해서 각각의 행의 dow 값에 따라 적절한 dom_f 값을 지정한 ‘요일’을 나타내는 열에 나타내서 최종적으로 달력과 같은 모양을 만들어 낼 수 잇습니다.

6 마치며

MySQL 환경에서 복잡한 SQL문을 작성할일이 자주 있는데, 그때마다 MySQL이 가지는 다양한 기능적 한계때문에 이 글에서 소개한 기법들을 자주 구글에서 검색해서 사용하곤 했습니다. 이번 글을 통해서 자주 검색하던 내용들을 링크와 함께 정리했고 평소 정확하게 몰랐던 주차 표시에 대한 표준을 조금 더 알게 되었습니다. 아무쪼록 저와 비슷한 고민을 하는 다른 분들에게도 참고자료가 되었으면 좋겠습니다.

정리한 내용중 잘못된 내용이나 궁금한점, 혹은 더 나은 방법에 대한 조언은 개인적인 연락(@y0ngbin)이나 Github 이슈를 통해 남겨주시기 바랍니다.

Name		Name	Last commit message	Last commit date
Latest commit History 18 Commits
.gitignore		.gitignore
README.org		README.org
query.sql		query.sql

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

MySQL 쿼리로 인자화된 달력 표현하기

1 What’s this?

2 문제

3 제출한 답안

4 결과

5 풀이

5.1 동적 테이블 생성

5.2 내장 함수를 이용한 2차 자료 생성

5.3 Column Row Transformation

6 마치며

About

Releases

Packages

week	일	월	화	수	목	금	토
26				1	2	3	4
27	5	6	7	8	9	10	11
28	12	13	14	15	16	17	18
29	19	20	21	22	23	24	25
30	26	27	28	29	30	31

week	일	월	화	수	목	금	토
26				1	2	3	4
27	5	6	7	8	9	10	11
28	12	13	14	15	16	17	18
29	19	20	21	22	23	24	25
30	26	27	28	29	30	31

yongbin/parameterize-calendar-mysql

Folders and files

Latest commit

History

Repository files navigation

MySQL 쿼리로 인자화된 달력 표현하기

1 What’s this?

2 문제

3 제출한 답안

4 결과

5 풀이

5.1 동적 테이블 생성

5.2 내장 함수를 이용한 2차 자료 생성

5.3 Column Row Transformation

6 마치며

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages 0

Packages

week	일	월	화	수	목	금	토
26				1	2	3	4
27	5	6	7	8	9	10	11
28	12	13	14	15	16	17	18
29	19	20	21	22	23	24	25
30	26	27	28	29	30	31