Bigquery12. PIVOT

이유민·2025년 2월 12일

BigQuery

목록 보기

14/15

PIVOT

	$\to$

PIVOT은 다음과 같이 특정 축을 중심으로 회전시키는 것을 말함
PIVOT이 왜 필요한가?
- Row 수를 줄일 수 있음
  - 보통 쿼리의 결과는 row가 길게 늘어진 형태로 저장됨.
  - 이때 PIVOT해서 데이터를 저장하면 row를 줄일 수 있음
- 데이터 시각화에 편리함
  - 또한 PIVOT한 데이터의 형태가 시각화하기 편함

PIVOT 쿼리 과정

student	subject	score
A	수학	85
A	영어	90
A	과학	70
B	수학	75
B	영어	95
B	과학	80

SELECT
	student,
    IF(subject = "수학", score, NULL) AS 수학,   # IF조건문: IF(조건기준, TRUE일 때 출력값, FALSE일 때 출력값)
    IF(subject = "수학", score, NULL) AS 수학,
    IF(subject = "영어", score, NULL) AS 영어,
    IF(subject = "과학", score, NULL) AS 과학
FROM table

↓

student	수학	영어	과학
A	85	NULL	NULL
A	NULL	90	NULL
A	NULL	NULL	70
B	75	NULL	NULL
B	NULL	95	NULL
B	NULL	NULL	80

SELECT
	student, 
    MAX(IF(subject = "수학", score, NULL)) AS 수학,
    MAX(IF(subject = "영어", score, NULL)) AS 영어,
    MAX(IF(subject = "과학", score, NULL)) AS 과학
FROM table
GROUP BY
	student

↓

student	수학	영어	과학
A	85	90	70
B	75	95	80

PIVOT하면서 SUM, COUNT 같은 연산도 가능

SUM(IF(조건, TRUE값, FALSE값))
 COUNT(IF(조건, TRUE값, FALSE값))

PIVOT할 때 모든 값이 같거나, 연산 가능한 값이 1개인 경우 => MAX or ANY_VALUE 사용
- MAX
  - 모든 값이 같은 경우: 모든 값이 최댓값
  - 1개 빼고 나머지 값이 NULL인 경우: NULL은 연산이 안되므로 1개인 값을 MAX로 출력
- ANY_VALUE: 집계될 값 중 1개를 랜덤으로 출력
  - 모든 값이 같은 경우: 랜덤으로 출력해도 동일한 값 나옴
  - 1개 빼고 나머지 값이 NULL인 경우: NULL은 연산이 안되므로 1개인 값을 출력

연습문제

Table1.

위 테이블에서 user_id(유저)별로 amount(주문금액)의 합계를 pivot해주세요. order_date를 행으로, user_id를 열(Column)으로 만들어주세요.
- 기대하는 output:
  order_date | user_1 | user_2 | user_3
- 풀이방법1) 집계한 후 PIVOT
- 풀이방법2) PIVOT하면서 바로 집계
```
- 이 경우, if절 이후 true인 값이 각 1개씩이고 나머지는 모두 0이므로 MAX나 ANY_VALUE로 집계해도 무방함
```

쿼리 실행은 항상 Row 기준으로 출력된다는 것을 기억!!

order_date(날짜) 별로 유저들의 주문금액의 합계를 piovt해주세요. user_id를 행으로, order_date를 열(Column)으로 만들어야 합니다.
- 기대하는 output:
  user_id | 2023-05-01 | 2023-05-02 | 2023-05-03 | 2023-05-04 | 2023-05-05
- 컬럼의 이름은 영어 외로 지정할 때는 backtick( ` )을 사용해야 함
유저 별(user_id), 날짜 별(order_date)로 주문 횟수를 구해주세요. user_id를 행으로 order_date를 열로 만들어주세요.
- 문제의도:
  - IF문 안에 amount 대신 1로 표현
  - IF문 안에 TRUE값을 어떤 컬럼이 아니라 1로도 표현할 수 있음

Table2. 앱 로그 데이터 pivot

위 테이블에서 user_id 32888이 카트 추가하기(click_cart)를 누를 때 어떤 음식(food_id)로 담았나요?
- 기대하는 output :
  user_id | user_pseudo_id | event_date | event_name | firebase_screen | food_id | session_id
- key 컬럼의 값들을 column으로 만들고, string_value나 int_value를 각 column의 값으로 설정하는 것이 필요
- ARRAY, STRUCT 데이터 => UNNEST => PIVOT 해야 함

1) 데이터 탐색

비용 문제로 날짜 필터링과 LIMIT 걸고 탐색해야 함

2) 우선 event_params에 UNNEST가 필요해보임

아직 쿼리 작성 시도 중이므로 날짜 필터링과 limit은 풀지 않음
unnest 후 event_params 컬럼은 궁금한 정보가 아니므로 EXCEPT 처리

3) 컬럼 key를 PIVOT하여 각 'firebase_screen', 'food_id', 'session_id'를 컬럼으로 만들기

2)의 결과를 보면,
firebase_screen은 string_value에 값을 갖고 int_value는 null
food_id는 int_value에 값을 갖고 string_value는 null
session_id는 string_value에 값을 갖고 int_value는 null을 가짐
=> 'firebase_screen', 'food_id', 'session_id' 각 컬럼에 대한 값으로 string_value, int_value, string_value 값을 할당해야 함을 알 수 있음