[SQL] 그룹 함수 (1) ROLLUP(소그룹 간 소계 출력), CUBE(다차원 소계 출력)

먼저, 그룹 함수란 무엇인지 살펴보자.

그룹 합수(Group Funtion)는 테이블의 전체 행을

하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여

그룹별로 결과를 출력하는 함수를 말한다. 

 

소계* 및 총계 등을 구하기 위해서 그룹 함수를

사용한다면 단일 DML만으로도 원하는 작업을 할 수 있다.

 

*소계 : 전체가 아닌 어느 한 부분만 계산한 합계

 

 

그룹 함수의 유형에는 ROLLUP, CUBE, GROUPING SETS가 있다.

 

그 중 ROLLUP과 CUBE는  GROUP BY절에서 사용되어

그룹별 소계를 추가로 보여주는 역할을 한다.

 

이번에 다룰 ROLLUP과 CUBE의 특징을 요약하자면,

ROLLUP의 경우 레벨별로, 

CUBE의 경우 가능한 조합별로 집계를 수행한다.

 

---

 

ROLLUP(expr1, expr2, ...)

 

ROLLUP은 expr로 명시한 표현식을 기준으로 집계한 결과,

즉 추가적인 집계 정보를 보여 준다.

 

ROLLUP 절에 명시할 수 있는 표현식에는 그룹화 대상, 

즉 SELECT 리스트에서 집계 함수를 제외한 컬럼 등의

표현식이 올 수 있다. 

 

ROLLUP은 '말아 올린다'는 의미로, 

ROLLUP에서 명시한 표현식 개수에 따라

레벨이 정해지고, 하위 레벨부터 상위 레벨로

'말아 올려진' 집계 결과가 계산된다.

 

명시한 표현식의 수와 순서(오른쪽에서 왼쪽 순으로)에 따라

레벨별로 집계한 결과가 반환된다. 

표현식 개수가 N개이면 N+1레벨까지,

즉 지정 컬럼의 수보다 하나 더 큰 레벨만큼

중간 집계 값이 생성된다.

 

순서로는 하위 레벨에서 상위 레벨 순으로 데이터가 집계되는데

ROLLUP의 지정 컬럼은 계층별로 구성되기 때문에

순서가 바뀌면 수행 결과가 바뀌기 때문에 유의해야 한다.

 

 

활용 구문은 다음과 같다.

SELECT 컬럼명1, ... , 집계함수
FROM 테이블명
[WHERE ... ]
GROUP BY [컬럼명1, ...] ROLLUP (그룹화할 열)
[HAVING ...]
[ORDER BY ... ]

 

소계 집계 대상이 되는 컬럼을 ROLLUP키워드 뒤에 기재하고,

소계 집계 대상이 아닌 경우에는 GROUP BY 키워드 뒤에 기재한다.

 

SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 ROLLUP 키워드

뒤에 기재되어야 한다. 또한 ORDER BY 구문을 활용해 계층 내 

정렬을 할 수 있다.

 

 

 

예시 코드와 함께 좀 더 자세히 살펴보자.

먼저 customers 테이블을 생성하고 

고객정보 데이터를 삽입했다. 

 

이후 ROLLUP을 사용하여 지역과 성별에 따른 

고객 수와 총 매출을 계산해보고자 한다.

// customers 테이블 생성
CREATE TABLE customers (
    id INT,
    name VARCHAR(50),
    region VARCHAR(50),
    gender VARCHAR(10),
    sales DECIMAL(10, 2)
);

// 데이터 삽입
INSERT INTO customers VALUES (1, 'Alice', 'North', 'Female', 100.50);
INSERT INTO customers VALUES (2, 'Bob', 'South', 'Male', 200.75);
INSERT INTO customers VALUES (3, 'Charlie', 'East', 'Male', 150.25);
INSERT INTO customers VALUES (4, 'David', 'West', 'Male', 300.00);
INSERT INTO customers VALUES (5, 'Emma', 'North', 'Female', 120.00);
INSERT INTO customers VALUES (6, 'Frank', 'South', 'Male', 250.50);
INSERT INTO customers VALUES (7, 'Grace', 'East', 'Female', 180.75);
INSERT INTO customers VALUES (8, 'Henry', 'West', 'Male', 400.25);
INSERT INTO customers VALUES (9, 'Ivy', 'North', 'Female', 150.25);
INSERT INTO customers VALUES (10, 'Jack', 'South', 'Male', 220.50);
INSERT INTO customers VALUES (11, 'Kate', 'East', 'Female', 190.00);
INSERT INTO customers VALUES (12, 'Leo', 'West', 'Male', 280.75);
INSERT INTO customers VALUES (13, 'Mary', 'West', 'Female', 180.00);
INSERT INTO customers VALUES (14, 'Nick', 'North', 'Male', 210.25);
INSERT INTO customers VALUES (15, 'Olivia', 'South', 'Female', 240.50);

//지역(region)과 성별(gender)에 따라 고객 수와 총 매출을 계산
SELECT region, gender, COUNT(*) as customer_count, SUM(sales) as total_sales
FROM customers
GROUP BY ROLLUP (region, gender);

 

 

위 쿼리문의 결과에서 확인할 수 있는 그룹화 수준은 아래와 같다.

표현식의 개수가 region, gender로 2개이기 때문에 2+1 = 3 으로 

총 3개의 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계되었다.

 

ROLLUP (region, gender)을 사용하는 경우에는

지역(region)과 성별(gender) 두 열을 기준으로 그룹화를 하게 된다.

 

하위 레벨 : 지역과 성별의 모든 조합

상위 레벨 : 지역별 총계와 전체 총계

 

즉, 하위 레벨은 주어진 열들의 모든 값의 조합을 포함하는

가장 상세한 그룹화 수준이고, 상위 레벨은 주어진 열들의

각각에 따른 총계를 포함하는 더 높은 수준의 그룹화에 해당한다.

 

총 3개의 레벨에 대한 그룹의 데이터가 위 규칙에 맞게 출력되었다.

 

1. 지역과 성별의 모든 조합에 대한 총계 

2. 지역별 총계 

3. 전체 총계 

 

여기에서 성별 총계가 빠진 이유는

ROLLUP을 사용하여 집계할 때

 N+1개의 레벨까지 조회되므로

레벨이 총 3개까지만 집계가능하기 때문에 빠졌다.

 

만약 성별총계가 필요하다면, 

(region, gender)이 아니라

( gender, region)으로 변경하면 된다.

하지만 이렇게 했을 때 지역별 총계가 누락된다.

 

바로 이런 누락 문제를 해결하기 위해 CUBE를 활용할 수 있다.

---

 

CUBE(expr1, expr2, ...)

 

CUBE는 ROLLUP과 유사한 부분도 있지만 약간 다르다.

ROLLUP이 레벨별로 순차적 집계를 했다면,

CUBE는 명시한 표현식 개수에 따라 모든 조합별로 

집계한 결과를 반환한다.

 

따라서 ROLLUP을 사용했을 때와 같이 

누락되는 경우의 수가 발생하지 않고 

결합 가능한 모든 조합별로 집계 결과를 추출할 수 있다.

 

CUBE는 내부적으로 대상 컬럼의 순서를 변경하여

또 한 번의 쿼리를 수행한다.단점이 있다면,

연산이 많아 시스템에 부담을 줄 수도 있다.

 

활용 구문은 다음과 같다.

SELECT 컬럼명1, ... , 집계함수
FROM 테이블명
[WHERE ... ]
GROUP BY [컬럼명1, ...] CUBE (그룹화할 열)
[HAVING ...]
[ORDER BY ... ]

 

소계 집계 대상이 되는 컬럼을 CUBE 키워드 뒤에 기재하고,

소계 집계 대상이 아닌 경우에는 GROUP BY 키워드 뒤에 기재한다.

 

SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 CUBE 키워드

뒤에 기재되어야 한다. 또한 ORDER BY 구문을 활용해 계층 내 

정렬을 할 수 있다.

 

 

CUBE는 2(expr의 수)의 제곱 만큼 종류별로 집계된다.

예를 들어 expr의 수가 3이면, 

집계 결과의 유형은 총 8이 된다.( 2^3 = 8 )

 

예시를 통해 살펴보자.

// 지역과 성별에 따라 고객 수와 총 매출을 계산
SELECT region, gender, COUNT(*) as customer_count, SUM(sales) as total_sales
FROM customers
GROUP BY CUBE (region, gender);

 

ROLLUP과 같은 배경을 가정하고, CUBE를 사용하여 

지역과 성별에 따라 고객 수와 총 매출을 계산해보겠다.

 

결과의 튜플 수만 보았을 때, 

ROLLUP과 달리 조금 더 많은 데이터가 추출되었다. 

바로 ROLLUP의 예시에서는 누락되었던

성별총계에 대한 데이터까지 추출되었기 때문이다.

 

CUBE(region, gender)에 근거하여 

명시한 표현식의 개수가 총 2개이므로

2^2= 4 에 근거하여

총 4개의 그룹 수에 대한 그룹의 데이터가 모두 출력되었다.

4개는 명시한 표현식을 근거로 산출될 수 있는 

모든 경우의 수에 해당한다. 

 

1. 전체 총계 

2. 성별 총계 

3. 지역별 총계 

4. 지역과 성별의 모든 조합에 대한 총계 

 

ROLLUP을 사용하여 추출된 결과와

CUBE를 사용하여 추출된 결과를 비교했을 때

한 가지 또 다른 차이가 있다면

바로 추출 순서인데, ROLLUP의 경우는

레벨에 따라 추출된다는 명확한 룰이 있지만,

CUBE의 경우는 조금 더 복잡한 상황이다.

 

이렇게 순서가 다른 이유는

각각의 연산이 그룹화된 데이터를 

처리하는 방식이 다르기 때문이다. 

 

CUBE의 결과는 모든 가능한 그룹화 수준에 대한 집계를 포함하므로,

그룹의 순서가 ROLLUP과 다를 수 있다는 점만 확인하고 넘어가자!

 

마지막으로 여러 상황에 대해 어떻게 집계되는지 간단한 표로 정리해보았다.

이 표를 참고하면 ROLLUP과 CUBE에 대해 어느 정도 개념이 잡힐 것이다..(부디!)

ROLLUP (표현식)	집계 종류
ROLLUP (expr1, expr2)	expr1 + expr2
	expr1
	전체
GROUP BY expr1, ROLLUP (expr2, expr3)	expr1 + (expr2 + expr3)
	expr1 + (expr2)
	expr1
GROUP BY ROLLUP (expr1), expr2	expr2 + expr1
	expr2

 

CUBE (표현식)	집계 종류
CUBE (expr1, expr2)	expr1 + expr2
	expr1
	expr2
	전체
GROUP BY expr1, CUBD (expr2, expr3)	expr1 + (expr2 + expr3)
	expr1 + (expr2)
	expr1 + (expr3)
	expr1

 

 

+ GROUPING SETS에 대해서도 관심이 있다면, 아래 글을 참고해주세요!

 

2024.02.18 - [DB/SQL - Oracle] - [SQL] 그룹함수 (2) GROUPING SETS(특정 항목에 대한 소계 출력)

[SQL] 그룹 함수 (2) GROUPING SETS(특정 항목에 대한 소계 출력)