MySQL WITH ROLLUP

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

GROUP BY는 그룹당 1개의 행을 주고 전체에 대한 행은 주지 않습니다. WITH ROLLUP은 그 행을 추가하고, 여러 열로 그룹화할 때는 그 사이의 소계도 추가합니다. 두 번째 쿼리를 실행하고 숫자를 직접 더하는 수고를 덜어 줍니다. 시작하기 전에 mysql -u root -p sakila로 클라이언트를 여세요.

WITH ROLLUP의 동작 방식

SELECT GROUPING_COLUMN, AGGREGATE_FUNCTION(COLUMN_NAME)
FROM TABLE_NAME
GROUP BY GROUPING_COLUMN WITH ROLLUP;

GROUPING_COLUMN을 그룹을 정의하는 열로, TABLE_NAME을 테이블로, AGGREGATE_FUNCTION(COLUMN_NAME)을 그룹마다 계산할 것으로 바꾸세요. WITH ROLLUP은 GROUP BY 절의 끝에 옵니다. 서버는 일반 그룹을 반환한 다음, 그룹화 열 하나를 제외하고 그 집계가 전체를 포괄하는 추가 행을 반환합니다. 제외한 열에는 NULL을 넣는데, 그것으로 그 행들을 알아봅니다.

예제

총계 행 추가하기

SELECT rating, COUNT(*) AS films
FROM film
GROUP BY rating WITH ROLLUP;

+--------+-------+
| rating | films |
+--------+-------+
| G      |   178 |
| PG     |   194 |
| PG-13  |   223 |
| R      |   195 |
| NC-17  |   210 |
| NULL   |  1000 |
+--------+-------+
6 rows in set (0.00 sec)

5개가 아니라 6개의 행입니다. 마지막 행은 등급이 있을 자리에 NULL이 있고, 그 개수는 테이블 전체입니다.

소계도 함께

2개의 열로 그룹화하면 첫 번째 열의 값마다 소계를 얻고, 그다음 총합계를 얻습니다.

SELECT rating, rental_rate, COUNT(*) AS films
FROM film
WHERE length = 185
GROUP BY rating, rental_rate WITH ROLLUP;

+--------+-------------+-------+
| rating | rental_rate | films |
+--------+-------------+-------+
| G      |        2.99 |     1 |
| G      |        4.99 |     2 |
| G      |        NULL |     3 |
| PG     |        2.99 |     1 |
| PG     |        NULL |     1 |
| PG-13  |        2.99 |     2 |
| PG-13  |        4.99 |     1 |
| PG-13  |        NULL |     3 |
| R      |        2.99 |     1 |
| R      |        4.99 |     2 |
| R      |        NULL |     3 |
| NULL   |        NULL |    10 |
+--------+-------------+-------+
12 rows in set (0.00 sec)

어떤 열이 NULL인지로 행을 읽으세요. G / NULL / 3은 모든 요금에 걸친 G의 소계입니다. NULL / NULL / 10은 총합계이고, 185분인 10편의 영화와 일치합니다. 롤업 행은 맨 아래에 모이지 않고 자신이 요약하는 그룹 바로 뒤에 오므로, 결과가 위에서 아래로 보고서처럼 읽힙니다.

롤업 NULL과 실제 NULL 구별하기

롤업 행의 NULL은 “이 열의 모든 값”을 뜻합니다. 데이터의 NULL은 “기록된 값 없음”을 뜻합니다. 둘은 똑같이 출력되므로, NULL이 될 수 있는 열에서는 보기만 해서는 둘을 구별할 수 없습니다. GROUPING()이 그 질문에 직접 답합니다. 그 행에서 열이 제외되었으면 1을, 아니면 0을 반환합니다.

SELECT rating, GROUPING(rating) AS is_total, COUNT(*) AS films
FROM film
GROUP BY rating WITH ROLLUP;

+--------+----------+-------+
| rating | is_total | films |
+--------+----------+-------+
| G      |        0 |   178 |
| PG     |        0 |   194 |
| PG-13  |        0 |   223 |
| R      |        0 |   195 |
| NC-17  |        0 |   210 |
| NULL   |        1 |  1000 |
+--------+----------+-------+
6 rows in set (0.00 sec)

Sakila에서 rating은 절대 NULL이 아니므로, 여기서는 2개의 질문이 같은 답을 가집니다. NULL을 실제로 담는 열에서는 rating IS NULL 검사가 두 종류의 행을 모두 잡고, GROUPING(rating) = 1은 롤업만 잡습니다.

ORDER BY는 총계를 옮기고 정렬을 바꿉니다

위의 예제에는 ORDER BY가 없는데, 의도한 것입니다: WITH ROLLUP은 각 롤업 행을 그것이 요약하는 그룹 바로 아래에 두고, 정렬은 그 배치를 버립니다.

SELECT rating, COUNT(*) AS films
FROM film
GROUP BY rating WITH ROLLUP
ORDER BY rating;

+--------+-------+
| rating | films |
+--------+-------+
| NULL   |  1000 |
| G      |   178 |
| NC-17  |   210 |
| PG     |   194 |
| PG-13  |   223 |
| R      |   195 |
+--------+-------+
6 rows in set (0.00 sec)

2가지가 움직였습니다. 총합계가 맨 위로 정렬되었는데, NULL이 모든 실제 값보다 앞에 정렬되기 때문입니다. 그리고 등급이 GROUP BY가 보여준 ENUM 선언 순서가 아니라 알파벳순으로 나왔는데, 롤업 결과를 정렬하면 텍스트로 정렬하기 때문입니다. 그러니 어느 쪽을 원하는지 정하세요. 소계가 그룹 아래에 놓인 보고서로 읽히길 원하면 ORDER BY를 빼고, 아니면 추가하고 롤업 행이 정렬이 놓는 곳에 간다는 것을 받아들이세요.

롤업 행에 레이블 붙이기

NULL로 된 열은 보고서에서 원하는 것이 아닙니다. COALESCE는 NULL이 아닌 첫 번째 인수를 반환하므로, 그 자리에 대신 단어를 넣습니다.

SELECT COALESCE(rating, 'All ratings') AS label, COUNT(*) AS films
FROM film
GROUP BY rating WITH ROLLUP;

+-------------+-------+
| label       | films |
+-------------+-------+
| G           |   178 |
| PG          |   194 |
| PG-13       |   223 |
| R           |   195 |
| NC-17       |   210 |
| All ratings |  1000 |
+-------------+-------+
6 rows in set (0.00 sec)

읽기 좋지만, 여기서 안전한 것은 데이터에서 rating이 절대 NULL이 아니기 때문입니다. NULL이 될 수 있는 열에서는 COALESCE가 실제 빈 값에 총계 레이블을 붙이므로, 대신 검사에 GROUPING()을 쓰세요.

요약

소계와 총계 행을 얻으려면 GROUP BY 끝에 WITH ROLLUP을 추가하세요.
그룹화 열의 NULL은 “이 열의 모든 값”으로 읽으세요.
여러 열로 그룹화하면 앞선 각 열의 값마다 소계가 나온다고 예상하세요.
롤업 행과 데이터가 NULL인 행을 구별하려면 GROUPING(column)을 쓰세요.
ORDER BY는 롤업 행을 옮기고 ENUM을 텍스트로 정렬한다고 예상하세요.

함께 보기

GROUP BY — WITH ROLLUP이 총계를 추가하는 그룹
집계 함수 — 합산되는 것
IS NULL — 그 열의 NULL이 뜻할 수 있는 다른 것
MySQL GROUP BY 성능 — 대규모에서 그룹화의 비용

MySQL Tutorial

가이드

데이터 저장

쿼리 작성

데이터 보안

성능 최적화

AI로 구축

MySQL 운영

애플리케이션 연결

확장 작성