BigQuery PIVOT 사용법(Pivot Operator)

2021년 5월에 추가된 BigQuery PIVOT 함수에 대해 정리한 글입니다
- BigQuery PIVOT, Biquery PIVOT Operator, BigQuery PIVOT 함수, BigQuery PIVOT 사용법

PIVOT

PIVOT은 스프레드시트, 엑셀, Pandas 등 다양한 데이터 전처리 도구에서 지원하는 기능
21년 5월 전엔 BigQuery가 피벗 기능을 제공하지 않아서, 스프레드시트, Pandas 등에서 피벗하곤 함
- 그 이후엔 스크립팅을 사용한 PIVOT 글도 나왔으나, 스크립팅을 사용한 부분은 여전히 어려웠음
하지만 이제 PIVOT 오퍼레이터가 생겨서, 빅쿼리 상에서 데이터를 처리할 수 있음
- 단, 아직 이 기능은 정시 출시가 아님. 프러덕션 환경에서 쓰면 추후에 갑자기 장애가 발생할 수 있음
피벗이 왜 필요한가요?
- 보통 쿼리의 결과는 ROW로 길게 데이터가 저장됩니다. GROUP BY한 후 저장한 값은 피벗해서 저장하면 더 적은 ROW를 유지할 수 있음
- 빅쿼리에서 데이터 추출시 ROW 제한이 있는데, 이런 경우 피벗해서 추출하면 ROW 제한을 피할 수 있음
- 또한 피벗한 데이터의 형태가 데이터 시각화하기 편함

위 이미지에서 파란색 과정이 PIVOT을 의미함

PIVOT 문법

BigQuery PIVOT Document

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

FROM 절 이후에 PIVOT을 추가하고, 집계 함수 + FOR 컬럼(피벗할 컬럼) + IN 조건
pivot_operator
- aggregate_function_call : 피벗시 사용할 집계 함수
- input_column : input_column을 가져와서 컬럼의 row 값을 검색함
- pivot_column은 집계 함수를 호출할 때 생성할 피벗 컬럼

예제

  WITH Produce AS (
    SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
    SELECT 'Kale', 23, 'Q2' UNION ALL
    SELECT 'Kale', 45, 'Q3' UNION ALL
    SELECT 'Kale', 3, 'Q4' UNION ALL
    SELECT 'Apple', 77, 'Q1' UNION ALL
    SELECT 'Apple', 0, 'Q2' UNION ALL
    SELECT 'Apple', 25, 'Q3' UNION ALL
    SELECT 'Apple', 2, 'Q4')
  SELECT * FROM Produce
	
  +---------+-------+---------+
  | product | sales | quarter |
  +---------+-------+---------+
  | Kale    | 51    | Q1      |
  | Kale    | 23    | Q2      |
  | Kale    | 45    | Q3      |
  | Kale    | 3     | Q4      |
  | Apple   | 77    | Q1      |
  | Apple   | 0     | Q2      |
  | Apple   | 25    | Q3      |
  | Apple   | 2     | Q4      |
  +---------+-------+---------+
	

PIVOT

  SELECT * 
  FROM Produce
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
	
  +---------+----+----+----+----+
  | product | Q1 | Q2 | Q3 | Q4 |
  +---------+----+----+----+----+
  | Apple   | 77 | 0  | 25 | 2  |
  | Kale    | 51 | 23 | 45 | 3  |
  +---------+----+----+----+----+

  SELECT * 
  FROM (SELECT sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
		
  +-----+----+----+----+
  | Q1  | Q2 | Q3 | Q4 |
  +-----+----+----+----+
  | 128 | 23 | 70 | 5  |
  +-----+----+----+----+

데이터

빅쿼리의 공공 데이터인 austin_bikeshare 데이터를 활용

2015년 10월 1일 ~ 2015년 10월 7일의 데이터를 일자별, start_station_id 별로 건수를 COUNT한 데이터

  SELECT 
    DATE(start_time) AS start_date,
    start_station_id,
    COUNT(trip_id) AS trip_cnt
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  WHERE 
    DATE(start_time) BETWEEN "2015-10-01" AND "2015-10-07"
    AND start_station_id IS NOT NULL
  GROUP BY
    start_date,
    start_station_id
  ORDER BY start_date, start_station_id

현재 7일치 데이터인데 347개의 ROW가 있음
기간이 늘어나면 더 많은 ROW가 있게 됨

PIVOT 함수 없이 피벗하기

우선 위에 데이터 쿼리를 WITH 문으로 정의하고, 해당 데이터를 활용
PIVOT은 MAX(IF(col = col_value, TRUE일 경우 집계할 값, NULL)) 이런 방식으로 생성할 수 있음

WITH BASE AS (
SELECT 
  DATE(start_time) AS start_date,
  start_station_id,
  COUNT(trip_id) AS trip_cnt
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
WHERE 
  DATE(start_time) BETWEEN "2015-10-01" AND "2015-10-07"
  AND start_station_id IS NOT NULL
GROUP BY
  start_date,
  start_station_id
ORDER BY start_date, start_station_id
)
SELECT
  start_date,
  MAX(IF(start_station_id = 1006, trip_cnt, NULL)) AS station_1006,
  MAX(IF(start_station_id = 1007, trip_cnt, NULL)) AS station_1007,
  MAX(IF(start_station_id = 1008, trip_cnt, NULL)) AS station_1008,
  MAX(IF(start_station_id = 2494, trip_cnt, NULL)) AS station_2494
FROM base
GROUP BY
  start_date
ORDER BY start_date

기본적으로 집계 함수와 IF문으로 PIVOT을 할 수 있지만, 여기선 다음과 같은 이슈가 생김
- start_station_id가 100개가 있다면 하나씩 지정해야 함

PIVOT 함수

PIVOT 함수는 FROM 절에 위치
집계할 함수를 명시해야 함
피벗할 컬럼을 명시해야 함
이제 PIVOT 함수 과정에서 집계하기 때문에, base 데이터에서 먼저 집계하지 않아도 괜찮음

WITH BASE AS (
SELECT 
  DATE(start_time) AS start_date,
  start_station_id,
  trip_id
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
WHERE 
  DATE(start_time) BETWEEN "2015-10-01" AND "2015-10-07"
  AND start_station_id IS NOT NULL
)

SELECT * 
FROM base #1 FROM 절
PIVOT (
  -- #2 집계 함수
  COUNT(trip_id) AS trip_cnt
  -- #3 피벗할 컬럼
  FOR start_station_id IN (1006, 1007, 1008, 2494)
)
ORDER BY start_date

PIVOT 함수는 FROM 절에 위치
- FROM base PIVOT ( ~ )
집계할 함수를 명시해야 함
- COUNT(trip_id) AS trip_cnt
피벗할 컬럼을 명시해야 함
- FOR start_station_id IN (1006, 1007, 1008, 2494)

하지만 이 방법도 역시 start_station_id IN을 보면 컬럼을 직접 추가해야 하는 이슈 존재

Dynamic하게 Pivot 컬럼 생성하기(컬럼의 값이 정렬이 필요한 경우)

Lak Lakshmanan님의 글인 PIVOT in BigQuery을

1) 피벗할 컬럼의 고유값(DISTINCT) 추출

  DECLARE station_ids STRING;
  SET station_ids = (
      SELECT
        CONCAT('("', STRING_AGG(SAFE_CAST(start_station_id AS STRING), '", "'), '")')
      FROM (
        SELECT 
      DISTINCT start_station_id
        FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
        WHERE start_station_id IS NOT NULL 
        ORDER BY start_station_id
      )
  );

2) EXECUTE IMMEDIATE를 사용해 다이나믹하게 실행

  DECLARE station_ids STRING;
  SET station_ids = (
      SELECT
        CONCAT('("', STRING_AGG(SAFE_CAST(start_station_id AS STRING), '", "'), '")')
      FROM (
        SELECT 
      DISTINCT start_station_id
        FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
        WHERE start_station_id IS NOT NULL 
        ORDER BY start_station_id
      )
  );
  EXECUTE IMMEDIATE format("""
  WITH BASE AS (
  SELECT 
    DATE(start_time) AS start_date,
    start_station_id,
    trip_id
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
  WHERE 
    DATE(start_time) BETWEEN "2015-10-01" AND "2015-10-07"
    AND start_station_id IS NOT NULL
  )
  SELECT * 
  FROM base #1 from 절
  PIVOT
  (
    -- #2 집계 함수
    COUNT(trip_id) AS trip_cnt
    -- #3 피벗할 컬럼
    FOR SAFE_CAST(start_station_id AS STRING) in %s
  )
  ORDER BY start_date
  """, station_ids);

EXECUTE IMMEDIATE format을 사용하며, %s에 station_ids 주입
station_ids는 (“1001”, “1002”) 이런 형태로 . STRING이므로 FOR에 SAFE_CAST로 STRING으로 변환해서 쿼리 실행

10:28 절차 우측에 있는 결과 보기를 클릭하면 다음과 같이 나옴

이제 trip_cnt에 start_station_id가 정렬된 상태로 컬럼에 붙음

Dynamic하게 Pivot 컬럼 생성하기(컬럼의 값이 정렬이 필요없는 STRING인 경우)

위에 진행한 방법처럼 진행하되, ORDER BY가 없이 사용하면 됨

DECLARE station_ids STRING;
SET station_ids = (
  SELECT 
    CONCAT('("', STRING_AGG(DISTINCT SAFE_CAST(start_station_id AS STRING), '", "'), '")')
  FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
);

EXECUTE IMMEDIATE format("""
WITH BASE AS (
SELECT 
  DATE(start_time) AS start_date,
  start_station_id,
  trip_id
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
WHERE 
  DATE(start_time) BETWEEN "2015-10-01" AND "2015-10-07"
  AND start_station_id IS NOT NULL
)

SELECT * 
FROM base #1 from 절
PIVOT
(
  -- #2 집계 함수
  COUNT(trip_id) AS trip_cnt
  -- #3 피벗할 컬럼
  FOR SAFE_CAST(start_station_id AS STRING) in %s
)
ORDER BY start_date
""", station_ids);

쿼리를 실행하고 맨 아래에 있는 결과 보기를 클릭

컬럼을 확인해보면 trip_cnt_1006이 먼저 위치한 것을 볼 수 있음
- 즉, 컬럼의 순서를 제어하고 싶다면 Dynamic하게 Pivot 컬럼 생성하기(컬럼의 값이 정렬이 필요한 경우)의 방법을 따르면 됨

추가로 활용할 수 있는 곳

GA4, Firebase 데이터를 Flatten하는 경우 Dynamic하게 PIVOT
- BigQuery UNNEST, ARRAY, STRUCT 사용 방법의 응용에 있는 Firebase 쿼리를 더 유동적으로 수정할 수 있을듯

Reference

카일스쿨 유튜브 채널을 만들었습니다. 데이터 사이언스, 성장, 리더십, BigQuery 등을 이야기할 예정이니, 관심 있으시면 구독 부탁드립니다 :)

PM을 위한 데이터 리터러시 강의를 만들었습니다. 문제 정의, 지표, 실험 설계, 문화 만들기, 로그 설계, 회고 등을 담은 강의입니다

이 글이 도움이 되셨거나 다양한 의견이 있다면 댓글 부탁드립니다 :)

Buy me a coffee

PIVOT

PIVOT 문법

데이터

PIVOT 함수 없이 피벗하기

PIVOT 함수

Dynamic하게 Pivot 컬럼 생성하기(컬럼의 값이 정렬이 필요한 경우)

Dynamic하게 Pivot 컬럼 생성하기(컬럼의 값이 정렬이 필요없는 STRING인 경우)

추가로 활용할 수 있는 곳

Reference

Share this post