카테고리:

쿼리 구문

GROUP BY ROLLUP

GROUP BY ROLLUP은 계층 구조의 여러 수준에서 집계된 행(세부적으로 그룹화된 행 외에)을 생성하는 GROUP BY 절의 확장명입니다. 예를 들어, 도시 및 주별로 그룹화하는 경우 ROLLUP은 각 도시/주 조합, 각 주 합계, 모든 주에 걸친 총계에 대한 집계를 생성합니다. 이러한 집계는 SELECT 절에 지정된 동일한 집계 함수를 사용하여 계산됩니다.

ROLLUP은 다른 GROUP BY 식과 결합할 수 있습니다. 예를 들어 GROUP BY x, ROLLUP(y, z)`를 작성하여 ``x` 열을 기준으로 그룹화하고 y 및 ``z``에 대한 롤업 집계와 결합할 수 있습니다.

롤업은 여러 결과 세트를 생성하는 것으로 생각할 수 있으며, 여기서 각 결과 세트(첫 번째 이후)은 이전 결과 세트의 집계입니다. 따라서 예를 들어, 소매점 체인을 소유하고 있는 경우 다음 항목에 대한 이익을 확인하려 할 수 있습니다.

  • 각 매장.

  • 각 도시(대도시에는 여러 매장이 있을 수 있음).

  • 각 주.

  • 모든 것(모든 주에 있는 모든 매장).

해당 정보를 얻기 위해 별도의 보고서를 만들 수 있지만, 데이터를 한 번 스캔하는 것이 더 효율적입니다.

그룹화 세트 개념에 익숙하다면 ROLLUP 그룹화를 일련의 그룹화 세트와 동일하며 본질적으로 더 짧은 사양이라고 생각할 수 있습니다. ROLLUP 사양의 N 요소는 :code:`N+1 GROUPING SETS`에 해당합니다.

참고 항목

구문

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] ROLLUP ( groupItem [ , groupItem [ , ... ] ] )
[ ... ]
Copy

여기서:

groupItem ::= { <column_alias> | <position> | <expr> }
Copy

매개 변수

column_alias

쿼리 블록의 SELECT 목록에 나타나는 열 별칭입니다.

position

SELECT 목록에서 식의 위치입니다.

expr

현재 범위의 테이블에 대한 모든 식입니다.

사용법 노트

  • 쿼리가 점점 더 높은 수준에서 집계됨에 따라 각 행의 더 많은 열에 NULL 값이 표시됩니다. 이는 적절합니다. 다음 예제에서 주 수준 집계의 경우 profit 열의 값이 하나의 도시에 해당하지 않기 때문에 city 열은 NULL입니다. 마찬가지로, 모든 주와 모든 도시의 데이터를 집계하는 최종 합계에서 수익은 특정 주나 특정 도시의 수익이 아니므로 해당 행의 state 및 city 행은 모두 NULL입니다.

  • 쿼리는 ROLLUP 다음의 괄호 안에 “가장 중요한 수준”을 먼저 나열해야 합니다. 예를 들어 주에는 도시가 포함되므로 주와 도시 간에 데이터를 롤업하는 경우 절은 :code:`GROUP BY ROLLUP (state, city)`이어야 합니다.

    열 이름의 순서를 반대로 바꾸면 원하는 결과가 나오지 않을 수 있습니다. 다음 예제에서 ROLLUP 절의 city 및 state 순서를 반대로 바꾸는 경우 캘리포니아와 푸에르토리코 모두에 산호세(SJ)라는 도시가 있기 때문에 적어도 부분적으로는 결과가 올바르지 않을 수 있으며, 모든 수익의 최종 합계를 제외하고 두 개의 다른 산호세 도시의 수익을 결합하는 것은 바람직하지 않을 수 있습니다. 이름이 같은 다른 도시의 데이터를 결합하지 않는 다른 방법은 각 도시에 대해 고유한 ID를 사용하고 쿼리에 이름이 아닌 ID를 사용하는 것입니다.

  • GROUPING 유틸리티 함수는 롤업 집계의 결과인 NULL 값과 데이터의 실제 NULL 값을 구분하는 데 도움이 될 수 있습니다. GROUPING은 지정된 열에 그룹화된 행에 대해 ``0``을 반환하고 집계로 인해 열에 NULL이 표시되는 행에 대해 ``1``을 반환합니다.

예

다양한 도시와 주/지역에 지점이 있는 체인점의 판매 정보가 포함된 테이블을 생성하고 로딩하는 것으로 시작합니다.

-- Create some tables and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES 
    (1, 1.00),
    (2, 2.00);

CREATE TABLE sales (product_ID INTEGER, retail_price REAL, 
    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');
Copy

모든 주에 걸쳐 도시, 주 및 합계를 기준으로 이익을 표시하는 롤업 쿼리를 실행합니다. 이 쿼리는 세 가지 집계 “레벨”을 생성합니다.

  • 각 도시.

  • 각 주.

  • 모든 주에 걸쳐 결합된 모든 수익.

이 쿼리는 :code:`ORDER BY state, city NULLS LAST`를 사용하여 각 주의 롤업이 해당 주의 모든 도시 바로 뒤에 오고, 최종 롤업이 출력 끝에 표시되도록 합니다.

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY ROLLUP (state, city)
 ORDER BY state, city NULLS LAST
 ;
+-------+---------+--------+
| STATE | CITY    | PROFIT |
|-------+---------+--------|
| CA    | SF      |     13 |
| CA    | SJ      |     26 |
| CA    | NULL    |     39 |
| FL    | Miami   |     48 |
| FL    | Orlando |     96 |
| FL    | NULL    |    144 |
| PR    | SJ      |    192 |
| PR    | NULL    |    192 |
| NULL  | NULL    |    375 |
+-------+---------+--------+
Copy

일부 롤업 행에는 NULL 값이 포함되어 있습니다. 예를 들어, 테이블의 마지막 행에는 데이터가 특정 도시 및 주가 아니라 모든 도시 및 주에 관한 것이기 때문에 시에 대한 NULL 값과 주에 대한 NULL 값이 포함됩니다.