날짜 속성이 하나도 없는 데이터는 거의 없다. 회원과 같은 마스터 데이터에는 가입일시, 생년월일과 같은 속성이, 예약과 같은 실적 데이터에는 예약일시, 예약확정일시와 같은 속성이 존재한다. 데이터에 날짜 속성이 흔히 쓰이는 만큼 우리는 날짜 데이터를 잘 다룰 수 있어야 한다. 날짜 데이터를 효율적으로 사용하기 위해 필요한 날짜 테이블을 소개해보려 한다.
날짜 테이블에는 과거와 현재, 그리고 넉넉한 미래의 데이터가 담겨져 있다. 아래와 같이 조회를 해보기 바란다.
-- [SQL-10-3-1-a]
SELECT T1.*
FROM startdb.BaseDt T1
ORDER BY T1.BaseDt ASC
LIMIT 5;
BaseDt BaseYmd BaseDtSeq BaseWkd
---------- -------- --------- -------
2010-01-01 20100101 1 Fri
2010-01-02 20100102 2 Sat
2010-01-03 20100103 3 Sun
2010-01-04 20100104 4 Mon
2010-01-05 20100105 5 Tue
-- [SQL-10-3-1-b]
DESC startdb.BaseDt;
Field Type Null Key Default Extra
--------- ----------- ---- --- ------- -----
BaseDt date NO PRI NULL
BaseYmd varchar(8) NO NULL
BaseDtSeq int NO NULL
BaseWkd varchar(10) NO NULL
DATE 자료형의 BaseDt가 PK로 구성되어 있고 BaseYmd라는 YYYYMMDD 형태로 날짜를 관리하는 문자형 컬럼도 있다. BaseDtSeq는 일자에 따른 순번을 관리한다. 이 순번을 이용해 일 수 계산을 대신할 수 있다.
BaseDt 테이블을 대략 살펴봤다. 어떤 용도로 사용할 수 있는지 살펴보자. S017 매장의 2021년 2월 1일부터 7일 간(1주간)의 주문 데이터를 조회해보자. 다음과 같다.
-- [SQL-10-3-2-a]
-- 21년 2월 1일(월)부터 7일간 1주일간 데이터를 조회
SELECT DATE_FORMAT(A.OrdDtm,'%Y%m%d') OrdYmd ,COUNT(*) OrdCnt
FROM startdb.Ord A
WHERE A.OrdDtm >= STR_TO_DATE('20210201','%Y%m%d')
AND A.OrdDtm < DATE_ADD(STR_TO_DATE('20210201','%Y%m%d'), interval 7 day)
AND A.ShopID = 'S017'
GROUP BY A.ShopId ,DATE_FORMAT(A.OrdDtm,'%Y%m%d')
ORDER BY A.ShopId ,DATE_FORMAT(A.OrdDtm,'%Y%m%d');
OrdYmd OrdCnt
-------- ------
20210202 1
20210205 3
7일 간의 데이터를 조회했지만 주문이 존재하는 두 날짜에 대한 데이터만 출력되었다. 날짜 테이블을 활용하면 아래와 같이 7일 간의 데이터를 모두 표시해줄 수 있다.
OrdYmd OrdCnt
-------- ------
20210201 0
20210202 1
20210203 0
20210204 0
20210205 3
20210206 0
20210207 0
날짜 테이블에서 7일 간의 데이터를 조회한 후에, 날짜 테이블을 기준집합으로 일별 주문 데이터와 아우터 조인을 처리하면 된다. 다음과 같다.
-- [SQL-10-3-2-b]
SELECT T1.BaseYmd OrdYmd ,T1.BaseWkd Wkd ,IFNULL(T2.OrdCnt,0) OrdCnt
FROM startdb.BaseDt T1
LEFT OUTER JOIN (
-- [SQL-10-3-2-a]
SELECT A.ShopID ,DATE_FORMAT(A.OrdDtm,'%Y%m%d') OrdYmd ,COUNT(*) OrdCnt
FROM startdb.Ord A
WHERE A.OrdDtm >= STR_TO_DATE('20210201','%Y%m%d')
AND A.OrdDtm < DATE_ADD(STR_TO_DATE('20210201','%Y%m%d'), interval 7 day)
AND A.ShopID = 'S017'
GROUP BY A.ShopId ,DATE_FORMAT(A.OrdDtm,'%Y%m%d')
) T2
ON (T1.BaseYmd = T2.OrdYmd)
WHERE T1.BaseDt >= STR_TO_DATE('20210201','%Y%m%d')
AND T1.BaseDt < DATE_ADD(STR_TO_DATE('20210201','%Y%m%d'), interval 7 day)
ORDER BY T1.BaseYmd;
OrdYmd Wkd OrdCnt
-------- --- ------
20210201 Mon 0
20210202 Tue 1
20210203 Wed 0
20210204 Thu 0
20210205 Fri 3
20210206 Sat 0
20210207 Sun 0