GROUP BY

• GROUP BY는 데이터를 그룹화(GROUPING)하는 문법이다.
• GROUP BY로 정의된 항목(컬럼)은 중복 값이 제거되어 결과로 나온다.
• 중복을 제거하는 기능만을 놓고 보면 SELECT 절의 DISTINCT와 같지만 GROUP BY는 집계함수를 동시에 사용할 수 있다는 장점이 있다.
• 보통은 집계함수를 사용하기 위해 GROUP BY를 사용한다.
• GROUP BY는 WHERE 절 다음, ORDER BY 절 이전에 위치한다.

--주문일시, 지불유형별 주문총금액의 합
SELECT
    T1.ORD_DT,
    T1.PAY_TP,
    SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY T1.ORD_DT, T1.PAY_TP
ORDER BY T1.ORD_DT, T1.PAY_TP;

--결과
17/01/02    BANK    2700
17/01/02    CARD    3430
17/01/03    BANK    9700
17/01/03    CARD    7230
17/01/04    BANK    10700

 

• 집계함수는 GROUP BY 없이도 단독으로 사용할 수 있는 데, 이때 집계함수를 사용하지 않은 컬럼은 SELECT 절에서 같이 사용할 수 없다.
• GROUP BY의 기본적인 규칙
  1. GROUP BY에 사용한 컬럼한 SELECT 절에서 그대로 사용할 수 있다.
  2. GROUP BY에서 사용하지 않은 컬럼은 SELECT 절에서 집계함수를 사용해야 한다.
• GROUP BY에 컬럼을 정의할 때 컬럼을 변형할 수 있다.
• 변형을 위해 TO_CHAR, TO_DATE와 같은 오라클의 기본 함수뿐 아니라 CASE(또는 DECODE)와 같은 치환 문법도 사용할 수 있다. 또는 문자와 문자를 결합하거나 산술연산도 할 수 있다.
• CASE는 GROUP BY에도 사용할 수 있다.

--주문총금액이 5000 이상이면 High Order로
--5000 미만 3000 이상이면 Middle Order로
--그 외에는 Low Order로 분류하여 치환하여 금액에 대한 등급을 나타낸다.
--주문상태, 주문총금액등급별 주문 건수
SELECT
    T1.ORD_ST,
    CASE WHEN T1.ORD_AMT >= 5000 THEN 'High Order'
         WHEN T1.ORD_AMT >= 3000 THEN 'Middle Order'
         ELSE 'Low Order'
    END ORD_AMT_TP,
    COUNT(*) ORD_CNT
FROM T_ORD T1
GROUP BY T1.ORD_ST,
         CASE WHEN T1.ORD_AMT >= 5000 THEN 'High Order'
              WHEN T1.ORD_AMT >= 3000 THEN 'Middle Order'
              ELSE 'Low Order'
         END
ORDER BY 1, 2;

--결과
COMP    High Order       90
COMP    Low Order        2255
COMP    Middle Order     397
WAIT    High Order       45
WAIT    Low Order        188
WAIT    Middle Order     72

 

• 다만 일회성으로 사용하는 SQL라면 위와 같이 작성해도 상관없지만 실제 운영 화면에서는 이처럼 SQL을 사용하면 안 된다. 혹시라도 ORD_AMT의 기준이 변경되면 SQL을 변경해야 하기 때문이다.

--주문년월, 지불방법별 주문 건수
SELECT
    TO_CHAR(T1.ORD_DT, 'YYYYMM') ORD_YM,
    T1.PAY_TP,
    COUNT(*) ORD_CNT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY TO_CHAR(T1.ORD_DT, 'YYYYMM'), T1.PAY_TP
ORDER BY TO_CHAR(T1.ORD_DT, 'YYYYMM'), T1.PAY_TP;

 

• 집계함수의 괄호 안에도 CASE 문을 사용할 수 있다.
• CASE 조건에 따라 집계를 수행할 수 있으며 로우 데이터를 컬럼으로 출력할 수 있다.
• 아래는 계좌이체 건수와 카드결제 건수를 각각의 컬럼으로 표시한 것이다. 지불유형 컬럼을 CASE 문장을 이용해 두 개의 컬럼으로 만든 것으로 정규화된 테이블 구조에서 분석용 데이터를 보여주기 위해 자주 사용하는 기법이다.

--주문년월별 계좌이제 건수와 카드결제 건수
--숫자 1로 치환하고 치환된 결과에 SUM을 수행하면 각 건수를 구할 수 있다
SELECT
    TO_CHAR(T1.ORD_DT, 'YYYYMM') ORD_YM,
    SUM(CASE WHEN T1.PAY_TP = 'BANK' THEN 1 END) BANK_PAY_CNT,
    SUM(CASE WHEN T1.PAY_TP = 'CARD' THEN 1 END) CARD_PAY_CNT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY TO_CHAR(T1.ORD_DT, 'YYYYMM')
ORDER BY TO_CHAR(T1.ORD_DT, 'YYYYMM');

 

• 아래는 가로를 세로로 바꾸고, 세로를 가로로 바꾸기 위해 GROUP BY와 CASE를 조합했다.
• 많은 분석 화면이 월이나 주와 같은 시간 속성을 가로로 보여주길 원한다. 시간에 따른 데이터 수치 변화를 살펴보기에 시간 속성을 가로로 보는 것이 효율적이기 때문이다.
• 로우 데이터를 컬럼으로 변환하거나 반대로 컬럼을 로우로 변환하는 기능을 피벗(PIVOT)이라고 한다.
  • BI 툴이 있다면 피벗 기능을 툴 안에서 지원해주므로 GROUP BY와 CASE 조합을 사용할 일은 많지 않지만 BI 툴 없이 SQL만으로 분석 리포트를 개발해야 한다면 GROUP BY와 CASE 조합은 필수이다.
• 오라클 11g 버전부터 PIVOT 함수를 제공하기 때문에 GROUP BY~CASE를 사용하지 않아도 같은 결과를 만들어 낼 수 있다.
  • 다만 PIVOT을 사용할 수 없는 DBMS도 많다.
  • [Oracle] 그룹핑으로 피벗(PIVOT) 사용법

--지불유형을 세로(로우)로 나오게 하고 주문년월을 가로(컬럼)로 조회
--지불유형별 주문 건수
SELECT
    T1.PAY_TP,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201701' THEN 'X' END) ORD_CNT_1701,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201702' THEN 'X' END) ORD_CNT_1702,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201703' THEN 'X' END) ORD_CNT_1703,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201704' THEN 'X' END) ORD_CNT_1704,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201705' THEN 'X' END) ORD_CNT_1705,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201706' THEN 'X' END) ORD_CNT_1706,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201707' THEN 'X' END) ORD_CNT_1707,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201708' THEN 'X' END) ORD_CNT_1708,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201709' THEN 'X' END) ORD_CNT_1709,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201710' THEN 'X' END) ORD_CNT_1710,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201711' THEN 'X' END) ORD_CNT_1711,
    COUNT(CASE WHEN TO_CHAR(T1.ORD_DT, 'YYYYMM') = '201712' THEN 'X' END) ORD_CNT_1712
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY T1.PAY_TP
ORDER BY T1.PAY_TP;

--인라인 뷰를 이용한 지불유형별 주문 건수
--인라인 뷰에서 지불유형과 주문년월별로 먼저 카운트한 다음에
--인라인 뷰 바깥에서 지불유형별로 GROUP BY~CASE로 처리한다
SELECT
    T1.PAY_TP,
    MAX(CASE WHEN T1.ORD_YM = '201701' THEN T1.ORD_CNT END) ORD_CNT_1701,
    MAX(CASE WHEN T1.ORD_YM = '201702' THEN T1.ORD_CNT END) ORD_CNT_1702,
    MAX(CASE WHEN T1.ORD_YM = '201703' THEN T1.ORD_CNT END) ORD_CNT_1703,
    MAX(CASE WHEN T1.ORD_YM = '201704' THEN T1.ORD_CNT END) ORD_CNT_1704,
    MAX(CASE WHEN T1.ORD_YM = '201705' THEN T1.ORD_CNT END) ORD_CNT_1705,
    MAX(CASE WHEN T1.ORD_YM = '201706' THEN T1.ORD_CNT END) ORD_CNT_1706,
    MAX(CASE WHEN T1.ORD_YM = '201707' THEN T1.ORD_CNT END) ORD_CNT_1707,
    MAX(CASE WHEN T1.ORD_YM = '201708' THEN T1.ORD_CNT END) ORD_CNT_1708,
    MAX(CASE WHEN T1.ORD_YM = '201709' THEN T1.ORD_CNT END) ORD_CNT_1709,
    MAX(CASE WHEN T1.ORD_YM = '201710' THEN T1.ORD_CNT END) ORD_CNT_1710,
    MAX(CASE WHEN T1.ORD_YM = '201711' THEN T1.ORD_CNT END) ORD_CNT_1711,
    MAX(CASE WHEN T1.ORD_YM = '201712' THEN T1.ORD_CNT END) ORD_CNT_1712
FROM 
    (
    SELECT 
        T1.PAY_TP,
        TO_CHAR(T1.ORD_DT, 'YYYYMM') ORD_YM,
        COUNT(*) ORD_CNT
    FROM T_ORD T1
    WHERE T1.ORD_ST = 'COMP'
    GROUP BY T1.PAY_TP, TO_CHAR(T1.ORD_DT, 'YYYYMM')
    ) T1
GROUP BY T1.PAY_TP;

지불유형별 주문 건수 결과

• 자주 사용하는 집계함수는 SUM, COUNT, MIN, MAX가 있지만 COUNT는 사용방법에 따라 다른 결과가 나올 수 있다.
• COUNT 집계함수 사용 시 주의할 점
  • COUNT는 NULL 값을 0으로 카운트한다.
  • COUNT(*)는 로우 자체의 건수를 카운트하기 때문에 로우를 구성하는 컬럼이 모두 NULL이어도 1로 카운트하므로 COUNT(컬럼명)과 다른 결과를 나타낸다.

--COUNT는 NULL 값을 0으로 카운트한다
SELECT
    COUNT(COL1) CNT_COL1, --2
    COUNT(COL2) CNT_COL2, --0
    COUNT(COL3) CNT_COL3  --1
FROM
    (
    SELECT 'A' COL1, NULL COL2, 'C' COL3 FROM DUAL UNION ALL
    SELECT 'B' COL1, NULL COL2, NULL COL3 FROM DUAL
    ) T1;
 
--COUNT(컬럼명)과 COUNT(*)의 차이
SELECT 
    COUNT(COL1) CNT_COL1, --0
    COUNT(*) CNT_ALL      --2
FROM
    (
    SELECT NULL COL1 FROM DUAL UNION ALL
    SELECT NULL COL1 FROM DUAL
    ) T1;

 

• COUNT 안에서도 DISTINCT를 사용할 수 있다.
• 단, COUNT(DISTINCT)는 여러 컬럼을 동시에 사용할 수 없다. 두 개 이상의 컬럼을 사용하려면 컬럼을 파이프(||)로 결합해 사용해야 한다.
• COUNT(DISTINCT)는 아래와 같은 데이터를 구할 때 사용할 수 있으며 SELECT~EXISTS로도 구현할 수 있다.
  • 한 번이라도 로그인이 있는 고객 수
  • 한 번이라도 사용 기록이 있는 메뉴 수
  • 한 번이라도 판매가 이루어진 아이템 수

--주문년원별 주문고객 수(중복을 제거해서 카운트)와 주문건수
--2017년 1월에 A 고객이 주문을 3번 했어도 1번으로만 카운트한다
SELECT
    TO_CHAR(T1.ORD_DT, 'YYYYMM') ORD_YM,
    COUNT(DISTINCT T1.CUS_ID) CUS_CNT,
    COUNT(*) ORD_CNT
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170101', 'YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170401', 'YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT, 'YYYYMM')
ORDER BY TO_CHAR(T1.ORD_DT, 'YYYYMM');

--두 개 이상의 컬럼을 사용할 때
--주문상태와 지불유형의 조합에 대한 종류 수
SELECT COUNT(DISTINCT T1.ORD_ST||'-'||T1.PAY_TP)
FROM T_ORD T1;
 
--인라인 뷰
SELECT COUNT(*)
FROM
    (
    SELECT DISTINCT T1.ORD_ST, T1.PAY_TP
    FROM T_ORD T1
    ) T2;

--한 번이라도 로그인이 있는 고객 수
SELECT COUNT(DISTINCT T1.고객ID)
FROM 로그인 T1;
 
--SELECT~EXISTS를 이용
SELECT COUNT(*)
FROM 고객 T1
WHERE EXISTS
    (
    SELECT *
    FROM 로그인 A
    WHERE A.고객ID = T1.고객ID
    );

• HAVING 절은 GROUP BY가 수행된 결과 집합에 조건을 줄 때 사용한다. (WHERE 절과 같은 기능)
• HAVING은 GROUP BY 뒤에 위치하며 WHERE 절처럼 AND나 OR를 사용해 여러 개의 조건을 동시에 사용할 수 있다.
• HAVING에서는 GROUP BY에 정의한 컬럼은 그대로 사용할 수 있으나, GROUP BY에 정의하지 않은 내용은 집계함수 처리 후에 사용해야 한다.

--고객ID, 지불유형별 주문금액이 10000 이상인 데이터만 조회
SELECT
    T1.CUS_ID, 
    T1.PAY_TP,
    SUM(T1.ORD_AMT) ORD_TTL_MT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY T1.CUS_ID, T1.PAY_TP
HAVING SUM(T1.ORD_AMT) >= 10000
ORDER BY SUM(T1.ORD_AMT) ASC;

--HAVING 절 대신 인라인 뷰를 사용
--HAVING 조건은 인라인 뷰에 대한 WHERE 절로도 대신할 수 있다
SELECT T0.*
FROM
    (
    SELECT
        T1.CUS_ID, 
        T1.PAY_TP,
        SUM(T1.ORD_AMT) ORD_TTL_AMT
    FROM T_ORD T1
    WHERE T1.ORD_ST = 'COMP'
    GROUP BY T1.CUS_ID, T1.PAY_TP
    ) T0
WHERE T0.ORD_TTL_AMT >= 10000
ORDER BY T0.ORD_TTL_AMT ASC;