[MYSQL] 📚 WITH ROLLUP & Grouping 함수

WITH ROLLUP

RollUp은 그룹 항목 총합(총계)이나, 각 그룹 별 중간합계(소계)가 필요할 경우 사용된다.

 

예를들어 그냥 ​GROUP BY를 사용하면 GROUP BY 뒤에 나오는 컬럼별로 합계를 구해준다.

이때, 아쉬운 점이 있다면 항목별 합계에 전체 합계가 같이 나오게 하는 것이다.

 

이럴 때에 사용하는 것이 WITH ROLLUP 이다.

WITH ROLLUP는 그룹별로 합계를 한번에 구할때 사용한다.

이때 합계값만 추가하는거라, 나머지 필드 이름 자리에는 당연히 NULL값이 들어가게 되는데, 쿼리를 통해서 이름을 명명해줄수 있다.

​

웹 서버단에서 사용할 쿼리에는 사용하지 않고 데이터베이스 내에서 데이터를 조회할때만 사용된다.
서버단에서 동일한 기능을 사용하고 싶다면, GROUP BY로 조회해서 데이터를 처리해야 한다.

---

ROLLUP 예제

 

전체 총계

 

[ store_information 테이블 ]

region_name	store_name	sales
West	Los Angeles	1500
West	San Diego	250
West	Los Angeles	300
East	Boston	700

 

SELECT store_name, SUM(sales)
FROM store_information
GROUP BY store_name WITH ROLLUP

​store_name	SUM(sales)
Boston	700
Los Angeles	1800
San Diego	250
NULL	2750

​* 그룹 총합 : 마지막 열에 합계가 2750이 표시되고 있다.

---

부분 소계

SELECT region_name, store_name, SUM(sales)
FROM store_information
GROUP BY region_name, store_name WITH ​ROLLUP

이번에는 select 필드를 두가지로 했다.

그러면 두번 째 필드 종류에 따라 중간 소계가 나오게 된다.

[ 각 그룹별 중간총합 ]

region_name	store_name	SUM(sales)
East	Boston	700
East	NULL	700
West	Los Angeles	1800
West	San Diego	250
West	NULL	2050
NULL	NULL	2750

2번째 열에 East의 합계가 700이고, 4번째 열에 West의 합계가 2050이 표시되고 있다. 그리고, 5열에 이 모든 합계인 2750이 표시된다.

---

Grouping 함수

만일 null을 허용하는 필드가 있다고 가정하자.

다음과 같이 그럼 실제 데이터가 NULL 이어서 NULL 로 쓰인 것과, 부분 소계를 나타내기 위해 쓰인 NULL 은 어떻게 구분할까?

아래와 같은 테이블에서 region 이 실제 데이터가 NULL 인건지 부분 소계 NULL 인건지 구분하기는 사실 어렵다.

 

최근 Mysql 8버젼에서는 이 둘을 구분할 수 있게 해주는 함수를 지원해 주었다.

바로 GROUPING 이라는 함수다.

GROUPING 함수는 그 인자를 그루핑 기준에서 고려하지 않은 부분 총계인 경우에 1을 리턴하고, 그렇지 않은 경우 0을 리턴한다. 

> select * from table;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
| 1111 | NULL |  112 |
| NULL |  112 | NULL |
+------+------+------+


> SELECT a, b, SUM(c) as SUM, GROUPING(a), GROUPING(b) 
FROM table 
GROUP BY a,b WITH ROLLUP;

+------+------+------+-------------+-------------+
| a    | b    | SUM  | GROUPING(a) | GROUPING(b) |
+------+------+------+-------------+-------------+
| NULL |  112 | NULL |           0 |           0 |
| NULL | NULL | NULL |           0 |           1 | 					-- 필드b 소계1
|  111 |   11 |   11 |           0 |           0 |
|  111 |   12 |   12 |           0 |           0 |
|  111 | NULL |   23 |           0 |           1 | 					-- 필드b 소계2
|  222 |   22 |   22 |           0 |           0 |
|  222 |   23 |   23 |           0 |           0 |
|  222 | NULL |   45 |           0 |           1 | 					-- 필드b 소계3
| 1111 | NULL |  112 |           0 |           0 |
| 1111 | NULL |  112 |           0 |           1 | 					-- 필드b 소계4
| NULL | NULL |  180 |           1 |           1 | -- 필드a 총계
+------+------+------+-------------+-------------+

GROUPING(a)는 a컬럼의 전체 sum 결과를 1로 표시해주며, GROUPING(b)는 각 a컬럼 그룹화한 c를 합한 값을 SUM컬럼에 출력해주고 1로 표시해준다.