카테고리:: 쿼리 구문

PIVOT¶

입력 식의 한 열에서 고유 값을 여러 열로 변환하고, 필요한 경우 나머지 열 값에 대해 결과를 집계하여 테이블을 회전합니다. 쿼리에서 테이블 이름 또는 하위 쿼리 뒤에 FROM 절에 지정됩니다.

연산자는 기본 제공 집계 함수 AVG, COUNT, MAX, MIN, SUM 을 지원합니다.

PIVOT은 좁은 테이블(예: empid, month, sales)을 더 넓은 테이블(예: empid, jan_sales, feb_sales, mar_sales)로 변환하는 데 사용할 수 있습니다.

참고 항목:: UNPIVOT

구문¶

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN (
              <pivot_value_1> [ , <pivot_value_2> ... ]
              | ANY [ ORDER BY ... ]
              | <subquery>
            )
            [ DEFAULT ON NULL (<value>) ]
         )

[ ... ]

Copy

매개 변수¶

aggregate_function: pivot_column 에서 그룹화된 값을 결합하기 위한 집계 함수입니다.
pivot_column: 집계될 소스 테이블 또는 하위 쿼리의 열입니다.
value_column: 열 이름이 생성될 값이 포함된 소스 테이블 또는 하위 쿼리의 열입니다.
pivot_value_N: 쿼리 결과에서 머리글로 피벗할 피벗 열의 값 목록입니다.
ANY [ ORDER BY ... ]: 피벗 열의 모든 고유 값을 기준으로 피벗할 수 있습니다. 출력에서 피벗 열의 순서를 제어하려면 ANY 키워드 뒤에 ORDER BY 절을 지정합니다. 피벗 열에 NULLs가 포함된 경우 NULL도 피벗 값으로 처리됩니다.
subquery: 하위 쿼리에서 발견된 모든 값에 대해 피벗을 실행합니다. 하위 쿼리에 ORDER BY 절이 포함된 경우 DISTINCT 키워드는 필수입니다. 하위 쿼리는 단일 열을 반환하는 상관관계가 없는 하위 쿼리여야 합니다. 피벗은 하위 쿼리에서 반환된 모든 고유 값에 대해 수행됩니다. 상관관계가 없는 하위 쿼리에 대한 정보는 하위 쿼리 관련 작업하기 섹션을 참조하십시오.
DEFAULT ON NULL (value): 피벗 결과의 모든 NULLs를 지정된 기본값으로 바꿉니다. 기본값은 피벗 및 집계 열에 종속되지 않는 모든 스칼라 식이 될 수 있습니다.

사용법 노트¶

Snowflake는 동적 피벗 을 지원합니다. 동적 피벗 쿼리는 피벗 값을 명시적으로 지정하는 대신 ANY 키워드 또는 PIVOT 하위 절의 하위 쿼리를 사용합니다.
뷰 정의에서 동적 피벗을 사용하는 경우 기본 데이터가 변경되어 피벗 출력 열이 변경되면 뷰에 대한 쿼리가 실패할 수 있습니다.
저장 프로시저나 사용자 정의 함수(UDF)의 본문에서는 동적 피벗이 지원되지 않습니다.
동적 피벗을 사용하지 않는 피벗 쿼리는 중복 열이 있는 출력을 반환할 수 있습니다. 중복된 열이 있는 출력은 피하는 것이 좋습니다. 동적 피벗 쿼리는 중복된 열을 제거합니다.
동적 피벗을 사용하지 않는 피벗 쿼리가 VARIANT 열을 다른 데이터 타입으로 CAST 를 시도하면 실패할 수 있습니다. 동적 피벗 쿼리에는 이러한 제한이 없습니다.

예¶

PIVOT 예제에서는 다음 quarterly_sales 테이블을 사용합니다.

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT, 
  amount INT, 
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4');

Copy

동적 피벗을 사용하여 모든 고유 열 값을 자동으로 피벗합니다.¶

테이블 quarterly_sales 가 주어지면 ANY 키워드를 사용하여 amount 열을 피벗해 모든 고유 분기에 대한 직원당 총 매출을 합산하고 피벗 열이 순서대로 정렬되도록 ORDER BY를 지정합니다.

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
+-------+-----------+-----------+-----------+-----------+

동적 피벗을 사용하여 하위 쿼리를 사용하여 열 값에 피벗¶

quarterly_sales 테이블 외에 ad_campaign_types_by_quarter 테이블이 특정 분기에 실행된 광고 유형을 추적한다고 가정해 보겠습니다. 이 테이블의 구조와 데이터는 다음과 같습니다.

CREATE OR REPLACE TABLE ad_campaign_types_by_quarter(
  quarter VARCHAR,
  television BOOLEAN,
  radio BOOLEAN,
  print BOOLEAN)
  AS SELECT * FROM VALUES
    ('2023_Q1', TRUE, FALSE, FALSE),
    ('2023_Q2', FALSE, TRUE, TRUE),
    ('2023_Q3', FALSE, TRUE, FALSE),
    ('2023_Q4', TRUE, FALSE, TRUE);

Copy

피벗 쿼리에서 하위 쿼리를 사용하여 특정 광고 캠페인이 진행된 분기의 매출 합계를 확인할 수 있습니다. 예를 들어, 다음 피벗 쿼리는 TV 광고 캠페인이 있는 분기에 대한 데이터만 반환합니다.

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      SELECT DISTINCT quarter
        FROM ad_campaign_types_by_quarter
        WHERE television = TRUE
        ORDER BY quarter)
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q4' |
|-------+-----------+-----------|
|     1 |     10400 |     18000 |
|     2 |     39500 |      5300 |
+-------+-----------+-----------+

피벗 열에 대한 지정된 열 값 목록에서 피벗¶

테이블 quarterly_sales 가 주어졌을 때 amount 열을 피벗하여 지정된 분기의 직원당 총 매출을 합산합니다.

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3')
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' |
|-------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |
|     2 |     39500 |     90700 |     12000 |
+-------+-----------+-----------+-----------+

다음 쿼리를 실행하여 amount 열의 모든 분기를 피벗할 수 있습니다.

SELECT * 
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
+-------+-----------+-----------+-----------+-----------+

AS 절을 사용하여 출력의 열 이름을 수정할 수 있습니다. 예를 들어, 열 이름을 줄여 따옴표 없이 표시하려면 다음 쿼리를 실행합니다.

SELECT * 
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    ) AS p (empid_renamed, Q1, Q2, Q3, Q4)
  ORDER BY empid_renamed;

Copy

+---------------+-------+-------+-------+-------+
| EMPID_RENAMED |    Q1 |    Q2 |    Q3 |    Q4 |
|---------------+-------+-------+-------+-------|
|             1 | 10400 |  8000 | 11000 | 18000 |
|             2 | 39500 | 90700 | 12000 |  5300 |
+---------------+-------+-------+-------+-------+

또한 SELECT 목록에 특정 열을 나열하고 열 이름을 변경할 수도 있습니다.

SELECT empid, 
       "'2023_Q1'" AS Q1, 
       "'2023_Q2'" AS Q2, 
       "'2023_Q3'" AS Q3,
       "'2023_Q4'" AS Q4
  FROM quarterly_sales
    PIVOT(sum(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    ) 
  ORDER BY empid;

Copy

+-------+-------+-------+-------+-------+
| EMPID |    Q1 |    Q2 |    Q3 |    Q4 |
|-------+-------+-------+-------+-------|
|     1 | 10400 |  8000 | 11000 | 18000 |
|     2 | 39500 | 90700 | 12000 |  5300 |
+-------+-------+-------+-------+-------+

쿼리가 null을 반환하는 경우 기본값으로 바꿀 수 있습니다. 예를 들어, 다음 쿼리는 2024_Q1 에 대해 null을 반환합니다.

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount)
      FOR quarter IN (
        '2023_Q1',
        '2023_Q2',
        '2023_Q3',
        '2023_Q4',
        '2024_Q1')
      )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' | '2024_Q1' |
|-------+-----------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |      NULL |
|     2 |     39500 |     90700 |     12000 |      5300 |      NULL |
+-------+-----------+-----------+-----------+-----------+-----------+

다음 쿼리를 실행하여 null을 기본값인 0 으로 바꿀 수 있습니다.

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount)
      FOR quarter IN (
        '2023_Q1',
        '2023_Q2',
        '2023_Q3',
        '2023_Q4',
        '2024_Q1')
      DEFAULT ON NULL (0)
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' | '2024_Q1' |
|-------+-----------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |         0 |
|     2 |     39500 |     90700 |     12000 |      5300 |         0 |
+-------+-----------+-----------+-----------+-----------+-----------+