BigQuery 에서 PIVOT 하는 방법

  • BigQuery 에서2021년 7월 19일, PIVOT 기능이 신규 추가되었다.
  • PIVOT 이란 테이블의 열과 행을 변경하는 것을 말한다.
  • 해당 PIVOT 기능에 대한 문법과 유의점에 대해 알아보자.

1. Syntax

--PIVOT 연산자
FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

2. Example

다음과 같은 테이블이 있다고 해보자.

productsalesquarteryear
Kale51Q12020
Kale23Q22020
Kale45Q32020
Kale3Q42020
Kale70Q12021
Kale85Q22021
Apple77Q12020
Apple0Q22020
Apple1Q12021
table_A

위 table A 를 아래 table B 와 같이 변경하고 싶다면,

productyearQ1Q2Q3Q4
Apple2020770NULLNULL
Apple20211NULLNULLNULL
Kale20205123453
Kale20217085NULLNULL
table_B

다음과 같이 쿼리를 작성하면 된다.

SELECT * FROM
 table_A
 PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

3. 유의사항

PIVOT 하고자 하는 테이블에서 내가 참조하지 않는 속성이 있는 경우,
명확하게 PIVOT 앞의 FROM 절에서 명시를 해주어야 한다.

그렇지 않을 경우, PIVOT 결과 값이 왜곡된다.
(행 중복)

3.1. Bad Case

단순히 쿼터 별로 합계를 보고 싶다고 할 때(연도와 상관 없이),

아래와 같이 쿼리를 작성하지 말자.

SELECT product, Q1, Q2, Q3, Q4 FROM
 table_A
 PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

이에 대한 Result 는 다음과 같다.

productQ1Q2Q3Q4
Kale5123453
Kale7085NULLNULL
Apple770NULLNULL
Apple1NULLNULLNULL
3.1. 잘못된 결과

3.2. Good Case

참조하고자 하는 속성 중 year 가 없는 경우, 다음과 같이 명시해주어야 한다.

SELECT product, Q1, Q2, Q3, Q4 FROM
(SELECT product, quarter, sales FROM table_A)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

이에 대한 Result 는 다음과 같다.

productQ1Q2Q3Q4
Kale121108453
Apple780NULLNULL
3.2. 잘된 결과