SQL DB GROUP BY ROLL UP

Oracle - GROUP BY

SQL BOOSTER를 읽고

Posted by Yan on February 12, 2024

SQL 스터디 1주차!

🖇 GROUP BY

  • 데이터를 그룹화하는 문법. 같은 값을 가진 데이터끼리 모으는 것.
  • 중복된 값이 제거되어 결과로 나올 수 있도록 한다 = DISTINCT의 역할과 같다.
    • DISTINCT 와 다른 점: SUM, MIN 같은 집계함수를 쓸 수 있다.
  • TO_CHAR, TO_DATE같은 기본 함수와 CASE문 같은 문법을 쓸 수 있다.
  • 문법 : WHERE 절과 ORDER BY 절 사이에 쓴다. SELECT 절에는 GROUP BY에 사용할 컬럼을 똑같이 써준다.

주요 집계함수

  1. SUM : 수량, 금액과 같은 숫자형 데이터의 합
  2. COUNT : 데이터의 건수
  3. MIN : 데이터의 최솟값
  4. MAX : 데이터의 최댓값
  • 집계함수는 GROUP BY가 없어도 단독 사용할 수 있다.
  • 집계함수 괄호 안에서도 CASE문을 사용할 수 있다.
  • 집계함수를 사용하지 않은 컬럼은 SELECT절에서 같이 사용할 수 없다.
	-- 집계함수 - 에러가 발생하는 SQL
	SELECT  T1.ORD_ST -- 집계함수를 사용하지 않은 컬럼
		,COUNT(*) CNT
		,SUM(T1.ORD_AMT) TTL_ORD_AMT
		,MIN(T1.ORD_SEQ) MIN_ORD_SEQ
  	,MAX(T1.ORD_SEQ) MAX_ORD_SEQ
	FROM    T_ORD T1
	WHERE T1.ORD_DT>= TO_DATE('20170101','YYYYMMDD')
	AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD');

	-- 집계함수 - 정상적인 SQL
	SELECT  COUNT(*) CNT
		,SUM(T1.ORD_AMT) TTL_ORD_AMT
		,MIN(T1.ORD_SEQ) MIN_ORD_SEQ
		,MAX(T1.ORD_SEQ) MAX_ORD_SEQ
	FROM    T_ORD T1
	WHERE T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD')
	AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD');

COUNT 집계 함수 사용시 주의점

NULL을 집계하는지?

  • COUNT는 NULL 값을 0으로 카운트한다.
  • 그러나, COUNT(*)은 로우 자체를 건수로 카운트하여 로우를 구성하는 컬럼이 모두 NULL이어도 1로 카운트한다.

중복을 제거하려면?

  • COUNT(DISTINCT 컬럼명)으로 중복을 제거할 수 있다.
  • 한번이라도 ~~한 데이터를 찾을 때 유용하다.
	-- 주문년월별 주문고객 수(중복을 제거해서 카운트), 주문건수
	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');

  • COUNT(DISTINCT)는 여러 컬럼을 동시에 사용할 수 없다. 2개 컬럼 사용시 ||로 결합해 사용해야 한다.

    --USE CONCAT
  SELECT  COUNT(DISTINCT T1.ORD_ST||'-'||T1.PAY_TP)
  FROM    T_ORD T1;
SELECT ~ EXISTS로 대신하기

DISTINCT가 아닌 EXISTS로 구현할 수도 있다.

HAVING

  • GROUP BY가 수행된 결과 집합에 조건을 줄 때 사용
  • WHERE 절과 같은 기능
  • 문법 : GROUP BY뒤에 위치. ORDER BY 앞에 위치. AND, OR 사용 가능
  • GROUP BY에 정의한 컬럼은 그대로 사용할 수 있으나, GROUP BY에 정의하지 않은 내용은 집계함수 처리 후에 사용해야 한다.
  • 인라인-뷰에 대한 WHERE절로 대신하는 방법: GROUP BY결과를 인라인-뷰로 처리, 인라인-뷰 바깥에서 WHERE절로 처리

📍GROUP BY 관련 기억할 것들

  1. GROUP BY에 사용한 컬럼만 SELECT 절에서 그대로 사용할 수 있다.
  2. GROUP BY에 사용하지 않은 컬럼은 SELECT 절에서 집계함수를 사용해야 한다.
  3. HAVING 사용시 GROUP BY에 정의한 컬럼은 그대로 사용할 수 있으나, GROUP BY에 정의하지 않은 내용은 집계함수 처리 후에 사용해야 한다.

🖇 ROLLUP

  • 소계와 전체합계가 필요한 경우 사용
  • 문법 : GROUP BY뒤에 ROLL UP이라고 적어서 사용
    • 예시 : GROUP BY ROLL UP(A,B,C,D)
      • GROUP BY된 A+B+C+D별 데이터
      • A+B+C별 소계 데이터
      • A+B별 소계 데이터
      • A별 소계 데이터
      • 전체합계
	-- GROUP BY
	SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM 
			,T1.CUS_ID
			,SUM(T1.ORD_AMT) ORD_AMT
	FROM    T_ORD T1
	WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
	AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD') 
	AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
	GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID
	ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID;

  -- GROUP BY~ROLLUP
	SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM 
			,T1.CUS_ID
			,SUM(T1.ORD_AMT) ORD_AMT
	FROM    T_ORD T1
	WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
	AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD') 
	AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
	GROUP BY 
	ROLLUP(TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID)
	ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID;

ROLL UP 주의사항 : 컬럼 순서

  • 컬럼 순서에 따라 다른 소계가 나온다. 컬럼 순서대로 계층적 소계를 만들기 때문.

    • 예시1 : GROUP BY ROLL UP(A,B,C,D)
      • GROUP BY된 A+B+C+D별 데이터
      • A+B+C별 소계 데이터
      • A+B별 소계 데이터
      • A별 소계 데이터
      • 전체합계
    • 예시2 : GROUP BY ROLL UP(B,A,C,D)
      • GROUP BY된 B+A+C+D별 데이터
      • B+A+C별 소계 데이터
      • B+A별 소계 데이터
      • B별 소계 데이터
      • 전체합계

특정 부분 소계만 구하고 싶을 때

ROLLUP의 위치를 옮기거나 소계가 필요한 대상을 괄호로 조정한다.

컬럼 묶기

  • 여러 컬럼을 하나의 괄호로 묶을 수 있다.
  • 전체합계와 일부 컬럼만 소계를 내야할 때 유용하다.
	-- 주문년월, 지역ID, 고객등급별 주문금액 – 전체 합계만 구하기
	SELECT  CASE  WHEN GROUPING(TO_CHAR(T2.ORD_DT,'YYYYMM'))=1 THEN 'Total' 
				  ELSE TO_CHAR(T2.ORD_DT,'YYYYMM') END ORD_YM 
			,CASE WHEN GROUPING(T1.RGN_ID) = 1 THEN 'Total' ELSE T1.RGN_ID END RGN_ID
			,CASE WHEN GROUPING(T1.CUS_GD) = 1 THEN 'Total' ELSE T1.CUS_GD END CUS_GD
			,SUM(T2.ORD_AMT) ORD_AMT
	FROM    M_CUS T1
			,T_ORD T2
	WHERE   T1.CUS_ID = T2.CUS_ID
	AND     T2.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
	AND     T2.ORD_DT < TO_DATE('20170401','YYYYMMDD')
	AND     T1.RGN_ID IN ('A','B')
	GROUP BY ROLLUP((TO_CHAR(T2.ORD_DT,'YYYYMM') ,T1.RGN_ID ,T1.CUS_GD))
	ORDER BY TO_CHAR(T2.ORD_DT,'YYYYMM') ,T1.RGN_ID ,T1.CUS_GD;

전체 합계를 구할 때는 괄호가 두개다.

ROLLUP안에 정의된 모든 컬럼이 하나로 인식되어, 전체합계만 구할 수 있다.

GROUPING

  • 컬럼값이 소계인지 아닌지 구분해준다. 해당 컬럼이 ROLLUP 처리되었으면 1을 반환하고, 그렇지 않으면 0 반환.
  • ROLLUP으로 소계를 만든 컬럼이 NULL로 표시되어, 실제 데이터가 NULL인지, ROLLUP된 결과인지 구분하기 어려워지기 때문에 GROUPING이 필요하다.
  • ROLLUP된 컬럼을 다른 값으로 치환할 때는 꼭 GROUPING을 사용해야 한다.
	-- NULL이 존재하는 컬럼인 PAY_TP에 대해 ROLLUP을 수행. GROUPING함수 사용
	SELECT  T1.ORD_ST ,GROUPING(T1.ORD_ST) GR_ORD_ST 
			,T1.PAY_TP ,GROUPING(T1.PAY_TP) GR_PAY_TP
			,COUNT(*) ORD_CNT
	FROM    T_ORD T1
	GROUP BY ROLLUP(T1.ORD_ST, T1.PAY_TP);

📍ROLLUP 관련 기억할 것들

  1. 여러 개 칼럼이 GROUP BY될 때 전체 합계만 필요하다면 GROUP BY ROLLUP((A, B, C, D))와 같이 사용한다.
  2. 여러 칼럼 중 앞 3개 컬럼까지의 소계와 전체합계가 필요하다면 GROUP BY ROLLUP(A, B, C, (D, E, F))와 같이 사용한다.

ROLLUP을 대신하는 세 가지 방법

1. UNION ALL
  • ROLLUP대신에 흔하기 사용하는 방법.
  • 성능에서 손해를 볼 수 있다. UNION ALL이 많아질수록 SELECT절의 컬럼 순서를 맞추는 작업이 번거롭다.
	-- ROLLUP을 UNION ALL로 대신하기
	SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,T1.CUS_ID
			,SUM(T1.ORD_AMT) ORD_AMT
	FROM    T_ORD T1
	WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
	AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD') 
	AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
	GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID
	UNION ALL
	SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,'Total' CUS_ID
			,SUM(T1.ORD_AMT) ORD_AMT
	FROM    T_ORD T1
	WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
	AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD') 
	AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
	GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')
	UNION ALL
	SELECT  'Total' ORD_YM ,'Total' CUS_ID
			,SUM(T1.ORD_AMT) ORD_AMT
	FROM    T_ORD T1
	WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
	AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD') 
	AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD');
2. 카르테시안 조인

조인조건을 주지 않고 집합을 구성하는 모든 데이터의 조인이 발생한다. 조인 과정에서 성능 저하가 발생할 수 있다.

	-- ROLLUP을 카테시안 조인으로 대신하기
	SELECT  CASE WHEN T2.RNO = 1 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
				  WHEN T2.RNO = 2 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
				  WHEN T2.RNO = 3 THEN 'Total' END ORD_YM
			,CASE WHEN T2.RNO = 1 THEN T1.CUS_ID
				  WHEN T2.RNO = 2 THEN 'Total'
				  WHEN T2.RNO = 3 THEN 'Total' END CUS_ID
			,SUM(T1.ORD_AMT) ORD_AMT
	FROM    T_ORD T1
			,(
				SELECT ROWNUM RNO FROM DUAL CONNECT BY ROWNUM <= 3
			) T2
	WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
	AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD') 
	AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
	GROUP BY CASE WHEN T2.RNO = 1 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
				  WHEN T2.RNO = 2 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
				  WHEN T2.RNO = 3 THEN 'Total' END
			,CASE WHEN T2.RNO = 1 THEN T1.CUS_ID
				  WHEN T2.RNO = 2 THEN 'Total'
				  WHEN T2.RNO = 3 THEN 'Total' END;
3. WITH 와 UNION ALL
	-- ROLLUP을 WITH 절과 UNION ALL로 대체
	WITH T_RES AS(
		  SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,T1.CUS_ID
				  ,SUM(T1.ORD_AMT) ORD_AMT
		  FROM    T_ORD T1
		  WHERE   T1.CUS_ID IN ('CUS_0001','CUS_0002')
		  AND     T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD') 
		  AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
		  GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM') ,T1.CUS_ID
		  )
	SELECT  T1.ORD_YM ,T1.CUS_ID ,T1.ORD_AMT
	FROM    T_RES T1
	UNION ALL
	SELECT  T1.ORD_YM ,'Total' ,SUM(T1.ORD_AMT)
	FROM    T_RES T1
	GROUP BY T1.ORD_YM
	UNION ALL
	SELECT  'Total' ,'Total' ,SUM(T1.ORD_AMT)
	FROM    T_RES T1;

CUBE

  • 조합 가능한 모든 소계를 만들어 낸다.
  • 문법: ROLLUP과 동일
    • 예시: GROUP BY CUBE(A, B, C)
      • A+B별 소계
      • A별 소계
      • A+C별 소계
      • B+C별 소계
      • B별 소계
      • C별 소계
  • 컬럼이 많고 처리할 데이터가 많을수록 성능이 좋지 못하다.

GROUPING SETS

  • ROLLUP이나 CUBE처럼 소계를 만든다.
  • 문법 : GROUP BY뒤에 ROLLUP, CUBE대신에 GROUPING SETS라고 표기한 ㅅ후, 그룹화할 컬럼을 괄호로 묶어 나열
  • 소계가 필요한 집합을 일일이 지정할 수 있다.
	-- 주문년월, 고객ID별 주문건수와 주문 금액 – GROUPING SETS 활용
	SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
			,T1.CUS_ID
			,COUNT(*) ORD_CNT
			,SUM(T1.ORD_AMT) ORD_AMT
	FROM    T_ORD T1
	WHERE   T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
	AND     T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
	AND     T1.CUS_ID IN ('CUS_0061','CUS_0062')
	GROUP BY GROUPING SETS(
					  (TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID)  --GROUP BY기본 데이터
					  ,(TO_CHAR(T1.ORD_DT,'YYYYMM'))  --주문년월별 소계
					  ,(T1.CUS_ID)  --고객ID별 소계
					  ,()   --전체합계
				  );
reference

SQL BOOSTER

CATALOG
    FEATURED TAGS
    Udemy java CS SQL DB Spring algorithm 디자인패턴 Gof effective Java class spring Ubuntu 이것이 자바다 자바의 정석 TDD object 데이터 OS gradle javascript quick_sort stream 객체지향 AWS Algorithm Khan_academy MSA NodeJS Nomad_Coders OOP cloud controller design_pattern domain frontend gof heap_sort lambda node