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 algorithm 디자인패턴 Gof Spring effective Java class spring Ubuntu 이것이 자바다 자바의 정석 object 데이터 OS TDD javascript quick_sort stream 객체지향 AWS Algorithm Khan_academy MSA NodeJS Nomad_Coders OOP cloud controller design_pattern frontend gof gradle heap_sort lambda node