1. Pivoting

SQL을 활용하다보면, Pivoting이 필요한 시점이 있다. 대표적으로, 한 칼럼안에 있는 다중 ROW 값들을 다중 Column 으로 전환하고 싶을 때 이다.

예를 들어, 우리 서비스에서 발생한 모든 Event LOG 를 파악하고 싶다. Event 라는 칼럼 안에 500개가 존재하는데 하나하나 모두 CASE WHEN 문으로 활용해서 할수 없기 때문이다. GROUP BY COUNT도 유저 별로 다중 RAW를 만드는 테이블 형식 일뿐, 칼럼형태론 변환하지 못한다.

Pandas 에서 Pivot 하는 함수가 존재하지만, Bigquery 에서도 존재한다. 빅쿼리에서 제공하는 Sample Data를 기준으로 따라가보자.

## SAMPLE : PIVOTING 할 칼럼을 정함. 열로 분할할 것들

# 1. station_ids 라는 칼럼을 Pivoting 할 것이다. SET station_ids 를 통해 statioN_ids 의 값들을 변수화 한 것이다.
# 2. EX : for 문의 List 안에 station_ids 를 담았다고 생각하면 된다.
# 3. 여기서, 만약 이벤트 로그라면 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
    )
);

# 4. Base WITH 절은 내가 피봇팅 하고 싶은 원천 테이블이다.
# 해당 쿼리를 빅쿼리에서 실행시 start_date 별 station_ids(자전거 대여소 번호)와 trip_id가 RAW Data로 생성된다.
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-08" AND "2015-10-15"
  AND start_station_id IS NOT NULL -- 있어도 그만 없어도 그만이긴 하나, null 제외
)


# 5. SELECT 문으로 BASE 데이터를 가져온다.
# 6. 그리고, PIVOT 함수를 활용한다. station_id 가 존재하는 것들만 칼럼으로 만들고, station_id 에 존재하는 trip_id 를 COUNT 하는 것이다.
# 7. 꿀팁은 EVENT LOG등을 활용할 때, 이 PIVOT 함수는 빛을 발휘한다. 
SELECT * 
FROM base # 4 번의 FROM 절 BASE RAW DATA 
PIVOT (
  -- #2 집계 함수
  COUNT(trip_id) AS trip_cnt #수셈
  -- #3 피벗할 컬럼
  FOR start_station_id IN (1006, 1007, 1008, 2494) # 해당 칼럼에 대해서만 PIVOTING
)
ORDER BY start_date

빅쿼리에만 제공하는 기능이니, 다른 DBMS 툴에서는 사용이 불가할 수 있다. 이럴땐, Pandas 가져가서 Pivot 하는게 가장 나은 결과를 기대할 수 있다.

예시 함수 ->

df.pivot(index='user_id',columns='event', values='cnt']))