SQL 그룹함수 예제 - WITH ROLLUP, WITH CUBE, GROUPING SETS, GROUPING

저번 포스팅에 이어, SQL에서 그룹화를 할 때 많이 사용하는 그룹함수에 대한 포스팅이다.

그룹함수는 집계함수만큼 자주 사용하지는 않지만, 알아두고 있으면 총계나 소계를 구할 때 아주 유용한 아이들이다.

이러한 그룹함수들은 [GROUP BY 항목1, 항목2,... ] 이런 구문을 사용할 때 함께 사용하며,

GROUP BY 뒤에 들어오는 항목들을 그룹으로 묶는다. 

처음 접하게 되면 생소할 수 있으니 포스팅을 통해 차근차근 설명하려고 한다 :)

 

✔Table of Contents

 

그룹함수 4가지는 다음의 기능을 가지고 있다.

 

• WITH ROLLUP: 오른쪽 → 왼쪽 순으로 그룹을 묶는다. 총계, 소계를 구할 때 활용
• WITH CUBE : 모든 경우의 수에 대한 그룹을 묶는다. 총계, 소계를 구할 때 활용
• GROUPING SETS : 항목들을 개별 그룹으로 묶는다. 소계를 구할 때 활용
• GROUPING : WITH ROLLUP, WITH CUBE와 함께 사용되며 그룹화된 경우 0, 아니면 1을 반환한다.

보통 WITH ROLLUP, WITH CUBE를 많이 사용하게 된다.

엇, 그런데 두 함수 모두 총계와 소계를 뽑는다면 무슨 차이가 있는 걸까?

→ WITH CUBE가 WITH ROLLUP을 포함하는 개념으로 보면 된다. CUBE는 모든 경우의 수에 대한 총계, 소계를 산출하기 때문이다.

 

바로 예제로 알아보자!

 

---

GROUP BY만 사용하는 경우

오늘의 예제는 바로, 연도별/채널코드별 주문금액(sales_amt)의 합계를 구하는 것이다.

우리가 배운 대로, GROUP BY만을 사용해서 쿼리를 짜보면 다음과 같다.

SELECT YEAR(order_date) AS year1,
		channel_code AS ch_code,
		SUM(sales_amt) AS tot_amt
FROM [ORDER]
GROUP BY YEAR(order_date), channel_code
ORDER BY 1 DESC, 2 ASC

 

이렇게 GROUP BY만 사용하더라도 우리가 원하는 집계값을 뽑아낼 수 있다.

하지만 가끔 분석을 하다 보면 이런 값뿐만 아니라, 

• 연도별 tot_amt 값은 얼마일까?
• 채널코드별 tot_amt 값은 얼마일까?
• 전체 tot_amt 값은 얼마일까?

이런 것들이 궁금할 때가 있다. 엑셀을 이용해서 합계를 구해도 되지만, SQL 상에서 바로 이런 총계와 소계까지 뽑을 수 있을까?

 

당연히 있다! 이제 총계/소계를 뽑아보자.

 

---

1) WITH ROLLUP: 오른쪽 → 왼쪽 항목 순서대로 그룹을 묶는다

SELECT YEAR(order_date) AS year1,
		channel_code AS ch_code,
		SUM(sales_amt) AS tot_amt
FROM [ORDER]
GROUP BY YEAR(order_date), channel_code
WITH ROLLUP
ORDER BY 1 DESC, 2 ASC

 

위의 쿼리처럼 WITH ROLLUP을 사용하려면 GROUP BY 바로 밑에 WITH ROLLUP을 써주면 된다.

WITH ROLLUP은 오른쪽에서 왼쪽 항목으로 그룹을 묶는다.

즉, 채널코드 → 연도 순으로 그룹을 묶는다는 뜻이다.

 

이것이 잘 이해가 되지 않는 분들을 위해 다시 설명해보자면,

먼저 채널코드로 그룹을 묶는다. - 그럼 채널코드가 1, 2, 3, 4인 것과 전체 채널코드(NULL로 표시)에 대한 tot_amt 값이 뽑힐 것이다.

그리고 나서 연도로 그룹을 묶는것이다. - 그럼 채널코드가 1인 것은 2020년과 2021년 / 채널코드가 2일 때 2020년과 2021년 tot_amt 값 / ... 이런 식으로 채널코드가 3, 4, 전체일 때 값까지 구할 수 있다.

 

▶ 이렇게 하여, 소계로는 연도별 tot_amt 값(1, 6행)과 총계(11행)을 뽑았다!

 

이 순서를 바꿀 수도 있다! 이번엔 연도 → 채널코드 순으로 그룹화해볼까?

SELECT channel_code AS ch_code,
		YEAR(order_date) AS year1,
		SUM(sales_amt) AS tot_amt
FROM [ORDER]
GROUP BY channel_code, YEAR(order_date)
WITH ROLLUP
ORDER BY 1 ASC, 2 DESC

 

이번엔 반대로 연도 → 채널코드 순으로 그룹화가 완료되었다. 아까와 그룹화 결과가 달라졌음을 확인할 수 있다.

---

2) WITH CUBE: 모든 경우의 수에 대해 그룹을 묶는다

앞에서 WITH ROLLUP으로 연도별/채널코드별 tot_amt 값을 알아보았다.

하지만 우리가 알지 못한 부분을 눈치챘는가?

바로 연도별 tot_amt 값은 아는데, 채널코드별 tot_amt 값은 뽑히지 않았다.

 

WITH CUBE는 이런 경우, 연도별 tot_amt와 채널코드별 tot_amt 값, 그리고 전체 tot_amt 값 모두를 뽑는 그룹함수이다.

아까 쿼리에서 WITH ROLLUP을 WITH CUBE로 바꿔보자.

 

SELECT YEAR(order_date) AS year1,
		channel_code AS ch_code,
		SUM(sales_amt) AS tot_amt
FROM [ORDER]
GROUP BY YEAR(order_date), channel_code
WITH CUBE 
ORDER BY 1 DESC, 2 ASC

▶ 연도별 tot_amt(1, 6행) 뿐만 아니라, 채널코드별 tot_amt(12행~15행) 그리고 전체 tot_amt(11행) 모든 값이 뽑혔다.

이렇게 모든 경우의 총계/소계를 알고 싶은 경우는 WITH CUBE를 사용하자.

---

3) GROUPING SETS: GROUP BY 항목들의 "소계"만 뽑는다

GROUPING SETS는 앞의 두 함수가 소계/총계 둘 다 뽑았던 것과 달리, "소계"만 뽑고 싶은 경우 사용한다.

GROUPING SETS는 사용법이 독특한데, GROUP BY 절에 써주어야 한다.

 

앞의 예제에서, 연도별 tot_amt와 채널코드별 tot_amt 만 뽑고싶다고 해보자.

 

SELECT YEAR(order_date) AS year1,
		channel_code AS ch_code,
		SUM(sales_amt) AS tot_amt
FROM [ORDER]
GROUP BY GROUPING SETS(YEAR(order_date), channel_code)

▶ 이처럼 연도별 tot_amt와 채널코드별 tot_amt 값만 뽑힌다.

 

지금까지 배운 내용을 표로 정리해보면 다음과 같다.

함수	개별 GROUP BY 결과	소계	총계
WITH ROLLUP	O	O	O
WITH CUBE	O	O (모든 경우의 수)	O
GROUPING SETS	X	O	X

---

4) GROUPING: 그룹화된 경우 0, 아니면 1을 반환하는 함수

GROUPING 이라는 함수는 혼자서는 잘 사용되지 않고, WITH ROLLUP 또는 WITH CUBE와 함께 사용된다.

그룹화가 되면 0, 아니면 1을 반환하는데, 즉 앞서 봤던 'NULL' 부분에만 1이 나온다고 생각하면 쉽다.

 

-- WITH ROLLUP에 GROUPING 사용
SELECT YEAR(order_date) AS year1,
		GROUPING(YEAR(order_date)) AS year1_group,
		channel_code AS ch_code,
		GROUPING(channel_code) AS chcode_group,
		SUM(sales_amt) AS tot_amt
FROM [ORDER]
GROUP BY YEAR(order_date), channel_code
WITH ROLLUP
ORDER BY 1 DESC, 2 ASC

이렇게 year1과 ch_code에 GROUPING을 적용한 결과, 

NULL 옆에 1, 나머지는 0이 나왔다.

GROUP BY가 적용된 부분은 0, 총계/소계 부분(NULL)은 1이 나오게 된다.

 

그렇다면 GROUPING은 또 어떤 경우에 사용될까?

위의 결과로 뽑힌 표를 보면, 이를 그대로 보고서에 사용하기엔 좀 껄끄럽다. NULL이 들어있기 때문에 뭔가 에러가 난 것 같아 보이기도 한다.

바로 이럴 때! GROUPING은 CASE WHEN 절과 함께 'NULL' 이라는 이름을 바꿔줄 수 있다.

 

GROUPING(year1) 값이 1이면 '총계' 로,

GROUPING(ch_code) 값이 1이면 '소계'로 나오도록 위 표를 바꿔보자. 여기엔 이전에 배웠던 서브쿼리를 사용하면 된다.

 

SELECT CASE WHEN GROUPING(year1) = 1 THEN '총계'
		ELSE year1 END AS 연도_총계,
		CASE WHEN GROUPING(ch_code) = 1 THEN '소계'
		ELSE ch_code END AS 채널코드_총계,
		SUM(sales_amt) AS tot_amt
FROM (SELECT YEAR(order_date) AS year1,
				channel_code AS ch_code,
				sales_amt 
		FROM [ORDER]) A
GROUP BY year1, ch_code
WITH ROLLUP

 

위와 같이 쿼리를 짜보았다.

Q. 엇!! 근데 왜 GROUPING(year1)으로 썼나요? GROUPING(YEAR(order_date)) 이렇게 해야 하는 것 아닌가요??
→ FROM 절에 서브쿼리를 썼기 때문에 가능하다.
FROM절 서브쿼리 상에서 이미 YEAR(order_date)가 'year1' 이라는 열, channel_code가 'ch_code'로 열 이름이 바뀌었기 때문이다.

 

아무튼 위와 같이 쿼리를 실행하면 에러가 난다.

그 이유는 NULL값을 '총계' , '소계' 로 이름바꾸려면 문자형이어야 하는데, 지금 연도와 채널코드가 모두 숫자이기 때문.

이전 포스팅에서 배운 CAST 함수를 사용해 숫자형 → 문자형으로 바꾼다.

 

--CAST 사용하여 문자형으로 바꿔준 경우
SELECT CASE WHEN GROUPING(year1) = 1 THEN '총계'
		ELSE year1 END AS 연도_총계,
		CASE WHEN GROUPING(ch_code) = 1 THEN '소계'
		ELSE ch_code END AS 채널코드_총계,
		SUM(sales_amt) AS tot_amt
FROM (SELECT CAST(YEAR(order_date) AS VARCHAR) AS year1,
		CAST(channel_code AS VARCHAR) AS ch_code,
		sales_amt 
		FROM [ORDER]) A
GROUP BY year1, ch_code
WITH ROLLUP

위처럼 결과가 잘 뽑힌다.

근데 '채널코드_총계' 열의 맨 마지막 11번째 행은 '소계'가 아니라 '총계'로 나와야 한다. 

GROUPING(ch_code) = 1이면 '소계'로 쓰는 것에 하나 조건을 더 추가해서 GROUPING(year1) = 1이면 '총계'로 나오도록 하자.

 

-- 하지만 문제. 채널코드_총계의 맨 마지막 행은 '총계'로 나와야 한다
SELECT CASE WHEN GROUPING(year1) = 1 THEN '총계'
		ELSE year1 END AS 연도_총계,
		CASE WHEN GROUPING(year1) = 1 THEN '총계' -- 이게 더 큰단위이므로 먼저 써준다
		WHEN GROUPING(ch_code) = 1 THEN '소계'
		ELSE ch_code END AS 채널코드_총계,
		SUM(sales_amt) AS tot_amt
FROM (SELECT CAST(YEAR(order_date) AS VARCHAR) AS year1,
		CAST(channel_code AS VARCHAR) AS ch_code,
		sales_amt 
		FROM [ORDER]) A
GROUP BY year1, ch_code
WITH ROLLUP

원하는 결과가 나왔다.

 

참고로, 코드는 여기서 다운받을 수 있습니다 :)

GitHub - suy379/SQL-basic