데브러너 (DevRunner)

14.19.2 GROUP BY Modifiers

GROUP BY 절은 WITH ROLLUP 수정자를 허용하는데, 이 수정자는 요약 결과에 더 높은 수준(즉, super-aggregate) 요약 연산을 나타내는 추가 행을 포함시킵니다. 따라서 ROLLUP을 사용하면 하나의 쿼리로 여러 수준의 분석 질문에 답할 수 있습니다. 예를 들어, ROLLUP은 OLAP(Online Analytical Processing) 연산을 지원하는 데 사용될 수 있습니다.

sales 테이블이 판매 수익성을 기록하기 위해 year, country, product, profit 컬럼을 가진다고 가정해 보겠습니다:

1CREATE TABLE sales
2(
3    year    INT,
4    country VARCHAR(20),
5    product VARCHAR(32),
6    profit  INT
7);

테이블 내용을 연도별로 요약하려면 다음과 같이 간단한 GROUP BY를 사용합니다:

1mysql> SELECT year, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year;
4+------+--------+
5| year | profit |
6+------+--------+
7| 2000 |   4525 |
8| 2001 |   3010 |
9+------+--------+

출력은 연도별 총(집계) profit을 보여 줍니다. 모든 연도에 걸친 전체 profit 합계를 구하려면 개별 값을 직접 더하거나 쿼리를 한 번 더 실행해야 합니다. 혹은 ROLLUP을 사용해서 하나의 쿼리로 두 수준의 분석 결과를 모두 얻을 수 있습니다. GROUP BY 절에 WITH ROLLUP 수정자를 추가하면, 모든 year 값에 대한 총합을 보여 주는 또 하나의 (super-aggregate) 행이 생성됩니다:

1mysql> SELECT year, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year WITH ROLLUP;
4+------+--------+
5| year | profit |
6+------+--------+
7| 2000 |   4525 |
8| 2001 |   3010 |
9| NULL |   7535 |
10+------+--------+

year 컬럼의 NULL 값은 전체 합계를 나타내는 super-aggregate 행임을 식별해 줍니다.

MySQL은 이 수정자에 대해 Section 15.2.13, “SELECT Statement”에 설명된 것처럼 추가적인 대체 구문도 지원합니다. 이 대체 구문을 사용하면, 앞선 쿼리를 다음과 같이 수행할 수 있습니다:

1mysql> SELECT year, SUM(profit) AS profit
2       FROM sales
3       GROUP BY ROLLUP (year);
4+------+--------+
5| year | profit |
6+------+--------+
7| 2000 |   4525 |
8| 2001 |   3010 |
9| NULL |   7535 |
10+------+--------+

ROLLUP은 여러 개의 GROUP BY 컬럼이 있을 때 더 복잡한 효과를 냅니다. 이 경우 마지막 그룹화 컬럼을 제외한 어느 컬럼에서든 값이 변경될 때마다, 쿼리는 추가적인 super-aggregate 요약 행을 생성합니다.

예를 들어, ROLLUP 없이 year, country, product를 기준으로 sales 테이블을 요약하면, 출력은 year/country/product 수준에서만 요약 값을 보여 주며 다음과 같을 수 있습니다:

1mysql> SELECT year, country, product, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year, country, product;
4+------+---------+------------+--------+
5| year | country | product    | profit |
6+------+---------+------------+--------+
7| 2000 | Finland | Computer   |   1500 |
8| 2000 | Finland | Phone      |    100 |
9| 2000 | India   | Calculator |    150 |
10| 2000 | India   | Computer   |   1200 |
11| 2000 | USA     | Calculator |     75 |
12| 2000 | USA     | Computer   |   1500 |
13| 2001 | Finland | Phone      |     10 |
14| 2001 | USA     | Calculator |     50 |
15| 2001 | USA     | Computer   |   2700 |
16| 2001 | USA     | TV         |    250 |
17+------+---------+------------+--------+

여기에 ROLLUP을 추가하면, 쿼리는 여러 개의 추가 행을 생성합니다:

1mysql> SELECT year, country, product, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year, country, product WITH ROLLUP;
4+------+---------+------------+--------+
5| year | country | product    | profit |
6+------+---------+------------+--------+
7| 2000 | Finland | Computer   |   1500 |
8| 2000 | Finland | Phone      |    100 |
9| 2000 | Finland | NULL       |   1600 |
10| 2000 | India   | Calculator |    150 |
11| 2000 | India   | Computer   |   1200 |
12| 2000 | India   | NULL       |   1350 |
13| 2000 | USA     | Calculator |     75 |
14| 2000 | USA     | Computer   |   1500 |
15| 2000 | USA     | NULL       |   1575 |
16| 2000 | NULL    | NULL       |   4525 |
17| 2001 | Finland | Phone      |     10 |
18| 2001 | Finland | NULL       |     10 |
19| 2001 | USA     | Calculator |     50 |
20| 2001 | USA     | Computer   |   2700 |
21| 2001 | USA     | TV         |    250 |
22| 2001 | USA     | NULL       |   3000 |
23| 2001 | NULL    | NULL       |   3010 |
24| NULL | NULL    | NULL       |   7535 |
25+------+---------+------------+--------+

이제 출력에는 하나의 수준뿐 아니라 네 가지 수준의 분석 정보가 포함됩니다:

주어진 year와 country에 대한 각 product 행 집합 뒤에는, 모든 product의 합계를 보여 주는 추가 super-aggregate 요약 행이 나타납니다. 이 행에서는 product 컬럼이 NULL로 설정됩니다.
각 year에 대한 행 집합 뒤에는, 모든 country와 product의 합계를 보여 주는 추가 super-aggregate 요약 행이 나타납니다. 이 행에서는 country와 products 컬럼이 NULL로 설정됩니다.
마지막으로, 다른 모든 행 뒤에는 모든 year, country, product에 대한 전체 합계를 보여 주는 추가 super-aggregate 요약 행이 나타납니다. 이 행에서는 year, country, products 컬럼이 모두 NULL로 설정됩니다.

각 super-aggregate 행의 NULL indicator는 행이 클라이언트로 전송될 때 생성됩니다. 서버는 GROUP BY 절에서 값이 변경된 가장 왼쪽 컬럼 뒤에 오는 컬럼들을 살펴봅니다. 결과 집합에서 이름이 그 컬럼 이름들과 일치하는 모든 컬럼에 대해, 해당 컬럼의 값을 NULL로 설정합니다. (컬럼 위치로 그룹화 컬럼을 지정한 경우, 서버는 위치를 기준으로 어떤 컬럼을 NULL로 설정할지 식별합니다.)

super-aggregate 행의 NULL 값은 쿼리 처리의 매우 늦은 단계에서 결과 집합에 삽입되기 때문에, select 목록이나 HAVING 절에서만 이 값들을 NULL로 테스트할 수 있습니다. 조인 조건이나 WHERE 절에서 이 값들을 NULL로 테스트하여 어떤 행을 선택할지 결정할 수는 없습니다. 예를 들어, 출력에서 super-aggregate 행을 제외한 모든 행을 제거하려고 쿼리에 WHERE product IS NULL을 추가할 수는 없습니다.

NULL 값은 클라이언트 측에서 NULL로 나타나며, 어떤 MySQL 클라이언트 프로그래밍 인터페이스를 사용하든 그렇게 테스트할 수 있습니다. 하지만 이 시점에서는 NULL이 일반 그룹화 값인지 super-aggregate 값인지 구분할 수 없습니다. 이 구분을 테스트하려면, 아래에서 설명하는 GROUPING() 함수를 사용합니다.

GROUP BY ... WITH ROLLUP 쿼리의 경우, 결과에 있는 NULL 값이 super-aggregate 값을 나타내는지 테스트하기 위해 select 목록, HAVING 절, ORDER BY 절에서 사용할 수 있는 GROUPING() 함수가 제공됩니다. 예를 들어, GROUPING(year)은 year 컬럼에 있는 NULL이 super-aggregate 행에서 발생한 경우 1을, 그렇지 않은 경우 0을 반환합니다. 마찬가지로, GROUPING(country)와 GROUPING(product)은 각각 country와 product 컬럼의 super-aggregate NULL 값에 대해 1을 반환합니다:

1mysql> SELECT
2         year, country, product, SUM(profit) AS profit,
3         GROUPING(year) AS grp_year,
4         GROUPING(country) AS grp_country,
5         GROUPING(product) AS grp_product
6       FROM sales
7       GROUP BY year, country, product WITH ROLLUP;
8+------+---------+------------+--------+----------+-------------+-------------+
9| year | country | product    | profit | grp_year | grp_country | grp_product |
10+------+---------+------------+--------+----------+-------------+-------------+
11| 2000 | Finland | Computer   |   1500 |        0 |           0 |           0 |
12| 2000 | Finland | Phone      |    100 |        0 |           0 |           0 |
13| 2000 | Finland | NULL       |   1600 |        0 |           0 |           1 |
14| 2000 | India   | Calculator |    150 |        0 |           0 |           0 |
15| 2000 | India   | Computer   |   1200 |        0 |           0 |           0 |
16| 2000 | India   | NULL       |   1350 |        0 |           0 |           1 |
17| 2000 | USA     | Calculator |     75 |        0 |           0 |           0 |
18| 2000 | USA     | Computer   |   1500 |        0 |           0 |           0 |
19| 2000 | USA     | NULL       |   1575 |        0 |           0 |           1 |
20| 2000 | NULL    | NULL       |   4525 |        0 |           1 |           1 |
21| 2001 | Finland | Phone      |     10 |        0 |           0 |           0 |
22| 2001 | Finland | NULL       |     10 |        0 |           0 |           1 |
23| 2001 | USA     | Calculator |     50 |        0 |           0 |           0 |
24| 2001 | USA     | Computer   |   2700 |        0 |           0 |           0 |
25| 2001 | USA     | TV         |    250 |        0 |           0 |           0 |
26| 2001 | USA     | NULL       |   3000 |        0 |           0 |           1 |
27| 2001 | NULL    | NULL       |   3010 |        0 |           1 |           1 |
28| NULL | NULL    | NULL       |   7535 |        1 |           1 |           1 |
29+------+---------+------------+--------+----------+-------------+-------------+

앞에서 언급한 대체 구문을 사용하면, 이 쿼리를 다음과 같이 다시 쓸 수 있습니다:

1SELECT
2    year, country, product, SUM(profit) AS profit,
3    GROUPING(year) AS grp_year,
4    GROUPING(country) AS grp_country,
5    GROUPING(product) AS grp_product
6FROM sales
7GROUP BY ROLLUP (year, country, product);

GROUPING() 결과를 직접 표시하는 대신, 이 함수를 사용하여 super-aggregate NULL 값에 레이블을 대체할 수 있습니다:

1mysql> SELECT
2         IF(GROUPING(year), 'All years', year) AS year,
3         IF(GROUPING(country), 'All countries', country) AS country,
4         IF(GROUPING(product), 'All products', product) AS product,
5         SUM(profit) AS profit
6       FROM sales
7       GROUP BY year, country, product WITH ROLLUP;
8+-----------+---------------+--------------+--------+
9| year      | country       | product      | profit |
10+-----------+---------------+--------------+--------+
11| 2000      | Finland       | Computer     |   1500 |
12| 2000      | Finland       | Phone        |    100 |
13| 2000      | Finland       | All products |   1600 |
14| 2000      | India         | Calculator   |    150 |
15| 2000      | India         | Computer     |   1200 |
16| 2000      | India         | All products |   1350 |
17| 2000      | USA           | Calculator   |     75 |
18| 2000      | USA           | Computer     |   1500 |
19| 2000      | USA           | All products |   1575 |
20| 2000      | All countries | All products |   4525 |
21| 2001      | Finland       | Phone        |     10 |
22| 2001      | Finland       | All products |     10 |
23| 2001      | USA           | Calculator   |     50 |
24| 2001      | USA           | Computer     |   2700 |
25| 2001      | USA           | TV           |    250 |
26| 2001      | USA           | All products |   3000 |
27| 2001      | All countries | All products |   3010 |
28| All years | All countries | All products |   7535 |
29+-----------+---------------+--------------+--------+

여러 expression 인수를 사용할 경우, GROUPING()은 각 expression에 대한 결과를 결합한 비트마스크를 반환합니다. 여기서 최하위 비트는 가장 오른쪽 expression의 결과에 해당합니다. 예를 들어, GROUPING(year, country, product)는 다음과 같이 계산됩니다:

1  result for GROUPING(product)
2+ result for GROUPING(country) << 1
3+ result for GROUPING(year) << 2

이러한 GROUPING()의 결과는, expression 중 어느 하나라도 super-aggregate NULL을 나타내면 0이 아닌 값을 가지므로, 이를 이용해 super-aggregate 행만 반환하고 일반 그룹화 행은 필터링할 수 있습니다:

1mysql> SELECT year, country, product, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year, country, product WITH ROLLUP
4       HAVING GROUPING(year, country, product) <> 0;
5+------+---------+---------+--------+
6| year | country | product | profit |
7+------+---------+---------+--------+
8| 2000 | Finland | NULL    |   1600 |
9| 2000 | India   | NULL    |   1350 |
10| 2000 | USA     | NULL    |   1575 |
11| 2000 | NULL    | NULL    |   4525 |
12| 2001 | Finland | NULL    |     10 |
13| 2001 | USA     | NULL    |   3000 |
14| 2001 | NULL    | NULL    |   3010 |
15| NULL | NULL    | NULL    |   7535 |
16+------+---------+---------+--------+

sales 테이블에는 NULL 값이 없으므로, ROLLUP 결과의 모든 NULL 값은 super-aggregate 값을 나타냅니다. 데이터 세트에 NULL 값이 포함된 경우, ROLLUP 요약 결과에는 super-aggregate 행뿐 아니라 일반 그룹화 행에도 NULL 값이 포함될 수 있습니다. GROUPING()을 사용하면 이를 구분할 수 있습니다. 예를 들어, 테이블 t1이 특정 quantity 값 집합에 대해 두 개의 그룹화 요인을 가진 단순 데이터 세트를 포함하고 있고, 여기에서 NULL은 “기타” 또는 “unknown”과 같은 의미를 나타낸다고 가정해 보겠습니다:

1mysql> SELECT * FROM t1;
2+------+-------+----------+
3| name | size  | quantity |
4+------+-------+----------+
5| ball | small |       10 |
6| ball | large |       20 |
7| ball | NULL  |        5 |
8| hoop | small |       15 |
9| hoop | large |        5 |
10| hoop | NULL  |        3 |
11+------+-------+----------+

간단한 ROLLUP 연산은 다음과 같은 결과를 생성하며, 여기서는 super-aggregate 행의 NULL과 일반 그룹화 행의 NULL을 쉽게 구분하기 어렵습니다:

1mysql> SELECT name, size, SUM(quantity) AS quantity
2       FROM t1
3       GROUP BY name, size WITH ROLLUP;
4+------+-------+----------+
5| name | size  | quantity |
6+------+-------+----------+
7| ball | NULL  |        5 |
8| ball | large |       20 |
9| ball | small |       10 |
10| ball | NULL  |       35 |
11| hoop | NULL  |        3 |
12| hoop | large |        5 |
13| hoop | small |       15 |
14| hoop | NULL  |       23 |
15| NULL | NULL  |       58 |
16+------+-------+----------+

GROUPING()을 사용해 super-aggregate NULL 값에 레이블을 대체하면, 결과를 해석하기가 더 쉬워집니다:

1mysql> SELECT
2         IF(GROUPING(name) = 1, 'All items', name) AS name,
3         IF(GROUPING(size) = 1, 'All sizes', size) AS size,
4         SUM(quantity) AS quantity
5       FROM t1
6       GROUP BY name, size WITH ROLLUP;
7+-----------+-----------+----------+
8| name      | size      | quantity |
9+-----------+-----------+----------+
10| ball      | NULL      |        5 |
11| ball      | large     |       20 |
12| ball      | small     |       10 |
13| ball      | All sizes |       35 |
14| hoop      | NULL      |        3 |
15| hoop      | large     |        5 |
16| hoop      | small     |       15 |
17| hoop      | All sizes |       23 |
18| All items | All sizes |       58 |
19+-----------+-----------+----------+

Other Considerations When using ROLLUP

다음 설명은 MySQL에서 구현된 ROLLUP에 특화된 동작을 나열합니다.

ORDER BY와 ROLLUP은 함께 사용할 수 있으며, 이를 통해 ORDER BY와 GROUPING()을 이용해 그룹화 결과에 대해 특정 정렬 순서를 달성할 수 있습니다. 예를 들어:

1mysql> SELECT year, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year WITH ROLLUP
4       ORDER BY GROUPING(year) DESC;
5+------+--------+
6| year | profit |
7+------+--------+
8| NULL |   7535 |
9| 2000 |   4525 |
10| 2001 |   3010 |
11+------+--------+

두 경우 모두 super-aggregate 요약 행은 계산에 사용된 행들과 함께 정렬되며, 그 위치는 정렬 순서(오름차순일 때는 끝, 내림차순일 때는 시작)에 따라 달라집니다.

LIMIT은 클라이언트로 반환되는 행 수를 제한하는 데 사용할 수 있습니다. LIMIT은 ROLLUP 이후에 적용되므로, ROLLUP이 추가한 행들에도 limit가 적용됩니다. 예를 들어:

1mysql> SELECT year, country, product, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year, country, product WITH ROLLUP
4       LIMIT 5;
5+------+---------+------------+--------+
6| year | country | product    | profit |
7+------+---------+------------+--------+
8| 2000 | Finland | Computer   |   1500 |
9| 2000 | Finland | Phone      |    100 |
10| 2000 | Finland | NULL       |   1600 |
11| 2000 | India   | Calculator |    150 |
12| 2000 | India   | Computer   |   1200 |
13+------+---------+------------+--------+

LIMIT을 ROLLUP과 함께 사용하면, super-aggregate 행을 이해하기 위한 문맥이 줄어들기 때문에 결과를 해석하기가 더 어려워질 수 있습니다.

MySQL 확장 기능에 따라, GROUP BY 목록에 나타나지 않는 컬럼도 select 목록에 이름을 올릴 수 있습니다. (GROUP BY와 비집계 컬럼에 대한 정보는 Section 14.19.3, “MySQL Handling of GROUP BY”를 참조하십시오.) 이 경우, 서버는 요약 행에서 이 비집계 컬럼에 대해 어떤 값을 선택해도 되며, 여기에는 WITH ROLLUP이 추가한 행도 포함됩니다. 예를 들어, 다음 쿼리에서 country는 GROUP BY 목록에 나타나지 않는 비집계 컬럼이고, 이 컬럼에 대해 선택되는 값은 비결정적입니다:

1mysql> SELECT year, country, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year WITH ROLLUP;
4+------+---------+--------+
5| year | country | profit |
6+------+---------+--------+
7| 2000 | India   |   4525 |
8| 2001 | USA     |   3010 |
9| NULL | USA     |   7535 |
10+------+---------+--------+

이 동작은 ONLY_FULL_GROUP_BY SQL 모드가 활성화되지 않았을 때 허용됩니다. 해당 모드가 활성화된 경우, country가 GROUP BY 절에 나열되어 있지 않기 때문에 서버는 이 쿼리를 잘못된 것으로 보고 거부합니다. ONLY_FULL_GROUP_BY가 활성화된 상태에서도, 비결정적 값 컬럼에 대해 ANY_VALUE() 함수를 사용하면 쿼리를 실행할 수 있습니다:

1mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
2       FROM sales
3       GROUP BY year WITH ROLLUP;
4+------+---------+--------+
5| year | country | profit |
6+------+---------+--------+
7| 2000 | India   |   4525 |
8| 2001 | USA     |   3010 |
9| NULL | USA     |   7535 |
10+------+---------+--------+

rollup 컬럼은 WHERE 절에서 호출되는 경우를 제외하고는 MATCH()의 인수로 사용할 수 없으며, 그렇게 사용하면 오류와 함께 거부됩니다. 더 많은 정보는 Section 14.9, “Full-Text Search Functions”를 참조하십시오.