카테고리:

쿼리 구문

GROUP BY GROUPING SETS

GROUPING SETS는 단일 문에서 여러 GROUP BY 절을 계산하는 GROUP BY 절의 강력한 확장명입니다. *그룹화 세트*는 차원 열의 세트입니다.

GROUPING SETS 식은 다른 GROUP BY 식과 결합할 수 있으므로 이 구문은 별도의 구문이 아닌 GROUP BY 절의 통합된 부분이 됩니다. 예를 들어 GROUP BY x, GROUPING SETS(y, z)`를 작성하여 ``x` 열을 기준으로 그룹화하고 y 및 ``z``에 대한 별도의 그룹화와 결합할 수 있습니다.

GROUPING SETS 식은 동일한 결과 세트에서 두 개 이상의 GROUP BY 연산의 합집합과 동등합니다. 예를 들면 다음과 같습니다.

  • GROUP BY GROUPING SETS((a)) 는 단일 그룹화 세트 연산 GROUP BY a 와 동등합니다.

  • GROUP BY GROUPING SETS((a), (b)) 은 GROUP BY a UNION ALL GROUP BY b 과 동등합니다.

추가 괄호가 없는 GROUPING SETS(a, b)`는 논리적으로 :code:`GROUPING SETS((a), (b))`와 동일합니다. 둘 다 열 ``a` 및 열 ``b``에 대해 각각 하나씩 두 개의 별도 그룹화 세트를 생성하기 때문입니다. 이 식은 두 열을 기준으로 그룹화하는 단일 그룹화 세트를 생성하는 :code:`GROUPING SETS((a, b))`와는 다릅니다.

구문

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] GROUPING SETS ( groupSet [ , groupSet [ , ... ] ] )
[ ... ]
Copy

여기서:

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

groupSet ::= groupItem | ( groupItem [ , groupItem [ , ... ] ] )
Copy

매개 변수

column_alias

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

position

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

expr

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

사용법 노트

  • Snowflake는 동일한 쿼리 블록에서 최대 128개의 그룹화 세트를 허용합니다.

  • 괄호가 있는 구문 변형:

    • GROUPING SETS(a, b)`는 :code:`GROUPING SETS((a), (b))`의 축약형입니다. 둘 다 열 ``a` 및 열 ``b``를 기준으로 그룹화하는 그룹화 세트를 각각 하나씩 생성하여 두 개의 별도 그룹화 세트가 됩니다.

    • GROUPING SETS((a, b))`는 열 ``a` 및 b 모두를 기준으로 그룹화하는 단일 그룹화 세트를 생성합니다(:code:`GROUP BY a, b`와 유사함).

  • 일반적인 GROUP BY 열을 GROUPING SETS: GROUP BY x, GROUPING SETS(y, z)`와 결합하여 ``x` 열을 기준으로 그룹화하고 y 및 ``z``에 대한 별도의 그룹화와 결합할 수 있습니다.

  • 출력에는 일반적으로 일부 NULL 값이 포함됩니다. GROUP BY GROUPING SETS가 각각 다른 조건으로 그룹화된 둘 이상의 결과 세트의 결과를 병합하기 때문에, 하나의 결과 세트에 단일 값이 있는 일부 열은 다른 결과 세트에 많은 해당 값이 있을 수 있습니다. 예를 들어, 부서별로 그룹화된 직원 세트와 연수별로 그룹화된 세트의 합집합 연산을 수행하는 경우, 연수가 가장 높은 세트의 구성원이 모두 같은 부서에 있는 것은 아니므로 ``department_name``의 값은 NULL로 설정됩니다. 다음 예제에는 이러한 이유로 NULL 값이 포함되어 있습니다.

참고 항목

  • :doc:`/sql-reference/functions/grouping`(각 행을 생성한 그룹화 수준을 식별하는 유틸리티 함수)

  • GROUP BY ROLLUP

  • GROUP BY CUBE

예

이 예제에서는 재해 지원을 위해 훈련된 간호사에 대한 정보 테이블을 사용합니다. 이러한 모든 간호사에게는 간호사 면허(예: RN은 “등록 간호사” 면허 보유) 및 추가 면허(예: 수색 및 구조, 무선 통신 등과 같은 재해 관련 전문 분야)가 있습니다. 이 예제에서는 두 가지 카테고리의 면허만 단순화하여 사용합니다.

  • 간호: RN(등록 간호사) 및 LVN(면허가 있는 직업 간호사).

  • 아마추어(“햄”) 무선: 햄 무선 면허에는 “Technician”, “General”, “Amateur Extra”가 포함됩니다.

다음 명령은 테이블을 생성하고 로드합니다.

CREATE or replace TABLE nurses (
  ID INTEGER,
  full_name VARCHAR,
  medical_license VARCHAR,   -- LVN, RN, etc.
  radio_license VARCHAR      -- Technician, General, Amateur Extra
  )
  ;

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (201, 'Thomas Leonard Vicente', 'LVN', 'Technician'),
    (202, 'Tamara Lolita VanZant', 'LVN', 'Technician'),
    (341, 'Georgeann Linda Vente', 'LVN', 'General'),
    (471, 'Andrea Renee Nouveau', 'RN', 'Amateur Extra')
    ;
Copy

이 쿼리는 GROUP BY GROUPING SETS를 사용합니다.

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

처음 두 행은 RNs 및 LVNs(간호 면허의 두 가지 유형) 수를 보여줍니다. 이 두 행에 대한 radio_license 열의 NULL 값은 의도적입니다. 쿼리는 해당 무선 면허와 관계없이 모든 LVNs(및 모든 RNs을 함께)을 그룹화하므로, 결과는 해당 행에 그룹화된 모든 LVNs 또는 RNs에 반드시 적용되는 각 행의 radio_license 열에 하나의 값을 표시할 수 없습니다.

다음 세 행은 각 유형의 아마추어 무선 면허(“Technician”, “General” 및 “Amateur Extra”)를 보유한 간호사의 수를 보여줍니다. 이러한 세 행 각각의 ``medical_license``에 대한 NULL 값은 의도적입니다. 단일 의료 면허가 각 행의 모든 구성원에게 반드시 적용되는 것은 아니기 때문입니다.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        3 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

다음 예제에서는 열을 별도로 한 그룹화 및 열과 함께한 그룹화의 차이를 보여줍니다. 이 쿼리는 medical_license 및 radio_license 모두를 조합하여 그룹화합니다.

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS ((medical_license, radio_license))
  ORDER BY 3 DESC NULLS FIRST;
Copy

이 쿼리는 medical_license 및 ``radio_license``의 각 조합이 해당 개수와 함께 표시되는 행을 생성합니다. 이전 예제와 달리, 이 쿼리는 각 열에 대해 별도의 그룹화를 생성하는 대신 두 열 모두를 기준으로 그룹화하기 때문에 출력에 NULL 값이 없습니다.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

다음 예에서는 일부 열에 NULL 값이 포함된 경우 어떤 일이 발생하는지 보여줍니다. 아직 햄 무선 면허가 없는 새 간호사 3명을 추가하여 시작합니다.

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (101, 'Lily Vine', 'LVN', NULL),
    (102, 'Larry Vancouver', 'LVN', NULL),
    (172, 'Rhonda Nova', 'RN', NULL)
    ;
Copy

그런 다음 이전과 동일한 쿼리를 실행합니다.

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

왜 이제 두 열에 NULL이 있는 행이 있을까요? 모든 값이 NULL이라면 COUNT(*) 결과는 3일까요?

해당 행의 radio_license 열에서 NULL이 발생하는 이유는 세 간호사에게 무선 면허가 없기 때문입니다. (이제 쿼리 :code:`SELECT DISTINCT radio_license FROM nurses`는 “Technician”, “General”, “Amateur Extra”, “NULL”이라는 4가지 고유한 값을 반환합니다.)

medical_licenses 열의 NULL 값은 이전 쿼리 결과에서 NULL 값이 발생한 것과 같은 이유로 발생합니다. 즉, 이 행에서 계산된 간호사에게는 다른 의료 면허가 있으므로 하나의 값(”RN” 또는 “LVN”)이 이 행에 계산된 모든 간호사에게 반드시 적용되는 것은 아닙니다.

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        3 | NULL            | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

다음 예제에서는 일반적인 GROUP BY 열과 GROUPING SETS의 조합을 보여줍니다. 이 쿼리는 medical_license``를 기준으로 그룹화하며, 각 의료 면허 그룹 내에서 각 ``radio_license 값 및 결합된 모든 무선 면허에 대해 별도의 집계를 생성합니다.

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, GROUPING SETS (radio_license, ())
  ORDER BY 3 DESC NULLS FIRST;
Copy

각 의료 면허(LVN 및 RN)의 경우 출력은 다음과 같습니다.

  • 각 특정 radio_license 값을 기준으로 그룹화된 행(Technician, General, Amateur Extra 또는 무선 면허가 없는 경우 NULL)

  • 무선 면허와 관계없이 해당 의료 면허를 보유한 모든 간호사를 나타내는 radio_license 열에 NULL이 있는 요약 행

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

이 출력을 GROUPING SETS 절이 없는 GROUP BY 쿼리의 출력과 비교할 수 있습니다.

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license
  ORDER BY 3 DESC NULLS FIRST;
Copy
+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

GROUPING 함수 사용하기

GROUPING 유틸리티 함수는 각 행을 생성한 집계 수준을 식별하는 데 도움이 됩니다. 이는 특히 그룹화 작업의 결과인 NULL 값과 데이터의 실제 NULL 값을 구분하는 데 유용합니다.

GROUPING 함수는 다음을 반환합니다.

  • 지정된 열에 그룹화된 행의 경우 0

  • 지정된 열에 그룹화되지 않은 행의 경우 ``1``(여기서 NULL은 집계로 인해 나타남)

이 예제에서는 출력을 명확하게 하기 위해 쿼리에 GROUPING 함수를 추가합니다.

SELECT
    COUNT(*),
    medical_license,
    radio_license,
    GROUPING(medical_license) AS grp_medical,
    GROUPING(radio_license) AS grp_radio
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license);
Copy

grp_medical 및 grp_radio 열은 그룹화에 사용된 열을 보여줍니다.

  • 행 1-2: radio_license``(``grp_radio=1)가 아닌 medical_license``(``grp_medical=0)를 기준으로 그룹화됨

  • 행 3-6: medical_license``(``grp_medical=1)가 아닌 radio_license``(``grp_radio=0)를 기준으로 그룹화됨

  • 행 6: radio_license``의 NULL 값은 실제 데이터(``grp_radio=0)인 반면, medical_license``의 NULL은 집계(``grp_medical=1)의 결과임

+----------+-----------------+---------------+-------------+-----------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE | GRP_MEDICAL | GRP_RADIO |
|----------+-----------------+---------------+-------------+-----------|
|        2 | RN              | NULL          |           0 |         1 |
|        5 | LVN             | NULL          |           0 |         1 |
|        2 | NULL            | Technician    |           1 |         0 |
|        1 | NULL            | General       |           1 |         0 |
|        3 | NULL            | NULL          |           1 |         0 |
|        1 | NULL            | Amateur Extra |           1 |         0 |
+----------+-----------------+---------------+-------------+-----------+