영상 촬영 시기와 현재 크롬 화면이 달라서 질문드립니다. 개발자 모드가 없어서 그런데 등록을 마쳐야 적용 가능할까요?

--연습문제 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 ###팁 #같은 단어를 수정할 때,빨리하고 싶은 - 단어를 커서위에 올리고 커맨드 디 범위설정하고 수정하면 일괄수정 -> 인텔리데이에서는 어떻게 하지? #기대하는 아웃풋의 형태를 적어보는것 좋다 -> 쉐어포인트 컬럼에 만들기 프로젝트 시작전 - 어떤 업무를 함에 있어서 흐름을 아는 것이 중요하다(흐름을 모르면 어떤것을 왜 해야하는지 모를 수 있음) - 맥락 -> 목적 -> 퍼널 -> 가설 -> 분석 서비스의 목표 파악(어떤 문제를 해결하려고 하는지) 문제 정의: 핵심 문제 목표 정의 퍼널 정의 -> 우리도 이 데이터가 있는지 물어보기 

한번에 쓰는 것과 8kb씩 버퍼를 이용해서 쓰는 것에 대해서어차피 시스템 콜에서 8kb씩 전송하는데한번에 가져다 주는 게 더 빨라야 하는 것이라고 인식되는데어떻게 8kb씩 버퍼로 주는게 더 빠른걸까요?한번에 주든 8kb씩 버퍼로 주든시스템 콜에서 8kb씩 전송하는 거면한번에 주는 게 나은 거 아닌가요!?

22강 마지막 부분에 제출용 데이터 프레임 생성하는 과정에서 영상과 동일하게 코드를 작성했는데 오류가 납니다. 혹시 몰라 자료로 올려주신 코드를 붙여넣기해도 동일한 오류가 나옵니다. 이런 경우에는 어떻게 해결해야 하는지 궁금합니다.  아래와 같은 오류입니다.ValueError: array length 161 does not match index length 268

// node tree에 설정(부모-자식 관계 설정) bookmarkItem.appendChild(bookmarkInfo); bookmarkItem.appendChild(bookmarkDelBtn); bookmarkInfo.appendChild(bookmarkUrl); bookmarkUrl.appendChild(urlIcon); bookmarkUrl.appendChild(nameElement); urlIcon.append(urlIconImg); bookmarkItemList.appendChild(bookmarkItem); 섹션 7의 6강 수강중에 해당 코드에 의문이 생겨서 질문 드립니다. append와 appendchild 2가지 메소드를 활용하셨는데 두 가지 차이가 검색해 봤을 땐 append는 노드뿐만 아니라 텍스트도 추가 가능하다고해서 appendchild로 바꿔봤더니 오류가 발생했습니다. 두가지 차이가 무엇인지 알 수 있을까요? 그리고 메소드 호출시 괄호가 있는 것과 없는 것의 차이가 궁금합니다. addEbentListener같은 곳에 사용되는 콜백함수에는 괄호를 안 붙여도 되는 건지 궁금합니다.// 1번 document.getElementById("cancel-btn").addEventListener("click", newBookmarkToggle); // 2번 document.getElementById("cancel-btn").addEventListener("click", newBookmarkToggle());

4:19 에서 스크린에 이렇게 나오네요.그리고 , 잠시후 정상적으로 표시됩니다.

22강 모델링 및 평가(회귀) 강의에서 선생님이 푸신 것에서는 랜덤포레스트에서 베이스라인보다 스탠다드스켈러에서 점수가 더 안좋아지는 결과가 나왔는데, 제가 따라서 풀어보면 베이스라인과 스탠다드스켈러의 점수도 동일하게 나오지 않고, 오히려 스탠다드스켈러의 점수가 더 좋게 나옵니다. 이렇게 다른 결과가 나오는 이유가 무엇일까요?

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

[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 쿼리 결과

Array, Struct 연습문제# 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 SELECT title, ge FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(genres) AS ge # 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 SELECT title, ac.actor, ac.character FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS ac # 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 SELECT title, ac.actor AS actor, ac.character AS character, genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS ac CROSS JOIN UNNEST(genres) AS genre # 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 SELECT event_date, event_timestamp, event_name, ep.key AS key, ep.value.string_value AS string_value, ep.value.int_value AS int_value, user_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS ep최근에 입사해서 야근 때문에 시간이 부족해 Array와 Struct까지만 제출합니다.입문편과 활용편을 병행하고 있는데, 2주차는 2주차 과제와 병행하며 1주차 과제에 추가하겠습니다.

1. ARRAY, STRUCT 1)SELECT title, movie_genres FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS movie_genres LIMIT 1002)SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor3)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_date2)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_id3)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_id4)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  

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_iduser_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퍼널 쿼리 연습 문제일자별 이벤트 집계 후 PIVOTWITH 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

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-18WITH 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 ;  

<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 

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주차에 더 실력이 올라갔으면 좋겠습니다!

1. ARRAY, STRUCT 문제1)SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre;2)SELECT title , actor.actor , actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor;3)SELECT title , actor.actor , actor.character , genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre4)SELECT user_id , event_date , event_name , user_pseudo_id , event_param.key , event_param.value.string_value event_param.value.int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date = '2022-08-01'2. PIVOT 연습문제1)

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 DESC2.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

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는 데이터 양이 많고, 어떤 데이터들이 어떤 특성을 가지고 담겨있는지 정확하게 모른다면 활용하면 위험하겠다는 생각이 들었음.     

UNNEST1) array_exercises 테이블에서 각 영화(title)별로 장르를(genres) unnest 해서 보여주세요SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre2) 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; PIVOT1) 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 ASC2) 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_id4) 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_date3. 퍼널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 

print(df['f1'].dropna())작성하면, 널값 삭제된 f1을 볼 수 있는데 이 값을df['f1']에 대입 후 프린트를 하면 적용이 안됩니다.왜 그런 것인가요?