172만명의 커뮤니티!! 함께 토론해봐요.
해결됨
김영한의 실전 자바 - 고급 2편, I/O, 네트워크, 리플렉션
File, Files의 4페이지 밑에서 세 번째 줄 Path newFile = Paths.get("temp/newExample.txt"); 교안에는 위와 같이 나와있는데, 강의 영상에서는 아래와 같이 코드를 작성해주셨습니다. Path newFile = Path.of("temp/newExample.txt"); 두 코드는 같은 역할을 하는 것인가요??
미해결
김영한의 실전 자바 - 고급 2편, I/O, 네트워크, 리플렉션
1. 1byte 씩 전송하면 당연히 시스템콜이 많이 작동하므로 속도가 느리다 2. 8바이트씩 전송하면 시스템 콜에서 어차피 8바이트씩 전송하므로 시스템 콜을 적게 호출 할 수 있다 [질문] 한번에 전송해도 시스템콜에서 8kb바이트씩 보낼텐데 시스템콜 요청하는 횟수가 2번과 비슷할텐데 왜 버퍼를 쓰는게 더 빠르게 나오는 걸까요? 혹시 이게 한번 전송하면 한꺼번에 시스템콜 직전까지 전달하므로 병목현상 뭐 그런걸까요? ++ 강의에서 한번에 써도 os상에서 8kb씩 보내신다고 했던것 같은데 맞나요? 메모리에 한번에 올려서 시스템콜에 가져다 주는 것이 부하가 걸려서 8kb씩 버퍼로 하는 것보다 더 느린걸까요?
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
[ARRAY, STRUCT] array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, #genres, genre FROM `advanced.array_excercises` ae cross join unnest(genres) as genre; 2. array_exercies 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요 배우와 배역은 별도의 컬럼으로 나와야 합니다. select title, actor.actor, actor.character from `advanced.array_excercises` cross join unnest(actors) as actor; array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. select title, actor.actor, actor.character, genre from `advanced.array_excercises` cross join unnest(actors) as actor cross join unnest(genres) as genre; 앱 로그 데이터(app_logs) 배열 풀기 select user_id, event_date, event_name, user_pseudo_id, params.key, params.value.string_value as string_value, params.value.int_value as int_value from `advanced.app_logs` cross join unnest(event_params) as params where event_date = "2022-08-01 [PIVOT] orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. select order_date, # amount의 합 sum(if(user_id=1, amount, 0)) as user_1, sum(if(user_id=2, amount, 0)) as user_2, sum(if(user_id=3, amount, 0)) as user_3 FROM `advanced.orders` group by order_date order by order_date; orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 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; orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. 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; user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? with base as ( select -- * EXCEPT(event_params), # * except(컬럼) :컬럼을 제외하고 모두 다 보여줘 -- param event_date, event_timestamp, event_name, user_id, user_pseudo_id, max(if(param.key = "firebase_screen", param.value.string_value, null)) as firebase_screen, -- max(if(param.key = "food_id", param.value.string_value, null)) as food_id, max(if(param.key = "food_id", param.value.int_value, null)) as food_id, max(if(param.key = "session_id", param.value.string_value, null)) as session_id from `advanced.app_logs` cross join unnest(event_params) as param group by all ) select user_id, event_date, count(user_id) as user_cnt, food_id from base where user_id = 32888 and event_name = "click_cart" group by all [퍼널분석] with base as ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, max(if(event_param.key = "firebase_screen", event_param.value.string_value, null)) as firebase_screen, 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 ), filter_event as( select * except(event_name, firebase_screen, event_timestamp), concat(event_name, "-", firebase_screen) as event_name_with_screen, DATETIME(timestamp_micros(event_timestamp),'Asia/Seoul')AS event_datetime from base where event_name IN("screen_view", "click_payment") ), screen_view 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 group by all having step_number is not null order by event_date ) select event_date, max(if(event_name_with_screen = "screen_view-welcome", cnt, null)) as screen_view_welcome, max(if(event_name_with_screen = "screen_view-home", cnt, null)) as screen_view_home, max(if(event_name_with_screen = "screen_view-food_category", cnt, null)) as screen_view_food_category, max(if(event_name_with_screen = "screen_view-restaurant", cnt, null)) as screen_view_restaurant, max(if(event_name_with_screen = "screen_view-cart", cnt, null)) as screen_view_cart, max(if(event_name_with_screen = "click_payment-cart", cnt, null)) as click_payment_cart from screen_view group by all order by event_date
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
--연습문제 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 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 select title , genres_new from advanced,.array_exercise AS a, UNNEST(genres) as genres_new -- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 select title , actors_new.actor , actors_new.character from advanced,.array_exercise AS a, UNNEST(actors) as actors_new --3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 --방법 1 with gen as ( select title , genres_new from advanced,.array_exercise AS a, UNNEST(genres) as genres_new ) , actors as ( select title , actors_new.actor , actors_new.character from advanced,.array_exercise AS a, UNNEST(actors) as actors_new ) select from gen g join actors a on g.title=a.title --방법 2 select title , actors_new.actor , actors_new.character , genre_new from advanced,.array_exercise AS a, UNNEST(actors) as actors_new, UNNEST(genres) as genre_new --방법 3 select title , actors_new.actor , actors_new.character , genre_new from advanced.array_exercise cross join UNNEST(actors) as actors_new cross join UNNEST(genres) as genre_new where actors_new.actor 로 조건을 걸어야함 --actor(키값바로) 또는 actors_new로는 안된다 actors_new는 스트럭트 구조이고 actor는 이전 값임 --4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 --하루 사용자 집계, 어떤 이벤트가 있는가? select user_id , event_date , event_name , user_pseudo_id , event_component.key , event_component.value.string_value , event_component.value.int_value from app_logs as app, UNNEST(event_pharams) as event_component where event_date = '2022-08-11' --피봇 과제 --1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 with raw as ( select user_id , order_date , sum(amount) as amounts from orders ) SELECT order_date , MAX(IF(user_id=1, amounts, NULL)) AS user_1 , MAX(IF(user_id=2, amounts, NULL)) AS user_2 , MAX(IF(user_id=3, amounts, NULL)) AS user_3 ... FROM raw GROUP BY order_date --2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 with raw as ( --혹시나 하나의 유저가 하루에 여러 주문을 했을수도 있을것 같아서 이것 사용(MAX 쓸 예정이라서) select user_id , order_date , sum(amount) as amounts from orders ) SELECT user_id , MAX(IF(order_date='2023-05-01', amounts, NULL)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', amounts, NULL)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', amounts, NULL)) AS '2023-05-03' ... FROM raw GROUP BY user_id --3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 with raw as ( select user_id , order_date , sum(amount) as amounts , count(distinct order_id) as order_cnt from orders ) 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' --second case , MAX(IF(order_date='2023-05-01', order_cnt, 0)) AS '2023-05-01' , MAX(IF(order_date='2023-05-02', order_cnt, 0)) AS '2023-05-02' , MAX(IF(order_date='2023-05-03', order_cnt, 0)) AS '2023-05-03' ... FROM raw GROUP BY user_id -- 4)user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? -- ##데이터 조회할때 유용한 except(column):특정 컬럼 제외하고 모두 다 -- select * except(event_params) -- GROUP BY ALL with raw as ( select user_id , event_date , event_name , user_pseudo_id , event_component.key , event_component.value.int_value as food_id from app_logs as app, UNNEST(event_pharams) as event_component where user_id = 32888 and event_name = 'click_cart' and event_component.key = 'food_id' ) select distinct event_time --나는 일별로 보고 싶어서 추가 , food_id from raw --성윤님 강의 내용 select user_id , event_name , event_date , event_timestamp --같은일에 중복 주문이 있을까봐서 , MAX(IF(event_component.key='food_id', event_component.value.int_value, NULL)) AS food_id from app_logs as app, UNNEST(event_pharams) as event_component where user_id = 32888 and event_name = 'click_cart' and event_component.key = 'food_id' GROUP BY ALL --알아서 컬럼들 픽 --퍼널 별 유저 수 집계 with raw as ( select user_id , event_date , event_timestamp , event_name , user_pseudo_id , platform , event_component.key , event_component.value.string_value , event_component.value.int_value , MAX(IF(event_component.key = "firebase_screen", event_component.value.string_value, NULL)) AS firebase_screen -- , MAX(IF(event_component.key = "food_id", event_component.value.int_value, NULL)) AS food_id , MAX(IF(event_component.key = "session_id", event_component.value.int_value, NULL)) AS session_id from app_logs as app, UNNEST(event_pharams) as event_component where event_date BETWEEN "2022-08-01" AND "2022-08-18" group by all ) , filter_event_and_concat_event_and_screen AS( SELECT * EXCEPT(event_name, firebase_screen,event_timestamp) , CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen , DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) 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 [0,1,1,2,3,4] as array_practice array<int64>[0,1,3] as array_practice generate_array(1,5,2) generate_date_array('2024-01-01', '2024-02-01', interval 1 week) WITH programming_languages AS ( SELECT "python" AS programming_language UNION ALL SELECT "go" UNION ALL SELECT "scala" ) select array_agg(programming_languages) as output from programming_languages --배열에 접근하기 offset: #0 ordinal: #1 #out of range를 방지하기 위해서 safe_ 추가하기 --사용 예시 select some_numbers[safe_offset(1)] as second_value 컬럼명[safe_offset(가져오고 싶은 위치)] Array(like list): 비슷한 카테고리에 대해 데이터를 저장할때 예시) 메뉴(컬럼): 돼지국밥, 떡볶이, 치킨 Struct(like dict): 다양한 속성에 대해 데이터를 한 컬럼에 다 넣고 싶을때 예시) 주소록(컬럼): 이름, 전화번호,이메일, 생일 등등 SELECT (1,2,3) AS struct_test SELECT STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test SELECT struct_test.hi, struct_test.hello FROM ( SELECT STRUCT<hi INT64, hello INT64, awesome STRING>(1, 2, 'HI') AS struct_test ) -- UNNEST를 사용해 중첩된 데이터 구조 풀기(평면화, Flatten) WITH example_data AS( SELECT 'kyle' AS name, ['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 'Incheon' AS hometown UNION ALL SELECT 'max' AS name, ['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 'Seoul' AS hometown UNION ALL SELECT 'yun' AS name, ['Python', 'SQL'] AS preferred_language, 'Incheon' AS hometown ) SELECT name, pref_lang, hometown FROM example_data CROSS JOIN UNNEST(preferred_language) AS pref_lang FROM exaple_data AS a, UNNEST(preferred_language) AS pref_lang --그럼 unnest안에는 array만? struct는? 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 ###팁 #같은 단어를 수정할 때,빨리하고 싶은 - 단어를 커서위에 올리고 커맨드 디 범위설정하고 수정하면 일괄수정 -> 인텔리데이에서는 어떻게 하지? #기대하는 아웃풋의 형태를 적어보는것 좋다 -> 쉐어포인트 컬럼에 만들기 프로젝트 시작전 - 어떤 업무를 함에 있어서 흐름을 아는 것이 중요하다(흐름을 모르면 어떤것을 왜 해야하는지 모를 수 있음) - 맥락 -> 목적 -> 퍼널 -> 가설 -> 분석 서비스의 목표 파악(어떤 문제를 해결하려고 하는지) 문제 정의: 핵심 문제 목표 정의 퍼널 정의 -> 우리도 이 데이터가 있는지 물어보기
미해결
김영한의 실전 자바 - 고급 2편, I/O, 네트워크, 리플렉션
한번에 쓰는 것과 8kb씩 버퍼를 이용해서 쓰는 것에 대해서 어차피 시스템 콜에서 8kb씩 전송하는데 한번에 가져다 주는 게 더 빨라야 하는 것이라고 인식되는데 어떻게 8kb씩 버퍼로 주는게 더 빠른걸까요? 한번에 주든 8kb씩 버퍼로 주든 시스템 콜에서 8kb씩 전송하는 거면 한번에 주는 게 나은 거 아닌가요!?
미해결
Flutter로 SNS 앱 만들기
4:19 에서 스크린에 이렇게 나오네요. 그리고 , 잠시후 정상적으로 표시됩니다.
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. ARRAY, STRUCT ### 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 # ARRAY : 같은 타입의 여러 데이터를 저장하고 싶을 때 # ARRAY를 Flatten(평면화) => UNNEST # UNNEST를 할 때는 CROSS JOIN + UNNEST(ARRAY_COLUMN) # UNNEST(ARRAY_COLUMN) AS 새로운 이름 # SELECT 절에서 새로운 이름으로 사용한다. 기존의 ARRAY_COLUMN은 사용하지 않는다! -- SELECT -- title -- , genre -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(genres) AS genre -- ORDER BY 1, 2 ## 같은 결과를 출력하기 위해 정렬함. -- ; ### 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. # 직접 접근하려면 actors = [ STRUCT(STRING, STRING)] # actors[SAFE_OFFSET(0)].actor # actors[SAFE_OFFSET(0)].character -- SELECT -- title -- , act.actor# AS actor -- , act.character# AS character -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(actors) AS act -- ORDER BY 1 -- ; ### 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. # 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN) -- SELECT -- title -- -- actors, # ARRAY<STRUCT(STRING, STRING)> -- , act.actor# AS actor -- , act.character# AS character -- -- genres # ARRAY<STRING> -- , genre -- FROM `advanced.array_exercises` -- CROSS JOIN UNNEST(actors) AS act -- CROSS JOIN UNNEST(genres) AS genre -- -- WHERE 1=1 -- -- ## 강의 촬영 시점 이후에 수정된 듯 두 쿼리 모두 오류없이 실행 되는 것 같아요 ! -- -- AND act.actor = "Chris Evans" -- -- AND actor = "Chris Evans" -- ORDER BY 1 -- ; ### 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요. -- SELECT -- user_id -- , event_date -- , event_name -- , user_pseudo_id -- , evt_prm.key AS key -- , evt_prm.value.string_value AS string_value -- , evt_prm.value.int_value AS int_value -- FROM `advanced.app_logs` -- CROSS JOIN UNNEST(event_params) AS evt_prm -- WHERE 1=1 -- AND event_date = "2022-08-01" -- ORDER BY 2 -- ; ### WITH 문 변경 WITH base AS ( SELECT user_id , event_date , event_name , user_pseudo_id , evt_prm.key AS key , evt_prm.value.string_value AS string_value , evt_prm.value.int_value AS int_value FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS evt_prm WHERE 1=1 AND event_date = "2022-08-01" ) SELECT event_date , event_name , COUNT(DISTINCT user_id) AS cnt FROM base GROUP BY ALL ORDER BY cnt DESC 2. PIVOT # 1) orders 테이블에서 유저(user_id)별로 주문금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. -- 기대하는 output의 형태 -- order_date | user_1 | user_2 | user_3 -- PIVOT : MAX(IF(조건, TRUE일 때의 값, FALSE일 때의 값)) AS new_column + GROUP BY -- MAX 대신 집계 함수를 사용할 수도 있음. SUM -- FALSE일 때의 값은 NULL -- SELECT -- order_date -- , SUM(IF(user_id = 1, amount, 0)) AS user_1 -- , SUM(IF(user_id = 2, amount, 0)) AS user_2 -- , SUM(IF(user_id = 3, amount, 0)) AS user_3 -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; # 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요.user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. -- 기대하는 output의 형태 -- user_id | 2023-05-01 | 2023-05-02 | 2023-05-03 | 2023-05-04 | 2023-05-05 -- 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` -- 컬럼의 이름을 지정할 때, 영어 제외하고 backtick(`) -- ANY_VALUE : 그훕화 할 대상 중에 임의의 값을 선택한다 (NULL을 제외하고). ANY_VALUE에선 나머지 값들이 NULL이거나 확정적으로 값을 기대할 수 있을 때 사용한다! -- ANY_VALUE(IF(order_date="2023-05-01", amount, NULL)) AS `2023-05-01` -- FROM `advanced.orders` -- GROUP BY 1 -- ORDER BY 1 -- ; # 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. -- 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 1 -- ORDER BY 1 -- ; ## 앱 로그 PIVOT WITH base AS( SELECT -- * EXCEPT(event_params) # * EXCEPT(column) : 컬럼을 제외하고 다 보여줘! event_date , event_timestamp , event_name , user_id , user_pseudo_id , MAX(IF(param.key = "fierbase_screen", param.value.string_value, NULL)) AS fierbase_screen -- , MAX(IF(param.key = "food_id", param.value.string_value, NULL)) AS food_id # string_value엔 food_id 값들이 저장되어 있지 않음. , MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id , MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS sessioon_id FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS param WHERE 1=1 AND event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date , COUNT(user_id) AS user_cnt FROM base WHERE 1=1 AND event_name = "click_cart" -- AND food_id = 1544 GROUP BY event_date 3. 퍼널 연습 문제 # 퍼널 분석 -- 퍼널 데이터 -- 우리가 사용할 이벤트 => 단계 -- - screen_view : welcome, home, food_category, restaurant, cart -- - click_payment -- step_number : 추후에 정렬을 위해 만들 것 -- 사용할 데이터 : 앱 로그 데이터, GA/Firebase => UNNEST => PIVOT -- 기간 : 2022-08-01 ~ 2022-08-18 WITH base as ( SELECT event_date , event_timestamp , event_name , user_id , user_pseudo_id , platform , 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 ), base2 as ( SELECT * , CONCAT(event_name, "-", firebase_screen) as event_screen FROM base WHERE 1=1 AND event_name IN ("screen_view", "click_payment") ), base3 as ( SELECT event_screen , event_date , CASE WHEN event_screen = "screen_view-welcome" THEN 1 WHEN event_screen = "screen_view-home" THEN 2 WHEN event_screen = "screen_view-food_category" THEN 3 WHEN event_screen = "screen_view-restaurant" THEN 4 WHEN event_screen = "screen_view-cart" THEN 5 WHEN event_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 ) SELECT event_date , MAX(IF(base3.event_screen ="screen_view-welcome", cnt, NULL)) AS screen_view_welcome , MAX(IF(base3.event_screen ="screen_view-home", cnt, NULL)) AS screen_vie_home , MAX(IF(base3.event_screen ="screen_view-food_category", cnt, NULL)) AS screen_view_food_category , MAX(IF(base3.event_screen ="screen_view-restaurant", cnt, NULL)) AS screen_view_restaurant , MAX(IF(base3.event_screen ="screen_view-cart", cnt, NULL)) AS screen_view_cart FROM base3 GROUP BY ALL ORDER BY event_date
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
[ARRAY, STRUCT] 문제 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre 쿼리 결과 1) 문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor 쿼리 결과 2) 문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. SELECT title, actor.actor AS actor, actor.character AS character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre CROSS JOIN UNNEST(actors) AS actor 쿼리 결과 3) 문제 4) 앱 로그 데이터(app_logs) 배열 풀기 SELECT event_date, event_timestamp, event_name, event_param.key AS key, event_param.value.string_value AS string_value, event_param.value.int_value AS int_value, user_id, user_pseudo_id, platform FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date = "2022-08-01" LIMIT 100 쿼리 결과 4) [PIVOT] 문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY ALL ORDER BY order_date 쿼리 결과 1) 문제 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 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 ALL ORDER BY user_id 쿼리 결과 2) 문제 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 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 ALL ORDER BY user_id 쿼리 결과 3) 문제 4)user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, 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 GROUP BY ALL ) SELECT user_id, event_date, COUNT(user_id) AS user_cnt, food_id FROM base WHERE user_id = 32888 and event_name = 'click_cart' GROUP BY ALL 쿼리 결과 4) [퍼널분석] WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, 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 ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) 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 쿼리 결과
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. ARRAY, STRUCT 1) SELECT title, movie_genres FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS movie_genres LIMIT 100 2) SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor 3) SELECT title, actors.actor, actors.character, genres FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actors CROSS JOIN UNNEST(genres) AS genres WHERE actor = 'ChrisEvans' 4) select user_id , event_date , event_name , user_pseudo_id , param.key as key , param.value.string_value as string_value , param.value.int_value as int_value from advanced.app_logs , unnest(event_params) as param 2. PIVOT 1) select order_date , sum(if(user_id = 1,amount,0)) as user_1 , sum(if(user_id = 2,amount,0)) as user_2 , sum(if(user_id = 3,amount,0)) as user_3 from advanced.orders group by order_date order by order_date 2) select user_id , max(if(order_date = '2023-05-01',amount,0)) as `2023-05-01` , max(if(order_date = '2023-05-02',amount,0)) as `2023-05-02` , max(if(order_date = '2023-05-03',amount,0)) as `2023-05-03` , max(if(order_date = '2023-05-04',amount,0)) as `2023-05-04` , max(if(order_date = '2023-05-05',amount,0)) as `2023-05-05` from advanced.orders group by user_id order 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 order by user_id 4) 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 ) 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 WHERE user_id = 32888 AND event_name = 'click_cart' GROUP BY ALL 3. 퍼널 1) with base as ( select event_date , event_name , event_timestamp , user_id , user_pseudo_id , platform , max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen from advanced.app_logs , unnest(event_params) as param where event_date between '2022-08-01' and '2022-08-18' group by all ), filter_event as ( select * except(event_name, firebase_screen) , concat(event_name, "-", firebase_screen) as event_name_with_screen from base where event_name in ('screen_view', 'click_payment') ) 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 group by all having step_number is not null order by event_date 2) with base as ( select event_date , event_name , event_timestamp , user_id , user_pseudo_id , platform , max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen from advanced.app_logs , unnest(event_params) as param where event_date between '2022-08-01' and '2022-08-18' group by all ), filter_event as ( select * except(event_name, firebase_screen) , concat(event_name, "-", firebase_screen) as event_name_with_screen from base where event_name in ('screen_view', 'click_payment') ), daily_group 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 group by all having step_number is not null order by event_date ) select event_date , max(if(event_name_with_screen = 'screen_view-welcome',cnt,null)) as screen_view_welcome , max(if(event_name_with_screen = 'screen_view-home',cnt,null)) as screen_view_home , max(if(event_name_with_screen = 'screen_view-food_category',cnt,null)) as screen_view_food_category , max(if(event_name_with_screen = 'screen_view-restaurant',cnt,null)) as screen_view_restaurant , max(if(event_name_with_screen = 'screen_view-cart',cnt,null)) as screen_view_cart , max(if(event_name_with_screen = 'click_payment-cart',cnt,null)) as click_payment_cart from daily_group group by all order by event_date
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
ARRAY, STRUCT 연습 문제 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(genres) AS genre 각 영화(title)별 배우(actor)와 배역(character)을 보여주세요.(별도 칼럼) SELECT title, actor.actor, actor.character FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS actor 각 영화(title)별로 배우(actor),배역(character),장르(genre)를 출력하세요. SELECT title, actor, character, genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre 앱 로그 데이터(app_logs)의 배열을 풀어주세요. SELECT event_date, event_timestamp, 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 PIVOT 연습 문제 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT (날짜를 행, user_id를 열) SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date 날짜별로 유저들의 주문금액의 합계를 PIVOT (user_id를 행, order_date를 열) 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 사용자별, 날짜별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요 (user_id를 행, order_date를 열) 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-03`, IF(SUM(IF(order_date = '2023-05-03', amount, 0))>0,1,0) AS `2023-05-02`, 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 user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? SELECT user_id, 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 FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE user_id = 32888 AND event_name = 'click_cart' GROUP BY user_id, event_timestamp -- 카트에 담은 음식(food_id): 1559, 1942 퍼널 쿼리 연습 문제 일자별 이벤트 집계 후 PIVOT WITH funnel_data AS ( SELECT *, 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 END AS step_number FROM ( SELECT event_date, event_timestamp, user_pseudo_id, concat(event_name, '-', event_param.value.string_value) AS event_name_with_screen FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_param.key = 'firebase_screen' ) AS unnested_app_logs WHERE event_name_with_screen IN ( 'screen_view-welcome', 'screen_view-home', 'screen_view-food_category', 'screen_view-restaurant', 'screen_view-cart', 'click_payment-cart' ) )SELECT event_date, COUNT(IF(step_number = 1, user_pseudo_id, NULL)) AS `screen_view-welcome`, COUNT(IF(step_number = 2, user_pseudo_id, NULL)) AS `screen_view-home`, COUNT(IF(step_number = 3, user_pseudo_id, NULL)) AS `screen_view-food_category`, COUNT(IF(step_number = 4, user_pseudo_id, NULL)) AS `screen_view-restaurant`, COUNT(IF(step_number = 5, user_pseudo_id, NULL)) AS `screen_view-cart`, COUNT(IF(step_number = 6, user_pseudo_id, NULL)) AS `click_payment-cart` FROM funnel_data GROUP BY event_date ORDER BY event_date
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. ARRAY, STRUCT 연습문제 문제 1) array_exercise테이블에서 각 영화(title)별로 장르(genres)를 UNNEST 해서 보여주세요 SELECT title, genres FROM `analystic-project.advanced.array_exercises` , UNNEST(genres) AS genres ; 문제 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야합니다 SELECT title, actors.actor, actors.character FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors ; 문제 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 SELECT title, actors.actor, actors.character, genres FROM `analystic-project.advanced.array_exercises` , UNNEST(actors) AS actors, UNNEST(genres) genres ; 문제 4) 앱 로그 데이터(app_logs) 배열 풀기 SELECT user_id, event_date, event_name, user_pseudo_id, pr.key, pr.value.string_value, pr.value.int_value FROM `analystic-project.advanced.app_logs` , UNNEST(event_params) AS pr WHERE event_date = "2022-08-01" LIMIT 1000 ; 2. PIVOT 연습문제 풀이 문제 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 SELECT order_date, COALESCE(SUM(IF(user_id = 1, amount, null)),0) AS user_1, COALESCE(SUM(IF(user_id = 2, amount, null)),0) AS user_2, COALESCE(SUM(IF(user_id = 3, amount, null)),0) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date ; 문제 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 SELECT user_id, COALESCE(SUM(IF(order_date = '2023-05-01', amount, null)),0) AS `2023-05-01`, COALESCE(SUM(IF(order_date = '2023-05-02', amount, null)),0) AS `2023-05-02`, COALESCE(SUM(IF(order_date = '2023-05-03', amount, null)),0) AS `2023-05-03`, COALESCE(SUM(IF(order_date = '2023-05-04', amount, null)),0) AS `2023-05-04`, COALESCE(SUM(IF(order_date = '2023-05-05', amount, null)),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를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 SELECT user_id, MAX(IF(order_date = '2023-05-01' AND order_id is not null, 1, 0)) AS `2023-05-01`, MAX(IF(order_date = '2023-05-02' AND order_id is not null, 1, 0)) AS `2023-05-02`, MAX(IF(order_date = '2023-05-03' AND order_id is not null, 1, 0)) AS `2023-05-03`, MAX(IF(order_date = '2023-05-04' AND order_id is not null, 1, 0)) AS `2023-05-04`, MAX(IF(order_date = '2023-05-05' AND order_id is not null, 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id ; 문제 4) user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? WITH app_order_raw AS ( SELECT user_id, event_date, event_name, user_pseudo_id, pr.key, pr.value.string_value, pr.value.int_value FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date = '2022-08-01' ) 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 app_order_raw GROUP BY user_id, event_date, event_name, user_pseudo_id ; 3. 퍼널분석 문제 1) 각 퍼널의 유저 수를 집계 / 데이터 기준: 2022-08-01 ~ 2022-08-18 WITH funnel_data_raw AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name, CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY 1,2,3,4,5 ) 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 END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel_data_raw WHERE event_name IN ('screen_view', 'click_payment') AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') GROUP BY 1,2 ORDER BY 2 ; 문제 2) 일자별 퍼널 유저 수 집계 WITH funnel_data_raw AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name, CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY 1,2,3,4,5 ) 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 END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel_data_raw WHERE event_name IN ('screen_view', 'click_payment') AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') GROUP BY 1,2,3 ORDER BY 1,3 ; 문제 3) 일자별 퍼널 유저 수 집계 형태를 PIVOT형태로 전환하기 WITH funnel_data_raw AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null)) AS screen_name, CONCAT(event_name, '-', MAX(IF(pr.key = 'firebase_screen', pr.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs, UNNEST(event_params) AS pr WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY 1,2,3,4,5 ), daily_funnel_user_count 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 END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel_data_raw WHERE event_name IN ('screen_view', 'click_payment') AND screen_name IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') GROUP BY 1,2,3 ORDER BY 1,3 ) 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 daily_funnel_user_count GROUP BY ALL ORDER BY 1 ;
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
<PART 1> ARRAY, STRUCT 연습문제 Q1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. -- 출제의도: 배열 UNNEST의 기본 형태를 사용할 수 있는가? SELECT title , genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre ORDER BY title; Q2. array_exercise 테이블에서 각 영화(title)별로 배우(actors)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. -- 출제의도: 다중 배열 구조에서 UNNEST를 사용할 수 있는가? SELECT title , actor.actor , actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor ORDER BY title; Q3. array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. -- 출제의도: 여러 칼럼을 동시에 UNNEST할 수 있는가? SELECT title , actor.actor , actor.character , genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre ORDER BY title; Q4. 앱 로그 데이터(app_logs)의 배열을 풀어주세요. -- 출제의도: 다중 struct 구조의 데이터를 평면화하여 쿼리로 호출할 수 있는가? SELECT user_id , event_date , event_name , user_pseudo_id , event.key , event.value.string_value , event.value.int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event WHERE event_date = '2022-08-01'; <PART 2> PIVOT 연습문제 Q1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT 해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. -- 출제의도: 집계 함수와 조건 함수를 결합하여 PIVOT 테이블을 만들 수 있는가? SELECT order_date , SUM(IF(user_id=1, amount, 0)) AS user_1 , SUM(IF(user_id=2, amount, 0)) AS user_2 , SUM(IF(user_id=3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY ALL ORDER BY order_date; Q2. orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id 를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. -- 출제의도 : PIVOT 테이블 구성 시, 날짜 칼럼을 이용하여 시계열 방식을 구성할 수 있는가? 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 ALL ORDER BY user_id; Q3. orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. -- 출제의도 : PIVOT 테이블 구성 시, 집계 함수로 MAX를 사용할 수 있는가? 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 ALL ORDER BY user_id; Q4. user_id = 32888 이 카트 추가하기(click_cart)를 누를 때 어떤 음식 (food_id)을 담았는지 구해주세요. key 를 Column 으로 두고, string_value 나 int_value를 Column의 값으로 설정해서 풀어주세요. -- 출제의도 : PIVOT 테이블을 앱로그 데이터에 사용하여, 조건문으로 개별 유저 데이터를 특정할 수 있는가? WITH base AS( SELECT event_date , event_timestamp , event_name , user_id , user_pseudo_id , MAX(IF(params.key = 'firebase_screen', params.value.string_value, NULL)) AS firebase_screen , MAX(IF(params.key = 'food_id', params.value.int_value, NULL)) AS food_id , MAX(IF(params.key = 'session_id', params.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date = '2022-08-01' GROUP BY ALL ) SELECT * FROM base WHERE event_name = 'click_cart' and user_id = 32888 -- 실행결과 : food_id = 1942 <PART 3> 퍼널 연습문제 -- 출제의도: 앱 로그 데이터에서 원하는 이벤트를 추출해, 퍼널 분석을 위한 전처리를 진행할 수 있는가? -- step 1. UNNEST를 통한 base 데이터 준비 WITH base AS( SELECT event_date , event_timestamp , event_name , event.key AS event_key , event.value.string_value AS event_string_value , event.value.int_value AS event_int_value , user_id , user_pseudo_id , platform FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event WHERE event_date BETWEEN '2022-08-01' AND '2022-08-22' ), -- step 2. 필요한 퍼널 이벤트에만 step_number를 세팅하여 준비 sorted_events AS( SELECT event_date , CONCAT(event_name, "-", event_string_value) AS event_name_with_screen , CASE WHEN event_name = 'screen_view' AND event_string_value = 'welcome' THEN 1 WHEN event_name = 'screen_view' AND event_string_value = 'home' THEN 2 WHEN event_name = 'screen_view' AND event_string_value = 'food_category' THEN 3 WHEN event_name = 'screen_view' AND event_string_value = 'restaurant' THEN 4 WHEN event_name = 'screen_view' AND event_string_value = 'cart' THEN 5 WHEN event_name = 'click_payment' AND event_string_value = 'cart' THEN 6 ELSE NULL END AS step_number , user_pseudo_id FROM base WHERE event_key = 'firebase_screen' ) -- step 3. 최종 조회 쿼리 SELECT event_date , event_name_with_screen , step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM sorted_events WHERE step_number IS NOT NULL GROUP BY ALL ORDER BY event_date, step_number
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
ARRAY -- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre --2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor --3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. SELECT title, actor.actor AS actor, actor.character AS character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre WHERE actor.actor = 'Chris Evans' AND genre = 'Action'es) AS genre -- 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 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') PIVOT --1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT 해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어아 합니다. SELECT order_date, MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1, MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2, MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM ( SELECT order_date, user_id, SUM(amount) AS sum_of_amount FROM advanced.orders GROUP BY order_date, user_id ) GROUP BY order_date ORDER BY order_date --2.orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. SELECT user_id, MAX(IF(order_date = "2023-05-01", amount, 0)) AS `2023-05-01`, MAX(IF(order_date = "2023-05-02", amount, 0)) AS `2023-05-02`, MAX(IF(order_date = "2023-05-03", amount, 0)) AS `2023-05-03`, MAX(IF(order_date = "2023-05-04", amount, 0)) AS `2023-05-04`, MAX(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를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. 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 퍼널 WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, 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 ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) 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 아직 SQL 익숙지 않아서, 강의 들으면서 코드를 이해하려고 했습니다. 얼른 빅쿼리 SQL입문 강의도 다 듣고, 2주차에 더 실력이 올라갔으면 좋겠습니다!
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1.ARRAY, STRUCT 연습문제 /* UNNEST를 사용하는 이유 : 중첩된 데이터를 평평하게 만들어 집계 및 분석을 쉽게 하기 위해 UNNEST된 결과를 사용하여 분석을 실행 : 1.프로그래밍 언어 선호도, 2.지역별 언어 선호도 분석을 통해 Action Itme을 도출 : 프로그래밍 강좌를 제공한다면 선호하는 언어 순으로 영상 제작 등 */ SELECT name, pref_lang, hometown FROM example_data CROSS JOIN UNNEST(preferred_language) AS pref_lang; # UNNEST란 장바구니(배열)에 있는 과일(배열의 값)을 모두 다 꺼내는 것 /* 연습문제 1 UNNEST된 결과를 사용하여 분석을 실행 : 1.영화 장르 선호도 분석을 통해 Action Itme을 도출 : 영화 제작사라면 어떤 장르가 선호되는 것을 보고 영화 제작 */ SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre; /* 연습문제 2 UNNEST된 결과를 사용하여 분석을 실행 : X 분석을 통해 Action Itme을 도출 : X */ SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor /* 연습문제 3 UNNEST된 결과를 사용하여 분석을 실행 : 1.배우의 영화 장르 선호도 분석을 통해 Action Itme을 도출 : 영화 제작시 배우의 장르 선호도 확인 후 */ SELECT title, actor.actor, actor.character, genre FROM advanced.array_exercises ,UNNEST(actors) AS actor, UNNEST(genres) AS genre /* 연습문제 4 */ WITH base AS ( SELECT user_id, event_date, event_name, user_pseudo_id, event_param.key AS key, -- event_param.value AS value, event_param.value.string_value, event_param.value.int_value FROM advanced.app_logs AS al CROSS JOIN UNNEST(event_params) AS event_param WHERE 1=1 AND event_date = '2022-08-01' ) SELECT event_date, event_name, COUNT(DISTINCT user_id) AS cnt FROM base GROUP BY ALL ORDER BY cnt DESC 2.PIVOT 연습문제 SELECT order_date, MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1, MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2, MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM ( SELECT order_date, user_id, #Amount의 합 SUM(amount) AS sum_of_amount FROM advanced.orders GROUP BY order_date, user_id ) GROUP BY order_date ORDER BY order_date; SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date; SELECT order_id, order_date, user_id, IF(order_date = '2023-05-01', amount, NULL) AS `2023-05-01`, IF(order_date = '2023-05-02', amount, NULL) AS `2023-05-02`, IF(order_date = '2023-05-03', amount, NULL) AS `2023-05-03`, IF(order_date = '2023-05-04', amount, NULL) AS `2023-05-04`, IF(order_date = '2023-05-05', amount, NULL) AS `2023-05-05` FROM advanced.orders; SELECT user_id, # amount 대신 1이라고 표시. IF 문 안에 TRUE 일 때의 값이 항상 특정 컬럼이 아니라 1이라고 할 수도 있음(유무에 따라서) 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; WITH base AS ( SELECT # * EXCEPT(event_params), # * EXCEPT(컬럼) : 컬럼을 제외하고 모두 다 보여줘! event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL)) AS firebase_screen, MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id2, MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS session_id, FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS param WHERE event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date, COUNT(user_id) AS user_cnt FROM base WHERE event_name = "click_cart" GROUP BY event_date 3.퍼널 분석 연습문제 WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, 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 1=1 AND event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) #일자별로 퍼널별 유저 수 쿼리 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
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
Q1 STRUCT, UNNEST 1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, -- genres, genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(genres) AS genre -- genres는 평면화가 된 데이터를 의미 -- genres가 지금 배열 -- ARRAY : 같은 타입의 여러 데이터를 저장하고 싶을 때 -- ARRAY를 풀때 Flattten(평면화) -> UNNEST -- UNNEST릃 할 때는 CROSS JOIN + UNNEST(ARRAY_COLUMN) 컬럼 명시 2) array_exercises 테이블에서 각 영화(title)q별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 함. SELECT title, -- actors -- actor에 직접 접근하면 어떨까 -> 새로운 컬럼으로 가능하나, 매번 SAFE_OFFSET을 지정해야 함 -- actors = [STRUCT(STRING,STRING)] actors[SAFE_OFFSET(0)].actor AS first_actor, actors[SAFE_OFFSET(0)].actor AS first_character, actors[SAFE_OFFSET(1)].actor AS second_actor, actors[SAFE_OFFSET(1)].actor AS second_character -- 배열에 직접 접근이 아닌 UNNEST로 풀어야 편리할 듯 FROM advanced.array_exercises as ae --------------------------------------------------------------- --------------------------------------------------------------- SELECT title, actor.actor, actor.character FROM advanced.array_exercises as ae CROSS JOIN UNNEST(actors) AS actor -- actors가 배열 3) array_exercises 테이블에서 각 영화(title) 별로 배우(actor), 배역(character), 장르(genre)를 출력. 한 row에 배우, 배역, 장르가 모두 표시되어야 함. SELECT title, -- actors, #ARRAY<STRUCT(STRING, STRING)> actor.actor as actor, actor.character as character, -- genres # ARRAY<STRING> genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre Q2 PIVOT 1-1) 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 SELECT order_date, IF(user_id = 1, amount , NULL) AS user_1, IF(user_id = 2, amount , NULL) AS user_2, IF(user_id = 3, amount , NULL) AS user_3 FROM( SELECT order_date, user_id, amount FROM advanced.orders GROUP BY order_date, user_id, amount ORDER BY order_date ) --------------------------------------------------------------- --------------------------------------------------------------- 1-2) SELECT order_date, MAX(IF(user_id = 1, amount , NULL)) AS user_1, MAX(IF(user_id = 2, amount , NULL)) AS user_2, MAX(IF(user_id = 3, amount , NULL)) AS user_3 FROM( SELECT order_date, user_id, amount FROM advanced.orders GROUP BY order_date, user_id, amount ORDER BY order_date ) GROUP BY order_date ORDER BY order_date --------------------------------------------------------------- --------------------------------------------------------------- 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. SELECT order_date, SUM(IF(user_id = 1, amount , NULL)) AS user_1, SUM(IF(user_id = 2, amount , NULL)) AS user_2, SUM(IF(user_id = 3, amount , NULL)) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date backtick 활용 any value는 어디에 활용할 수 있을지? -> 데이터는 믿을수 없기에 일부 데이터만 보고 사용 판단하기엔 위험할 것 같음. 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를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 3-1) 주문 여부 1,0 SELECT user_id, SUM(IF(order_date = "2023-05-01", 1, 0)) AS `2023-05-01`, SUM(IF(order_date = "2023-05-02", 1, 0)) AS `2023-05-02`, SUM(IF(order_date = "2023-05-03", 1, 0)) AS `2023-05-03`, SUM(IF(order_date = "2023-05-04", 1, 0)) AS `2023-05-04`, SUM(IF(order_date = "2023-05-05", 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id 3-2) 횟수 SELECT user_id, SUM(IF(order_date = "2023-05-01", 1, 0)) AS `2023-05-01`, SUM(IF(order_date = "2023-05-02", 1, 0)) AS `2023-05-02`, SUM(IF(order_date = "2023-05-03", 1, 0)) AS `2023-05-03`, SUM(IF(order_date = "2023-05-04", 1, 0)) AS `2023-05-04`, SUM(IF(order_date = "2023-05-05", 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id -- 앱 로그 PIVOT WITH base AS( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL)) AS firebase_screen, MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id, MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS session_id -- * EXCEPT(event_params) FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS param WHERE event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date, COUNT(user_id) AS user_cnt FROM base WHERE event_name = "click_cart" AND food_id = 1544 GROUP BY event_date Q3 퍼널 데이터 -- 이중 WITH 문 WITH BASE AS( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, -- event_param 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 ) --event_name + screen (필요한 이벤트만 조건 걸어서 사용) ,filter_event_and_concat_event_and_screen AS( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM BASE WHERE event_name IN ("screen_view","click_payment") ) --step_number + COUNT --CASE WHEN 사용 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 -- food_detail, search, search_result도 파악 STRUCT 과 UNNEST 처음 접해보는 내용이라, 복습 필요. PIVOT 내용 중 ANY_VALUE는 데이터 양이 많고, 어떤 데이터들이 어떤 특성을 가지고 담겨있는지 정확하게 모른다면 활용하면 위험하겠다는 생각이 들었음.
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
UNNEST 1) array_exercises 테이블에서 각 영화(title)별로 장르를(genres) unnest 해서 보여주세요 SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre 2) array_exercieses 테이블에서 각 영화(title) 별로 배우 (actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. SELECT title, actor_info.actor, actor_info.character, FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor_info; 3) array_exercises 테이블에서 각 영화(title) 별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 row 에 배우, 배역, 장르가 모두 표시되어야 합니다. SELECT title, actor_info.actor, actor_info.character, genre, FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor_info CROSS JOIN UNNEST(genres) AS genre; 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요. SELECT user_id, event_date, event_name, user_pseudo_id, params.key AS key, params.value.string_value AS string_value, params.value.int_value AS int_value, FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params ORDER BY event_date; PIVOT 1) orders 테이블에서 유저(user_id)별로 주문금액(amount)의 합계를 PIVOT 해주세요 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. SELECT order_date, SUM(IF(user_id=1, amount, 0)) AS user_1, SUM(IF(user_id=2, amount, 0)) AS user_2, SUM(IF(user_id=3, amount, 0)) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date ASC 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문금액(amount)의 합계를 PIVOT 해주세요. user_id를 행으로, order_date를 열으로 만들어야 합니다. 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로 처리합니다. 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) user_id = 32888 이 카트 추가하기 (click_cart)를 누를 때 어떤 음식(food_id)을 담았나요? WITH base AS ( SELECT event_date, event_name, user_pseudo_id, event_timestamp, user_id, MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL)) AS firebase_screen, MAX(IF(param.key = "food_id", param.value.int_value, NULL)) AS food_id, MAX(IF(param.key = "session_id", param.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS param WHERE event_date = "2022-08-01" GROUP BY ALL ) SELECT select_date, COUNT(user_id) AS user_cnt FROM base WHERE event_name = "click_cart" GROUP BY event_date 3. 퍼널 WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, -- event_param 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 = "2022-08-01" # 적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건 event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( -- (1) event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용) SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ), funnel 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 ) SELECT event_date, MAX(IF(funnel.event_name_with_screen = 'screen_view-welcome', cnt, null)) as `screen_view-welcome`, MAX(IF(funnel.event_name_with_screen = 'screen_view-home', cnt, null)) as `screen_view-home`, MAX(IF(funnel.event_name_with_screen = 'screen_view-food_category', cnt, null)) as `screen_view-food_category`, MAX(IF(funnel.event_name_with_screen = 'screen_view-restaurant', cnt, null)) as `screen_view-restaurant`, MAX(IF(funnel.event_name_with_screen = 'screen_view-cart', cnt, null)) as `screen_view-cart`, MAX(IF(funnel.event_name_with_screen = 'click_payment-cart', cnt, null)) as `click_payment-cart` FROM funnel GROUP BY ALL ORDER BY event_date
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. ARRAY, STRUCT 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 SELECT title, -- genres, genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(genres) AS genre 2) array_exercises테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요.배우와 배역은 별도의 컬럼으로 나와야합니다 SELECT title, actor.actor, actor.character -- actors, -- actors[SAFE_OFFSET(0)].actor AS actor, -- actors[SAFE_OFFSET(1)].character AS character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우,배역,장르가 모두 표시 되어야 합니다 SELECT title, -- actors, actor.actor, actor.character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 SELECT user_id, event_date, event_name, user_pseudo_id, event_param.key AS key, -- event_param.value AS value, 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' 2. PIVOT 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 SELECT order_date, SUM(IF(user_id = 1, sum_of_amount, 0)) AS user_1, SUM(IF(user_id = 2, sum_of_amount, 0)) AS user_2, SUM(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM (SELECT order_date, user_id, SUM(amount) as sum_of_amount FROM advanced.orders GROUP BY order_date, user_id ) GROUP BY order_date ORDER BY order_date SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(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를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 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를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 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) user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(param.key = 'firebase_screen', param.value.string_value, null)) AS firebase_screen, MAX(IF(param.key = 'food_id', param.value.int_value, null)) AS food_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS param WHERE user_id = 32888 and event_name = 'click_cart' GROUP BY ALL ORDER BY event_date 3. 퍼널 WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, -- event_param 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 = "2022-08-01" # 적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건 event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL ), filter_event_and_concat_event_and_screen AS ( -- (1) event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용) SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ), funnel 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 ) SELECT event_date, MAX(IF(funnel.event_name_with_screen = 'screen_view-welcome', cnt, null)) as `screen_view-welcome`, MAX(IF(funnel.event_name_with_screen = 'screen_view-home', cnt, null)) as `screen_view-home`, MAX(IF(funnel.event_name_with_screen = 'screen_view-food_category', cnt, null)) as `screen_view-food_category`, MAX(IF(funnel.event_name_with_screen = 'screen_view-restaurant', cnt, null)) as `screen_view-restaurant`, MAX(IF(funnel.event_name_with_screen = 'screen_view-cart', cnt, null)) as `screen_view-cart`, MAX(IF(funnel.event_name_with_screen = 'click_payment-cart', cnt, null)) as `click_payment-cart` FROM funnel GROUP BY ALL ORDER BY event_date
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. ARRAY, STRUCT 연습 문제 # 1. array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. # 쿼리를 작성하는 목표, 확인할 지표: title, genre # 쿼리 계산 방법: UNNEST # 데이터의 기간: X # 사용할 테이블: advanced.array_exercises # JOIN KEY: X # 데이터 특징: genres는 배열임 SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre ORDER BY title # 2. array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. # 쿼리를 작성하는 목표, 확인할 지표: title, actors.actor, actors.character # 쿼리 계산 방법: UNNEST # 데이터의 기간: X # 사용할 테이블: advanced.array_exercises # JOIN KEY: X # 데이터 특징: actors는 actor, character로 구성된 struct이고, actor, character은 배열임 SELECT title, a.actor, a.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS a ORDER BY title # 3. array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. # 쿼리를 작성하는 목표, 확인할 지표: title, actors.actor, actors.character, genres # 쿼리 계산 방법: UNNEST # 데이터의 기간: X # 사용할 테이블: advanced.array_exercises # JOIN KEY: X # 데이터 특징: -- actors는 actor, charactor로 구성된 struct이고, actor, charactor은 배열임 -- genres는 배열임 SELECT title, a.actor, a.character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS a CROSS JOIN UNNEST(genres) AS genre ORDER BY title # 4. 앱 로그 데이터(app_logs)의 배열을 풀어주세요 # 쿼리를 작성하는 목표, 확인할 지표: app_logs 데이터 평면화 # 쿼리 계산 방법: UNNEST # 데이터의 기간: X # 사용할 테이블: advanced.app_logs # JOIN KEY: X # 데이터 특징: -- event_params는 key, value로 구성된 struct이고, -- key는 배열이며, -- value는 string_value, int_value로 구성된 struct이고, -- string_value, int_value 배열임 SELECT user_id, event_date, event_name, user_pseudo_id, ep.key, ep.value.string_value, ep.value.int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS ep 2. PIVOT 연습 문제 # 1. orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. # 쿼리를 작성하는 목표, 확인할 지표: user_id 별 amount PIVOT # 쿼리 계산 방법: PIVOT # 데이터의 기간: X # 사용할 테이블: advanced.orders # JOIN KEY: X # 데이터 특징: 원본 테이블에 order_date, user_id, amount가 열로 저장되어 있음 SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(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를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. # 쿼리를 작성하는 목표, 확인할 지표: order_date 별 SUM(amount) # 쿼리 계산 방법: PIVOT # 데이터의 기간: X # 사용할 테이블: advanced.orders # JOIN KEY: X # 데이터 특징: 원본 테이블에 order_date, user_id, amount가 열로 저장되어 있음 SELECT user_id, SUM(IF(order_date = '2023-05-01', amount, 0)), SUM(IF(order_date = '2023-05-02', amount, 0)), SUM(IF(order_date = '2023-05-03', amount, 0)), SUM(IF(order_date = '2023-05-04', amount, 0)), SUM(IF(order_date = '2023-05-05', amount, 0)) FROM advanced.orders GROUP BY user_id ORDER BY user_id # 3. orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. # 쿼리를 작성하는 목표, 확인할 지표: user_id 별 order_date에 주문했는지 안 했는지 출력 # 쿼리 계산 방법: PIVOT # 데이터의 기간: X # 사용할 테이블: advanced.orders # JOIN KEY: X # 데이터 특징: 원본 테이블에 order_date, user_id가 열로 저장되어 있음 SELECT user_id, MAX(IF(order_date = '2023-05-01', 1, 0)), MAX(IF(order_date = '2023-05-02', 1, 0)), MAX(IF(order_date = '2023-05-03', 1, 0)), MAX(IF(order_date = '2023-05-04', 1, 0)), MAX(IF(order_date = '2023-05-05', 1, 0)) FROM advanced.orders GROUP BY user_id ORDER BY user_id # 4. user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? # 쿼리를 작성하는 목표, 확인할 지표: user_id = 32888의 click_cart 할 때 food_id # 쿼리 계산 방법: PIVOT # 데이터의 기간: X # 사용할 테이블: advanced.app_logs # JOIN KEY: X # 데이터 특징: -- event_params-> key/value-> string_value/int_value -- key: firebase_screen/food_id/session_id SELECT user_id, event_date, event_name, event_timestamp, user_pseudo_id, MAX(IF(key = 'firebase_screen', string_value, NULL)) AS firevase_screen, MAX(IF(key = 'food_id', int_value, NULL)) AS food_id, MAX(IF(key = 'session_id', string_value, NULL)) AS session_id FROM ( SELECT user_id, event_date, event_name, event_timestamp, 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 user_id = 32888 AND event_name = 'click_cart' ) GROUP BY ALL ORDER BY event_date 3. 퍼널 쿼리 연습 문제 # 1. 퍼널 별 유저 수 집계 # 쿼리를 작성하는 목표, 확인할 지표: 퍼널 별 유저 수 집계 # 쿼리 계산 방법: UNNEST -> event_name/string_value, event_date에 조건 설정 -> user_pseudo_id를 DISTINCT하게 COUNT # 데이터의 기간: 2022-08-01 ~ 2022-08-18 # 사용할 테이블: advanced.app_logs # JOIN KEY: X # 데이터 특징: -- screen name의 경우 event_params.key = 'firebase_screen'인 event_params.value.string_value를 추출 SELECT CONCAT(event_name, '-', string_value) AS event_name_with_screen, CASE WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN 1 WHEN event_name = 'screen_view' AND string_value = 'home' THEN 2 WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN 3 WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN 4 WHEN event_name = 'screen_view' AND string_value = 'cart' THEN 5 WHEN event_name = 'click_payment' AND string_value = 'cart' THEN 6 END AS step_number, COUNT(DISTINCT(user_pseudo_id)) AS cnt FROM ( SELECT event_date, event_name, event_param.value.string_value AS string_value, user_pseudo_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' AND event_param.key = 'firebase_screen' AND ( (event_name = 'screen_view' AND event_param.value.string_value = 'welcome') OR (event_name = 'screen_view' AND event_param.value.string_value = 'home') OR (event_name = 'screen_view' AND event_param.value.string_value = 'food_category') OR (event_name = 'screen_view' AND event_param.value.string_value = 'restaurant') OR (event_name = 'screen_view' AND event_param.value.string_value = 'cart') OR (event_name = 'click_payment' AND event_param.value.string_value = 'cart') ) ) GROUP BY ALL ORDER BY step_number # 2. 일자 별 퍼널 별 유저 수 집계 # 쿼리를 작성하는 목표, 확인할 지표: 일자별 퍼널 별 유저 수 집계 # 쿼리 계산 방법: UNNEST -> event_name/string_value, event_date에 조건 설정 -> user_pseudo_id를 DISTINCT하게 COUNT # 데이터의 기간: 2022-08-01 ~ 2022-08-18 # 사용할 테이블: advanced.app_logs # JOIN KEY: X # 데이터 특징: -- screen name의 경우 event_params.key = 'firebase_screen'인 event_params.value.string_value를 추출 SELECT event_date, CONCAT(event_name, '-', string_value) AS event_name_with_screen, CASE WHEN event_name = 'screen_view' AND string_value = 'welcome' THEN 1 WHEN event_name = 'screen_view' AND string_value = 'home' THEN 2 WHEN event_name = 'screen_view' AND string_value = 'food_category' THEN 3 WHEN event_name = 'screen_view' AND string_value = 'restaurant' THEN 4 WHEN event_name = 'screen_view' AND string_value = 'cart' THEN 5 WHEN event_name = 'click_payment' AND string_value = 'cart' THEN 6 END AS step_number, COUNT(DISTINCT(user_pseudo_id)) AS cnt FROM ( SELECT event_date, event_name, event_param.value.string_value AS string_value, user_pseudo_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' AND event_param.key = 'firebase_screen' AND ( (event_name = 'screen_view' AND event_param.value.string_value = 'welcome') OR (event_name = 'screen_view' AND event_param.value.string_value = 'home') OR (event_name = 'screen_view' AND event_param.value.string_value = 'food_category') OR (event_name = 'screen_view' AND event_param.value.string_value = 'restaurant') OR (event_name = 'screen_view' AND event_param.value.string_value = 'cart') OR (event_name = 'click_payment' AND event_param.value.string_value = 'cart') ) ) GROUP BY ALL ORDER BY event_date # 2-1. 일자 별 퍼널 별 유저 수 집계 PIVOT하기 SELECT event_date, MAX(IF(event_name_with_screen='screen_view-welcome', cnt, NULL)) AS `screen_view-welcome`, MAX(IF(event_name_with_screen='screen_view-home', cnt, NULL)) AS `screen_view-home`, MAX(IF(event_name_with_screen='screen_view-food_category', cnt, NULL)) AS `screen_view-food_category`, MAX(IF(event_name_with_screen='screen_view-restaurant', cnt, NULL)) AS `screen_view-restaurant`, MAX(IF(event_name_with_screen='screen_view-cart', cnt, NULL)) AS `screen_view-cart`, MAX(IF(event_name_with_screen='click_payment-cart', cnt, NULL)) AS `click_payment-cart`, FROM ( 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 ) GROUP BY ALL ORDER BY event_date
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
시간관리의 실패로 퀄리티있는 학습을 못 한 것 같습니다. 과제 제출 시간 이후에 복습하며 다시 꼼꼼하게 살피겠습니다! 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'] ) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요. SELECT title, genre FROM `advanced.array_exercises` CROSS JOIN UNNEST(genres) AS genre array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다. SELECT title, actor.actor, actor.character FROM `advanced.array_exercises`, UNNEST(actors) as actor array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다. SELECT title, actor.actor AS actor, actor.character AS character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre 앱 로그 데이터(app_logs)의 배열을 풀어주세요. SELECT event_timestamp, event_name, event_param.key AS key, event_param.value.string_value AS string_value, event_param.value.int_value AS int_value, user_id FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS event_param PIVOT orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다. SELECT order_date, SUM(IF(user_id = 1, amount, 0)) AS user_1, SUM(IF(user_id = 2, amount, 0)) AS user_2, SUM(IF(user_id = 3, amount, 0)) AS user_3, FROM `advanced.orders` GROUP BY order_date ORDER BY order_date orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다. SELECT user_id, MAX(IF(order_date = "2023-05-01", amount, 0)) AS `2023-05-01`, MAX(IF(order_date = "2023-05-02", amount, 0)) AS `2023-05-02`, MAX(IF(order_date = "2023-05-03", amount, 0)) AS `2023-05-03`, MAX(IF(order_date = "2023-05-04", amount, 0)) AS `2023-05-04`, MAX(IF(order_date = "2023-05-05", amount, 0)) AS `2023-05-05` FROM `advanced.orders` GROUP BY user_id ORDER BY user_id orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다. 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 앱 로그 PIVOT WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, MAX(IF(param.key = "firebase_screen", param.value.string_value, NULL)) AS firebase_screen, MAX(IF(param.key = "sesstion_id", param.value.string_value, NULL)) AS sesstion_id FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS param WHERE event_date = "2022-08-01" GROUP BY ALL ) SELECT event_date, COUNT(user_id) AS user_cnt FROM base WHERE event_name = "click_cart" GROUP BY event_date Funnel WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, MAX(IF(event_param.key = "firebase_screen", event_param.value.string_value, NULL)) AS firebase_screen, 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 ), filter_event_and_concat_event_and_screen AS ( SELECT * EXCEPT(event_name, firebase_screen, event_timestamp), CONCAT(event_name, "-", firebase_screen) AS event_name_with_screen, DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM base WHERE event_name IN ("screen_view", "click_payment") ) 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
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. ARRAY, STRUCT 연습문제 - 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 SELECT title , genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre -- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 SELECT title, , actor.actor AS actor , actor.character AS character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor -- 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 SELECT title , actor.actor AS actor , actor.character AS character , genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre -- 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 SELECT user_id , event_date , event_name , user_pseudo_id , params.key AS key , params.value.string_value AS str_value , params.value.int_value AS int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date = '2022-08-01' 2. PIVOT 연습문제 -- 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 WITH step1 AS ( SELECT order_date , user_id , sum(amount) AS sum_of_amount FROM advanced.orders GROUP BY ALL ) SELECT order_date , MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1 , MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2 , MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM step1 GROUP BY order_date ORDER BY order_date -- 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 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를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 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하기 SELECT user_id , event_date , event_name , user_pseudo_id , MAX(IF(params.key = 'firebase_screen', params.value.string_value, NULL)) AS firebase_screen , MAX(IF(params.key = 'food_id', params.value.int_value, NULL)) AS food_id , MAX(IF(params.key = 'session_id', params.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date = '2022-08-01' GROUP BY ALL 3. 퍼널분석 WITH step1 AS ( SELECT event_date , event_timestamp , event_name , user_id , user_pseudo_id , MAX(IF(params.key = 'firebase_screen', params.value.string_value, NULL)) AS firebase_screen , MAX(IF(params.key = 'session_id', params.value.string_value, NULL)) AS session_id , platform FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY ALL ), step2 AS ( SELECT * EXCEPT(event_timestamp) , CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM step1 ), step3 AS ( SELECT * , 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 END AS step_number FROM step2 ), step3_1 AS ( -- 1) 각 퍼널별 유저 수 집계 SELECT event_name_with_screen , step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM step3 GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY step_number ) , step3_2 AS ( -- 2) 일자별 각 퍼널별 유저 수 집계 SELECT event_date , event_name_with_screen , step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM step3 GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date , step_number ) -- 3) 2) 데이터를 PIVOT SELECT event_date , MAX(IF(event_name_with_screen = 'screen_view-welcome', cnt, NULL)) AS `screen_view-welcome` , MAX(IF(event_name_with_screen = 'screen_view-home', cnt, NULL)) AS `screen_view-home` , MAX(IF(event_name_with_screen = 'screen_view-food_category', cnt, NULL)) AS `screen_view-food_category` , MAX(IF(event_name_with_screen = 'screen_view-restaurant', cnt, NULL)) AS `screen_view-restaurant` , MAX(IF(event_name_with_screen = 'screen_view-cart', cnt, NULL)) AS `screen_view-cart` , MAX(IF(event_name_with_screen = 'click_payment-cart', cnt, NULL)) AS `click_payment-cart` FROM step3_2 GROUP BY event_date ORDER BY event_date
