MySQL5.6 GROUP BY 수식

12.19.2 GROUP BY 수식

GROUP BY 절은 추가 행이 요약 출력에 추가되는 WITH ROLLUP 수식이 허용됩니다. 이 라인은 높은 수준 (또는 슈퍼 통합) 요약 연산을 나타냅니다. 따라서 ROLLUP 에서 단일 쿼리를 사용하면 여러 수준의 분석 질문에 답변 할 수 있습니다. 예를 들어, 이것을 사용하면 OLAP (Online Analytical Processing) 연산을 지원할 수 있습니다.

sales 라는 이름이 붙은 테이블에 매출의 수익성을 기록하기위한 year , country , product , 그리고 profit 컬럼이 포함되어 있다고 가정합니다.

 CREATE TABLE sales
 (
     year INT NOT NULL,
     country VARCHAR (20) NOT NULL,
     product VARCHAR (32) NOT NULL,
     profit INT
 );

다음과 같은 간단한 GROUP BY 를 사용하면 테이블의 내용을 연도별로 정리할 수 있습니다.

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
+------+-------------+

이 출력은 연간 총 수익이 표시되지만 모든 년 동안 집계 된 총 수익을 확인하려면 각 값을 스스로 총하거나 추가 쿼리를 실행해야합니다.

또는 단일 쿼리에서 두 수준의 분석을 제공하는 ROLLUP 도 사용할 수 있습니다. GROUP BY 절에 WITH ROLLUP 수식을 추가하면 쿼리에서 모든 년에 총 합계를 나타내는 다른 행이 생성됩니다.

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |
+------+-------------+

총 합계의 초 집약 행은 year 컬럼의 NULL 값으로 식별됩니다.

여러 GROUP BY 열이있는 경우는 ROLLUP 의 효과가 더 복잡해집니다. 이 경우, 마지막 그룹화 컬럼 이외에서 "브레이크"(값의 변경)이 발생할 때마다 쿼리에서 추가 초 집약 요약 행이 생성됩니다.

예를 들어, ROLLUP 을 사용하지 않는 경우는 year , country , 그리고 product 에 기반 sales 테이블의 요약이 다음과 같이 표시됩니다.

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2001 | Finland | Phone      |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
+------+---------+------------+-------------+

이 출력은 year / country / product 분석 수준에서만 요약 값이 표시됩니다. ROLLUP 이 추가되면 쿼리에서 여러 추가 행이 생성됩니다.

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+

이 쿼리는 ROLLUP 절을 추가하면 출력에 하나뿐만 아니라 4 가지 분석 수준의 요약 정보가 포함됩니다. 다음은 ROLLUP 출력을 해석하는 방법을 보여줍니다.

지정된 year와 country에 대응하는 product 행의 각 세트에 이어 모든 product의 합계를 나타내는 추가 요약 행이 생성됩니다. 이 줄은 NULL 로 설정된 product 컬럼이 포함되어 있습니다.
지정된 year에 해당하는 행의 각 세트에 이어 모든 country과 product의 합계를 나타내는 추가 요약 행이 생성됩니다. 이 줄은 NULL 로 설정된 country 및 products 컬럼이 포함되어 있습니다.
마지막으로 다른 모든 행에 이어 모든 year, country, 그리고 product의 총 합계를 나타내는 추가 요약 행이 생성됩니다. 이 줄은 NULL 로 설정된 year , country 및 products 컬럼이 포함되어 있습니다.

ROLLUP 사용시 기타 고려 사항

다음 항목에는 MySQL에서의 ROLLUP 의 구현에 고유의 동작의 일부를 나열합니다.

ROLLUP 을 사용하려면 ORDER BY 절을 동시에 사용하여 결과를 정렬 할 수 없습니다. 즉, ROLLUP 와 ORDER BY 는 상호 배타적입니다. 그러나 정렬 순서를 일부 제어 할 수 있습니다. MySQL의 GROUP BY 에 의해 결과가 정렬됩니다. GROUP BY 에서 이름이 지정된 컬럼에 명시적인 ASC 및 DESC 키워드를 사용하면 컬럼마다 정렬 순서를 지정할 수 있습니다. ( ROLLUP 에 추가 된 높은 수준의 요약 행 정렬 순서에 관계없이 계산 된 행 뒤에 표시됩니다.)

LIMIT 를 사용하면 클라이언트에 반환되는 행의 수를 제한 할 수 있습니다. LIMIT 는 ROLLUP 의 후에 적용되기 때문에 ROLLUP 에 추가 된 추가 행에 대해 제한이 적용됩니다. 예 :

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP
    -> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
+------+---------+------------+-------------+

LIMIT 와 ROLLUP 을 동시에 사용하면 매우 집약 행을 이해하기위한 문맥이 적기 때문에 해석이 더 어려운 결과가 생성 될 수 있습니다.

각 초 집약 행 NULL 지시자는 행이 클라이언트로 전송 될 때 생성됩니다. 서버에서 변경된 값을 가지는 맨 왼쪽 열에 이어 GROUP BY 절에 이름이 지정된 컬럼이 조사됩니다. 이러한 이름 중 하나에 어휘가 일치하는 이름을 가지는 결과 세트의 컬럼에서는 그 값이 NULL 로 설정됩니다. (열 번호별로 그룹화 컬럼을 지정하면, 서버는 NULL 로 설정하는 컬럼을 번호로 식별됩니다.)

초 집약 행 NULL 값은 쿼리 처리 꽤 후반 단계에서 결과 세트에 배치되기 때문에 쿼리 자체에서 NULL 값으로 테스트 할 수 없습니다. 예를 들어, 쿼리에 HAVING product IS NULL 을 추가해도 초 집약 행을 제외한 모든 출력에서 제거 할 수 없습니다.

한편, NULL 값은 클라이언트 측에서 NULL 로 표시되고 MySQL 클라이언트 프로그래밍 인터페이스 중 하나를 사용하여 테스트 할 수 있습니다.

MySQL은 GROUP BY 목록에 표시되지 않는 컬럼을 선택 목록에서 이름을 지정할 수 허용됩니다. 이 경우 서버는 요약 행에 이러한 비 - 집합 컬럼에서 임의의 값을 자유롭게 선택할 수 있습니다. 여기에는 WITH ROLLUP 에 추가 된 추가 라인도 포함됩니다. 예를 들어, 다음 쿼리는 country 는 GROUP BY 목록에 표시되지 않는 비 - 집합 컬럼이며,이 컬럼에서 선택된 값은 불확정입니다.

mysql> SELECT year, country, SUM(profit)
    -> FROM sales GROUP BY year WITH ROLLUP;
+------+---------+-------------+
| year | country | SUM(profit) |
+------+---------+-------------+
| 2000 | India   |        4525 |
| 2001 | USA     |        3010 |
| NULL | USA     |        7535 |
+------+---------+-------------+

이 동작은 ONLY_FULL_GROUP_BY SQL 모드가 비활성화되어있는 경우에 발생합니다. 이 모드가 활성화되어있는 경우 country 이 GROUP BY 절에 나열되지 않기 때문에 서버는 쿼리를 무단으로 거부합니다. 비 - 집합 컬럼 및 GROUP BY 에 대한 자세한 내용은 섹션 12.19.3 "MySQL의 GROUP BY 처리" 를 참조하십시오.