[인프런 빅쿼리 빠짝스터디] 1주차 BigQuery ARRAY, STRUCT, PIVOT 테이블, 퍼널 분석

ARRAY ( 배열 )

여러 "동일한" 타입의 데이터를 하나의 컬럼으로 저장하는 데이터 타입

- ARRAY ( 배열 ) 생성하기

1. 대괄호 [ ] 사용하기

SELECT 
  [0, 1, 2, 3] AS number
UNION ALL
SELECT 
  [1, 3, 5, 7]
UNION ALL
SELECT 
  [2, 4, 6, 8];

※ UNION
- JOIN 과는 다르게 테이블 아래에 다른 테이블 값을 붙이는 함수

2. ARRAY<자료형>[ 데이터 ]사용하기

SELECT
  ARRAY<INT64>[0, 1, 2, 3] AS number;

3. 배열 생성 함수 사용

• GENERATE_DATE_ARRAY(start_date, end_date, step)
• GANERATE_ARRAY() 등등

4. ARRAY_AVG 함수 사용

• 여러 결과를 마지막에 배열로 저장하고 싶은 경우

- ARRAY ( 배열 )의 데이터 접근하기

1. OFFSET

• 배열_컬럼[OFFSET(숫자)] 
• 숫자에 들어가는 값은 0부터 시작 -> 파이썬 리스트 인덱스와 같은 개념

2. ORDINAL

• 배열_컬럼[ORDINAL(숫자)]
• 숫자에 들어가는 값은 1부터 시작 -> 흔히 우리가 알고 있는 ~~번째 숫자 개념

3. SAFE

• OFFSET이나 ORDINAL 사용시 배열의 길이보다 큰 값을 지정하면 오류가 발생하기 때문에 이를 방지하기 위해 함수 앞에 "SAFE_" 를 추가 함으로써 오류 방지

---

STRUCT ( 구조체 )

여러 "서로 다른" 타입의 데이터를 하나의 컬럼으로 저장하는 데이터 타입

- STRUCT ( 구조체 ) 생성하기

1. 소괄호 ( ) 사용하기

• 소괄호 사용시 이름이 지정되어 있지 않음 ( 컬럼이름.field_1, 컬럼이름.field_2, 컬럼이름.field_3 .... 이런식으로 지정됨 )  

SELECT
  (1, 2, 3) AS struct_test;

2. STRUCT<자료형>(데이터)

• STRUCT<key이름 자료형>(데이터)

SELECT
  STRUCT<one INT64, two INT64, hi STRING>(1, 2, 'HI') AS struct_test;

- STRUCT ( 구조체 ) 값에 접근하기

• STRUCT컬럼.key이름 으로 접근 가능

---

UNNEST -> 데이터 평면화

쉽게 생각해서 바구니 ( 배열 ) 에 있는 과일 ( 데이터 ) 을 모두 다 꺼내는 과정 = UNNEST = 평면화

- UNNEST 쿼리 문법

-- array_test 컬럼이 ARRAY타입이라고 가정
SELECT
  array_test,
  name
FROM Table_A AS a
CROSS JOIN UNNEST(array_test) AS name;

-- CROSS JOIN을 생략하고 ,로 대체가능

SELECT
  array_test,
  name
FROM Table_A AS a, UNNEST(array_test) AS name;

---

PIVOT

- PIVOT 작업이 필요한 이유

• 성능
  • 다른 방법으로 처리해도 되지만 ROW가 많아질 경우 느려짐
  • 미리 데이터를 가공해서 ROW를 줄임으로써 네트워크, 데이터 처리 비용 절감의 효과를 볼 수 있음 -> 결국 데이터를 처리하는 작업은 돈을 얼마나 줄여서 사용할 수 있느냐가 중요하다고 생각
• 사용이 쉬움
  • 값을 찾기가 쉬워짐 WHERE 조건 하나로 그 값에 대한 정보를 쉽게 볼 수 있음
• 데이터 시각화 도구에서 PIVOT한 형태를 지원

- PIVOT 테이블로 변경하기

• 기본적으로 MAX ( SUM, AVG, MIN 등등 ), IF, GROUP BY 를 사용하여 PIVOT 테이블로 변경시킨다
• PIVOT 할 때 모든 값이 같은 경우 MAX를 사용하거나 ANY_VALUE를 사용

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	subject	score
철수	수학	100
철수	영어	90
철수	국어	80
민지	수학	75
민지	영어	95
민지	국어	100

student	수학	영어	국어
철수	100	90	80
민지	75	95	100

---

퍼널 ( Funnel )

출처 : https://blog.datarize.ai/%ED%8D%BC%EB%84%90%EB%B6%84%EC%84%9D

- 하나의 퍼널에서 다음 퍼널로 얼마나 전환되는가를 파악

- 퍼널은 목적에 맞게 구성할 수 있음

- 특정 페이지를 하나의 퍼널로 볼 수 있고, 특정 페이지의 묶음을 하나의 퍼널로 정의 할 수도 있음.

- 대표적인 퍼널 : 회원 가입, 온보딩, 결제 등등

- 퍼널 ( Funnel ) 의 종류

• Open 퍼널
  • 특정 퍼널이 존재하는지 확인 후 집계
• Closed 퍼널
  • 정해놓은 퍼널의 순서를 모두 거쳐야 집계

---

느낀 점

데이터 분야를 접했을 때 가장 막막했던 부분이 어떻게 접근해야되는지, 실무에서 어떤 데이터를 사용해서 이 작업을 하는지가 가장 답답했던 부분이였는데 실무에서 주로 사용되는 GA4, Firebase 로그와 유사한 형태의 데이터로 실습을 하니 이부분이 조금이나마 해소될 수 있었던 부분이였고 새로운 것들을 배워나갈 때마다 다음엔 어떤 걸 배울까를 기대할 수있는 강의였다.

---

참고

- 인프런 BigQuery(SQL) 활용편(퍼널분석, 리텐션 분석)