11. 그룹함수 Cube, Roll up, Grouping set

📌 그룹함수의 필요성

그룹 함수(GROUP FUNCTION)의 필요성

결산 개념의 업무를 가지는 원가나 판매 시스템의 경우는 소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만드는 것이 중요 업무 중의 하나입니다. 개발자들이 이런 보고서를 작성하기 위해서는 레벨별 집계를 위한 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러 번 읽어 다시 재정렬하는 복잡한 단계를 거쳐야만 합니다.

하지만, 그룹 함수(GROUP FUNCTION)을 사용한다면, 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성할 수 있습니다. 또한, GROUPING 함수와 CASE 함수를 이용하면 쉽게 원하는 포맷의 보고서 작성도 가능합니다.

📌 요약

• ROLLUP 함수 : 소그룹 간의 소계를 계산함
• CUBE 함수 : GROUP BY 항목들간 다차원적인 소계를 계산할 수 있음 (모든 그룹케이스를 계산함)
• GROUPING SETS 함수 : 특정 항목에 대한 소계를 계산할 수 있음 (GROUPING SETS 은 특정항목을 그룹한 다수의 SQL들을 UNION한 것과 같음)

📌GROUPING SETS절

• Grouping sets은 왜 필요한가?

  • 만약 subject 별 평균과 name 별 평균, All 평균 정보를 한번에 보고 싶다면...

  • ⇒ 하나씩 Group by 한 결과를 Union All 해서 출력 가능

    • 근데 너무 쿼리문이 길어진다.

  • Union all 대신에 쓸 수 있는게 있다면 !?

    -- 1. 전체 평균 계산
    SELECT NULL,
           NULL,
           Avg(score)
    FROM   scorelist
    -- 2. 과목, 이름별 평균 계산
    UNION ALL
    SELECT subject,
           name,
           Avg(score)
    FROM   scorelist
    GROUP  BY subject,
              name
    -- 3. 과목별 평균 계산
    UNION ALL
    SELECT subject,
           NULL,
           Avg(score)
    FROM   scorelist
    GROUP  BY subject
    -- 4. 이름별 평균 계산
    UNION ALL
    SELECT NULL,
           name,
           Avg(score)
    FROM   scorelist
    GROUP  BY name;

  • 이렇게 긴 구문을

  • 아래와 같이 변환시킨다.

    SELECT subject,
           NAME,
           Avg(score)
    FROM   scorelist
    GROUP  BY grouping sets( ( subject, NAME ), -- 2. 과목, 이름별 평균 계산
    																	 subject, -- 3. 과목별 평균 계산
    																			NAME, -- 4. 이름별 평균 계산
    																			( ) ) -- 1. 전체 평균 계산

  • 장점은 수행 속도도 더 빠르고 간단하게 보이는 점

📌 Roll up

• ROLL UP이 뭔가?
  • 지정된 GROUPING 컬럼의 소계를 생성함
  • 소그룹 간의 소계를 계산
• 인자로 들어온 컬럼을 오른쪽(→)부터 하나씩 빼면서 Group 생성

  Rollup(월,부서)
  -- 부서 빼고 월별로 그룹화한 agg 함수값과 
  -- 마지막에는 둘다 빼고 총합으로 그룹화한 값이 나온다.

📌 Cube

• ROLL UP과의 차이
  • ROLL UP은 오른쪽부터 순서대로 하나씩 뺌
  • CUBE는 방향 상관없이 하나씩 빼며 모든 가능한 조합의 Group 생성

    CUBE(C1, C2)	ROLLUP(C1,C2)
    C1, C2	C1, C2
    C1	C1
    C2	X
    ()	()

GROUP BY CUBE((SUBJECT, NAME))

CUBE에 SUBJECT, NAME이 괄호안에 하나의 그룹이고, 제외 되면 아무 것도 없으므로 전체 평균 출력