[빠짝스터디 1주차 과제] ARRAY, STRUCT, PIVOT, 퍼널 쿼리 연습 문제 (재미난 흐름)
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
이은경 - 빠짝스터디 1주차 과제 제출합니다. -- ARRAY_STRUCT 연습 문제 -- 연습문제 데이터 생성 CREATE OR REPLACE TABLE advanced.array_exercises as SELECT movie_id, title, actors, genres FROM( SELECT 1 as movie_id, 'Avengers:Endgame' as title, ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Robert Downey Jr.','Tony Stark'), STRUCT('Chris Evans','Steve Rogers') ] as actors, ARRAY<STRING>['Action','Adventure','Drama'] as genres UNION ALL SELECT 2, 'Inception', ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Leonardo Dicaprio','Cobb'), STRUCT('Joseph Gordon-Levitt','Arthur') ], ARRAY<STRING>['Action','Adventure','Sci-Fi'] UNION ALL SELECT 3, 'The Dark Knight', ARRAY<STRUCT<actor STRING, character STRING>>[ STRUCT('Christian Bale','Bruce Wayne'), STRUCT('Heath Ledger','Joker') ], ARRAY<STRING>['Action','Crime','Drama'] ); -- 연습문제 1) array_exercises table에서 title 별로 영화 genres를 UNNEST해서 보여주세요. # UNNEST(ARRAY_COLUMN) AS 새로운 이름 # SELECT 절에서 새로운 이름으로 사용한다. 기존의 ARRAY_COLUMN은 사용하지 않는다. SELECT a.title, genre FROM advanced.array_exercises as a CROSS JOIN UNNEST(genres) as genre; -- 연습문제 2) array_exercises table에서 title 별로 배우(actor)와 배역(character)을 UNNEST해서 보여주세요. 배우와 배역은 별도의 column으로 나와야 합니다. SELECT a.title, person.actor as actor, person.character as character FROM advanced.array_exercises as a CROSS JOIN UNNEST(actors) as person; -- 연습문제 3) array_exercises table에서 title 별로 배우(actor), 배역(character), 장르를 한 row에 표시되도록 출력하세요. # CROSS JOIN 하면 데이터의 중복이 어느 정도 발생, 어쩔 수 없는 이슈이다. SELECT a.title, person.actor as actor, person.character as character, genre FROM advanced.array_exercises as a CROSS JOIN UNNEST(actors) as person CROSS JOIN UNNEST(genres) as genre; -- 3)번 문제 관련 추가 # 3) 결과를 기준으로, 조건문을 사용하고 싶다. # "WHERE"를 바로 사용하면 error 발생 : 실행 순서가 FROM -> JOIN -> SELECT # actors -> UNNEST(actors) : STRUCT<actor, character> # genres -> UNNEST(genres) : STRING SELECT a.title, person.actor as actor, person.character as character, genre FROM advanced.array_exercises as a CROSS JOIN UNNEST(actors) as person CROSS JOIN UNNEST(genres) as genre WHERE person.actor='Chris Evans' -- 그냥 actor 사용하면 error 발생함 and genre='Action'; -- 연습문제 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 SELECT a.user_id, a.event_date, a.event_name, a.user_pseudo_id, b.key as key, b.value.string_value as string_value, b.value.int_value as int_value FROM advanced.app_log as a CROSS JOIN UNNEST(event_params) as b; -- 4)번 문제 관련, 추가 사항(일 별, 사용자 수 집계) WITH base as( SELECT a.user_id, a.event_date, a.event_name, a.user_pseudo_id, b.key as key, b.value.string_value as string_value, b.value.int_value as int_value FROM advanced.app_log as a CROSS JOIN UNNEST(event_params) as b ) SELECT event_date,count(user_id) as cnt FROM base GROUP BY event_date ORDER BY event_date; ############################# -- PIVOT 연습문제 -- 연습문제 1) orders 테이블에서 유저(user_id) 별로 주문 금액(amount)의 합게를 PIVOT 해주세요. -- 날짜(order_date)를 행(Row)으로, user_id를 열로 만들어야 합니다. SELECT order_date, MAX(IF(user_id=1,amount,0)) as user_1, MAX(IF(user_id=2,amount,0)) as user_2, MAX(IF(user_id=3,amount,0)) as user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date; -- 연습문제 2) orders 테이블에서 날짜(order_date) 별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. -- user_id를 행으로, order_date를 열으로 만들어야 합니다. # column name을 어떻게 지정? -- backtick(`) 사용 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 ORDER BY user_id; -- 연습문제 3) orders 테이블에서 사용자(user_id) 별, 날짜(order_date) 별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행으로, order_date를 열로 만들고 주문을 많이 해도 1로 처리합니다. # column name을 어떻게 지정? (2번 문제와 연관) -- backtick(`) 사용 SELECT user_id, IF(SUM(IF(order_date='2023-05-01',amount,0))>0,1,0) as `2023-05-01`, IF(SUM(IF(order_date='2023-05-02',amount,0))>0,1,0) as `2023-05-02`, IF(SUM(IF(order_date='2023-05-03',amount,0))>0,1,0) as `2023-05-03`, IF(SUM(IF(order_date='2023-05-04',amount,0))>0,1,0) as `2023-05-04`, IF(SUM(IF(order_date='2023-05-05',amount,0))>0,1,0) as `2023-05-05` FROM advanced.orders GROUP BY user_id ORDER BY user_id; -- 연습문제 3번 다른 풀이 -- 특정 column 대신 "1"을 사용할 수 있다.(유무에 따라서) -- 만약, 횟수를 구해야 할 경우에는 MAX대신 SUM을 사용하면 된다. 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 ORDER BY user_id; -- 연습문제 4) 앱 로그 데이터 배열 PIVOT 하기 WITH example as ( SELECT a.user_id, a.event_date, a.event_timestamp, a.event_name, a.user_pseudo_id, b.key as key, b.value.string_value as string_value, b.value.int_value as int_value FROM advanced.app_log as a CROSS JOIN UNNEST(event_params) as b WHERE event_date='2022-08-01' ) SELECT user_id, event_date, event_timestamp, event_name, user_pseudo_id, ANY_VALUE(IF(key='firebase_screen',string_value,NULL)) as firebase_screen, ANY_VALUE(IF(key='food_id',int_value,NULL)) as food_id, ANY_VALUE(IF(key='session_id',string_value,NULL)) as session_id FROM example GROUP BY ALL ORDER BY event_date, event_name; -- 퍼널 SQL 연습문제 -- 데이터 PIVOT 한 형태로 변환(시각화 용이) # with 구문으로 만든 데이터들을 저장하는 방법은...? with base_data as( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id,platform, MAX(IF(event_params.key="firebase_screen",event_params.value. string_value,NULL)) as firebase_screen, MAX(IF(event_params.key="food_id",event_params.value.int_value,NULL)) as food_id, MAX(IF(event_params.key="session_id",event_params.value.string_value,NULL)) as session_id FROM `advanced.app_log` CROSS JOIN UNNEST(event_params) as event_params WHERE event_date between '2022-08-01' and '2022-08-18' GROUP BY ALL ), filter_event_and_concat_event_and_screen as( -- event_name + screen (필요한 이벤트만 가져올 필요가 있음) SELECT * EXCEPT(event_name, firebase_screen, food_id, event_timestamp), CONCAT(event_name,"-", firebase_screen) as event_name_with_screen, datetime(timestamp_micros(event_timestamp),"Asia/Seoul") as event_datetime FROM base_data WHERE event_name in ("screen_view","click_payment") ), funnel_data as( 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 filter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number is not null ORDER BY event_date, step_number ) SELECT event_date, MAX(IF(step_number=1,cnt,NULL)) as `screen_view-welcome`, MAX(IF(step_number=2,cnt,NULL)) as `screen_view-home`, MAX(IF(step_number=3,cnt,NULL)) as `screen_view-food_category`, MAX(IF(step_number=4,cnt,NULL)) as `screen_view-restaurant`, MAX(IF(step_number=5,cnt,NULL)) as `screen_view-cart`, MAX(IF(step_number=6,cnt,NULL)) as `click_payment-cart` FROM funnel_data GROUP BY ALL ORDER BY event_date; 느낀 점 새로운 자료형은 늘 어렵다... 퍼널 분석의 큰 흐름을 배울 수 있어서 좋았습니다! 의사 결정 관련 부분은 정말 많은 도움이 될 것 같습니다. connected sheet 기능 신기하네요..
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery