그룹 함수

그룹 함수란?

여러 행을 하나의 집합으로 보고 요약 결과를 만들어내는 함수다

대표적으로 ROLLUP, CUBE, GROUPING SETS가 있다. 이 함수들을 사용하면 여러 번의 GROUP BY 결과를 한 번에 표현할 수 있다.

그룹 함수가 필요한 이유

소계와 총계를 같이 보고 싶을 때 쿼리를 단순화할 수 있기 때문이다.
예를 들어 지역별 매출 합계를 구하는 쿼리와 전체 매출 합계를 구하는 쿼리를 각각 작성할 수도 있다. 하지만 이런 방식은 집계 기준이 많아질수록 쿼리가 길어지고 번거로워진다.

그래서 ROLLUP, CUBE, GROUPING SETS를 사용하면 여러 단계의 집계 결과를 한 번에 만들 수 있다. 따라서 그룹 함수는 단순히 합계를 구하는 기능이 아닌 다양한 집계 수준을 한 번에 보여주기 위한 도구다

ROLLUP이란?

계층적인 다차원 집계를 쉽게 구할 수 있도록 도와주는 그룹 함수다.
핵심은 왼쪽에서 오른쪽 방향으로 소계가 누적된다는 점이다.

ROLLUP 작동 원리

GROUP BY ROLLUP(컬럼1, 컬럼2)

다음 순서의 집계가 만들어진다.

• (컬럼1, 컬럼2) 기준 집계
• (컬럼1) 기준 집계
• 전체 집계

뒤에서부터 하나씩 빠지면서 상위 수준 집계가 만들어진다
그래서 ROLLUP은 소계와 총계를 같이 보고 싶을 때 특히 유용하다.

쉽게 생각해서 뒤에서 부터 하나씩 접는다고 생각하면 편하다

소계+총계 라는점을 까먹으면 안된다

ROLLUP 예시

SELECT REGION, PRODUCT, SUM(SALES_AMOUNT)
FROM SALES
GROUP BY ROLLUP(REGION, PRODUCT);

이 쿼리는 다음 결과를 한 번에 보여준다.

• 지역 + 상품별 매출
• 지역별 소계
• 전체 총계

East-Laptop, East-Tablet 같은 상세 집계가 먼저 나오고, 그다음 East 전체 소계, West 전체 소계가 나오며, 마지막에는 전체 합계가 나온다

ROLLUP 특징

ROLLUP은 입력된 컬럼 순서가 중요하다.

GROUP BY ROLLUP(A, B)와 GROUP BY ROLLUP(B, A)는 다른 결과를 만들 수 있다.

중요한점은 ROLLUP은 계층 순서에 따라 집계가 결정된다는 것이다

CUBE란?

입력한 컬럼들의 가능한 모든 조합에 대해 집계를 수행하는 그룹 함수다.
N개의 집합이면 2^N개의 집계가 생성된다

ROLLUP보다 더 많은 경우의 수를 만든다고 보면 된다.

CUBE 작동 원리

GROUP BY CUBE(A, B)

다음 집계가 모두 생성된다.

• (A, B) 집계
• (A) 집계
• (B) 집계
• 전체 집계

ROLLUP과 다른 점은, ROLLUP은 계층적으로 줄어드는 집계만 만들지만 CUBE는 가능한 모든 부분집합 집계를 만든다는 점이다. 그래서 A 기준 집계도 나오고, B 기준 집계도 따로 나온다.

CUBE 예시

SELECT REGION, PRODUCT, SUM(SALES_AMOUNT)
FROM SALES
GROUP BY CUBE(REGION, PRODUCT);

이 경우 결과에는 다음이 포함된다.

• 지역 + 상품별 집계
• 지역별 소계
• 상품별 소계
• 전체 총계

CUBE는 ROLLUP보다 한 단계 더 넓은 범위의 집계를 제공한다.
예를 들어 ROLLUP에서는 상품별 전체 합계가 따로 나오지 않을 수 있지만, CUBE에서는 상품만 기준으로 한 집계도 나온다.

CUBE 특징

• 모든 집계 조합을 만들기 때문에 경우의 수가 많다.
• 모든 차원의 집계를 수행하므로 연산량이 많아 CPU를 많이 사용할 수 있다.
• 결과가 ROLLUP보다 더 많고 복잡할 수 있다.

GROUPING SETS란?

GROUPING SETS는 원하는 집계 조합만 선택해서 생성하는 그룹 함수다.
ROLLUP처럼 계층적으로 묶지도 않고, CUBE처럼 모든 조합을 다 만들지도 않는다. 대신 필요한 GROUP BY 결과만 골라서 한 번에 합친다는 점이 핵심이다.

GROUPING SETS 작동 원리

GROUP BY GROUPING SETS(A, B)

 

다음 두 집계만 만들어진다.

• (A) 집계
• (B) 집계

A+B 상세 집계나 전체 집계는 포함되지 않는다.
이 점이 ROLLUP, CUBE와 가장 다른 부분이다.

GROUPING SETS 예시

SELECT REGION, PRODUCT, SUM(SALES_AMOUNT)
FROM SALES
GROUP BY GROUPING SETS(REGION, PRODUCT);

이 경우 결과는 다음 두 종류만 나온다.

• 지역별 집계
• 상품별 집계

즉 지역+상품 상세 집계도 없고, 전체 합계도 없다.
원하는 집계만 선택해서 보여주기 때문에 불필요한 결과를 줄일 수 있다.

GROUPING SETS 특징

 

• 원하는 집계 조합만 생성할 수 있다.
• 불필요한 소계나 전체합계를 만들지 않을 수 있다.
• “정해진 집계만” 빠르게 처리하고 싶을 때 유용하다.

GROUPING 함수란?

그룹 함수로 집계된 결과에서 NULL이 실제 데이터의 NULL인지, 아니면 집계 과정에서 만들어진 NULL인지 구분하기 위해 사용하는 함수다.
ROLLUP이나 CUBE를 쓰면 소계, 총계 행에서 특정 컬럼 값이 NULL로 표시될 수 있는데, 이 NULL이 원래 데이터 때문인지 집계 과정 때문인지 헷갈릴 수 있다. 이때 GROUPING 함수를 사용하면 구분할 수 있다.

GROUPING 사용 예시

SELECT CASE
         WHEN GROUPING(REGION) = 1 AND GROUPING(PRODUCT) = 1 THEN '전체'
         ELSE REGION
       END AS REGION_INFO,
       CASE
         WHEN GROUPING(PRODUCT) = 1 THEN '소계'
         ELSE PRODUCT
       END AS PRODUCT_INFO,
       SUM(SALES_AMOUNT) AS TOTAL_SALES
FROM SALES
GROUP BY ROLLUP(REGION, PRODUCT);

 

이 쿼리는 ROLLUP 결과에서 NULL로 보이던 소계, 총계 행을 사람이 읽기 좋게 “소계”, “전체” 같은 문자로 바꿔서 보여준다.

GROUPING 함수는 집계 결과를 해석하기 쉽게 만들어주는 보조 도구라고 보면 된다

GROUP BY와 그룹 함수를 함께 해석하는 방법

그룹 함수를 단순히 외우는 것보다, GROUP BY를 여러 번 수행한 결과를 한 번에 표현한 것처럼 해석하면 이해가 쉽다

ROLLUP 해석

GROUP BY ROLLUP(REGION, PRODUCT)

아래 세 GROUP BY 결과를 합친 것과 비슷하다.

• GROUP BY REGION, PRODUCT
• GROUP BY REGION
• GROUP BY ()

즉 상세 집계 → 상위 집계 → 전체 집계 순서로 생각하면 된다.

CUBE 해석

GROUP BY CUBE(REGION, PRODUCT)

아래 네 가지 GROUP BY의 합처럼 볼 수 있다.

• GROUP BY REGION
• GROUP BY PRODUCT
• GROUP BY REGION, PRODUCT
• GROUP BY ()

즉 가능한 모든 조합을 전부 만든다고 해석하면 된다.

GROUPING SETS 해석

GROUP BY GROUPING SETS((PRODUCT), (), REGION)

원하는 GROUP BY 결과만 선택해서 합친다
따라서 GROUPING SETS는 “여러 GROUP BY를 한 번에 실행하는 문법”처럼 생각하면 이해가 훨씬 쉬워진다.