[SQL/BigQuery] Big Query PIVOT 사용법과 GROUP BY ALL, ANY_VALUE 함수 정리 - 돌멩이의 데이터 분석

💻본 포스팅은 [인프런] 카일스쿨의 BigQuery(SQL) 활용편을 기반으로
요약 및 실습한 기록을 정리했습니다.

---

데이터 PIVOT 이란?

(1) Data PIVOT 정의

• 특정 데이터를 기준(PIVOT) 삼아서, 그 축을 중심으로 데이터를 보기 좋게 정렬하는 과정

(2) PIVOT이 필요한 이유

• 사용자 로그 데이터는 보통 "하나의 행동 = 하나의 ROW"로 길게 쌓임
• 이 형태는 가독성이 낮고, 분석 시 불편함이 많음
• BigQuery에는 추출 할 수 있는 행에 제한이 존재하므로, PIVOT을 활용해 이를 회피할 수 있음
  • 실제로 사용자는 한 번의 클릭을 하지만, 그 행동 하나에 UNNEST 시, 10개 이상의 ROW가 생성 될 수 있음

---

예시를 통한 PIVOT 문법

(1) 로그 해석

• PIVOT은 당연하게도 데이터 로그 구조에 대한 완벽한 이해가 선행되어야함
• 하고자 하는 일 : 사용자들에게서 발생하는 모든 unique한 event를 기준으로 데이터를 pivot 하고자함

• 예시 : 배달 앱
  • event_date : 날짜
  • event_timestamp : 구체적 시간
  • event_name : 발생한 이벤트
  • event_params : 이벤트에 대한 여러 속성 (ARRAY)
    • ARRAY에 속한 값들은 각 이벤트에 대한 속성과 값을 나타낸다.
    • EX) click_cart가 발생한 화면 : food_detail 화면

(2) PIVOT 하기

• 상황에 따라 SUM, MAX, COUNT 등 다양한 집계 함수를 사용하여 PIVOT
  • 저작권 문제로 예시 데이터로 대체
• 예시) 표
  

  Jan	Food	100
  Jan	Toy	200
  Feb	Food	150
  Feb	Toy	300

  • 문제 : 판매 된 제품의 카테고리를 기준으로 월별 판매액 데이터를 만들어주세요

SELECT 
  category,
  -- (1) 기준 행 선택
  SUM(IF(month = 'Jan', sales, 0)) AS sales_jan,
  SUM(IF(month = 'Feb', sales, 0)) AS sales_feb
  -- (2) 월별 합계를 위해 SUM 함수 사용
  -- (3) IF문으로 해당 월이면 sales값, 아니면 0 반환
FROM sales_data
GROUP BY category;
-- (4) category 기준으로 그룹화

---

실습 중 기록해 둔 참고사항

(1) GROUP BY ALL

• GROUP BY ALL은 SELECT 문에 선택 된 모든 비집계 컬럼을 기준으로 그룹화 하는 문법이다

SELECT 
  event_date,
  event_timestamp,
  event_name,
  user_id,
  user_pseudo_id,
  MAX(IF(param.key = "firebase_screen", value, NULL)) AS firebase_screen,
FROM practice
CROSS JOIN UNNEST(event_params) AS param
GROUP BY ALL

• 여기서 내가 GROUP BY ALL을 활용한다면, event_date ~ user_pseudo_id 까지의 컬럼들을 기준으로 모두 그룹화
  • BigQuery 전용 문법

(2) ANY_VALUE

• `GROUP BY`를 통해 쓸 수 있는 집계함수 중 하나
  • NULL값을 제외하고 그룹화 될 값 중 랜덤으로 하나를 선택
• 다중 값이 존재하는 경우, MAX()나 MIN()으로 대체하는 것이 안전

집계함수에 대한 요약

함수	집계 방식	결정성	사용해도 되는 상황
ANY_VALUE()	그룹 내 임의의 값	❌ 비결정적	그룹 내 값이 모두 동일할 때
MAX() / MIN()	가장 큰/작은 값	✅ 결정적	다중 값 중 특정 기준이 필요할 때
ARRAY_AGG()	그룹 내 전체 값 리스트	✅ 결정적	그룹 내 모든 값 확인할 때

(3) AS `컬럼명`  관련 오류

• Syntax error: Unexpected integer literal "2023" at [36:53] /
  Syntax error: Unexpected string literal "2023-05-01" at [36:53]
  • 컬럼명을 숫자나 한글로 지정할 때 발생하는 오류
  • " " / 이나 ' ' 으로 묶어도 동일하게 에러 발생
    • ` `(Backtick)으로 묶어주면 오류 해결