DataBase/SQL

SQL BOOSTER - GROUP BY와 ROLLUP

now0204 2024. 6. 2. 18:37

 

프로젝트를 수행하는데 최소한의 SQL임! 잘 알아두자 

 

 

1. GROUP 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;
  • T1.ORD_DT로 1차 그룹화 후 T1.PAY_TP로 2차 그룹화 (주문일시, 지불유형별 주문 금액 합계)

출처: https://thebook.io/006977/0175/

  • 자주 쓰는 집계함수는 다음과 같다.
    • SUM,COUNT,MIN,MAX
    • OVER 절과 함께 사용하는 SUM,LEAD,LAG와 같은 분석함수는 집계함수와 다른 개념이다. 
    • 집계함수는 GROUP BY 없이도 단독 사용 가능(단, 집계함수를 사용하지 않은 컬럼 동시 SELECT 불가)
      • 모든 ROW 데이터에 대해 집계함수 적용

 

  1. 규칙 1 GROUP BY에 사용한 컬럼만 SELECT 절에서 그대로 사용 가능하다.
  2. 규칙 2 GROUP BY에 사용하지 않은 컬럼은 SELECT절에서 집계함수를 사용해야한다. 

 

1.2 GROUP BY 컬럼 변형 

  • GROUP BY에 컬럼을 정의할 대 컬럼을 변형할 수 있다. 변형을 위해 TO_CHAR, TO_DATE와 같은 오라클 기본 함수 
  • CASE와 같은 치환 문법 사용할 수 있다. 
  • 또한 문자와 문자 결합 혹은 산술연산도 할 수 있다. 
SELECT ORD_ST, 
    CASE WHEN ORD_AMT >= 5000 THEN 'HIGH'
         WHEN ORD_AMT >= 3000 THEN 'Middle Order'
         ELSE 'Low Order' END ORD_AMT_TP, COUNT(*) ORD_CNT
FROM T_ORD
GROUP BY ORD_ST, 
     CASE WHEN ORD_AMT >= 5000 THEN 'HIGH'
          WHEN ORD_AMT >= 3000 THEN 'Middle Order'
          ELSE 'Low Order' END
 ORDER BY 1,2;
  • CASE 처리를 통해 GROUP BY를 하고 있다.
    • ORDER BY에서 사용한 컬럼 + 데이터 명  SELECT에서 사용한 컬럼 + 데이터 명 일치해야한다. (규칙 1)
  • 1회성 쿼리에선 주문금액을 CASE로 나누는 위와 같은 SQL이 괜찮지만, 운영할 때는 지양하자 
    • ORD_AMT의 기준이 변경되면 SQL을 모두 변경해야하기 때문이다. 
SELECT TO_CHAR(ORD_DT,'YYYYMM') ORD_YM, PAY_TP, COUNT(*) ORD_CNT
FROM T_ORD
WHERE ORD_ST = 'COMP'
GROUP BY TO_CHAR(ORD_DT,'YYYYMM'), PAY_TP
ORDER BY TO_CHAR(ORD_DT,'YYYYMM'), PAY_TP;
  •  위는 주문일시를 YYYYMM형태로 변경해서 주문년월별 주문 건수 조회하는 SQL이다. 

 

1.3 집계함수에서 CASE문 사용하기 

 

집계함수 괄호 안에서도 CASE문을 사용할 수도 있다. 

이를 사용하면, CASE 조건에 따라 집계를 수행할 수 있다. 

SELECT TO_CHAR(ORD_DT,'YYYYMM') ORD_YM, SUM(CASE WHEN PAY_TP = 'BANK' THEN 1 END) BANK_PAY_CNT,
SUM(CASE WHEN PAY_TP ='CARD' THEN 1 END) CARD_PAY_CNT
FROM T_ORD
WHERE ORD_ST = 'COMP'
GROUP BY TO_CHAR(ORD_DT,'YYYYMM')
ORDER BY TO_CHAR(ORD_DT,'YYYYMM');
  • 주문년월별로 계좌이체 건수와 카드결제 건수'를 조회하는 SQL이다. 
    • CASE에 따라 sum에 1을 넘겨서 계산하는 방식은 매우 자주 사용하니 잘 봐두자

201701 PAY_TP CARD, BANK -> 집계함수 -> 201701 PAY_TP BANK (1) PAY_TP CARD(1) 

SELECT PAY_TP,
        COUNT(CASE WHEN TO_CHAR(ORD_DT,'YYYYMM') = '201701' THEN 'X' END) ORD_CNT_1701
        , COUNT(CASE WHEN TO_CHAR(ORD_DT,'YYYYMM') = '201702' THEN 'X' END) ORD_CNT_1702
        , COUNT(CASE WHEN TO_CHAR(ORD_DT,'YYYYMM') = '201703' THEN 'X' END) ORD_CNT_1703
        , COUNT(CASE WHEN TO_CHAR(ORD_DT,'YYYYMM') = '201704' THEN 'X' END) ORD_CNT_1704
        , COUNT(CASE WHEN TO_CHAR(ORD_DT,'YYYYMM') = '201705' THEN 'X' END) ORD_CNT_1705
        , COUNT(CASE WHEN TO_CHAR(ORD_DT,'YYYYMM') = '201706' THEN 'X' END) ORD_CNT_1706
        , COUNT(CASE WHEN TO_CHAR(ORD_DT,'YYYYMM') = '201707' THEN 'X' END) ORD_CNT_1707
        
        FROM T_ORD
        WHERE ORD_ST = 'COMP'
        GROUP BY PAY_TP
        ORDER BY PAY_TP;
        
        
// 혹은 아래와 같은 방식 사용 가능
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
       
       FROM (
        SELECT t2.PAY_TP, TO_CHAR(t2.ORD_DT,'YYYYMM') ORD_YM,COUNT(*) ORD_CNT
        FROM T_ORD t2
        WHERE t2.ORD_ST = 'COMP'
        GROUP BY t2.PAY_TP, TO_CHAR(t2.ORD_DT,'YYYYMM')
        )t1
  GROUP BY t1.PAY_TP;
  
  -- CARD 201701 73
     CARD 201702 153
  -- PAY  201701 67
     PAY  201702 79 -> 인라인뷰 결과 
 -- MAX를 사용한 이유는 인라인뷰 COUNT 결과 가져올 때 집계함수 사용해야하는데 마땅한게 없어서 쓴듯

  • 건수를 시간 순서대로 표현했다
    • 이렇게 가로에 시간 속성을 보여주는 것이 굉장히 흔한 편이다. 
    • 물론 컬럼을 로우로 변환하는 PIVOT 기능을 지원하는 BI툴이 많음으로, GROUP BY와 CASE 조합 사용할 일은 많지는 않다.

1.4 COUNT 집계함수

 

  • COUNT는 다른 집계함수와 다르게 조금 더 살펴볼 필요가 있다. 
  1. COUNT(칼럼)는 NULL은 0으로 카운트한다. (칼럼 확인) 
  2. COUNT(*)는 로우를 COUNT로 (로우 갯수 확인)
  3. SELECT절에서 DISTINCT를 중복제거 가능하다 COUNT(DISTINCT 컬럼)
    1. COUNT(DISTINCT T1.ORD_ST || '-'|| T1.PAY_TP) 두개 이상 컬럼 DISTINCT 처리 
    2. 혹은 인라인뷰를 사용해야 두 개 이상의 컬럼에 사용할 수 있다.
      1. 한 번이라도 로그인이 있는 고객수
      2. 한 번이라도 사용 기록이 있는 메뉴 수 
      3. 한 번이라도 판매가 이루어진 아이템 수 
-- 각각 SELECT ~ EXISTS 혹은 DISTICT를 사용하여 
SELECT COUNT(*)
FROM 고객 t1
WHERE EXISTS(
        SELECT * 
        FROM 로그인 A
        WHERE A.고객ID = T1.고객ID
        );
                      
                      
SELECT COUNT(DISTINCT T1.고객ID)
FROM 로그인 T1

 

2. HAVING 

 

HAVING절은 GROUP BY가 수행된 결과 집합에 조건을 줄 때 사용한다. WHERE절 기능 수행 

HAVING은 AND나 OR을 이용해 여러 조건을 동시에 사용할 수도 있다. 

HAVING에서는 GROUP BY에서 정의한 내용은 그대로 사용할 수 있으나, GROUP BY에 없는 내용은 집계함수 처리 후에 사용해야한다. 

SELECT T1.CUS_ID,T1.PAY_TP,SUM(T1.ORD_AMT) ORD_TTL_AMT
FROM T_ORD T1
GROUP BY T1.CUS_ID, T1.PAY_TP
HAVING T1.ORD_ST ='COMP' -- ERROR
  • WHERE절에 사용하거나, T1.ORD_ST에 집계함수를 적용하면 사용할 수 있다.
  • 인라인 뷰를 사용해 처리할 수도 있다
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;
  • 그룹으로 묶고, WHERE절 처리 

https://www.yes24.com/Product/Goods/82818767

 

SQL BOOSTER - 예스24

SQL BOOSTER는 프로젝트 성공을 위한 SQL 필독서다. 이 책은 마치 프로젝트를 진행하는 순서처럼 구성되어 있다. 프로젝트 투입을 위해 필요한 SQL 기술을 설명하고, 성능 테스트를 위해 필요한 기술

www.yes24.com