집계 확장 - Amazon Redshift
GROUPING SETSROLLUPCUBEGROUPING/GROUPING_ID 함수부분 ROLLUP 및 CUBE연결된 그룹화중첩된 그룹화사용 노트

집계 확장

Amazon Redshift는 단일 문에서 여러 GROUP BY 작업을 수행하는 집계 확장을 지원합니다.

집계 확장의 예시에서는 전자 회사의 판매 데이터가 들어 있는orders 테이블을 사용합니다. 다음을 사용하여 orders를 생성할 수 있습니다.

CREATE TABLE ORDERS (
    ID INT,
    PRODUCT CHAR(20),
    CATEGORY CHAR(20),
    PRE_OWNED CHAR(1),
    COST DECIMAL
);

INSERT INTO ORDERS VALUES
    (0, 'laptop',       'computers',    'T', 1000),
    (1, 'smartphone',   'cellphones',   'T', 800),
    (2, 'smartphone',   'cellphones',   'T', 810),
    (3, 'laptop',       'computers',    'F', 1050),
    (4, 'mouse',        'computers',    'F', 50);

GROUPING SETS

단일 명령문에서 하나 이상의 그룹화 집합을 계산합니다. 그룹화 집합은 쿼리의 결과 집합을 그룹화할 수 있는 0개 이상의 열 집합인 단일 GROUP BY 절의 집합입니다. 집합을 그룹화하여 그룹화하는 것은 서로 다른 열로 그룹화된 하나의 결과 집합에서 UNION ALL 쿼리를 실행하는 것과 같습니다. 예를 들어, GROUP BY GROUPING SETS((a), (b))는 GROUP BY a UNION ALL GROUP BY b와 동일합니다.

다음 예에서는 제품 카테고리와 판매된 제품 종류에 따라 그룹화된 주문 테이블 제품의 비용을 반환합니다.


SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(category, product);

       category       |       product        | total
----------------------+----------------------+-------
 computers            |                      |  2100
 cellphones           |                      |  1610
                      | laptop               |  2050
                      | smartphone           |  1610
                      | mouse                |    50

(5 rows)

ROLLUP

이전 열이 후속 열의 부모로 간주되는 계층 구조를 가정합니다. ROLLUP은 제공된 열을 기준으로 데이터를 그룹화하여 그룹화된 행 외에 그룹화 열의 모든 수준에서 총계를 나타내는 추가 소계 행을 반환합니다. 예를 들어 GROUP BY ROLLUP ((a), (b) 를 사용하여 b가 a의 하위 섹션이라고 가정하면서 먼저 a로 그룹화된 다음 b로 그룹화된 결과 집합을 반환할 수 있습니다. ROLLUP은 또한 열을 그룹화하지 않고 전체 결과 집합이 있는 행을 반환합니다.

GROUP BY ROLLUP((a), (b))는 GROUP BY GROUPING SETS((a,b), (a), ())와 같습니다.

다음 예는 먼저 범주별로 그룹화된 주문 테이블의 제품 비용을 반환한 다음 제품을 범주의 하위 부문으로 사용하여 반환합니다.

SELECT category, product, sum(cost) as total
FROM orders
GROUP BY ROLLUP(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      |                      |  3710
(6 rows)

CUBE

제공된 열을 기준으로 데이터를 그룹화하여 그룹화된 행 외에 그룹화 열의 모든 수준에서 합계를 나타내는 추가 소계 행을 반환합니다. CUBE는 ROLLUP과 동일한 행을 반환하는 동시에 ROLLUP에서 다루지 않는 그룹화 열의 모든 조합에 대해 소계 행을 추가합니다. 예를 들어 GROUP BY CUBE ((a), (b) 를 사용하여 b가 a의 하위 섹션이고 그 다음 b만으로 그룹화된 결과 집합을 반환할 수 있습니다. CUBE는 또한 열을 그룹화하지 않고 전체 결과 집합이 있는 행을 반환합니다.

GROUP BY CUBE((a), (b))는 GROUP BY GROUPING SETS((a, b), (a), (b), ())와 같습니다.

다음 예는 먼저 범주별로 그룹화된 주문 테이블의 제품 비용을 반환한 다음 제품을 범주의 하위 부문으로 사용하여 반환합니다. ROLLUP에 대한 앞의 예와 달리 문은 그룹화 열의 모든 조합에 대한 결과를 반환합니다.

SELECT category, product, sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      | laptop               |  2050
                      | mouse                |    50
                      | smartphone           |  1610
                      |                      |  3710
(9 rows)

GROUPING/GROUPING_ID 함수

ROLLUP 및 CUBE는 소계 행을 나타내기 위해 결과 집합에 NULL 값을 추가합니다. 예를 들어 GROUP BY ROLLUP((a), (b))는 b 그룹화 열에서 값이 NULL인 하나 이상의 행을 반환하여 그룹화 열에 있는 필드의 소계임을 나타냅니다. 이러한 NULL 값은 반환 튜플의 형식을 충족하는 데만 사용됩니다.

NULL 값 자체를 저장하는 관계에서 ROLLUP 및 CUBE와 함께 GROUP BY 작업을 실행하면 동일한 그룹화 열이 있는 것으로 보이는 행이 있는 결과 집합이 생성될 수 있습니다. 이전 예제로 돌아가서 b 그룹화 열에 저장된 NULL 값이 포함된 경우 GROUP BY ROLLUP ((a), (b) 은 b 그룹화 열에서 소계가 아닌 값이 NULL인 행을 반환합니다.

ROLLUP 및 CUBE에 의해 생성된 NULL 값과 테이블 자체에 저장된 NULL 값을 구별하기 위해 GROUPING 함수 또는 별칭 GROUPING_ID를 사용할 수 있습니다. GROUPING은 단일 그룹화 집합을 인수로 사용하고 결과 집합의 각 행에 대해 해당 위치의 그룹화 열에 해당하는 0 또는 1비트 값을 반환한 다음 해당 값을 정수로 변환합니다. 해당 위치의 값이 집계 확장에 의해 생성된 NULL 값인 경우 GROUPING은 1을 반환합니다. 이 함수는 저장된 NULL 값을 비롯해 다른 모든 값에 대해 0을 반환합니다.

예를 들어 GROUPING(category, product)은 해당 행의 그룹화 열 값에 따라 지정된 행에 대해 다음 값을 반환할 수 있습니다. 이 예제의 목적을 위해 테이블의 모든 NULL 값은 집계 확장에 의해 생성된 NULL 값입니다.

범주 열 제품 열 GROUPING 함수 비트 값 십진수 값
Null이 아님 Null이 아님 00 0
Null이 아님 NULL 01 1
NULL Null이 아님 10 2
NULL NULL 11 3

GROUPING 함수는 쿼리의 SELECT 목록 부분에 다음 형식으로 나타납니다.

SELECT ... [GROUPING( expr )...] ...
  GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...

다음 예제는 CUBE에 대한 이전 예제와 동일하지만 그룹화 집합에 대한 GROUPING 함수가 추가되었습니

SELECT category, product,
       GROUPING(category) as grouping0,
       GROUPING(product) as grouping1,
       GROUPING(category, product) as grouping2,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 3,1,2;

       category       |       product        | grouping0 | grouping1 | grouping2 | total
----------------------+----------------------+-----------+-----------+-----------+-------
 cellphones           | smartphone           |         0 |         0 |         0 |  1610
 cellphones           |                      |         0 |         1 |         1 |  1610
 computers            | laptop               |         0 |         0 |         0 |  2050
 computers            | mouse                |         0 |         0 |         0 |    50
 computers            |                      |         0 |         1 |         1 |  2100
                      | laptop               |         1 |         0 |         2 |  2050
                      | mouse                |         1 |         0 |         2 |    50
                      | smartphone           |         1 |         0 |         2 |  1610
                      |                      |         1 |         1 |         3 |  3710
(9 rows)

부분 ROLLUP 및 CUBE

소계의 일부만으로 ROLLUP 및 CUBE 작업을 실행할 수 있습니다.

부분 ROLLUP 및 CUBE 작업의 구문은 다음과 같습니다.

GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])

여기에서 GROUP BY 절은 expr2 이후의 수준에서 소계 행만 생성합니다.

다음 예제는 주문 테이블에 대한 부분 ROLLUP 및 CUBE 작업을 보여줍니다. 먼저 제품이 중고인지 여부에 따라 그룹화한 다음 범주 및 제품 열에서 ROLLUP 및 CUBE를 실행합니다.

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(9 rows)

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(13 rows)

사전 소유 열은 ROLLUP 및 CUBE 작업에 포함되지 않으므로 다른 모든 행을 포함하는 총계 행이 없습니다.

연결된 그룹화

여러 GROUPING SETS/ROLLUP/CUBE 절을 연결하여 서로 다른 수준의 소계를 계산할 수 있습니다. 연결된 그룹화는 제공된 그룹화 집합의 데카르트 곱을 반환합니다.

GROUPING SETS/ROLLUP/CUBE 절을 연결하는 구문은 다음과 같습니다.

GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]),
         {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]

다음 예제를 통해 서로 연결된 소규모 그룹화가 어떻게 큰 최종 결과 집합을 생성할 수 있는지 확인해 보세요.

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ())
ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
           | cellphones           | smartphone           |        1 |  1610
           | computers            | laptop               |        1 |  2050
           | computers            | mouse                |        1 |    50
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
           | cellphones           |                      |        3 |  1610
           | computers            |                      |        3 |  2100
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
           |                      | laptop               |        5 |  2050
           |                      | mouse                |        5 |    50
           |                      | smartphone           |        5 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
           |                      |                      |        7 |  3710
(22 rows)

중첩된 그룹화

GROUPING SETS/ROLLUP/CUBE 작업을 GROUPING SETS expr로 사용하여 중첩된 그룹화를 형성할 수 있습니다. 중첩된 GROUPING SETS 내부의 하위 그룹화가 평면화됩니다.

중첩된 그룹화의 구문은 다음과 같습니다.

GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])

다음 예제를 살펴보세요.

SELECT category, product, pre_owned,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned))
ORDER BY 4,1,2,3;

       category       |       product        | pre_owned | group_id | total
----------------------+----------------------+-----------+----------+-------
 cellphones           |                      |           |        3 |  1610
 computers            |                      |           |        3 |  2100
                      | laptop               | F         |        4 |  1050
                      | laptop               | T         |        4 |  1000
                      | mouse                | F         |        4 |    50
                      | smartphone           | T         |        4 |  1610
                      | laptop               |           |        5 |  2050
                      | mouse                |           |        5 |    50
                      | smartphone           |           |        5 |  1610
                      |                      | F         |        6 |  1100
                      |                      | T         |        6 |  2610
                      |                      |           |        7 |  3710
                      |                      |           |        7 |  3710
(13 rows)

ROLLUP(category) 및 CUBE(product, pre_owned) 모두 그룹화 집합()을 포함하므로 총계를 나타내는 행이 중복됩니다.

사용 노트

  • GROUP BY 절은 최대 64개의 그룹화 세트를 지원합니다. ROLLUP 및 CUBE 또는 GROUPING SETS, ROLLUP 및 CUBE의 일부 조합의 경우 이 제한은 포함된 그룹화 집합 수에 적용됩니다. 예를 들어 GROUP BY CUBE((a), (b))는 2가 아닌 4개의 그룹화 집합으로 계산됩니다.

  • 집계 확장을 사용하는 경우 상수를 그룹화 열로 사용할 수 없습니다.

  • 중복된 열이 포함된 그룹화 집합은 만들 수 없습니다.