9-6. 날짜는 항상 조인 조건 후보다

9-6-1. 날짜 속성으로 조인하기

일반적으로 PK/FK 관계 컬럼을 사용해 조인을 하지만, PK/FK가 아니어도 얼마든지 조인할 수 있다. 특히 날짜 관련 속성은 데이터 분석을 위해 조인 컬럼으로 자주 사용하게 된다.

일자별로 주문금액과 신규로 가입한 회원수를 구해보자. 아래와 같은 결과를 얻으려고 한다.

-- 일자별 주문금액과 신규 가입한 회원
Ymd       SumOrdAmt  NewJoinCnt  
--------  ---------  ----------  
20200420  17000.000  9           
20200421  7000.000   25          
20200422  8000.000   23          
20200423  8500.000   33          
20200424  16000.000  27       
SQL
복사

위 데이터는 아래와 같은 로직으로 얻는 데이터다.

•

SumOrdAmt: Ord 테이블의 OrdDtm별 OrdAmt 합계

•

NewJoinCnt: Member 테이블의 JoinDtm별 데이터 건수

위 결과를 얻기 위해 Member와 Ord를 직접 조인해서는 안된다. 위 결과는 당일 가입한 회원에 대한 주문금액을 구하는 것이 아니다. 단순히 일별로 주문이 얼만큼 되었으며, 일별로 신규로 가입한 회원이 몇 명인지 확인하는 대쉬보드성 데이터다. 위 결과를 한 번에 만들지 말고, 우선은 일자별 주문금액합계와 가입일자별 회원수를 각각 구해보자.

-- [SQL-9-6-1-a] OrdDtm별 주문금액 구하기
SELECT  DATE_FORMAT(A.OrdDtm,'%Y%m%d') Ymd
        ,SUM(A.OrdAmt) SumOrdAmt
FROM    startdb.Ord A
WHERE   A.OrdDtm >= STR_TO_DATE('20200420','%Y%m%d')
AND     A.OrdDtm <  STR_TO_DATE('20200425','%Y%m%d')
GROUP BY DATE_FORMAT(A.OrdDtm,'%Y%m%d');

Ymd       SumOrdAmt     
--------  ---------  
20200420  17000.000  
20200421  7000.000   
20200422  8000.000   
20200423  8500.000   
20200424  16000.000  

-- [SQL-9-6-1-b] JoinDtm별 가입회원수 구하기
SELECT  DATE_FORMAT(B.JoinDtm,'%Y%m%d') Ymd
        ,COUNT(*) NewJoinCnt
FROM    startdb.Member B
WHERE   B.JoinDtm >= STR_TO_DATE('20200420','%Y%m%d')
AND     B.JoinDtm <  STR_TO_DATE('20200425','%Y%m%d')
GROUP BY DATE_FORMAT(B.JoinDtm,'%Y%m%d');

Ymd       NewJoinCnt  
--------  ----------  
20200424  27          
20200421  25          
20200423  33          
20200420  9           
20200422  23          
SQL
복사

두 SQL로 얻은 각각의 결과를 이제 조인으로 연결한다고 생각해보자. 어떤 컬럼을 조인으로 사용하면 되겠는가? 조인 컬럼에는 반드시 어느 한쪽의 데이터를 식별하는 Key가 사용되어야 한다. 위 데이터 집합을 살펴보면 각 데이터 집합을 식별하는 Key가 Ymd임을 알 수 있다. 그러므로 두 데이터 집합은 Ymd로 조인을 처리하면 된다. 다음과 같이 두 SQL을 각각의 인라인 뷰로 처리한 후에 조인하면 된다.

-- [SQL-9-6-1-c]
SELECT  T1.Ymd
        ,T1.SumOrdAmt
        ,T2.NewJoinCnt
FROM    (
            -- [SQL-9-6-1-a]
            SELECT  DATE_FORMAT(A.OrdDtm,'%Y%m%d') Ymd
                    ,SUM(A.OrdAmt) SumOrdAmt
            FROM    startdb.Ord A
            WHERE   A.OrdDtm >= STR_TO_DATE('20200420','%Y%m%d')
            AND     A.OrdDtm <  STR_TO_DATE('20200425','%Y%m%d')
            GROUP BY DATE_FORMAT(A.OrdDtm,'%Y%m%d')
        ) T1
        LEFT OUTER JOIN (
            -- [SQL-9-6-1-b]
            SELECT  DATE_FORMAT(B.JoinDtm,'%Y%m%d') Ymd
                    ,COUNT(*) NewJoinCnt
            FROM    startdb.Member B
            WHERE   B.JoinDtm >= STR_TO_DATE('20200420','%Y%m%d')
            AND     B.JoinDtm <  STR_TO_DATE('20200425','%Y%m%d')
            GROUP BY DATE_FORMAT(B.JoinDtm,'%Y%m%d')
        ) T2
            ON (T2.Ymd = T1.Ymd)
ORDER BY T1.Ymd;

Ymd       SumOrdAmt  NewJoinCnt  
--------  ---------  ----------  
20200420  17000.000  9           
20200421  7000.000   25          
20200422  8000.000   23          
20200423  8500.000   33          
20200424  16000.000  27          
SQL
복사

인라인 뷰에서 특정 형식의 날짜로 GROUP BY 처리한 후에 조인하는 패턴은 분석을 위해 자주 사용되니 잘 기억해두기 바란다. 조인 조건을 찾기 위해 무작정 PK/FK 만 살피지 않기 바란다. 데이터를 인라인 뷰에서 각각 그룹핑한 후에 조인을 해야 하는 것은 아니지 고민해보기 바란다.

9-6-2. 날짜의 부분 값으로 조인하기

날짜는 년과 월 그리고 일자로 구성되어 있다. DATE_FORMAT을 사용하면 날짜에서 필요한 부분 값만 가져올 수 있다. 날짜의 부분 값 역시 데이터 분석을 위해 조인 속성으로 종종 사용한다.

이번에는 년도별로 주문건수와 응모횟수를 취합해서 조회하려고 한다. 아래와 같은 결과를 얻으려는 것이다.

-- 년별 주문건수와 이벤트응모건수
Year  OrdCnt  EventEntryCnt  
----  ------  -------------  
2021  14443   905            
2022  41999   996            
2023  544906  1005        
SQL
복사

주문건수는 Ord 테이블을, 응모횟수는 EventEntry 테이블을 사용한다. Ord 테이블과 EventEntry 테이블은 직접적으로는 아무 관계가 없는 테이블들이다. 회원 테이블을 중심으로 조인 할 수는 있지만, 두 테이블을 직접 연결은 할 수는 없다. 아래 ERD를 살펴보기 바란다. Ord, Member, EventEntry는 M:1:M 관계따.

하지만, 우리에게 필요한 결과는 년도별 데이터이기 때문에 각각의 테이블을 년도별로 집계 처리한 후에 조인을 하면 된다. 아래와 같이 SQL을 작성해 위 결과를 얻어 낼 수 있다.

-- [SQL-9-6-2-a]
SELECT  T1.Year ,T1.OrdCnt ,T2.EventEntryCnt
FROM    (
            -- 년별 주문 건수 구하기
            SELECT  DATE_FORMAT(A.OrdDtm,'%Y') Year
                    ,COUNT(*) OrdCnt        
            FROM    startdb.Ord A
            WHERE   A.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
            AND     A.OrdDtm <  STR_TO_DATE('20240101','%Y%m%d')
            GROUP BY DATE_FORMAT(A.OrdDtm,'%Y')
        ) T1
        LEFT OUTER JOIN
        (
            -- 년별 이벤트응모 횟수 구하기
            SELECT  DATE_FORMAT(B.EntryDtm,'%Y') Year
                    ,COUNT(*) EventEntryCnt 
            FROM    startdb.EventEntry B
            WHERE   B.EntryDtm >= STR_TO_DATE('20210101','%Y%m%d')
            AND     B.EntryDtm <  STR_TO_DATE('20240101','%Y%m%d')
            GROUP BY DATE_FORMAT(B.EntryDtm,'%Y')
        ) T2 ON (T2.Year = T1.Year)
ORDER BY T1.Year;

Year  OrdCnt  EventEntryCnt  
----  ------  -------------  
2021  14443   905            
2022  41999   996            
2023  544906  1005           
SQL
복사

두 데이터 집합 모두 인라인 뷰에서 년도 값으로 GROUP BY 했기 때문에, 두 데이터 집합 모두 년도가 데이터를 식별하는 Key가 된다. Key가 같으므로 두 데이터 집합은 1:1로 조인이 이루어진다.

위와 같은 결과를 얻기 위한 또 다른 방법은 UNION ALL을 사용하는 것이다. 다음과 같은 방법도 있으니 참고하기 바란다. SQL은 언제나 같은 결과를 얻기 위한 다양한 방법을 제공하는 흥미로운 언어다.

-- [SQL-9-6-2-b]
-- UNION ALL을 활용한 해법
SELECT  T1.Year ,SUM(T1.OrdCnt) OrdCnt ,SUM(T1.EventEntryCnt) EventEntryCnt
FROM    (
            -- 년별 주문 건수 구하기
            SELECT  DATE_FORMAT(A.OrdDtm,'%Y') Year ,COUNT(*) OrdCnt  ,0 EventEntryCnt
            FROM    startdb.Ord A
            WHERE   A.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
            AND     A.OrdDtm <  STR_TO_DATE('20240101','%Y%m%d')
            GROUP BY DATE_FORMAT(A.OrdDtm,'%Y')
            UNION ALL
            -- 년별 이벤트응모 횟수 구하기
            SELECT  DATE_FORMAT(B.EntryDtm,'%Y') Year ,0 OrdCnt    ,COUNT(*) EventEntryCnt 
            FROM    startdb.EventEntry B
            WHERE   B.EntryDtm >= STR_TO_DATE('20210101','%Y%m%d')
            AND     B.EntryDtm <  STR_TO_DATE('20240101','%Y%m%d')
            GROUP BY DATE_FORMAT(B.EntryDtm,'%Y')
        ) T1
GROUP BY T1.Year
ORDER BY T1.Year;
SQL
복사

이번에는 2022년 각 월별 주문금액과 2021년 각 월별 주문금액을 비교하려고 한다. 아래와 같은 결과를 원하는 것이다.

-- 2022년, 2021년 주문을 월별 비교
m   2022년월별주문금액   2021년월별주문금액  주문금액증가율         
--  ------------------  ------------------  --------------  
4488500.000         2590000.000         73.30           
4702500.000         3154500.000         49.07           
5995000.000         3858000.000         55.39           
476500.000          468500.000          1.71            
9270000.000         5542500.000         67.25           
4300500.000         1751000.000         145.60          
19053500.000        6016500.000         216.69          
19568000.000        6018500.000         225.13          
19510500.000        5999500.000         225.20          
19400500.000        5997000.000         223.50          
19280500.000        5970000.000         222.96          
91350000.000        27390500.000        233.51     
SQL
복사

다양한 방법이 있겠지만 아래와 같이 인라인 뷰를 활용해 구할 수 있다. 각 년도별로 두 자리의 “월” 값만 이용해 조인 처리하는 방법이다.

-- [SQL-9-6-2-c]
SELECT  T1.m ,T1.`2022년월별주문금액` ,T2.`2021년월별주문금액`
        ,ROUND((T1.`2022년월별주문금액` - T2.`2021년월별주문금액`) / T2.`2021년월별주문금액`  * 100,2) 주문금액증가율
FROM    (
            -- 2022년 월별 주문금액
            SELECT  DATE_FORMAT(A.OrdDtm,'%m') m
                    ,SUM(A.OrdAmt) `2022년월별주문금액`
            FROM    startdb.Ord A
            WHERE   A.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
            AND     A.OrdDtm <  STR_TO_DATE('20230101','%Y%m%d')
            GROUP BY DATE_FORMAT(A.OrdDtm,'%m')
        ) T1
        LEFT OUTER JOIN (
            -- 2021년 월별 주문
            SELECT  DATE_FORMAT(B.OrdDtm,'%m') m
                    ,SUM(B.OrdAmt) `2021년월별주문금액`
            FROM    startdb.Ord B
            WHERE   B.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
            AND     B.OrdDtm <  STR_TO_DATE('20220101','%Y%m%d')
            GROUP BY DATE_FORMAT(B.OrdDtm,'%m')
        ) T2
            ON (T2.m = T1.m)
ORDER BY T1.m;     
SQL
복사

위 SQL은 나중에 배울 GROUP BY와 CASE를 적절히 사용하면 Ord 테이블을 한 번만 사용하고도 구현해낼 수 있다.

BOOSTER QUIZ

BOOSTER QUIZ ERD

BOOSTER QUIZ 9-6-1

2021년 12월의 일별 주문금액과 2022년 12월의 주문금액을 일별로 비교해주세요.

•

대상 테이블: 주문(Ord) 테이블을 두 번 사용할 것

•

조회 조건

◦

T1: OrdDtm이 2021년 12월인 주문 데이터

◦

T2: OrdDtm이 2022년 12월인 주문 데이터

◦

T1과 T2를 각각 두자리 일자(DATE_FORMAT(OrdDtm,’%d’))로 GROUP BY 처리하시오.

•

조회 컬럼: d, 2021년12월일별주문금액, 2022년12월일별주문금액, 주문금액증가율

◦

d: 12월의 두 자리 일자 값(DATE_FORMAT(OrdDtm,’%d’)

◦

2021년12월일별주문금액: T1의 두 자리 일자별 주문금액

◦

2022년12월일별주문금액: T2의 두 자리 일자별 주문금액

◦

주문금액증가율:

▪

(2022년12월일별주문금액 - 2021년12월일별주문금액) / 2021년12월일별주문금액 * 100

•

추가 조건

◦

T1과 T2를 각각의 인라인 뷰로 만들어서 처리

d   2021년12월일별주문금액   2022년12월일별주문금액  주문금액증가율         
--  ----------------------  ----------------------  --------------  
12500.000               50000.000               300.00          
4266500.000             16573000.000            288.44          
19500.000               44000.000               125.64          
312000.000              1013500.000             224.84          
784500.000              2559500.000             226.26          
315000.000              1011000.000             220.95          
23500.000               46000.000               95.74           
318000.000              999000.000              214.15          
9500.000                46000.000               384.21          
15500.000               53000.000               241.94          
791500.000              2549500.000             222.11          
15000.000               55000.000               266.67          
317000.000              999500.000              215.30          
11500.000               43000.000               273.91          
14500.000               46000.000               217.24          
14000.000               50000.000               257.14          
12000.000               41000.000               241.67          
20500.000               54500.000               165.85          
18500.000               45000.000               143.24          
12000.000               43000.000               258.33          
317500.000              997000.000              214.02          
3523000.000             11445500.000            224.88          
4317000.000             14026000.000            224.90          
3550500.000             11448000.000            222.43          
3518500.000             11403000.000            224.09          
19500.000               47000.000               141.03          
16500.000               44000.000               166.67          
10500.000               46500.000               342.86          
794500.000              2563000.000             222.59          
4008500.000             12958500.000            223.28          
11500.000               50000.000               334.78          
SQL
복사

Next: 9-7. INLINE VIEW와 TOP-N

Upper: 9. INLINE VIEW와 JOIN