저번 포스팅에 이어, 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
Contribute to suy379/SQL-basic development by creating an account on GitHub.
github.com
'Skillset > SQL' 카테고리의 다른 글
| SQL 집합 연산자 - UNION, UNION ALL, INTERSECT, EXCEPT (0) | 2022.06.03 |
|---|---|
| SQL 윈도우 함수 예제 - 순위함수(RANK), 누적 집계함수 (0) | 2022.06.02 |
| SQL 자주 사용하는 집계함수 예제로 알아보기 (0) | 2022.05.31 |
| 자주 쓰이는 SQL 연산자 & 함수 정리 (0) | 2022.05.29 |
| SQL 서브쿼리(Sub Query) 예제 - select절, from절, where절 (4) | 2022.05.28 |
