연습문제(1) ARRAY, STRUCT-- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 # 쿼리를 작성하는 목표, 확인할 지표 : 평면화 # 쿼리 계산 방법 : UNNEST # 데이터의 기간 : # 사용할 테이블 : array_exercises # Join KEY : # 데이터 특징 : SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre; -- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 # 쿼리를 작성하는 목표, 확인할 지표 : 영화별 배우와 배역 평면화 # 쿼리 계산 방법 : UNNEST # 데이터의 기간 : # 사용할 테이블 : array_exercises # Join KEY : # 데이터 특징 : 배우와 배역은 별도의 컬럼으로 나와야 합니다 SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor; -- 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 # 쿼리를 작성하는 목표, 확인할 지표 : 배우, 배역, 장르 # 쿼리 계산 방법 : UNNEST # 데이터의 기간 : # 사용할 테이블 : array_exercises # Join KEY : # 데이터 특징 : 한 Row에 배우, 배역, 장르가 모두 표시되어야 함 SELECT title, actor.actor, actor.character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre; -- 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 # 쿼리를 작성하는 목표, 확인할 지표 : 앱 로그 데이터 평면화 # 쿼리 계산 방법 : UNNEST # 데이터의 기간 : # 사용할 테이블 : app_logs # Join KEY : # 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!! 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) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 # 쿼리를 작성하는 목표, 확인할 지표 : # 쿼리 계산 방법 : MAX, IF, GROUP BY # 데이터의 기간 : # 사용할 테이블 : orders # Join KEY : # 데이터 특징 : 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; -- 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 # 쿼리를 작성하는 목표, 확인할 지표 : # 쿼리 계산 방법 : SUM, IF, GROUP BY # 데이터의 기간 : # 사용할 테이블 : orders # Join KEY : # 데이터 특징 : 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; -- 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 # 쿼리를 작성하는 목표, 확인할 지표 : # 쿼리 계산 방법 : MAX, IF, GROUP BY # 데이터의 기간 : # 사용할 테이블 : orders # Join KEY : # 데이터 특징 : 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; -- 연습문제 4) app_log를 pivot하기 # 쿼리를 작성하는 목표, 확인할 지표 : app_log 데이터 PIVOT 테이블로 변경 # 쿼리 계산 방법 : UNNEST, MAX, IF, GROUP BY # 데이터의 기간 : 2022-08-01 # 사용할 테이블 : app_logs # Join KEY : # 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!! WITH base AS( SELECT user_id, event_date, event_name, event_timestamp, 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 avdanced.app_logs CROSS JOIN UNNEST (event_params) AS event_param WHERE event_date ='2022-08-01' GROUP BY ALL ) SELECT event_date, COUNT(user_id) AS cnt FROM base WHERE event_name = 'click_cart' GROUP BY ALL;(3) 퍼널 ( Funnel )# 쿼리를 작성하는 목표, 확인할 지표 : screen_view-welcome, screen_view-home, screen_view-food_category, screen_view-restaurant, screen_view-cart, click_payment-cart 순서대로 step_number를 지정하고 퍼널 분석하기 # 쿼리 계산 방법 : MAX, IF, UNNEST, CASE WHEN # 데이터의 기간 : 2022-08-01 ~ 2022-08-18 # 사용할 테이블 : app_logs # Join KEY : # 데이터 특징 : event_params안에 ARRAY와 STRUCT 구조가 공존하고 있고 값이 string_value와 int_value 두 종류로 나뉨 확인 필요!! 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 fire_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 ), fiter_event_and_concat_event_and_screen AS ( -- event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용) SELECT * EXCEPT(event_name, fire_screen, event_timestamp), CONCAT(event_name, "-", fire_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 -- step_number : CASE WHEN을 사용해 숫자 지정 SELECT event_name_with_screen, CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM fiter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL WHERE user_pseudo_id = "1350836585.3421064109" -- 일자별 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 fiter_event_and_concat_event_and_screen GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date;배운점ARRAY와 STRUCT 라는 조금은 생소할 수 있는 데이터 타입과 이를 어떻게 하면 평면화로 풀고 어떻게 데이터를 처리할 수 있을지를 조금은 알 수 있었고BigQuery로 생각보다 많은 부분을 할 수 있구나 생각이 들면서 이를 프로젝트에 연결시키면 어떨까 하는 재밌는 고민이 생긴거 같다 BIgQuery를 배우고 싶은데 어떤 데이터로 어떻게 다뤄야 할지 막막했던 찰라에 많은 것을 배울 수 있던 1주차 였다

안녕하세요!!원인 찾아서 해결했습니다.!

✅연습문제 (1) Array & Struct -- 연습문제 1) 영화 제목 별로 장르를 unnest하여 출력 -- select 절에서 새로운 이름으로 사용해야 unnest한 값이 출력됨 (기존의 array칼럼은 사용하지 않기) select title, genre from`avdanced.array_exercises` as ori ,unnest(genres) as genre --연습문제 2) 영화별로 배우와 배역을 출력. -- safe_offset 사용시, 모든 칼럼에 대하여 일일히 [0], [1].. 해줘야 하는 문제 -> UNNEST 사용. select title, ac.actor, ac.character from`avdanced.array_exercises` as ori ,unnest(actors) as ac -- 연습문제 3) title, actor, character, genre 출력 select title, ac.actor, ac.character, genre from`avdanced.array_exercises` as ori ,unnest(actors) as ac ,unnest (genres) as genre --연습문제 4) 배열을 풀기 select user_id, event_date, event_name, user_pseudo_id, eve.key, eve.value.string_value, eve.value.int_value from`avdanced.app_logs_temp` as ori , unnest (event_params) as eve where event_date = '2022-08-01' (2) Pivot 연습문제--Pivot : 집계함수(if (조건문, true일 때 값, False일때 값)) as new_column + GROUP BY (False일때의 값은 NULL또는 0으로 해주기) --1) id를 기준으로 amount sum을 pivot, order_date를 행, user_id를 열 -- 연습문제 1-1) 서브쿼리 사용하기 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 `avdanced.orders` group by order_date, user_id ) group by order_date order by order_date -- 연습문제 1-2) 간략하게 작성하기 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 `.avdanced.orders` group by order_date order by order_date --연습문제 2) orde_date별로 amount의 합을 pivot (user_id row, drder_date column) -- (`)backstick을 사용하여 별칭지정 -- any_value: 그룹화할 대상 중 임의의 값을 선택(NULL 제외). any_value에서는 나머지 값이 NULL이거나 확정적으로 이값이 나올 것이다 기대할 때 사용! -- ANY_VALUE: 임의의 값을 반환하며, 특정한 규칙이나 일관성을 요구하지 않을 때 사용. (대표값, 랜덤 샘플링) -- MAX: 그룹 내에서 가장 큰 값을 반환하며, 항상 일관된 결과를 제공합니다. 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 `avdanced.orders` group by user_id order by user_id -- +) 강의 내용 (any_value) -- select -- user_id, -- any_value(if(order_date='2023-05-01', amount, 0)) as `2023-05-01`, -- any_value(if(order_date='2023-05-02', amount, 0)) as `2023-05-02`, -- any_value(if(order_date='2023-05-03', amount, 0)) as `2023-05-03`, -- any_value(if(order_date='2023-05-04', amount, 0)) as `2023-05-04`, -- any_value(if(order_date='2023-05-05', amount, 0)) as `2023-05-05` -- from `avdanced.orders` -- group by user_id -- order by user_id --연습문제 3) user_id, order_date별로 주문이 있으면 1, 없으면 0으로 pivot, user_id row, order_date column. 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 `avdanced.orders` group by user_id order by user_id -- 연습문제 4) app_log를 pivot하기 with date_user_cnt as( select ori.user_id, ori.event_date, ori.event_name, ori.event_timestamp, ori.user_pseudo_id, max(if(eve.key = "firebase_screen", eve.value.string_value, null)) as firebase_screen, max(if(eve.key ="food_id", eve.value.int_value, null)) as food_id, max(if(eve.key ="session_id", eve.value.string_value, null)) as session_id from`avdanced.app_logs_temp` as ori , unnest (event_params) as eve where event_date ='2022-08-01' group by all ) select event_date, count(user_id) as user_cnt from date_user_cnt where event_name ="click_cart" group by event_date (3) 퍼널 연습문제-- 퍼널 별 유저수 집계 -- 1-1) UNNEST하기 & PIVOT하기 WITH base AS( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_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, MAX(IF(param.key = 'firebase_screen', param.value.string_value,NULL)) AS firebase_screen, from `bigquery-432401.avdanced.app_logs_temp` , unnest (event_params) as param where event_date between '2022-08-01' and '2022-08-18' GROUP BY ALL ), base2 AS( -- 1-2) CONCAT하기 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") ), base3 AS ( --1-3) 일자별 이벤트 별 집계 SELECT event_date, event_name_with_screen, CASE WHEN event_name_with_screen = "screen_view-welcome" THEN 1 WHEN event_name_with_screen = "screen_view-home" THEN 2 WHEN event_name_with_screen = "screen_view-food_category" THEN 3 WHEN event_name_with_screen = "screen_view-restaurant" THEN 4 WHEN event_name_with_screen = "screen_view-cart" THEN 5 WHEN event_name_with_screen = "click_payment-cart" THEN 6 ELSE NULL END AS step_number, COUNT(DISTINCT user_pseudo_id) AS cnt FROM base2 GROUP BY ALL HAVING step_number IS NOT NULL ) --1-4) 집계한 데이터를 PIVOT SELECT event_date, MAX(IF(base3.event_name_with_screen ="screen_view-welcome", cnt, NULL)) AS screen_view_welcome, MAX(IF(base3.event_name_with_screen ="screen_view-home", cnt, NULL)) AS screen_vie_home, MAX(IF(base3.event_name_with_screen ="screen_view-food_category", cnt, NULL)) AS screen_view_food_category, MAX(IF(base3.event_name_with_screen ="screen_view-restaurant", cnt, NULL)) AS screen_view_restaurant, MAX(IF(base3.event_name_with_screen ="screen_view-cart", cnt, NULL)) AS screen_view_cart FROM base3 GROUP BY ALL ✅배운점‘cross join unnest 칼럼 as 별칭’에서 반드시 ‘AS 별칭’ 을 이용해야 ‘Cannot access field on a value with type ARRAY’ 에러가 안 뜬다.빅쿼리에서는 ‘’이 아닌, ``을 사용하여야 한다. pivot이라고 하면 pivot()함수를 반드시 사용해야 편할 것이라고 생각했었는데, max와 if로 pivot을 구현하는 것이 인상 깊었다.SQL 기반이라고 하면 모든 DB가 비슷한 기능을 가지고 있다고 생각했는데, DB마다 생성된 함수 및 기능이 미묘하게 다르다는 것을 느꼈다. 예를 들어 빅쿼리에서 배운 countif()을 MySQL에서 사용했는데 안되는 것도 그 예가 아닐까.생각보다 빅쿼리와 PostgreSQL에서 사용하는 함수가 동일한 게 많아 인상적이었다.✅익숙해져야 할 부분퍼널 분석을 배우면서, 이론적으로 이해하는 것은 쉽지만 내가 아는 쿼리적인 지식을 실제 데이터에 적용하는 것은 생각보다 손이 많이 가고 쉽지 않은 작업이라는 것을 느꼈다. 많이 해봐야 노하우가 생길 것 같다. 어떤 데이터를 어떻게 해야 어떤 결과가 나오고, 이를 어떻게 이용하고 해석할 수 있는지에 대한 일련의 과정에 익숙해져야 할 것 같다. TIMESTAMP를 DATE로 바꾸고 확인하는 부분에 대한 연습이 필요 쿼리 작성 시, 예약어를 대문자로 사용하는 것이 좋다고 알고 있는데, 아직 이 부분이 익숙하지 않아 연습이 필요하다.코드를 작성하면서 문장을 그대로 쿼리로 옮기는 편이라서 그런지 서브 쿼리를 사용하는 경우가 많다. 단순히 문장을 쿼리로 옮기기 보다 어떻게 하면 더 간결하고 가독성 높은 쿼리를 작성할 수 있을 지 고민하는 습관을 들여야 겠다.함수 이름 정하는 거 중요한 건 아는데, 잘 안되는 것 같다. 사용 기능을 이용하여 함수 이름 짓는 습관을 지어야겠다.

 강의를 들으면서 문제에 대한 답은 이해했습니다! 그런데 4번 문제 푸는 과정에서 위의 그림처럼 UNNEST안에 event_params.key를 넣어봤는데 아래의 에러가 떴습니다. 그럼 event_params는 STRUCT 구조체를 포함하는 ARRAY배열이라는 말일까요? 강의에서 array_excercised 스키마를 확인하며 event_params의 유형이 RECORD이면 보통 STRUCT 구조체를 의미한다고 말씀해주셨는데 event_params 자체는 ARRAY일까요 STRUCT일까요...?

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

변수 선언해서 사용할때와 EXECUTE IMMEDIATE 사용할 때의 차이점은 무엇인가요?DECLARE custom_event STRING DEFAULT 'screen_view'; SELECT COUNT(*) AS output FROM advanced.app_logs WHERE event_date = '2022-08-01' AND event_name = custom_event GROUP BY ALL

예제 쿼리 결과에 대해 궁금한 점이 있어 질문 드립니다쿼리 결과 i는 11이 왜 나왔는지 궁금합니다. i 변수는 1,2,3,4,5,6,7,8,9,10,11 있는데, 쿼리 결과에서 MAX함수를 사용하지 않았는데도 11이 나온 이유는 뭔가요?쿼리 결과 sum_value는 25가 왜 나왔는지 궁금합니다.sum_value 변수는 1,3,5,7,9 (홀수) 있는데, 쿼리 결과에서 sum함수를 사용하지 않았는데도 25가 나온 이유는 뭔가요?

스케줄 쿼리 강의를 듣다가 이해가 어려운 부분이 있어서 질문 드립니다스케줄 쿼리 작성하고 나서 특정 필터링할 때 백필 예약 하지 않고 where절에 직접 날짜 필터링해서 수정해도 될까요? 백필 예약을 하는 게 쿼리를 바꾸지 않고 날짜 필터링하는 걸로 이해했습니다!백필 예약을 할 때 쿼리에 @run_date/@run_time을 필수로 잘성해야하는 거 맞을까요?@run_date/@run_time 을 넣지 않으면 백필 예약을 해도 쿼리가 실행되지 않는 걸로 이해했습니다!

리텐션 연습문제 과제 업로드 합니다!피드백 부탁드립니다!감사합니다!

리텐션 연습 문제 푸는 중에 어려운 점이 있어 질문 남깁니다!click_payment 이벤트 기준으로 cohort 리텐션 분석 쿼리 올바르게 작성했을까요? 유저수의 합계가 0주차일때 안 맞는 거 같습니다... 코호트 분석 시각화 했을 때 저렇게 비어 보이는 경우에는 어떻게 해석하면 좋을까?  WITH base AS ( SELECT DISTINCT user_id, event_name, TIMESTAMP_MICROS(event_timestamp) AS event_datetime, DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date, user_pseudo_id FROM `advanced.app_logs` WHERE event_date > '2022-08-01' AND event_name = 'click_payment' ) ,first_week_data AS ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id),WEEK(MONDAY)) AS first_week, DATE_TRUNC(event_date,WEEK(MONDAY)) AS event_week FROM base ) ,weeks_after_first_week_data AS ( SELECT *, DATE_DIFF(event_week,first_week,WEEK) AS weeks_after_first_week FROM first_week_data ) ,active_user_count AS ( SELECT first_week, weeks_after_first_week, COUNT(DISTINCT user_pseudo_id) AS active_users FROM weeks_after_first_week_data GROUP BY ALL ) ,cohort_user_count AS ( SELECT *, FIRST_VALUE(active_users) OVER(PARTITION BY first_week ORDER BY weeks_after_first_week) AS cohort_users FROM active_user_count ) SELECT *, ROUND(SAFE_DIVIDE(active_users,cohort_users),2) AS rentention_rate FROM cohort_user_count

코호트 리텐션 시각화하기 부분에서피벗테이블의 값에 설정에 AVERAGE 하는 이유는 무엇인가요?

쿼리 작성하다가 DISTINCT에 대해 궁금한 점이 생겨 질문 남깁니다!WITH 문 base 와 first_week_data에서 DISTINCT는 중복 방지 즉, 비용 절약을 위해서 쓰는 것으로 이해했습니다. 그렇다면 DISTINCT를 쓰지 않아도 쿼리상 문제는 없는 게 맞을까요?user_count에서 DISTINCT는 유니크 유저수를 위해 꼭 사용해야하는 것으로 이해했습니다.WITH base AS ( SELECT DISTINCT user_id, event_name, DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime, DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul')) AS event_date, user_pseudo_id FROM advanced.app_logs ) ,first_week_data AS ( SELECT DISTINCT user_pseudo_id, DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id),WEEK(MONDAY)) AS first_week, DATE_TRUNC(event_date,WEEK(MONDAY)) AS week_date FROM base ) ,first_week_diff_data AS ( SELECT *, DATE_DIFF(week_date,first_week,week) AS first_week_diff FROM first_week_data ) ,user_count AS ( SELECT first_week_diff, COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM first_week_diff_data GROUP BY ALL ) ,first_user_count AS ( SELECT first_week_diff, user_cnt, FIRST_VALUE(user_cnt) OVER (ORDER BY first_week_diff) AS first_user_cnt FROM user_count ) SELECT *, ROUND(SAFE_DIVIDE(user_cnt,first_user_cnt),2) AS rentention_ratio FROM first_user_count

DATETIE_DIFF 함수 사용할 때 오류가 발생합니다무엇이 잘못되었을까요..?WITH base AS ( SELECT event_date, event_timestamp, DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') AS event_datetime, event_name, user_id, user_pseudo_id FROM advanced.app_logs ) , prev_event_datetime AS ( SELECT *, LAG(event_datetime) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS prev_event_datetime FROM base ) SELECT *, DATETIME_DIFF(event_datetime,prev_event_datetime, SECOND) AS second_diff FROM prev_event_datetime

현황 파악하기 링크 다시 올립니다!피드백 부탁드립니다~

https://www.notion.so/FOODIE-APP-0186b8e16c164701afc3d7afd66a551f?pvs=4현황 파악해봤습니다! 피드백 부탁드립니다~

"3-13. 리텐션 과제 : 꼭 풀어보시고 제출해주세요" 강의 과제 완료하여 제출 합니다. 링크 : 리텐션 분석 과제  "쿼리 검토 요청"이라고 기재되어 있는 부분 쿼리에 이상이 없는지 확인해주시면 감사하겠습니다!그리고 과제하다가 궁금한 점이 있어 "문의사항"도 남겨두었습니다. 해당 부분도 답변 부탁드립니다! 전반적으로 리텐션 분석을 진행하는 과정이 올바른지에 대해서도 피드백을 주시면 많은 도움이 될 것 같습니다!  강의를 통해 정말 많이 배울 수 있었습니다!감사합니다.

강의 보면서 '퍼널별 유저 수 집계' 쿼리 작성했는데 결과값이 올바르게 나오지 않아요... 어디서 잘못 되었을까요?WITH base AS( SELECT event_date, event_name, DATETIME(TIMESTAMP_MICROS(event_timestamp),'Asia/Seoul') as event_timestamp, event_param, user_id, user_pseudo_id, platform, -- event_parma.key PIVOT 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) -- event_name + screen (필요한 이벤트만 where 조건에 걸어서 사용) ,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_name_with_screen, -- step_number 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_pavement-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

# base 쿼리 작성 전 생각 흐름 ---------------------------------------------------- ## 1. 테이블 내 데이터 구조 파악 -- select -- * -- from advanced.app_logs -- where -- event_date = '2022-08-01' -- 목적에 맞는 쿼리를 기존 데이터로 구현할 수 있는지 여부 확인 -- 연습문제 결과물 컬럼인 'event_name_with_screen', 'step_number', 'cnt'는 기존 컬럼에 없음. -- 기존 컬럼에 없기 때문에 해당 컬럼을 어떻게 구현해내야 할 지 생각해보기 ## 2. event_name_with_screen 컬럼 구현 순서 및 방법 -- event_name_with_screen는 다음과 같은 value를 가짐 -- 1. screen_view-welcome -- 2. screen_view-home -- 3. screen_view-food_vategory -- 4. screen_view-restaurant -- 5. screen_view-cart -- 6. click_payment-cart -- 위 6가지 value가 기존 컬럼에 있는지 확인해보기 -- 있는 경우 -- 기존 컬럼 활용 -- 없는 경우 -- concat 등으로 새롭게 구현 -- 연습문제에서는 event_name과 event_params.key에 있는 value를 concat 했음. -- event_params.key는 데이터타입이 struct이기 때문에 unnest 필요. -- select -- event_name, -- unnest_event_params -- from advanced.app_logs -- cross join unnest(event_params) as unnest_event_params -- where -- event_date = '2022-08-01' -- unnest 결과물에 피봇테이블 적용하기 -- 피봇테이블로 만들면 직관적으로 event_name과 concat 하기가 용이해서. -- select -- event_name, -- event_date, -- event_timestamp, -- user_pseudo_id, -- max(if(unnest_event_params.key = 'firebase_screen', unnest_event_params.value.string_value, null)) as firebase_screen, -- max(if(unnest_event_params.key = 'food_id', unnest_event_params.value.int_value, null)) as food_id, -- max(if(unnest_event_params.key = 'session_id', unnest_event_params.value.string_value, null)) as session_id -- from advanced.app_logs -- cross join unnest(event_params) as unnest_event_params -- where event_date between '2022-08-01' and '2022-08-18' -- group by 1, 2, 3, 4 ---------------------------------------------------- with base as ( select event_name, event_date, event_timestamp, user_pseudo_id, max(if(unnest_event_params.key = 'firebase_screen', unnest_event_params.value.string_value, null)) as firebase_screen, max(if(unnest_event_params.key = 'food_id', unnest_event_params.value.int_value, null)) as food_id, max(if(unnest_event_params.key = 'session_id', unnest_event_params.value.string_value, null)) as session_id from advanced.app_logs cross join unnest(event_params) as unnest_event_params where event_date between '2022-08-01' and '2022-08-18' group by 1, 2, 3, 4 ), base2 as ( select *, concat(event_name, '-', firebase_screen) as event_name_with_screen from base ) ## 일자별 퍼널 쿼리 , base3 as (select event_date, event_name_with_screen, # 2개 이상 조건을 한 개 컬럼에 적용해야 하기 때문에 case when 사용 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 컬럼명) 사용 count(distinct user_pseudo_id) as cnt from base2 group by all # null 값 제외하기 위해서 그룹에 필터링 거는 having 사용 having step_number is not null # 1일부터 보고 싶기 때문에 오름차순(asc) 적용 order by 1 asc) ## 일자별 쿼리 (피벗테이블 적용) select event_date, max(if(event_name_with_screen = 'screen_view-welcome', cnt, 0)) as screen_view_welcome, max(if(event_name_with_screen = 'screen_view-home', cnt, 0)) as screen_view_home, max(if(event_name_with_screen = 'screen_view-food_category', cnt, 0)) as screen_view_food_category, max(if(event_name_with_screen = 'screen_view-restaurant', cnt, 0)) as screen_view_restaurant, from base3 group by all order by 1 asc

빅쿼리 날짜 타입 입력할때 큰따옴표, 작은 따옴표 둘 중에 아무거나 입력해도 상관없을까요?  

어느 부분에서 오류 났는지 모르겠어요!