카테고리:

쿼리 구문

GROUP BY ROLLUP

GROUP BY ROLLUP은 (그룹화된 행 외에도) 소계 행을 생성하는 GROUP BY 절의 확장입니다. 소계 행은 그룹화된 행을 생성하는 데 사용된 동일한 집계 함수를 계산함으로써 파생된 값을 추가로 집계하는 행입니다.

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

  • 각 매장.

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

  • 각 주.

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

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

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

참고 항목:

GROUP BY GROUPING SETS

구문

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

여기서:

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

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

position

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

expr

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

사용법 노트

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

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

    ...ROLLUP (state, city)

    열 이름의 순서를 반대로 하는 경우, 원하는 결과가 아닐 가능성이 높습니다. 아래 예에서, ROLLUP 절에서 도시 및 주의 순서를 반대로 하는 경우, 캘리포니아와 푸에르토리코에 산호세(“SJ”)라는 도시가 있기 때문에 적어도 부분적으로는 결과가 정확하지 않을 것입니다. 모든 수익의 최종 합계를 내는 경우를 제외하고는 이러한 서로 다른 두 산호세의 수익을 결합하는 것은 바람직하지 않습니다. (동일한 이름을 가진 서로 다른 도시의 데이터를 결합하는 것을 피하는 다른 방법은 각 도시에 대해 고유한 ID를 만들고, 쿼리에서 이름 대신 ID를 사용하는 것입니다.)

예

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

-- 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

모든 주에서 도시, 주, 총계별로 이익을 표시하는 롤업 쿼리를 실행합니다.

아래 예는 세 가지 “수준”이 있는 쿼리를 보여줍니다.

  • 각 도시.

  • 각 주.

  • 합산된 모든 수익.

이 예는 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 값이 포함됩니다.

언어: 한국어