10-3-1. 날짜 테이블
날짜 속성이 하나도 없는 데이터는 거의 없다. 회원과 같은 마스터 데이터에는 가입일시, 생년월일과 같은 속성이, 예약과 같은 실적 데이터에는 예약일시, 예약확정일시와 같은 속성이 존재한다. 데이터에 날짜 속성이 흔히 쓰이는 만큼 우리는 날짜 데이터를 잘 다룰 수 있어야 한다. 날짜 데이터를 효율적으로 사용하기 위해 필요한 날짜 테이블을 소개해보려 한다.
날짜 테이블에는 과거와 현재, 그리고 넉넉한 미래의 데이터가 담겨져 있다. 아래와 같이 조회를 해보기 바란다.
-- [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
SQL
복사
DATE 자료형의 BaseDt가 PK로 구성되어 있고 BaseYmd라는 YYYYMMDD 형태로 날짜를 관리하는 문자형 컬럼도 있다. BaseDtSeq는 일자에 따른 순번을 관리한다. 이 순번을 이용해 일 수 계산을 대신할 수 있다.
10-3-2. 빈 날짜 채우기
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
SQL
복사
7일 간의 데이터를 조회했지만 주문이 존재하는 두 날짜에 대한 데이터만 출력되었다. 날짜 테이블을 활용하면 아래와 같이 7일 간의 데이터를 모두 표시해줄 수 있다.
OrdYmd OrdCnt
-------- ------
20210201 0
20210202 1
20210203 0
20210204 0
20210205 3
20210206 0
20210207 0
SQL
복사
날짜 테이블에서 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
SQL
복사
10-3-3. 크로스 조인으로 기준 집합 만들기
이번에는 두 개의 매장에 대해 7일 간의 주문 데이터를 보여주려고 한다. 아래와 같이 두 매장을 조건으로 넣고 SQL을 실행해보자. 약간 갸우뚱할만한 결과가 출력된다. 매장별 7일간, 총 14건의 데이터가 나오는 것이 아니라, 두 매장에 주문이 동시에 존재하는 금요일만 두 건으로 나온 것을 알 수 있다.
-- [SQL-10-3-3-a]
SELECT T1.BaseYmd OrdYmd ,T1.BaseWkd Wkd ,T2.ShopId ,IFNULL(T2.OrdCnt,0) OrdCnt
FROM startdb.BaseDt T1
LEFT OUTER JOIN (
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 IN ('S017','S004') -- >
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 ShopId OrdCnt
-------- --- ------ ------
20210201 Mon NULL 0
20210202 Tue S017 1
20210203 Wed NULL 0
20210204 Thu NULL 0
20210205 Fri S004 3
20210205 Fri S017 3
20210206 Sat NULL 0
20210207 Sun NULL 0
SQL
복사
우리가 원하는 결과는 다음과 같이 매장별로 7일이 모두 존재하는 데이터다.
OrdYmd Wkd ShopID OrdCnt
-------- --- ------ ------
20210201 Mon S004 0
20210202 Tue S004 0
20210203 Wed S004 0
20210204 Thu S004 0
20210205 Fri S004 3
20210206 Sat S004 0
20210207 Sun S004 0
20210201 Mon S017 0
20210202 Tue S017 1
20210203 Wed S017 0
20210204 Thu S017 0
20210205 Fri S017 3
20210206 Sat S017 0
20210207 Sun S017 0
SQL
복사
위와 같은 결과를 만드려면 앞에서 배웠던 크로스 조인(CROSS JOIN)을 응용해야 한다. 아래와 같이 Shop과 BaseDt 테이블만 별도로 크로스 조인해보자. 매장별로 7일 간의 데이터를 출력할 수 있다.
-- [SQL-10-3-3-b]
SELECT A.BaseDt ,A.BaseYmd ,A.BaseWkd ,B.ShopID
FROM startdb.BaseDt A
CROSS JOIN startdb.Shop B
WHERE A.BaseDt >= STR_TO_DATE('20210201','%Y%m%d')
AND A.BaseDt < DATE_ADD(STR_TO_DATE('20210201','%Y%m%d'), interval 7 day)
AND B.ShopId IN ('S017','S004')
ORDER BY B.ShopId, A.BaseDt;
BaseDt BaseYmd BaseWkd ShopID
---------- -------- ------- ------
2021-02-01 20210201 Mon S004
2021-02-02 20210202 Tue S004
2021-02-03 20210203 Wed S004
2021-02-04 20210204 Thu S004
2021-02-05 20210205 Fri S004
2021-02-06 20210206 Sat S004
2021-02-07 20210207 Sun S004
2021-02-01 20210201 Mon S017
2021-02-02 20210202 Tue S017
2021-02-03 20210203 Wed S017
2021-02-04 20210204 Thu S017
2021-02-05 20210205 Fri S017
2021-02-06 20210206 Sat S017
2021-02-07 20210207 Sun S017
SQL
복사
이제 위 SQL을 인라인 뷰 처리하고, 위 결과를 기준집합으로 주문 데이터를 아우터 조인처리해보자. 다음과 같다.
-- [SQL-10-3-3-c]
SELECT T1.BaseYmd OrdYmd ,T1.BaseWkd Wkd ,T1.ShopId ,IFNULL(T2.OrdCnt,0) OrdCnt
FROM (
SELECT A.BaseDt ,A.BaseYmd ,A.BaseWkd ,B.ShopID
FROM startdb.BaseDt A
CROSS JOIN startdb.Shop B
WHERE A.BaseDt >= STR_TO_DATE('20210201','%Y%m%d')
AND A.BaseDt < DATE_ADD(STR_TO_DATE('20210201','%Y%m%d'), interval 7 day)
AND B.ShopId IN ('S017','S004')
) T1
LEFT OUTER JOIN (
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 IN ('S017','S004') -- >
GROUP BY A.ShopId ,DATE_FORMAT(A.OrdDtm,'%Y%m%d')
) T2
ON (T1.BaseYmd = T2.OrdYmd
AND T1.ShopId = T2.ShopId)
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.ShopId, T1.BaseYmd;
OrdYmd Wkd ShopID OrdCnt
-------- --- ------ ------
20210201 Mon S004 0
20210202 Tue S004 0
20210203 Wed S004 0
20210204 Thu S004 0
20210205 Fri S004 3
20210206 Sat S004 0
20210207 Sun S004 0
20210201 Mon S017 0
20210202 Tue S017 1
20210203 Wed S017 0
20210204 Thu S017 0
20210205 Fri S017 3
20210206 Sat S017 0
20210207 Sun S017 0
SQL
복사
방금 사용한 패턴은 간단하게 “크로스 조인으로 기준 집합 만들기”라고 이름을 지어 볼 수 있다. 크로스 조인을 사용해 분석에 필요한 차원 집합을 생성하는 것으로 데이터 분석을 위해 자주 사용되는 패턴이다.
10-3-4. 미래 데이터도 보여주기
이번에는 월별 상품카테고리별 주문 수량을 구해보자. 2024년 1월부터 2024년 3월까지의 데이터를 보여주고자 한다. 아래와 같이 SQL을 작성해볼 수 있다.
-- [SQL-10-3-4-a]
SELECT DATE_FORMAT(A.OrdDtm,'%Y%m') OrdYm
,C.ItemCat
,SUM(B.OrdQty) OrdQtySum
FROM startdb.Ord A
INNER JOIN startdb.OrdDet B ON (B.OrdNo = A.OrdNo)
INNER JOIN startdb.Item C ON (C.ItemId = B.ItemId)
WHERE A.OrdDtm >= STR_TO_DATE('20240101','%Y%m%d')
AND A.OrdDtm < STR_TO_DATE('20240401','%Y%m%d')
GROUP BY DATE_FORMAT(A.OrdDtm,'%Y%m') ,C.ItemCat
ORDER BY DATE_FORMAT(A.OrdDtm,'%Y%m') ,C.ItemCat;
OrdYm ItemCat OrdQtySum
------ ------- ---------
202401 BEV 34024
202401 BKR 31691
202401 COF 74478
SQL
복사
SQL 결과를 보면, 2024년 1월 데이터만 나온 것을 알 수 있다. Ord 테이블에는 2024년 1월까지만의 데이터가 있다. 그러므로 2024년 2월과 3월 데이터는 출력되지 않는다. 상품카테고리별 2024년 2월과 3월을 보여주기 위해 ““크로스 조인으로 기준 집합 만들기” 패턴을 사용해볼 수 있다. 아래 SQL을 실행하면 년월별 상품카테고리 데이터 집합이 만들어진다.
-- [SQL-10-3-4-b]
SELECT A.Ym ,B.ItemCat ,B.ItemCatNm
FROM (
SELECT DATE_FORMAT(T1.BaseDt,'%Y%m') Ym
FROM startdb.BaseDt T1
WHERE T1.BaseDt >= STR_TO_DATE('20240101','%Y%m%d')
AND T1.BaseDt < STR_TO_DATE('20240401','%Y%m%d')
GROUP BY DATE_FORMAT(T1.BaseDt,'%Y%m')
) A
CROSS JOIN startdb.ItemCat B
ORDER BY A.Ym, B.ItemCat;
Ym ItemCat ItemCatNm
------ ------- ---------
202401 BEV Beverage
202401 BKR Bakery
202401 COF Coffee
202402 BEV Beverage
202402 BKR Bakery
202402 COF Coffee
202403 BEV Beverage
202403 BKR Bakery
202403 COF Coffee
SQL
복사
이제 위 집합을 기준집합으로 주문 데이터와 아우터 조인 처리하면 2024년 2월과 2024년 3월 데이터도 출력할 수 있다. 물론 주문 수량은 0으로 채워져 있을 것이다.
-- [SQL-10-3-4-c]
SELECT T1.Ym ,T1.ItemCat ,T1.ItemCatNm ,IFNULL(T2.OrdQtySum,0) OrdQtySum
FROM (
SELECT A.Ym ,B.ItemCat ,B.ItemCatNm
FROM (
SELECT DATE_FORMAT(T1.BaseDt,'%Y%m') Ym
FROM startdb.BaseDt T1
WHERE T1.BaseDt >= STR_TO_DATE('20240101','%Y%m%d')
AND T1.BaseDt < STR_TO_DATE('20240401','%Y%m%d')
GROUP BY DATE_FORMAT(T1.BaseDt,'%Y%m')
) A
CROSS JOIN startdb.ItemCat B
ORDER BY A.Ym, B.ItemCat
) T1
LEFT OUTER JOIN (
SELECT DATE_FORMAT(A.OrdDtm,'%Y%m') OrdYm
,C.ItemCat
,SUM(B.OrdQty) OrdQtySum
FROM startdb.Ord A
INNER JOIN startdb.OrdDet B ON (B.OrdNo = A.OrdNo)
INNER JOIN startdb.Item C ON (C.ItemId = B.ItemId)
WHERE A.OrdDtm >= STR_TO_DATE('20240101','%Y%m%d')
AND A.OrdDtm < STR_TO_DATE('20240401','%Y%m%d')
GROUP BY DATE_FORMAT(A.OrdDtm,'%Y%m') ,C.ItemCat
) T2
ON (T2.OrdYm = T1.Ym
AND T2.ItemCat = T1.ItemCat)
ORDER BY T1.Ym ,T1.ItemCat
Ym ItemCat ItemCatNm OrdQtySum
------ ------- --------- ---------
202401 BEV Beverage 34024
202401 BKR Bakery 31691
202401 COF Coffee 74478
202402 BEV Beverage 0
202402 BKR Bakery 0
202402 COF Coffee 0
202403 BEV Beverage 0
202403 BKR Bakery 0
202403 COF Coffee 0
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 10-3-1
NickNm이 Air와 Wind3인 회원의 2023년 1월 1일부터 2023년 1월 3일까지의 주문 금액 회원별+일자별로 보여주세요. 주문이 없는 날짜도 보이도록 처리해주세요.
•
대상 테이블: 회원(Member), 주문(Ord), 날짜(BaseDt)
•
조회 조건
◦
NickNm이 Air, Wind3인 회원
◦
OrdDtm이 2023년 1월 1일부터 2023년 1월 3일까지
•
조회 컬럼: OrdYmd, MemberId, NickNm, OrdAmt
•
추가 조건
◦
주문이 없는 날짜의 데이터도 출력되도록 처리해주세요.
▪
BaseDt와 Member 테이블을 크로스 조인해 분석 마스터 데이터 집합을 만들어 처리합니다.
OrdYmd MemberId NickNm OrdAmt
-------- -------- ------ --------
20230101 M0001 Air 4500.000
20230101 M0200 Wind3 0.000
20230102 M0001 Air 3000.000
20230102 M0200 Wind3 4500.000
20230103 M0001 Air 4500.000
20230103 M0200 Wind3 0.000
SQL
복사
