10-2. PIVOT

10-2-1. PIVOT

피벗(PIVOT)의 사전적 의미는 물체가 회전하는데 사용하는 회전축(중심축)을 뜻한다. 데이터를 다루는 데 있어 피벗 역시, 회전축(중심축)을 기준으로 데이터를 회전시키는 기법을 이야기한다. SQL에서 피벗은 다음과 같이 요약할 수 있다.

•

세로(레코드)로 떨어진 데이터를, 중심축을 정해 가로(컬럼)로 펼쳐 보여주는 기술

•

중심축은 GROUP BY로 정의하고, 가로로 펼치기 위해서는 CASE와 집계함수를 사용합니다.

10-2-2. CASE 한 번 더 살펴보기

앞에서 이미 CASE를 설명했다. PIVOT 기능을 구현하기 위해서는 CASE를 사용해야 하므로 간단히 복습해보도록 하자.

•

CASE

◦

조건에 따라  데이터 값을 치환하는 문번

◦

CASE WHEN <조건식> THEN <조건 만족시 치환 값> ELSE <조건 불만족시 치환 값> END

▪

WHEN~THEN을 여러 개 사용할 수 있다. ELSE 는 생략할 수 있다.

◦

생각보다 많은 곳에 CASE를 사용할 수 있다.

▪

ORDER BY, GROUP BY 에도 사용할 수 있다. 집계 함수 안에도 사용할 수 있다.

아래는 CASE를 사용해 주문일자에 따라 각각 컬럼을 추가해서 보여주는 SQL이다. 주문일자가 2022년 1월 1일이면 AMT_01 컬럼에, 2022년 1월 2일이면 AMT_02에 값을 보여주고 있다.

-- [SQL-10-2-2-a] 주문일자에 따라 컬럼을 분리해서 표현
SELECT  T1.ShopID ,T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
        ,CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220101' THEN T1.OrdAmt END AMT_01
        ,CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220102' THEN T1.OrdAmt END AMT_02
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId = 'S003'
ORDER BY T1.ShopID, T1.OrdDtm;

ShopID  OrdNo  OrdDtm               OrdAmt    AMT_01    AMT_02    
------  -----  -------------------  --------  --------  --------  
S003    25278  2022-01-01 08:00:00  4000.000  4000.000  NULL      
S003    25281  2022-01-02 08:00:00  4500.000  NULL      4500.000  
S003    25295  2022-01-02 10:00:00  8000.000  NULL      8000.000  
S003    25311  2022-01-02 10:00:00  3500.000  NULL      3500.000  
S003    25334  2022-01-02 11:00:00  8500.000  NULL      8500.000  
SQL
복사

10-2-3. ShopId로 PIVOT하기

아래는 S003, S007 매장의 주문 데이터다.

-- [SQL-10-2-3-a]
SELECT  T1.ShopID ,T1.OrdDtm ,T1.OrdAmt
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId IN ('S003','S007')
ORDER BY T1.ShopID, T1.OrdDtm;

ShopID  OrdDtm               OrdAmt    
------  -------------------  --------  
S003    2022-01-01 08:00:00  4000.000  
S003    2022-01-02 08:00:00  4500.000  
S003    2022-01-02 10:00:00  8000.000  
S003    2022-01-02 10:00:00  3500.000  
S003    2022-01-02 11:00:00  8500.000  
S007    2022-01-01 08:00:00  4000.000  
S007    2022-01-02 08:00:00  4500.000  
S007    2022-01-02 11:00:00  4000.000  
S007    2022-01-02 11:00:00  4000.000 
SQL
복사

위 결과를 매장ID를 기준으로 2022년 1월 1일 주문 금액과 2022년 1월 2일 주문금액을 각각의 컬럼으로 표현해주려고 한다. 아래와 같이 매장ID를 기준으로 나머지 데이터를 옆으로 올려서 합계 처리해 보여주려고 한다.

위와 같이 데이터를 표현하기 위해 가장 먼저 할 일은 옆으로 펼쳐 보여지는 컬럼(AMT_0101, AMT_0102)을 구현하는 것이다. 아래와 같이 CASE를 사용해 각각 컬럼을 처리한다.

-- [SQL-10-2-3-b]
SELECT  T1.ShopID ,T1.OrdDtm ,T1.OrdAmt
        ,CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220101' THEN T1.OrdAmt END AMT_0101
        ,CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220102' THEN T1.OrdAmt END AMT_0102
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId IN ('S003','S007')
ORDER BY T1.ShopID, T1.OrdDtm;

ShopID  OrdDtm               OrdAmt    AMT_0101  AMT_0102  
------  -------------------  --------  --------  --------  
S003    2022-01-01 08:00:00  4000.000  4000.000  NULL      
S003    2022-01-02 08:00:00  4500.000  NULL      4500.000  
S003    2022-01-02 10:00:00  8000.000  NULL      8000.000  
S003    2022-01-02 10:00:00  3500.000  NULL      3500.000  
S003    2022-01-02 11:00:00  8500.000  NULL      8500.000  
S007    2022-01-01 08:00:00  4000.000  4000.000  NULL      
S007    2022-01-02 08:00:00  4500.000  NULL      4500.000  
S007    2022-01-02 11:00:00  4000.000  NULL      4000.000  
S007    2022-01-02 11:00:00  4000.000  NULL      4000.000 
SQL
복사

위 SQL로 얻은 결과 데이터 집합을 ShopId별로 GROUP BY 처리만 하면 원하는 결과를 얻을 수 있다. 아마도 가장 쉬운 접근법은 위 SQL을 WITH 절이나 인라인 뷰로 처리하고, 그곳에서 나오는 데이터 집합을 바로 GROUP BY 처리하는 것이다. 다음과 같다.

-- [SQL-10-2-3-c]
WITH W1 AS(
SELECT  T1.ShopID ,T1.OrdDtm ,T1.OrdAmt
        ,CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220101' THEN T1.OrdAmt END AMT_0101
        ,CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220102' THEN T1.OrdAmt END AMT_0102
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId IN ('S003','S007')
ORDER BY T1.ShopID, T1.OrdDtm
)
SELECT  T1.ShopId
        ,SUM(T1.AMT_0101) AMT_0101
        ,SUM(T1.AMT_0102) AMT_0102
FROM    W1 T1
GROUP BY T1.ShopId;

ShopID  AMT_0101  AMT_0102   
------  --------  ---------  
S003    4000.000  24500.000  
S007    4000.000  12500.000  
SQL
복사

조금 더 SQL에 자신 있다면 아래와 같이 WITH절을 제거하고 원래의 SQL에 바로 GROUP BY 처리를 해도 된다.

-- [SQL-10-2-3-d]
SELECT  T1.ShopID
        ,SUM(
          CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220101' THEN T1.OrdAmt END
          ) AMT_0101
        ,SUM(
          CASE WHEN DATE_FORMAT(T1.OrdDtm,'%Y%m%d') = '20220102' THEN T1.OrdAmt END
          ) AMT_0102
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId IN ('S003','S007')
GROUP BY T1.ShopId
ORDER BY T1.ShopID;
SQL
복사

이처럼 특정 축을 기준으로 값을 컬럼별로 보여주는 SQL은 원하는 리포트 양식에 따라 자주 사용하게 되는 기술이다. 패턴을 잘 익혀두기 바란다. 사실, 패턴을 익힌다기 보다는 데이터 집합이 변하는 과정을 잘 기억하면 된다.

10-2-4. 주문일자로 PIVOT하기

이번에는 주문일자로 PIVOT을 해보자. 아래는 기본 데이터다.

-- [SQL-10-2-4-a]
SELECT  T1.ShopID ,T1.OrdDtm ,T1.OrdAmt
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId IN ('S003','S007')
ORDER BY T1.ShopID, T1.OrdDtm;

ShopID  OrdDtm               OrdAmt    
------  -------------------  --------  
S003    2022-01-01 08:00:00  4000.000  
S003    2022-01-02 08:00:00  4500.000  
S003    2022-01-02 10:00:00  8000.000  
S003    2022-01-02 10:00:00  3500.000  
S003    2022-01-02 11:00:00  8500.000  
S007    2022-01-01 08:00:00  4000.000  
S007    2022-01-02 08:00:00  4500.000  
S007    2022-01-02 11:00:00  4000.000  
S007    2022-01-02 11:00:00  4000.000 
SQL
복사

이번에는 위 데이터를 아래와 같이 주문일자를 기준으로 하고 매장ID별 주문금액을 컬럼으로 보여주고자 한다.

언젠가는 한 번에 원하는 결과를 만들 수 있을 것이다. 지금은 단계적으로 SQL을 만들어가기 바란다. 가장 먼저 할일은 CASE를 사용해 AMT_S003, AMT_S007 컬럼을 추가하는 것이다.

-- [SQL-10-2-4-b]
SELECT  T1.ShopId ,DATE_FORMAT(T1.OrdDtm,'%Y%m%d') OrdYmd ,T1.OrdAmt
        ,CASE WHEN T1.ShopId = 'S003' THEN T1.OrdAmt END AMT_S003
        ,CASE WHEN T1.ShopId = 'S007' THEN T1.OrdAmt END AMT_S007
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId IN ('S003','S007')
ORDER BY T1.ShopID, T1.OrdDtm;

ShopId  OrdYmd    OrdAmt    AMT_S003  AMT_S007  
------  --------  --------  --------  --------  
S003    20220101  4000.000  4000.000  NULL      
S003    20220102  4500.000  4500.000  NULL      
S003    20220102  8000.000  8000.000  NULL      
S003    20220102  3500.000  3500.000  NULL      
S003    20220102  8500.000  8500.000  NULL      
S007    20220101  4000.000  NULL      4000.000  
S007    20220102  4500.000  NULL      4500.000  
S007    20220102  4000.000  NULL      4000.000  
S007    20220102  4000.000  NULL      4000.000 
SQL
복사

이제 위 결과 집합을 OrdYmd별로 GROUP BY 처리만 하면 원하는 결과가 얻어진다. 다음과 같다.

-- [SQL-10-2-4-c]
SELECT  DATE_FORMAT(T1.OrdDtm,'%Y%m%d') OrdYmd
        ,SUM(CASE WHEN T1.ShopId = 'S003' THEN T1.OrdAmt END) AMT_S003
        ,SUM(CASE WHEN T1.ShopId = 'S007' THEN T1.OrdAmt END) AMT_S007
FROM    startdb.Ord T1
WHERE   T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND     T1.OrdDtm <  STR_TO_DATE('20220103','%Y%m%d')
AND     T1.ShopId IN ('S003','S007')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
ORDER BY OrdYmd;

OrdYmd    AMT_S003   AMT_S007   
--------  ---------  ---------  
20220101  4000.000   4000.000   
20220102  24500.000  12500.000 
SQL
복사

10-2-5. 매장운영유형으로 PIVOT하기

2020년 1월부터 2020년 3월 주문에 대해, 매장운영유형별로 주문년월에 따른 주문금액을 구하려고 한다. 주문년월에 따른 주문금액을 아래와 같이 컬럼으로 표현하려고 한다.

ShopOperTpNm  OrdAmt_01    OrdAmt_02    OrdAmt_03    
------------  -----------  -----------  -----------  
Flagship      1346500.000  1395000.000  1522000.000  
Distributor   80500.000    307000.000   349000.000   
Directly      32000.000    308500.000   312000.000   
SQL
복사

위 결과를 얻기 위해 가장 먼저 할일은 기본 데이터인 ShopOperTp, 주문년월(DATE_FORMAT(OrdDtm, ’%Y%m’))별 주문금액을 구하는 것이다. 아래와 같다.

-- [SQL-10-2-5-a]
-- 매장운영유형별 주문년월별 주문금액을 먼저 구한다.
SELECT  T1.ShopOperTp
        ,DATE_FORMAT(T2.OrdDtm,'%Y%m') OrdYm
        ,SUM(T2.OrdAmt) OrdAmt
FROM    startdb.Shop T1
        INNER JOIN startdb.Ord T2
            ON (T2.ShopId = T1.ShopId)
WHERE   T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND     T2.OrdDtm <  STR_TO_DATE('20200401','%Y%m%d')
GROUP BY T1.ShopOperTp
        ,DATE_FORMAT(T2.OrdDtm,'%Y%m');


ShopOperTp  OrdYm   OrdAmt       
----------  ------  -----------  
FLAG        202001  1346500.000  
DIST        202001  80500.000    
DRCT        202001  32000.000    
FLAG        202002  1395000.000  
DIST        202002  307000.000   
DRCT        202002  308500.000   
FLAG        202003  1522000.000  
DIST        202003  349000.000   
DRCT        202003  312000.000 
SQL
복사

위 기본 SQL을 인라인 뷰로 처리한 후에 OrdYm에 따른 컬럼을 먼저 추가해보자. 다음과 같다.

-- [SQL-10-2-5-b]
-- CASE를 사용해 OrdYm에 따른 금액 컬럼을 추가
SELECT  T3.ShopOperTp
        ,T3.OrdYm
        ,T3.OrdAmt
        ,CASE WHEN T3.OrdYm = '202001' THEN T3.OrdAmt END OrdAmt_01
        ,CASE WHEN T3.OrdYm = '202002' THEN T3.OrdAmt END OrdAmt_02
        ,CASE WHEN T3.OrdYm = '202003' THEN T3.OrdAmt END OrdAmt_03
FROM    (
        SELECT  T1.ShopOperTp
                ,DATE_FORMAT(T2.OrdDtm,'%Y%m') OrdYm
                ,SUM(T2.OrdAmt) OrdAmt
        FROM    startdb.Shop T1
                INNER JOIN startdb.Ord T2
                    ON (T2.ShopId = T1.ShopId)
        WHERE   T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
        AND     T2.OrdDtm <  STR_TO_DATE('20200401','%Y%m%d')
        GROUP BY T1.ShopOperTp
                ,DATE_FORMAT(T2.OrdDtm,'%Y%m')
        ) T3;

ShopOperTp  OrdYm   OrdAmt       OrdAmt_01    OrdAmt_02    OrdAmt_03    
----------  ------  -----------  -----------  -----------  -----------  
FLAG        202001  1346500.000  1346500.000  NULL         NULL         
DIST        202001  80500.000    80500.000    NULL         NULL         
DRCT        202001  32000.000    32000.000    NULL         NULL         
FLAG        202002  1395000.000  NULL         1395000.000  NULL         
DIST        202002  307000.000   NULL         307000.000   NULL         
DRCT        202002  308500.000   NULL         308500.000   NULL         
FLAG        202003  1522000.000  NULL         NULL         1522000.000  
DIST        202003  349000.000   NULL         NULL         349000.000   
DRCT        202003  312000.000   NULL         NULL         312000.000   
SQL
복사

이제 위 SQL을 또 다시 인라인 뷰로 처리하고 ShopOperTp별 GROUP BY 처리만 하면 원하는 결과를 얻을 수 있다.

-- [SQL-10-2-5-c]
-- 최종 GROUP BY 처리
SELECT  (SELECT MAX(X.BaseCdNm)
         FROM startdb.BaseCd X
         WHERE X.BaseCdDv = 'ShopOperTp' 
         AND X.BaseCd = T4.ShopOperTp) ShopOperTpNm
        ,SUM(T4.OrdAmt_01) OrdAmt_01
        ,SUM(T4.OrdAmt_02) OrdAmt_02
        ,SUM(T4.OrdAmt_03) OrdAmt_03
FROM    (
        SELECT  T3.ShopOperTp
                ,T3.OrdYm
                ,T3.OrdAmt
                ,CASE WHEN T3.OrdYm = '202001' THEN T3.OrdAmt END OrdAmt_01
                ,CASE WHEN T3.OrdYm = '202002' THEN T3.OrdAmt END OrdAmt_02
                ,CASE WHEN T3.OrdYm = '202003' THEN T3.OrdAmt END OrdAmt_03
        FROM    (
                SELECT  T1.ShopOperTp
                        ,DATE_FORMAT(T2.OrdDtm,'%Y%m') OrdYm
                        ,SUM(T2.OrdAmt) OrdAmt
                FROM    startdb.Shop T1
                        INNER JOIN startdb.Ord T2
                            ON (T2.ShopId = T1.ShopId)
                WHERE   T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
                AND     T2.OrdDtm <  STR_TO_DATE('20200401','%Y%m%d')
                GROUP BY T1.ShopOperTp
                        ,DATE_FORMAT(T2.OrdDtm,'%Y%m')
                ) T3
        ) T4
GROUP BY T4.ShopOperTp;

ShopOperTpNm  OrdAmt_01    OrdAmt_02    OrdAmt_03    
------------  -----------  -----------  -----------  
Flagship      1346500.000  1395000.000  1522000.000  
Distributor   80500.000    307000.000   349000.000   
Directly      32000.000    308500.000   312000.000   
SQL
복사

데이터와 SQL에 익숙해지면 인라인 뷰를 최소화하고 아래와 같이 SQL을 작성할 수 있을 것이다.

-- [SQL-10-2-5-d]
SELECT  (SELECT MAX(X.BaseCdNm) FROM startdb.BaseCd X WHERE X.BaseCdDv = 'ShopOperTp' AND X.BaseCd = T1.ShopOperTp) ShopOperTpNm
        ,SUM(CASE WHEN T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d') AND T2.OrdDtm < STR_TO_DATE('20200201','%Y%m%d') THEN T2.OrdAmt END) OrdAmt_01
        ,SUM(CASE WHEN T2.OrdDtm >= STR_TO_DATE('20200201','%Y%m%d') AND T2.OrdDtm < STR_TO_DATE('20200301','%Y%m%d') THEN T2.OrdAmt END) OrdAmt_02
        ,SUM(CASE WHEN T2.OrdDtm >= STR_TO_DATE('20200301','%Y%m%d') AND T2.OrdDtm < STR_TO_DATE('20200401','%Y%m%d') THEN T2.OrdAmt END) OrdAmt_03
FROM    startdb.Shop T1
        INNER JOIN startdb.Ord T2
            ON (T2.ShopId = T1.ShopId)
WHERE   T2.OrdDtm >= STR_TO_DATE('20200101','%Y%m%d')
AND     T2.OrdDtm <  STR_TO_DATE('20200401','%Y%m%d')
GROUP BY T1.ShopOperTp;
SQL
복사

10-2-6. COUNT를 위한 SUM 처리

특정 기준별 카운트를 구하기 위해 COUNT 집계함수가 아닌 SUM 집계함수 활용하기도 한다. 2018년 시작한 매장들에 대해 ShopOperTp별로 Size에 따라 100미만매장수와 100이상매장수를 구하려고 한다. 아래와 같은 결과를 구하는 것이다.

ShopOperTpNm  Size:100미만매장수   Size:100이상매장수       
------------  ------------------  ------------------  
Flagship      22                  0                   
Distributor   12                  9                   
Directly      3                   4                   
SQL
복사

100미만매장수와 100이상매장수는 Shop 테이블의 건수를 카운트해야 한다. 건수를 세기 위해서는 일반적으로 COUNT 집계함수를 사용해야 하겠지만, 상황에 따라 SUM 집계함수를 활용할 수도 있다. 위 결과를 얻기 위해서 Shop 테이블을 조회하고 ShopSize에 따라 100미만인지 100이상인지를 판단하는 컬럼을 추가해보자. 다음과 같다.

-- [SQL-10-2-6-a]
-- 100미만인 경우와 이상인 경우에 따라 1로 치환한 컬럼을 추가
SELECT  T1.ShopId
        ,T1.ShopOperTp
        ,T1.ShopSize
        ,CASE WHEN T1.ShopSize < 100 THEN 1 END `Size:100미만여부`
        ,CASE WHEN T1.ShopSize >= 100 THEN 1 END `Size:100이상여부`
FROM    startdb.Shop T1
WHERE   T1.ShopStartYmd LIKE '2018%'
ORDER BY T1.ShopId;

ShopId  ShopOperTp  ShopSize  Size:100미만여부   Size:100이상여부      
------  ----------  --------  ----------------  ----------------  
S001    FLAG        26        1                 NULL              
S002    DIST        27        1                 NULL              
S003    FLAG        28        1                 NULL              
S004    FLAG        29        1                 NULL              
S005    FLAG        30        1                 NULL              
... 생략 ...
SQL
복사

100미만인 경우에는 ‘Size:100미만여부’에 1을, 100이상인 경우에는 ‘Size:100이상여부’에 1을 표시하도록 SQL을 구현했다. 이제 위 SQL을 인라인 뷰로 처리하고 ShopOperTp별 GROUP BY 처리하면서 ‘여부’ 컬럼을 SUM 처리하면 원하는 결과를 얻을 수 있다. 다음과 같다.

-- [SQL-10-2-6-b]
-- 1로 치환된 값을 SUM 처리(COUNT 집계함수를 대신할 수 있다.)
SELECT  (SELECT MAX(X.BaseCdNm) FROM startdb.BaseCd X
        WHERE X.BaseCdDv = 'ShopOperTp' AND X.BaseCd = T2.ShopOperTp) ShopOperTpNm
        ,IFNULL(SUM(T2.`Size:100미만여부`),0) `Size:100미만매장수`
        ,IFNULL(SUM(T2.`Size:100이상여부`),0) `Size:100이상매장수`
FROM    (
        SELECT  T1.ShopId
                ,T1.ShopOperTp
                ,CASE WHEN T1.ShopSize < 100 THEN 1 END `Size:100미만여부`
                ,CASE WHEN T1.ShopSize >= 100 THEN 1 END `Size:100이상여부`
        FROM    startdb.Shop T1
        WHERE   T1.ShopStartYmd LIKE '2018%'
        ORDER BY T1.ShopId
        ) T2
GROUP BY T2.ShopOperTp
ORDER BY T2.ShopOperTp;

ShopOperTpNm  Size:100미만매장수   Size:100이상매장수       
------------  ------------------  ------------------  
Distributor   12                  9                   
Directly      3                   4                   
Flagship      22                  0                                      
SQL
복사

특정 조건에 따른 건수를 집계하기 위해, 조건을 만족한 경우 1로 치환한 후에 SUM 처리로 원하는 결과를 얻었다. 다양한 조건에 맞는 건수를 세기 위해 자주 사용할 수 있는 패턴이다. 위 SQL은 인라인 뷰를 제거하고 아래와 같이 작성할 수도 있다.

-- [SQL-10-2-6-c]
SELECT  (SELECT MAX(X.BaseCdNm) FROM startdb.BaseCd X
        WHERE X.BaseCdDv = 'ShopOperTp' AND X.BaseCd = T1.ShopOperTp) ShopOperTpNm
        ,IFNULL(SUM(CASE WHEN T1.ShopSize < 100 THEN 1 END),0) `Size:100미만매장수`
        ,IFNULL(SUM(CASE WHEN T1.ShopSize >= 100 THEN 1 END),0) `Size:100이상매장수`
FROM    startdb.Shop T1
WHERE   T1.ShopStartYmd LIKE '2018%'
GROUP BY T1.ShopOperTp
ORDER BY T1.ShopOperTp;
SQL
복사

PIVOT 처리를 할때는 PIVOT 전의 데이터 집합에 따라 SUM이나 COUNT 또는 MIN/MAX 중 선택해서 사용해야 한다. 무턱대고 SUM이나 COUNT를 사용하지 말고 PIVOT 전의 데이터를 잘 살펴보고 결정하기 바란다.

10-2-7. 다른 구간 데이터 비교하기

아래 SQL은 [9-6. 날짜는 항상 조인 조건 후보다] 에서 살펴봤던 SQL이다. 2020년과 2021년 주문을 월별로 비교하기 위한 SQL이다.

-- [SQL-10-2-7-a] 9-7.날짜는 항상 조인 조건 후보다의 SQL
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;     

m   2022년월별주문금액   2021년월별주문금액    주문금액증가율         
--  ------------------  ------------------  --------------  
01  4488500.000         2590000.000         73.30           
02  4702500.000         3154500.000         49.07           
03  5995000.000         3858000.000         55.39           
04  476500.000          468500.000          1.71            
05  9270000.000         5542500.000         67.25           
06  4300500.000         1751000.000         145.60          
07  19053500.000        6016500.000         216.69          
08  19568000.000        6018500.000         225.13          
09  19510500.000        5999500.000         225.20          
10  19400500.000        5997000.000         223.50          
11  19280500.000        5970000.000         222.96          
12  91350000.000        27390500.000        233.51          
SQL
복사

위 SQL에서는 Ord 테이블에 두 번의 접근이 발생했다. 지금까지 배운 PIVOT 기술을 사용하면 Ord 테이블을 한 번만 사용하고 SQL을 작성할 수 있다. 기준축인 m(월)으로 하고, 컬럼별로 CASE 조건을 걸어 주문금액을 합계처리하면 된다.

-- [SQL-10-2-7-b] GROUP BY CASE를 사용해 처리하기
SELECT  T2.m
        ,T2.`2022년월별주문금액`
        ,T2.`2021년월별주문금액`
        ,ROUND((T2.`2022년월별주문금액` - T2.`2021년월별주문금액`) / T2.`2021년월별주문금액`  * 100,2) 주문금액증가율
FROM    (
        SELECT  DATE_FORMAT(A.OrdDtm,'%m') m
                ,SUM(   CASE   WHEN A.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
                                    AND A.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
                               THEN A.OrdAmt 
                        END) `2022년월별주문금액`
                ,SUM(   CASE   WHEN A.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
                                    AND A.OrdDtm < STR_TO_DATE('20220101','%Y%m%d') 
                               THEN A.OrdAmt 
                        END) `2021년월별주문금액`
        FROM    startdb.Ord A
        WHERE   A.OrdDtm >= STR_TO_DATE('20210101','%Y%m%d')
        AND     A.OrdDtm <  STR_TO_DATE('20230101','%Y%m%d')
        GROUP BY DATE_FORMAT(A.OrdDtm,'%m')
        ) T2;
        
m   2022년월별주문금액    2021년월별주문금액   주문금액증가율         
--  ------------------  ------------------  --------------  
01  4488500.000         2590000.000         73.30           
02  4702500.000         3154500.000         49.07           
03  5995000.000         3858000.000         55.39           
04  476500.000          468500.000          1.71            
05  9270000.000         5542500.000         67.25           
06  4300500.000         1751000.000         145.60          
07  19053500.000        6016500.000         216.69          
08  19568000.000        6018500.000         225.13          
09  19510500.000        5999500.000         225.20          
10  19400500.000        5997000.000         223.50          
11  19280500.000        5970000.000         222.96          
12  91350000.000        27390500.000        233.51          
SQL
복사

10-2. BOOSTER QUIZ

BOOSTER QUIZ ERD

BOOSTER QUIZ 10-2-1

2020년 1월과 2020년 2월 주문에 대해, 상품카테고리별로 주문수량을 구해주세요. 2020년 1월 주문수량과 2020년 2월 주문수량을 각각의 컬럼으로 보여주세요.

•

대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item), 상품카테고리(Itemcat)

•

조회 조건: OrdDtm이 2021년 1월부터 2021년 2월까지의 주문 데이터

•

조회 컬럼: ItemCat, ItemCatNm, Qty_202001, Qty_202002

•

추가 조건

◦

Qty_202001: OrdDtm이 2020년 1월 주문의 OrdQty를 SUM

◦

Qty_202002: OrdDtm이 2020년 2월 주문의 OrdQty를 SUM

◦

ItemCat별 Qty_202001과 Qty_202002를 각각 컬럼으로 보여주시오.

ItemCat  ItemCatNm  Qty_202001  Qty_202002  
-------  ---------  ----------  ----------  
BEV      Beverage   80          113         
BKR      Bakery     80          110         
COF      Coffee     218         298         
SQL
복사

BOOSTER QUIZ 10-2-2

2021년 1월부터 2021년 3월까지의 주문에 대해, 주문년월별로 오전주문건수와 오후주문건수를 보여주세요.

•

대상 테이블: 주문(Ord)

•

조회 조건: OrdDtm이 2021년 1월부터 2021년 3월까지의 주문 데이터

•

조회 컬럼: OrdYm, 오전주문, 오후주문

•

추가 조건

◦

OrdYm은 OrdDtm을 년월 형태로 변환한 값

◦

OrdYm별 GROUP BY 처리하시오.

◦

오전주문건수는 OrdDtm의 주문시간(DATE_FORMAT(T1.OrdDtm,'%H'))이 12시 이하인 주문

◦

오후주문건수는 OrdDtm의 주문시간(DATE_FORMAT(T1.OrdDtm,'%H'))이 12시 초과인 주문

◦

오전주문건수, 오후주문건수를 구하기 위해서는 GROUP BY ~ 집계함수(CASE) 패턴을 사용하시오.

OrdYm   오전주문  오후주문      
------  --------  --------  
202101  421       80        
202102  312       298       
202103  421       325 
SQL
복사

BOOSTER QUIZ 10-2-3

[BOOSTER QUIZ 10-2-1]의 내용에서 오전주문, 오후주문을 기준축으로 처리하고 년월별 주문 건수가 컬럼으로 표시되도록 변경하시오.

•

대상 테이블: 주문(Ord)

•

조회 조건: OrdDtm이 2021년 1월부텉 2021년 3월까지의 주문 데이터

•

조회 컬럼: 주문구분, 주문_202101, 주문_202102, 주문_202103

•

추가 조건

◦

주문구분은 주문시간에 따라 오전주문과 오후주문을 구분하는 항목

▪

12시 이하인 경우 오전주문, 12시 초과 오후주문

◦

주문구분별로 GROUP BY 처리하시오.

▪

주문_202101은 2021년 1월에 발생한 주문 건수

▪

주문_202102은 2021년 2월에 발생한 주문 건수

▪

주문_202103은 2021년 3월에 발생한 주문 건수

◦

주문_202101~주문_202103을 구하기 위해서는 GROUP BY ~ 집계함수(CASE) 패턴을 사용하시오.

주문구분  주문_202101  주문_202102  주문_202103    
--------  -----------  -----------  -----------  
오전주문  421          312          421          
오후주문  80           298          325 
SQL
복사

Next: 10-3. 날짜 테이블

Upper: 10. 데이터 분석을 위한 작은 기술들