연습 문제(1) Array, Struct-- 1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 SELECT title, genre # genres 아님 FROM `advanced.array_exercises` CROSS JOIN UNNEST(genres) AS genre처음에 'title'을 기준으로 묶여 있길래 UNNEST의 요소로 'title'을 넣으려 함 → ERROR생각해보니 CROSS JOIN이 목적이므로 개별 값을 가지는 'genres'를 기준으로 UNNEST-- 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 SELECT title, actor, character FROM `advanced.array_exercises`, UNNEST(actors)CROSS JOIN 대신 ','를 통해 UNNEST를 시도해 봄 -- 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. -- 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다 SELECT title, ae2.actor, ae2.character, genre FROM ( SELECT title, actor.actor, actor.character, genres FROM `advanced.array_exercises` AS ae, UNNEST(actors) AS actor ) AS ae2, UNNEST(genres) AS genre-- 강의 코드 SELECT title, actor.actor, actor.character, genre FROM `advanced.array_exercises` AS ae, UNNEST(actors) AS actor, UNNEST(genres) AS genreUNNEST 두번해야해서 서브쿼리로 해결하려 시도 → 서브 쿼리 안하고 UNNEST 두번 사용 가능실행 순서 : FROM → JOIN → SELECTcmd + D : 다음 해당 항목 같이 선택 → 반복되는 단어 쉽게 수정 가능-- 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 SELECT user_id, event_date, event_name, user_pseudo_id, EV.key, EV.value.string_value, EV.value.int_value FROM `advanced.app_logs` AS al, UNNEST(event_params) AS EV-- 강의 코드 SELECT event_date, event_timestamp, event_name, event_param.key AS key, event_param.value AS value, user_id FROM `advanced.app_logs`, UNNEST(event_params) AS event_param WHERE event_date = "2022-08-01"WHERE에 조건을 넣어 연산량 낮출 수 있음event_params로 UNNEST했을때 value값만 사용해도 string, int 둘다 표시됨 (2) PIVOTparquet : 대용량 데이터를 효율적으로 저장하고 처리하기 위해 설계된 컬럼 기반의 저장 형식공통 사항 : 행과 열에 대한 정의가 한번에 떠오르지 않았음-- 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. -- 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 SELECT order_date, SUM(IF(user_id=1, amount, 0)) AS user_1, SUM(IF(user_id=2, amount, 0)) AS user_2, SUM(IF(user_id=3, amount, 0)) AS user_3, FROM `advanced.orders` GROUP BY order_date ORDER BY order_dateNULL과 0은 다르다서브쿼리 이용한 방법도 있음(집계 후 PIVOT)-- 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. -- user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 SELECT user_id, SUM(IF(order_date="2023-05-01", amount, 0)) AS `2023-05-01`, SUM(IF(order_date="2023-05-02", amount, 0)) AS `2023-05-02`, SUM(IF(order_date="2023-05-03", amount, 0)) AS `2023-05-03`, SUM(IF(order_date="2023-05-04", amount, 0)) AS `2023-05-04`, SUM(IF(order_date="2023-05-05", amount, 0)) AS `2023-05-05`, FROM `advanced.orders` GROUP BY user_idAS에서 “ ”(따옴표) 대신 (백틱) 사용ANY_VALUE : 그룹화 할 대상 중에 임의의 값을 선택한다(NULL 제외) 따라서 나머지 값들이 NULL이거나 값이 확정적일 때 사용-- 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조건문에서 행 별로 주문 있으면 1, 없으면 0 설정한 뒤 MAX 조건 걸어줌 → 최종적으로 값이 있으면 1횟수일 경우에는 SUM-- user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? => 쿼리 작성이 어려움 -- => key를 Column으로 두고, string_value나 int_value를 Column의 값으로 설정하는 것이 필요 SELECT user_id, event_date, event_name, event_timestamp, ANY_VALUE(IF(event_param.key="firebase_screen", event_param.value.string_value, NULL)) AS `firebase_screen`, ANY_VALUE(IF(event_param.key="food_id", event_param.value.int_value, NULL)) AS `food_id`, ANY_VALUE(IF(event_param.key="session_id", event_param.value.string_value, NULL)) AS `session_id`, FROM `advanced.app_logs`, UNNEST(event_params) AS event_param WHERE (event_date = "2022-08-01") AND (event_name = "click_cart") AND (user_id = 32888) # 선택 사항 GROUP BY user_id, event_date, event_name, event_timestamp문제를 봤을 때 문제가 원하는게 무엇인지 한번에 파악되지 않았음32888의 click cart 행위에만 집중하는 것으로 판단GROUP BY ALL 을 통해 한번에 해결 가능(3) 퍼널 쿼리WITH base AS ( SELECT user_id, user_pseudo_id, event_date, event_name, event_timestamp, platform, ANY_VALUE(IF(event_param.key="firebase_screen", event_param.value.string_value, NULL)) AS `firebase_screen`, ANY_VALUE(IF(event_param.key="food_id", event_param.value.int_value, NULL)) AS `food_id`, ANY_VALUE(IF(event_param.key="session_id", event_param.value.string_value, NULL)) AS `session_id`, FROM `advanced.app_logs`, UNNEST(event_params) AS event_param WHERE event_date BETWEEN "2022-08-01" AND "2022-08-18" GROUP BY ALL )event_name과 event_params가 연결되어야 하므로 우선 UNNEST 필요하다고 생각함 → 이전에 사용했던 쿼리문 그대로 사용 후 WITH하지만 이후 과정에서 CONCAT 함수를 떠올리지 못해 진행이 불가했음), concat_event_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 )이후에 concat_event_screen이라는 이름으로 테이블 재가공 해줌CONCAT을 통해 event_name-firebase-screenevent_timestamp 알아보기 쉽게 바꿔준 후 제거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 concat_event_screen GROUP BY ALL HAVING step_number IS NOT NULL # CASE-WHEN에서 포함되지 않는 행위는 제거CASE-WHEN 이용해서 step_number 컬럼 만들어줌COUNT + DISTINC 이용해 해당 퍼널에 진입한 고유 유저 수 세주기퍼널이 아직 익숙하지 않아 혼자서 해결할 수는 없었음+) 일자별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 concat_event_screen GROUP BY ALL HAVING step_number IS NOT NULL # CASE-WHEN에서 포함되지 않는 행위는 제거 ORDER BY event_date간단한 EDA지금까지 배운 것을 바탕으로 간단한 데이터 탐색 및 분석행 개수 → 731873행2022-08-01(월) ~ 2023-01-20(금) 의 데이터user_id 고유 개수 → 49678pseudo_user_id 고유 개수 → 52823event_params → 22개아래 표는 전체 기간에 대한 각 event_params별 개수 총합(내림차순)-> 개수가 같은 항목들이 존재-> 앱 작동 흐름에 대해 알 필요 있어 보임event_date에 따른 pseudo_user_id 고유 개수 그래프-> 8월부터 10월 중순까지는 이용자수가 늘어나는 추세-> 이후 안정기 들어서면서 일정한 주기로 반복되는 형태느낀점보통 데이터 분석 프로젝트를 하면 Python이나 R을 통해 진행했기에 아직 쿼리문이 생소함머릿속으로는 대충 어떤 식으로 데이터를 건드려야겠다는 생각이 들긴 하지만, 표현이 잘 안되는 경우가 많아서 까다로웠던 것 같음업무 경험이 없는 입장에서 퍼널 분석 같은 것들은 실제로 해본적이 없는데, 비교적 이해하기 쉽게 알려주신 것 같음 

연습문제(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 기능 신기하네요.. 

안녕하세요. 열혈 수강자 이지수입니다 : )문제가 하나씩 풀리니 넘 재밌고 뿌듯합니다 ㅎㅎ17번 문제에서 풀어준 포켓몬 수를 B전체 포켓몬 수를 A로 두었는데요COUNT IF B/A 이런식으로 축약해서 연산할 수는 없나요? 감사합니다.SELECT trainer_id, COUNTIF(status="Released") as B , #풀어준 포켓몬의 수 COUNT(pokemon_id) as A, #전체 포켓몬의 수 COUNTIF(status="Released")/COUNT(pokemon_id) as released_ratio FROM basic.trainer_pokemon GROUP BY trainer_id HAVING released_ratio >=0.2

목차과제별 작성 쿼리쿼리 작성 후 느낀점 과제별 작성 쿼리(1) ARRAY·STRUCT## 연습문제 1번-- select-- title,-- genre-- from advanced.array_exercises-- cross join unnest(genres) as genre## 연습문제 2번-- select-- title,-- actor.actor,-- actor.character,-- from advanced.array_exercises-- cross join unnest(actors) as actor## 연습문제 3번-- select-- title,-- actor.actor,-- actor.character,-- genre-- from advanced.array_exercises-- cross join unnest (actors) as actor-- cross join unnest (genres) as genre## 연습문제 4번-- select-- user_id,-- event_date,-- event_name,-- user_pseudo_id,-- event_param.key,-- event_param.value,-- event_param.value.int_value-- from advanced.app_logs-- cross join unnest(event_params) as event_param-- where-- event_date = '2022-08-01' PIVOT## 연습문제 1번-- select-- order_date,-- sum(if(user_id=1, amount, 0)) as user_1,-- sum(if(user_id=1, amount, 0)) as user_2,-- sum(if(user_id=1, amount, 0)) as user_3,-- from advanced.orders-- group by-- order_date-- order by-- order_date asc## 연습문제 2번-- select-- user_id,-- sum(if(order_date='2023-05-01', amount, 0)) as 2023-05-01,-- sum(if(order_date='2023-05-02', amount, 0)) as 2023-05-02,-- sum(if(order_date='2023-05-03', amount, 0)) as 2023-05-03,-- sum(if(order_date='2023-05-04', amount, 0)) as 2023-05-04,-- sum(if(order_date='2023-05-05', amount, 0)) as 2023-05-05-- from advanced.orders-- group by-- user_id## 연습문제 3번-- select-- user_id,-- max(if(order_date = '2023-05-01', 1, 0)) as 2023-05-01,-- max(if(order_date = '2023-05-02', 1, 0)) as 2023-05-02,-- max(if(order_date = '2023-05-03', 1, 0)) as 2023-05-03,-- max(if(order_date = '2023-05-04', 1, 0)) as 2023-05-04,-- max(if(order_date = '2023-05-05', 1, 0)) as 2023-05-05-- from advanced.orders-- group by-- user_id## 앱 로그 데이터 배열 PIVOT-- with base as (-- select-- user_id,-- event_date,-- event_name,-- user_pseudo_id,-- event_param.key as key,-- event_param.value.string_value as string_value,-- event_param.value.int_value as int_value-- from advanced.app_logs-- cross join unnest(event_params) as event_param-- where-- event_date = '2022-08-01' and-- event_name = 'click_cart'-- )-- select-- user_id,-- event_date,-- event_name,-- user_pseudo_id,-- max(if(key = 'firebase_screen', string_value, null)) as firebase_screen,-- max(if(key = 'food_id', int_value, null)) as food_id,-- max(if(key = 'session_id', string_value, null)) as session_id,-- from base-- group by all 퍼널## step1 : 퍼널정의, 유저 집계 방식 정하기## step2 : 데이터 확인-- select-- *,-- event_param.key,-- event_param.value.string_value,-- event_param.value.int_value,-- from advanced.app_logs-- cross join unnest(event_params) as event_param-- where-- event_date between '2022-08-01' and '2022-08-18'-- limit 100## step3 : pivot 해서 concat 하기 좋은 형태로 만들기with base as (select*,max(if(event_param.key = 'firebase_screen', event_param.value.string_value, null)) as firebase_screen,max(if(event_param.key = 'food_id', event_param.value.int_value, null)) as food_id,max(if(event_param.key = 'session_id', event_param.value.string_value, null)) as session_idfrom advanced.app_logscross join unnest(event_params) as event_paramwhereevent_date between '2022-08-01' and '2022-08-18'group by all)## step4 : concat해서 퍼널 컬럼 정의하기, base2 as (select*,concat(event_name, '-', firebase_screen) as event_name_with_screenfrom base)## step5 : 퍼널별 유저수 쿼리 작성 I step_number, cnt 컬럼 생성-- select-- event_name_with_screen,-- case-- when(event_name_with_screen = 'screen_view-welcome') then 1-- when(event_name_with_screen = 'screen_view-home') then 2-- when(event_name_with_screen = 'screen_view-food_category') then 3-- when(event_name_with_screen = 'screen_view-restaurant') then 4-- when(event_name_with_screen = 'screen_view-cart') then 5-- when(event_name_with_screen = 'click_payment-cart') then 6-- else null-- end as step_number,-- count(distinct user_pseudo_id) as cnt-- from base2-- group by-- 1-- having-- step_number is not null## step6 : 일자별 퍼널 쿼리 작성selectevent_date,event_name_with_screen,casewhen(event_name_with_screen = 'screen_view-welcome') then 1when(event_name_with_screen = 'screen_view-home') then 2when(event_name_with_screen = 'screen_view-food_category') then 3when(event_name_with_screen = 'screen_view-restaurant') then 4when(event_name_with_screen = 'screen_view-cart') then 5when(event_name_with_screen = 'click_payment-cart') then 6else nullend as step_number,count(distinct user_pseudo_id) as cntfrom base2group by allhavingstep_number is not nullorder byevent_date 쿼리 작성 후 느낀점(1) ARRAY·STRUCT I 쿼리를 풀고난 이후 느낀점1. '~별'이라고 해서 무조건 GROUP BY는 아니다ARRAY 또는 STRUCT 같은 구조체 타입 컬럼을 만나기 전에는 '~별'이라는 텍스트가 있을 때 GROUP BY를 떠올렸습니다.그러나 구조체 타입 컬럼을 다루고난 이후, 생각이 달라졌습니다.비구조체 컬럼을 구조체 컬럼과 함께 출력할 때는 '~별'을 GROUP BY로 출력할 수 없다는 걸 알았습니다.2. CROSS JOIN을 2개 이상 사용할 때는CROSS JOIN을 2개 이상 입력할 때는 쉼표를 사용하지 않는다는 걸 알았습니다.다른 구간에서 요소를 2개 이상 입력할 때는 쉼표를 사용해서 요소를 분리 했었는데, CROSS JOIN은 아니였습니다.CROSS JOIN을 2개 이상 사용할 때 쉼표를 쓰면 다음과 같은 오류가 발생하는 걸 알았습니다 :--> Syntax error: Unexpected keyword CROSS at [24:1] (2) PIVOT I 쿼리를 풀고난 이후 느낀점1. 날짜 데이터와 다르게 일반 숫자데이터는 따옴표를 쓰지 않는다EX)if(user_id=1, amount, 0) --> Oif(user_id='1', amount, 0) --> X+if(user_id='2024-10-22', amount, 0) --> O2. 컬럼명에 대시(또는 한글)를 사용할 때는 따옴표가 아닌 백틱으로 컬럼명을 감싼다대시를 컬럼명으로 할 때는 백틱을 사용.as 2023-05-01 --> Oas '2023-05-01' --> X+as 빠짝스터디 --> Oas '빠짝스터디' --> X3. 일반 테이블을 피봇테이블로 만들 때 고려해 봐야 할 2가지1. 행에 2개 이상 중복이 있는지 확인한다.2. 2개 이상 중복이 있지만, 피봇테이블로 변경 했을 때 효율적인지 생각해 본다.위 2개 조건을 모두 충족하는 컬럼을 피봇테이블 만들 때 기준 컬럼으로 삼음.EX)*'앱 로그 데이터 배열 PIVOT 하기' 테이블 참고student 열중복 있음 (A, A, A, B, B, B)기준 열로 삼으면 → 각 학생의 모든 과목 점수를 한눈에 볼 수 있어서 효율적!subject 열중복 있음 (수학, 영어, 과학이 2번씩)기준 열로 삼으면 → 과목별로 학생들의 점수를 볼 수는 있지만, 한 학생의 전체 성적을 보기는 어려움score 열중복이 없음.기준 열로 삼으면 → 같은 점수끼리 모이겠지만, 효율이 떨어짐. (3) 퍼널 I 쿼리를 풀고난 이후 느낀점1. concat 할 때 따옴표 사용 주의처음 concat 할 때 다음과 같이 입력했습니다.concat(event_name, '-', 'firebase_screen')'firebase_screen'라고 작성했었는데, 이렇게 하니깐 firebase_screen 내에 있는 데이터랑 concat 된 게 아니라 'firebase_screen'라는 문자 자체랑 concat이 되어서 혼란스러웠습니다.concat 내에서는 따옴표를 쓰지 않고, 컬럼끼리 연결해야겠다는 걸 알게 됐습니다.

변수 선언해서 사용할때와 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 을 넣지 않으면 백필 예약을 해도 쿼리가 실행되지 않는 걸로 이해했습니다!

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

안녕하세요! 덕분에 남이 짠 쿼리의 의미는 파악이 가능한 수준이 될 것 같다는 자신감이 든 수강생입니다! 감사합니다.2-6 5번 문제를 직면했을 때 동명이인 = 이름 이외 다른 값이 다 다른 경우로 정의했고, 다행히 강사님의 파일에는 해당하지 않지만, 이름이 같고 다른 값도 다 같은 "중복 데이터"가 있을 수 있다는 가능성도 생각을 했습니다.그래서 혼자 골똘히 고민했는데, (조건이 2개인가? "이름이 같다" and "다른 컬럼이 다르다" 등) 답이 나오지 않아 재생해보니 생각보다 단순한 쿼리여서 놀랐어요.동명이인이 있는 이름은 무엇일까요? + 전체 파일의 중복 데이터는 제거(ex. 엑셀의 중복 값 제거 )는 아래와 같이 하면 될까요?SELECT DISTINCT name, COUNT (name) AS CNT FROM basic.trainer GROUP BY name HAVING CNT>=2

리텐션 연습 문제 푸는 중에 어려운 점이 있어 질문 남깁니다!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

ORDER BY 2 DESC를 쿼리문에 추가한 것과 하지 않은 것이 동일한 결과가 나오는 것 같아 질문드립니다! 

코호트 리텐션 시각화하기 부분에서피벗테이블의 값에 설정에 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

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