인프런 커뮤니티 질문&답변

목차

1. 과제별 작성 쿼리

2. 쿼리 작성 후 느낀점

과제별 작성 쿼리

(1) ARRAY·STRUCT

## 연습문제 1번

-- select

-- title,

-- genre

-- from advanced.array_exercises

-- cross join unnest(genres) as genre

## 연습문제 2번

-- select

-- title,

-- actor.actor,

-- actor.character,

-- from advanced.array_exercises

-- cross join unnest(actors) as actor

## 연습문제 3번

-- select

-- title,

-- actor.actor,

-- actor.character,

-- genre

-- from advanced.array_exercises

-- cross join unnest (actors) as actor

-- cross join unnest (genres) as genre

## 연습문제 4번

-- select

-- user_id,

-- event_date,

-- event_name,

-- user_pseudo_id,

-- event_param.key,

-- event_param.value,

-- event_param.value.int_value

-- from advanced.app_logs

-- cross join unnest(event_params) as event_param

-- where

-- event_date = '2022-08-01'

PIVOT

## 연습문제 1번

-- select

-- order_date,

-- sum(if(user_id=1, amount, 0)) as user_1,

-- sum(if(user_id=1, amount, 0)) as user_2,

-- sum(if(user_id=1, amount, 0)) as user_3,

-- from advanced.orders

-- group by

-- order_date

-- order by

-- order_date asc

## 연습문제 2번

-- select

-- user_id,

-- sum(if(order_date='2023-05-01', amount, 0)) as 2023-05-01,

-- sum(if(order_date='2023-05-02', amount, 0)) as 2023-05-02,

-- sum(if(order_date='2023-05-03', amount, 0)) as 2023-05-03,

-- sum(if(order_date='2023-05-04', amount, 0)) as 2023-05-04,

-- sum(if(order_date='2023-05-05', amount, 0)) as 2023-05-05

-- from advanced.orders

-- group by

-- user_id

## 연습문제 3번

-- select

-- user_id,

-- max(if(order_date = '2023-05-01', 1, 0)) as 2023-05-01,

-- max(if(order_date = '2023-05-02', 1, 0)) as 2023-05-02,

-- max(if(order_date = '2023-05-03', 1, 0)) as 2023-05-03,

-- max(if(order_date = '2023-05-04', 1, 0)) as 2023-05-04,

-- max(if(order_date = '2023-05-05', 1, 0)) as 2023-05-05

-- from advanced.orders

-- group by

-- user_id

## 앱 로그 데이터 배열 PIVOT

-- with base as (

-- select

-- user_id,

-- event_date,

-- event_name,

-- user_pseudo_id,

-- event_param.key as key,

-- event_param.value.string_value as string_value,

-- event_param.value.int_value as int_value

-- from advanced.app_logs

-- cross join unnest(event_params) as event_param

-- where

-- event_date = '2022-08-01' and

-- event_name = 'click_cart'

-- )

-- select

-- user_id,

-- event_date,

-- event_name,

-- user_pseudo_id,

-- max(if(key = 'firebase_screen', string_value, null)) as firebase_screen,

-- max(if(key = 'food_id', int_value, null)) as food_id,

-- max(if(key = 'session_id', string_value, null)) as session_id,

-- from base

-- group by all

퍼널

## step1 : 퍼널정의, 유저 집계 방식 정하기

## step2 : 데이터 확인

-- select

-- *,

-- event_param.key,

-- event_param.value.string_value,

-- event_param.value.int_value,

-- from advanced.app_logs

-- cross join unnest(event_params) as event_param

-- where

-- event_date between '2022-08-01' and '2022-08-18'

-- limit 100

## step3 : pivot 해서 concat 하기 좋은 형태로 만들기

with base as (

select

*,

max(if(event_param.key = 'firebase_screen', event_param.value.string_value, null)) as firebase_screen,

max(if(event_param.key = 'food_id', event_param.value.int_value, null)) as food_id,

max(if(event_param.key = 'session_id', event_param.value.string_value, null)) as session_id

from advanced.app_logs

cross join unnest(event_params) as event_param

where

event_date between '2022-08-01' and '2022-08-18'

group by all

)

## step4 : concat해서 퍼널 컬럼 정의하기

, base2 as (

select

*,

concat(event_name, '-', firebase_screen) as event_name_with_screen

from base

)

## step5 : 퍼널별 유저수 쿼리 작성 I step_number, cnt 컬럼 생성

-- select

-- event_name_with_screen,

-- case

-- when(event_name_with_screen = 'screen_view-welcome') then 1

-- when(event_name_with_screen = 'screen_view-home') then 2

-- when(event_name_with_screen = 'screen_view-food_category') then 3

-- when(event_name_with_screen = 'screen_view-restaurant') then 4

-- when(event_name_with_screen = 'screen_view-cart') then 5

-- when(event_name_with_screen = 'click_payment-cart') then 6

-- else null

-- end as step_number,

-- count(distinct user_pseudo_id) as cnt

-- from base2

-- group by

-- 1

-- having

-- step_number is not null

## step6 : 일자별 퍼널 쿼리 작성

select

event_date,

event_name_with_screen,

case

when(event_name_with_screen = 'screen_view-welcome') then 1

when(event_name_with_screen = 'screen_view-home') then 2

when(event_name_with_screen = 'screen_view-food_category') then 3

when(event_name_with_screen = 'screen_view-restaurant') then 4

when(event_name_with_screen = 'screen_view-cart') then 5

when(event_name_with_screen = 'click_payment-cart') then 6

else null

end as step_number,

count(distinct user_pseudo_id) as cnt

from base2

group by all

having

step_number is not null

order by

event_date

쿼리 작성 후 느낀점

(1) ARRAY·STRUCT I 쿼리를 풀고난 이후 느낀점

1. '~별'이라고 해서 무조건 GROUP BY는 아니다

ARRAY 또는 STRUCT 같은 구조체 타입 컬럼을 만나기 전에는 '~별'이라는 텍스트가 있을 때 GROUP BY를 떠올렸습니다.

그러나 구조체 타입 컬럼을 다루고난 이후, 생각이 달라졌습니다.

비구조체 컬럼을 구조체 컬럼과 함께 출력할 때는 '~별'을 GROUP BY로 출력할 수 없다는 걸 알았습니다.

2. CROSS JOIN을 2개 이상 사용할 때는

CROSS JOIN을 2개 이상 입력할 때는 쉼표를 사용하지 않는다는 걸 알았습니다.

다른 구간에서 요소를 2개 이상 입력할 때는 쉼표를 사용해서 요소를 분리 했었는데, CROSS JOIN은 아니였습니다.

CROSS JOIN을 2개 이상 사용할 때 쉼표를 쓰면 다음과 같은 오류가 발생하는 걸 알았습니다 :

--> Syntax error: Unexpected keyword CROSS at [24:1]

(2) PIVOT I 쿼리를 풀고난 이후 느낀점

1. 날짜 데이터와 다르게 일반 숫자데이터는 따옴표를 쓰지 않는다

EX)

if(user_id=1, amount, 0) --> O

if(user_id='1', amount, 0) --> X

+

if(user_id='2024-10-22', amount, 0) --> O

2. 컬럼명에 대시(또는 한글)를 사용할 때는 따옴표가 아닌 백틱으로 컬럼명을 감싼다

대시를 컬럼명으로 할 때는 백틱을 사용.

as 2023-05-01 --> O

as '2023-05-01' --> X

+

as 빠짝스터디 --> O

as '빠짝스터디' --> X

3. 일반 테이블을 피봇테이블로 만들 때 고려해 봐야 할 2가지

1. 행에 2개 이상 중복이 있는지 확인한다.

2. 2개 이상 중복이 있지만, 피봇테이블로 변경 했을 때 효율적인지 생각해 본다.

위 2개 조건을 모두 충족하는 컬럼을 피봇테이블 만들 때 기준 컬럼으로 삼음.

EX)

*'앱 로그 데이터 배열 PIVOT 하기' 테이블 참고

student 열

중복 있음 (A, A, A, B, B, B)

기준 열로 삼으면 → 각 학생의 모든 과목 점수를 한눈에 볼 수 있어서 효율적!

subject 열

중복 있음 (수학, 영어, 과학이 2번씩)

기준 열로 삼으면 → 과목별로 학생들의 점수를 볼 수는 있지만, 한 학생의 전체 성적을 보기는 어려움

score 열

중복이 없음.

기준 열로 삼으면 → 같은 점수끼리 모이겠지만, 효율이 떨어짐.

(3) 퍼널 I 쿼리를 풀고난 이후 느낀점

1. concat 할 때 따옴표 사용 주의

처음 concat 할 때 다음과 같이 입력했습니다.

concat(event_name, '-', 'firebase_screen')

'firebase_screen'라고 작성했었는데, 이렇게 하니깐 firebase_screen 내에 있는 데이터랑 concat 된 게 아니라 'firebase_screen'라는 문자 자체랑 concat이 되어서 혼란스러웠습니다.

concat 내에서는 따옴표를 쓰지 않고, 컬럼끼리 연결해야겠다는 걸 알게 됐습니다.