데이터베이스_Database/SQL

[SQL] 그룹함수(ROLLUP, CUBE, GROUPING SET) 예시를 통해 완전 습득하기!

200scs 2024. 8. 2. 10:33

SQL에서 집계 함수는 데이터를 그룹화하고 요약하는 데 사용됩니다. 이 중에서 GROUPING SETS, ROLLUP, CUBE는 여러 차원의 집계를 쉽게 처리할 수 있도록 도와주는 기능입니다. 각각의 집계 방식은 집계 데이터를 산출하는 방식에서 차이가 있습니다. 아래에 예시 테이블을 사용하여 각 집계 함수의 사용법과 결과를 설명하겠습니다.

예시 테이블

product region sales
A North 100
A South 150
B North 200
B South 250

GROUPING SETS

GROUPING SETS는 특정 컬럼 조합에 대해 개별적으로 집계를 수행하는 방법을 제공합니다. 이는 원하는 조합에 대해 각기 다른 집계 결과를 한 번에 얻을 수 있습니다.

예제 쿼리

SELECT product, region, SUM(sales) 
FROM sales_data 
GROUP BY GROUPING SETS ((product), (region), (product, region));

실행결과 

product region SUM(sales) 설명(실제 컬럼 X)
A NULL 250 A 제품의 전체 매출 합계
B NULL 450 B 제품의 전체 매출 합계
NULL North 300 North 지역의 모든 제품 매출 합계
NULL South 400 South 지역의 모든 제품 매출 합계
A North 100 A 제품의 North 지역 매출 합계
A South 150 A 제품의 South 지역 매출 합계
B North 200 B 제품의 North 지역 매출 합계
B South 250 B 제품의 South 지역 매출 합계

쿼리 해석 CASE 1 : 쿼리 관점

  • GROUPING SETS는 지정된 컬럼 조합에 대해 개별적으로 집계합니다.
  • 이 쿼리에서는 product, region, product와 region의 조합에 대해 각각 집계 결과를 보여줍니다.

ROLLUP

ROLLUP은 다차원 집계의 결과를 계층적으로 계산해주는 기능입니다. Excel의 소계 계산과 비슷한 개념으로, 특정 컬럼들의 계층 구조를 따라 부분 집계를 수행합니다.

ROLLUP 인자 2개

예제 쿼리

SELECT product, region, SUM(sales) 
FROM sales_data 
GROUP BY ROLLUP (product, region);

실행 결과

product region SUM(sales) 설명(실제 컬럼 X)
A North 100 (A, North)인 주문의 매출 합계
A South 150 (A, South)인 주문의 매출 합계
A NULL 250 product A에 대한 집계
B North 200 (B, North)인 주문의 매출 합계
B South 250 (B, South)인 주문의 매출 합계
B NULL 450 product B에 대한 집계
NULL NULL 700 전체 집계

쿼리 해석

  • ROLLUP은 지정된 컬럼 계층을 따라 차례로 집계합니다.
  • 인자 2개인 경우, product, region, 그리고 전체 집계를 포함한 계층적 결과를 제공합니다.

ROLLUP 인자 3개

예제 쿼리

SELECT product, region, sales, SUM(sales) 
FROM sales_data 
GROUP BY ROLLUP (product, region, sales);

실행 결과

product region sales total_sales 설명(실제 컬럼 X)
A North 100 100 (A, North, 100)인 주문의 집계
A South 150 150 (A, South, 150)인 주문의 집계
A NULL NULL 250 (A) 제품의 전체 매출 합계
B North 200 200 (B, North, 200)인 주문의 집계
B South 250 250 (B, South, 250)인 주문의 집계
B NULL NULL 450 (B) 제품의 전체 매출 합계
NULL NULL NULL 700 모든 제품의 총 매출 합계
각 차원 별 집계 설명
  1. (product, region, sales): 개별 제품, 지역, 판매 데이터에 대한 가장 구체적인 집계
    예를 들어, (A, North, 100)은 제품 A의 North 지역에서 100개의 판매를 나타냅니다.
  2. (product, region): 제품과 지역 수준에서의 집계로, 특정 제품이 특정 지역에서의 총 매출 합계
    예를 들어, (A, North)은 제품 A가 North 지역에서 판매된 총 매출을 의미합니다. (위 예시에서 이 집계는 결과에 누락되었습니다.)
  3. (product): 제품 수준에서의 집계로, 특정 제품의 전체 매출 합계
    예를 들어, (A)는 제품 A의 모든 지역에서의 총 매출을 의미합니다.
  4. 전체 집계: 모든 제품과 모든 지역의 총 매출을 나타냅니다. 전체 데이터 집합의 합계를 계산한 것으로, 이는 (NULL, NULL, NULL)로 표시되며 모든 조건이 포함되지 않은 경우를 나타냅니다.

쿼리 해석

  • ROLLUP 인자가 3개일 때, 각각의 컬럼 조합과 전체 집계를 포함하여 더욱 상세한 집계 결과를 제공합니다.
  • product, region, sales의 순서로 부분 집계를 수행하고 최종적으로 모든 행의 합계를 나타냅니다.

CUBE

CUBE는 지정된 컬럼들에 대한 모든 조합에 대해 집계를 계산합니다. 가능한 모든 집계 결과를 계산하고 제공합니다.

예제 쿼리

SELECT product, region, SUM(sales) 
FROM sales_data 
GROUP BY CUBE (product, region);

실행 결과

product region SUM(sales) 설명(실제 컬럼 X)
A North 100 A 제품의 North 지역 매출 합계
A South 150 A 제품의 South 지역 매출 합계
A NULL 250 A 제품의 모든 지역 매출 합계
B North 200 B 제품의 North 지역 매출 합계
B South 250 B 제품의 South 지역 매출 합계
B NULL 450 B 제품의 모든 지역 매출 합계
NULL North 300 모든 제품의 North 지역 매출 합계
NULL South 400 모든 제품의 South 지역 매출 합계
NULL NULL 700 전체 매출 합계
CUBE(product, region)에서 나올 수 있는 조합에 대한 집계
  1. (product, region, sales):
    • 개별 제품, 지역, 판매 데이터에 대한 가장 구체적인 집계
    • 예: (A, North, 100)은 제품 A가 North 지역에서 100의 판매를 기록했음을 나타냅니다.
  2. (product, region):
    • 제품과 지역 수준에서의 집계로, 특정 제품이 특정 지역에서 기록한 총 매출 합계
    • 예: (A, North)은 제품 A가 North 지역에서 판매된 총 매출을 의미합니다.
  3. (product):
    • 제품 수준에서의 집계로, 특정 제품이 모든 지역에서 기록한 총 매출 합계
    • 예: (A)는 제품 A의 모든 지역에서의 총 매출을 의미합니다.
  4. 전체 집계:
    • 모든 제품과 모든 지역의 총 매출을 나타내는 집계
    • 전체 데이터 집합의 합계를 계산한 것으로, 이는 (NULL, NULL)로 표시되며 모든 조건이 포함되지 않은 경우를 나타냅니다

쿼리 해석

  • CUBE는 지정된 모든 컬럼 조합에 대해 집계를 수행합니다.
  • 모든 가능한 조합에 대해 집계를 계산하고, 추가로 전체 집계도 제공합니다.
  • 이는 각 조합의 매출 합계와, 전체 매출 합계를 모두 구할 수 있어 데이터 분석에 유용합니다.

GROUPING SETS함수로 ROLLUP과 동일한 결과 산출하기!

위의 ROLLUP 결과와 동일하게 산출되게 하려면, GROUPING 함수로 어떻게 쿼리를 작성하면 될까요?

GROUPING SETS함수는 다양하게 그룹을 지을 수 있어서 CUBE, ROLLUP과 같은 특징이 있는 함수들과 동일한 결과를 산출할 수 있습니다.

우선 위의 ROLLUP에서 어떤 순서로 집계하여 결과를 산출했는지 분석해봅시다!

실행 결과의 설명(실제 컬럼 X) 부분을 확인해보면 아래의 순서대로 집계되었음을 알 수 있습니다.

  1. (product, region)
  2. (product)
  3. 전체

그렇다면 이 순서대로 GROUPING SETS에 넣어준다면 아래와 같이 되겠죠?

SELECT product, region, sales, SUM(sales) 
FROM sales_data 
GROUP BY GROUPING SETS (
	(product, region),
	(product), 
	()
);

실행 결과

product region SUM(sales) 설명(실제 컬럼 X)
A North 100 제품 A의 North 지역 매출 합계
A South 150 제품 A의 South 지역 매출 합계
B North 200 제품 B의 North 지역 매출 합계
B South 250 제품 B의 South 지역 매출 합계
A NULL 250 제품 A의 전체 매출 합계
B NULL 450 제품 B의 전체 매출 합계
NULL NULL 700 전체 제품의 전체 매출 합계

사실 컬럼의 순서는 ROLLUP의 결과와 조금 다릅니다.

하지만 내용만 필요하다고 했을 때는 일치하게

SUMMARY

  • GROUPING SETS: 특정 조합에 대해 개별적으로 집계.
  • ROLLUP: 계층 구조에 따라 집계를 수행.
  • CUBE: 모든 조합에 대해 집계.
저작자표시