PIVOT

PIVOT은 다음과 같은 작업을 의미합니다.

많은 ROW에 있는 데이터를 Column으로 옮길 수 있으며, 데이터 시각화 도구들에서 피벗해야 데이터 시각화를 수월하게 할 수 있습니다.

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);

블로그에 작성한 BigQuery PIVOT 사용법(Pivot Operator) (opens new window) 글로 내용을 대체합니다.