1. ARRAY, STRUCT1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요.SELECT title , genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre ;2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. (배우와 배역은 별도의 컬럼으로 나와야 합니다)-- 동일한 단어에 대해 선택할 수 있는 함수 : cmd+d 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 , actor.character , genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre ;4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요.SELECT user_id , event_date , event_name , user_pseudo_id , event_param.key , event_param.value.string_value , event_param.value.int_value , platform FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS event_param ;2. PIVOT1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다.SELECT order_date , SUM(IF(user_id = 1, amount, NULL)) AS `user_id_1` , SUM(IF(user_id = 2, amount, NULL)) AS `user_id_2` , SUM(IF(user_id = 3, amount, NULL)) AS `user_id_3` FROM advanced.orders GROUP BY 1 ORDER BY 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 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 ;4) user_id = 32888이 카트 추가하기(click_cart)를 누를 때 어떤 음식(food_id)을 담았나요?WITH app_logs_info AS ( SELECT user_id , event_name , 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 GROUP BY 1, 2 ) SELECT food_id FROM app_logs_info WHERE user_id = 32888 AND event_name = 'click_cart' ; 3. Funnel1) 일자별, 이벤트별 집계WITH app_logs_info AS ( SELECT user_id , event_date , event_timestamp , event_name , user_pseudo_id , event_param.key , 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 event_date BETWEEN '2022-08-01' AND '2022-08-18' AND event_name IN ('screen_view', 'click_payment') GROUP BY ALL ) , add_step_number AS ( SELECT event_date , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date_time , user_id , user_pseudo_id , CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen , CASE CONCAT(event_name, '-', firebase_screen) WHEN 'screen_view-welcome' THEN 1 WHEN 'screen_view-home' THEN 2 WHEN 'screen_view-food_category' THEN 3 WHEN 'screen_view-restaurant' THEN 4 WHEN 'screen_view-cart' THEN 5 WHEN 'click_payment-cart' THEN 6 ELSE NULL END AS step_number FROM app_logs_info ) SELECT event_date , step_number , event_name_with_screen , COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM add_step_number WHERE step_number IS NOT NULL GROUP BY 1, 2, 3 ORDER BY 1, 2 ; 2) 집계 데이터 PIVOTWITH app_logs_info AS ( SELECT user_id , event_date , event_timestamp , event_name , user_pseudo_id , event_param.key , 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 event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY ALL ) , add_step_number AS ( SELECT event_date , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date_time , user_id , user_pseudo_id , CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen , CASE CONCAT(event_name, '-', firebase_screen) WHEN 'screen_view-welcome' THEN 1 WHEN 'screen_view-home' THEN 2 WHEN 'screen_view-food_category' THEN 3 WHEN 'screen_view-restaurant' THEN 4 WHEN 'screen_view-cart' THEN 5 WHEN 'click_payment-cart' THEN 6 ELSE NULL END AS step_number FROM app_logs_info WHERE event_name IN ('screen_view', 'click_payment') ) , agg_user_cnt AS ( SELECT event_date , step_number , event_name_with_screen , COUNT(DISTINCT user_pseudo_id) AS user_cnt FROM add_step_number WHERE step_number IS NOT NULL GROUP BY 1, 2, 3 ORDER BY 1, 2 ) SELECT event_date , MAX(IF(step_number = 1, user_cnt, NULL)) AS `screen_view-welcome` , MAX(IF(step_number = 2, user_cnt, NULL)) AS `screen_view-home` , MAX(IF(step_number = 3, user_cnt, NULL)) AS `screen_view-food_category` , MAX(IF(step_number = 4, user_cnt, NULL)) AS `screen_view-restaurant` , MAX(IF(step_number = 5, user_cnt, NULL)) AS `screen_view-cart` , MAX(IF(step_number = 6, user_cnt, NULL)) AS `click_payment-cart` FROM agg_user_cnt GROUP BY 1 ORDER BY 1 ;

안녕하세요 루키스님 강의에 질문이 있어서 글 작성합니다. rsp에 16을 더해주는 이유에 대해서 정확히 이해를 하지 못했습니다. push를 2회 하면서 이미 rsp가 16이 더해진 상태로 있다고 생각을 하고 있는데왜 또다시 16을 더해준것인지 잘 이해가 안갑니다.

예제 데이터 프레임에서 결측치를 np.nan으로 적어주셨는데, 결측치를 무조건 이렇게 사용해야 하는건가요?

1. ARRAY, STRUCT 연습문제연습문제 1SELECT title , genre FROM `advanced.array_exercises` CROSS JOIN UNNEST(genres) AS genre 연습문제 2SELECT title , actor.actor , actor.character FROM `advanced.array_exercises` CROSS JOIN UNNEST(actors) AS actor 연습문제 3SELECT 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[메모]여기서 CROSS JOIN 다음 라인에 WHERE actor = 'Chris Evans' 이렇게 조건을 걸어줄 경우 오류가 발생한다. 오류는 실행 순서와 관련이 있다. 실행 순서: FROM -> JOIN -> SELECT따라서 SELECT 에서 알리아스로 이름 붙인 actor 가 아닌, CROSS JOIN 결과를 사용하여 조건을 만들어야 한다. => WHERE actor.actor = 'Chris Evans'  연습문제 4SELECT user_id , event_date , event_name , user_pseudo_id , parameter.key AS key , parameter.value.string_value AS string_value , parameter.value.int_value AS int_value FROM `inflearn-bigquery-437203.advanced.app_logs` CROSS JOIN UNNEST (event_params) AS parameter WHERE event_date = "2022-08-01"  2. PIVOT 연습문제연습문제 1SELECT 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 `inflearn-bigquery-437203.advanced.orders` GROUP BY order_date ORDER BY order_date 연습문제 2SELECT 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 `inflearn-bigquery-437203.advanced.orders` GROUP BY user_id ORDER BY user_id[메모]알리아스로 컬럼명 지정할 때, 영어 제외하고 다른 문자열가 포함될 경우 → backtick (`) 으로 감싸준다. 연습문제 3SELECT 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 `inflearn-bigquery-437203.advanced.orders` GROUP BY user_id ORDER BY user_id 연습문제 4SELECT 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 `inflearn-bigquery-437203.advanced.app_logs` CROSS JOIN UNNEST(event_params) AS params WHERE event_date = '2022-08-01' GROUP BY ALL  3. 퍼널 쿼리 연습문제연습문제 1: 각 퍼널별 유저 수 집계-- 퍼널 단계: 6 -- screen_view(welcome) -- screen_view(home) -- screen_view(food_category) -- screen_view(restaurant) -- screen_view(cart) -- click_payment(cart) WITH funnel AS ( SELECT CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen , CASE WHEN event_name = 'screen_view' AND param.value.string_value = 'welcome' THEN 1 WHEN event_name = 'screen_view' AND param.value.string_value = 'home' THEN 2 WHEN event_name = 'screen_view' AND param.value.string_value = 'food_category' THEN 3 WHEN event_name = 'screen_view' AND param.value.string_value = 'restaurant' THEN 4 WHEN event_name = 'screen_view' AND param.value.string_value = 'cart' THEN 5 WHEN event_name = 'click_payment' AND param.value.string_value = 'cart' THEN 6 END AS step_number , user_pseudo_id FROM `inflearn-bigquery-437203.advanced.app_logs` CROSS JOIN UNNEST(event_params) AS param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' AND event_name IN ('screen_view', 'click_payment') AND param.key = 'firebase_screen' AND param.value.string_value IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') ) SELECT event_name_with_screen , MAX(step_number) AS step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel GROUP BY event_name_with_screen 연습문제 2: 일자별 각 퍼널의 유저 수 집계-- 퍼널 단계: 6 -- screen_view(welcome) -- screen_view(home) -- screen_view(food_category) -- screen_view(restaurant) -- screen_view(cart) -- click_payment(cart) WITH funnel AS ( SELECT event_date -- 날짜 컬럼 추가 , CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen , CASE WHEN event_name = 'screen_view' AND param.value.string_value = 'welcome' THEN 1 WHEN event_name = 'screen_view' AND param.value.string_value = 'home' THEN 2 WHEN event_name = 'screen_view' AND param.value.string_value = 'food_category' THEN 3 WHEN event_name = 'screen_view' AND param.value.string_value = 'restaurant' THEN 4 WHEN event_name = 'screen_view' AND param.value.string_value = 'cart' THEN 5 WHEN event_name = 'click_payment' AND param.value.string_value = 'cart' THEN 6 END AS step_number , user_pseudo_id FROM `inflearn-bigquery-437203.advanced.app_logs` CROSS JOIN UNNEST(event_params) AS param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' AND event_name IN ('screen_view', 'click_payment') AND param.key = 'firebase_screen' AND param.value.string_value IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') ) SELECT event_date -- 날짜 컬럼 추가 , event_name_with_screen , MAX(step_number) AS step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel GROUP BY ALL ORDER BY event_date, step_number 연습문제 3: 일자별 각 퍼널의 유저 수 집계한 결과 → PIVOT 하기-- 퍼널 단계: 6 -- screen_view(welcome) -- screen_view(home) -- screen_view(food_category) -- screen_view(restaurant) -- screen_view(cart) -- click_payment(cart) WITH funnel AS ( SELECT event_date , CONCAT(event_name, '-', param.value.string_value) AS event_name_with_screen , CASE WHEN event_name = 'screen_view' AND param.value.string_value = 'welcome' THEN 1 WHEN event_name = 'screen_view' AND param.value.string_value = 'home' THEN 2 WHEN event_name = 'screen_view' AND param.value.string_value = 'food_category' THEN 3 WHEN event_name = 'screen_view' AND param.value.string_value = 'restaurant' THEN 4 WHEN event_name = 'screen_view' AND param.value.string_value = 'cart' THEN 5 WHEN event_name = 'click_payment' AND param.value.string_value = 'cart' THEN 6 END AS step_number , user_pseudo_id FROM `inflearn-bigquery-437203.advanced.app_logs` CROSS JOIN UNNEST(event_params) AS param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' AND event_name IN ('screen_view', 'click_payment') AND param.key = 'firebase_screen' AND param.value.string_value IN ('welcome', 'home', 'food_category', 'restaurant', 'cart') ), -- 일자별 각 퍼널의 유저수 집계 funnel_daily AS( SELECT event_date , event_name_with_screen , MAX(step_number) AS step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM funnel GROUP BY ALL ORDER BY event_date, step_number ) -- 일자별 각 퍼널의 유저수 집계 -> 피벗하기 SELECT event_date , SUM(IF(event_name_with_screen = 'screen_view-welcome', cnt, 0)) AS `screen_view-welcome` , SUM(IF(event_name_with_screen = 'screen_view-home', cnt, 0)) AS `scree_view-home` , SUM(IF(event_name_with_screen = 'screen_view-food_category', cnt, 0)) AS `screen_view-food_category` , SUM(IF(event_name_with_screen = 'screen_view-restaurant', cnt, 0)) AS `screen_view-restaurant` , SUM(IF(event_name_with_screen = 'screen_view-cart', cnt, 0)) AS `screen_view-cart` , SUM(IF(event_name_with_screen = 'click_payment-cart', cnt, 0)) AS `click_payment-cart` FROM funnel_daily GROUP BY event_date ORDER BY event_date 

[실습] 개인 프로젝트에서 많이 쓰는 CI/CD 구축 방법 - 2 에서 깃액션 배포할때 계속 이렇게 뜨는데 알려주세요 ㅠㅠㅠㅠㅠㅠㅠ 5번해봤는데 계속 저렇게뜹니다..

1-4. Array, Struct 연습문제 (1~4번)연습문제1문제array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 쿼리select title, genres2 from advanced.array_exercises cross join unnest(genres) as genres2 ;결과 연습문제2문제array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다쿼리select title, actor.actor, actor.character from advanced.array_exercises cross join unnest(actors) as actor order by movie_id ;결과 연습문제3문제array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르 (genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다쿼리select title, actor.actor, actor.character genre2 # array<string> from advanced.array_exercises cross join unnest (actors) as actor cross join unnest (genres) as genre order by movie_id ;결과 연습문제4문제앱 로그 데이터(app_logs)의 배열을 풀어주세요쿼리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" limit 10 ;결과 1-9. 퍼널 SQL 쿼리 작성하기연습문제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 1 ;결과연습문제2문제orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다쿼리select user_id, # 컬럼의 이름을 지정할때, 영어를 제외하고 backtick(`)사용 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 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 user_id order by 1 ;결과연습문제4문제앱 로그 데이터 배열 PIVOT하기 - user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요?쿼리select event_date, event_timestamp, event_name, user_id, user_pseudo_id, max(if(param.key = 'firebase_screen', param.value.string_value, null)) as firebase_screen, -- max(if(param.key = 'food_id', param.value.string_value, null)) as food_id, max(if(param.key = 'food_id', param.value.int_value, null)) as food_id, max(if(param.key = 'session_id', param.value.string_value, null)) as session_id from advanced.app_logs cross join unnest(event_params) as param where event_date = "2022-08-01" and user_id = 32888 and event_name = "click_cart" group by all limit 100 ; 결과퍼널 분석문제step_number별 count, 일자별 퍼널별 유저 수 쿼리쿼리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 where 1=1 and event_date between "2022-08-01" and "2022-08-18" group by all ), filter_event_and_concat_event_and_acreen AS ( -- event_name + screen 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 1=1 and event_name in ("screen_view", "click_payment")) # 일자별로 퍼널별 유저 수 select -- distinct(event_name_with_screen) 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_acreen group by all having step_number is not null order by event_date ;결과    

강의에 사용되는 자료와 코드들은 어디서 받을 수 있나요?

1. ARRAY, STRUCT 연습문제 연습 문제 1번# 1)array_exercises테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요 SELECT title, genre FROM `advanced.array_exercises` AS exercise CROSS JOIN UNNEST(genres) AS genre SELECT title, # 기존에 array_exercises에 저장되어 있던 컬럼 genre FROM `advanced.array_exercises` AS ae, UNNEST(genres) AS genre # ARRAY : 같은 타입의 여러 데이터를 저장하고 싶을 때 # ARRAY를 Flatten(평면화) => UNNEST # UNNEST를 할 때는 CROSS JOIN + UNNEST(ARRAY_COLUMN) # UNNEST(ARRAY_COLUMN) AS 새로운 이름 # SELECT 절에서 새로운 이름으로 시작한다. 기존의 ARRAY_COLUMN은 사용하지 않는다! 연습 문제 2번# 2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. 배우와 배역은 별도의 컬럼으로 나와야 합니다 SELECT title, actor.actor, actor.character -- FROM `advanced.array_exercises` AS ae, UNNEST(actors) AS actor FROM `advanced.array_exercises` AS ae CROSS JOIN UNNEST(actors) AS actor # actors = [STRUCT(STRING, STRING)]  연습 문제 3번# 3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시 되어야 합니다 SELECT title, actor.actor, actor.character, genre FROM `advanced.array_exercises` AS ae ,UNNEST(actors) AS actor, UNNEST(genres) as genre -- FROM `advanced.array_exercises` AS ae -- CROSS JOIN UNNEST(actors) AS actor -- CROSS JOIN UNNEST(genres) as genre # 이 문제의 의도 : UNNEST를 2번 연속 사용할 수 있다. # CROSS JOIN => JOIN 연속 2번과 맥락은 동일한데, UNNEST라는 것이 어색할 수 있었다 # 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN) -- FROM `advanced.array_exercises` AS ae -- CROSS JOIN UNNEST(actors) AS actor, UNNEST(genres) as genre SELECT title, actor.actor, actor.character, genre FROM `advanced.array_exercises` AS ae ,UNNEST(actors) AS actor, UNNEST(genres) as genre -- FROM `advanced.array_exercises` AS ae -- CROSS JOIN UNNEST(actors) AS actor -- CROSS JOIN UNNEST(genres) as genre # 이 문제의 의도 : UNNEST를 2번 연속 사용할 수 있다. # CROSS JOIN => JOIN 연속 2번과 맥락은 동일한데, UNNEST라는 것이 어색할 수 있었다 # 데이터의 중복이 어느정도 생기는데, 그것은 어쩔 수 없는 이슈(CROSS JOIN) -- FROM `advanced.array_exercises` AS ae -- CROSS JOIN UNNEST(actors) AS actor, UNNEST(genres) as genre WHERE actor.actor = 'Chris Evans' AND genre = 'Action' -- WHERE actor = 'Chris Evans' (X) # 실행 순서 : FROM -> JOIN -> SELECT # actors : ARRAY<STRUCT> => UNNEST => STRUCT # genres : ARRAY<STRING> => STRING  연습 문제 4번# 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요 SELECT event_date, event_timestamp, event_name, event_param.key as key, event_param.value as value, event_param.value.string_value as string_value, event_param.value.int_value as int_value, -- event_params, user_id, event_param FROM `advanced.app_logs` CROSS JOIN UNNEST(event_params) AS event_param WHERE event_date ='2022-08-01'  2. PIVOTPIVOT 연습 문제 1## SubQuery 방식 SELECT order_date, SUM(IF(user_id = 1, sum_of_amount, NULL)) AS user_1, SUM(IF(user_id = 2, sum_of_amount, NULL)) AS user_2, SUM(IF(user_id = 3, sum_of_amount, NULL)) AS user_3 -- MAX를 써도 동일한 결과 값이 나옴 -- 그룹화 할때 값이 하나밖에 없음 FROM ( SELECT order_date, user_id, # Amount의 합 SUM(amount) AS sum_of_amount FROM advanced.orders GROUP BY order_date, user_id -- ORDER BY order_date ) GROUP BY order_date ORDER BY order_date  PIVOT 연습 문제 2# ANY_VALUE 활용 -- ANY_VALUE : 그룹화 할 대상 중에 임의의 값을 선택한다 (NULL)을 제외하고 -- ANY_VALUE에선 나머지 값들이 NULL 이거나 확정적으로 이 값이 나올 것이다 기대할 때 사용한다 SELECT user_id, ANY_VALUE(IF(order_date = PARSE_DATE('%Y-%m-%d', '2023-05-01'), amount, NULL)) AS `2023-05-01`, ANY_VALUE(IF(order_date = date('2023-05-02'), amount, NULL)) AS `2023-05-02`, ANY_VALUE(IF(order_date = date('2023-05-03'), amount, NULL)) AS `2023-05-03`, ANY_VALUE(IF(order_date = date('2023-05-04'), amount, NULL)) AS `2023-05-04`, ANY_VALUE(IF(order_date = date('2023-05-05'), amount, NULL)) AS `2023-05-05` FROM `advanced.orders` GROUP BY user_id ORDER BY user_id   PIVOT 연습 문제 3# 3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요.user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다 SELECT user_id, # amount 대신 1 이라고 표시. IF 문 안에 TRUE 일 때의 값이 항상 특정 컬럼이 아니라 1이라고 할 수도 있음(유무에 따라서) MAX(IF(order_date = PARSE_DATE('%Y-%m-%d', '2023-05-01'), 1, 0)) AS `2023-05-01`, MAX(IF(order_date = date('2023-05-02'), 1, 0)) AS `2023-05-02`, MAX(IF(order_date = date('2023-05-03'), 1, 0)) AS `2023-05-03`, MAX(IF(order_date = date('2023-05-04'), 1, 0)) AS `2023-05-04`, MAX(IF(order_date = date('2023-05-05'), 1, 0)) AS `2023-05-05` FROM `advanced.orders` GROUP BY user_id  PIVOT 연습 문제 4 앱 로그 데이터 배열 PIVOT 하기WITH base AS ( SELECT event_date, event_name, user_id, user_pseudo_id, event_timestamp, 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 GROUP BY ALL -- WHERE event_name ='click_cart' ORDER BY user_pseudo_id LIMIT 100 ) SELECT event_date, COUNT(user_id) AS user_cnt FROM base WHERE event_name ='click_cart' GROUP BY event_date ORDER BY event_date   3. 퍼널 분석 -- event_name + screen (필요한 이벤트만 WHERE 조건에 걸어서 사용) -- step_number + COUNT WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, -- param 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 -- LIMIT 100 ), filter_event_and_concat_event_and_screen 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 * FROM filter_event_and_concat_event_and_screen  최종 RESULT# 일자 상관 없이 퍼널의 유저 수를 집계한 쿼리 => 일자별로 하기 위해 event_date 추가WITH base AS ( SELECT event_date, event_timestamp, event_name, user_id, user_pseudo_id, platform, -- param MAX(IF(param.key = 'firebase_screen', param.value.string_value, NULL)) AS firebase_screen, 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' # 적은 데이터로 쿼리를 작성하기 위해 만들어둔 조건 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), 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 -- step_number : CASE WHEN 을 사용해 숫자 지정, 조건문을 여러 개 하고싶을 때 사용하는 함수 # 일자 상관 없이 퍼널의 유저 수를 집계한 쿼리 => 일자별로 하기 위해 event_date 추가 SELECT event_date, # 일자별로 퍼널별 유저 수 쿼리 event_name_with_screen, -- event_name_with_screen, -- event_datetime, user_pseudo_id, 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   

안녕하세요, 오랜만에 흥미로운 강의를 알게되어 주말동안 강의를 몰아보다보니 9강까지 듣게 되었네요. 23분 59초 정도에 setter를 지양하기 떄문에 custom setter를 잘 사용하지 않고, update함수를 만들어 사용한다 라는 내용에 예시가 있다면 알 수 있을까요? java 프로젝트를 활용할 때 setter를 커스텀하게 수정해서 쓰는 경우가 아주 간혹 값이 업데이트 될 때 다른 필드를 함께 업데이트 해야하는 케이스들 때문에 사용했던 기억이 있는데, setter를 사용하지 않고 update를 사용한다는게 어떤 말씀이신지 조금 더 상세히 알려주시면 감사하겠습니다!

1. ARRAY, STRUCT1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요SELECT title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. (배우와 배역은 별도의 컬럼으로 나와야 합니다)SELECT title, actor.actor, actor.character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다SELECT title, actor.actor, actor.character, genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요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'2. 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 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다-- 2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다 SELECT user_id, SUM(IF(order_date = '2023-05-01', amount, 0)) AS `2023-05-01`, SUM(IF(order_date = '2023-05-02', amount, 0)) AS `2023-05-02`, SUM(IF(order_date = '2023-05-03', amount, 0)) AS `2023-05-03`, SUM(IF(order_date = '2023-05-04', amount, 0)) AS `2023-05-04`, SUM(IF(order_date = '2023-05-05', amount, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다SELECT user_id, MAX(IF(order_date = '2023-05-01', 1, 0)) AS `2023-05-01`, MAX(IF(order_date = '2023-05-02', 1, 0)) AS `2023-05-02`, MAX(IF(order_date = '2023-05-03', 1, 0)) AS `2023-05-03`, MAX(IF(order_date = '2023-05-04', 1, 0)) AS `2023-05-04`, MAX(IF(order_date = '2023-05-05', 1, 0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id4) 앱 로그 데이터 배열 PIVOT하기# 앱 로그 PIVOT # 쿼리를 작성하는 목표, 확인할 지표 : user_id = 32888이 카트 추가하기(click_cart)를 누를때 어떤 음식(food_id)을 담았나요? # 쿼리 계산 방법 : UNNEST -> PIVOT # 데이터의 기간 : X # 사용할 테이블 : app_logs # Join KEY : X # 데이터 특징: -- event_params ARRAY, STRUCT / event_params.value ARRAY, STRUCT 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 ALL3. 퍼널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') ), daily_event_summary 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 ) -- 집계한 데이터 PIVOT 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, MAX(IF(event_name_with_screen = 'screen_view-cart', cnt, 0)) AS screen_view_cart, MAX(IF(event_name_with_screen = 'click_payment-cart', cnt, 0)) AS click_payment_cart FROM daily_event_summary GROUP BY ALL ORDER BY event_date

Module 'library-app' output path is incompatible with the Eclipse format which supports output under content root only. Make sure that "Inherit project compile output path" is not selected혹시 배포하신 프로젝트를 제가 STS에서 실행하기 위해 인텔리제이에서 Export to Eclips를 하고 Project Status Modul에서 Eclipse를 선택하고 ok를 눌렀더니 저런 경고 문이 뜨는데.. 배포하신 프로젝트는 이클립스 환경으로 익스포트가 안되는건가요??

안녕하세요 답안 제출 관련 문의드립니다.실기시험 시 문제10번까지 있는 경우문제를 풀며 D드라이브에 만들어놓는 것처럼 각 문제별 폴더화한 후 답안제출용 USB에 복사/붙여넣기해서 제출해도 되나요?(제공해주신 시나리오1의 완료보고서처럼 1개 파일로 만들어서 완성본 보고서를 제출해야할 것같아서요..)폴더화하는 경우 답안 hwp 외 '증거파일(원본)' 넣어도 되나요? 예를 들어 문제1 폴더 내 .hwp 외 '증거파일 폴더(원본 증거파일 수록)'

 1. ARRAY, STRUCT1) array_exercises 테이블에서 각 영화(title)별로 장르(genres)를 UNNEST해서 보여주세요SELECT title , genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre2) array_exercises 테이블에서 각 영화(title)별로 배우(actor)와 배역(character)을 보여주세요. (배우와 배역은 별도의 컬럼으로 나와야 합니다)SELECT title, , actor.actor AS actor , actor.character AS character FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor3) array_exercises 테이블에서 각 영화(title)별로 배우(actor), 배역(character), 장르(genre)를 출력하세요. 한 Row에 배우, 배역, 장르가 모두 표시되어야 합니다SELECT title , actor.actor AS actor , actor.character AS character , genre FROM advanced.array_exercises CROSS JOIN UNNEST(actors) AS actor CROSS JOIN UNNEST(genres) AS genre 4) 앱 로그 데이터(app_logs)의 배열을 풀어주세요SELECT user_id , event_date , event_name , user_pseudo_id , params.key AS key , params.value.string_value AS str_value , params.value.int_value AS int_value FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date = '2022-08-01'   2. PIVOT1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다-- 1) orders 테이블에서 유저(user_id)별로 주문 금액(amount)의 합계를 PIVOT해주세요. 날짜(order_date)를 행(Row)으로, user_id를 열(Column)으로 만들어야 합니다 WITH step1 AS ( SELECT order_date , user_id , sum(amount) AS sum_of_amount FROM advanced.orders GROUP BY ALL ) SELECT order_date , MAX(IF(user_id = 1, sum_of_amount, 0)) AS user_1 , MAX(IF(user_id = 2, sum_of_amount, 0)) AS user_2 , MAX(IF(user_id = 3, sum_of_amount, 0)) AS user_3 FROM step1 GROUP BY order_date ORDER BY order_date2) orders 테이블에서 날짜(order_date)별로 유저들의 주문 금액(amount)의 합계를 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)으로 만들어야 합니다SELECT user_id , SUM(IF(order_date = '2023-05-01', amount, 0)) AS `2023-05-01` , SUM(IF(order_date = '2023-05-02', amount, 0)) AS `2023-05-02` , SUM(IF(order_date = '2023-05-03', amount, 0)) AS `2023-05-03` , SUM(IF(order_date = '2023-05-04', amount, 0)) AS `2023-05-04` , SUM(IF(order_date = '2023-05-05', amount, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY user_id ORDER BY user_id3) orders 테이블에서 사용자(user_id)별, 날짜(order_date)별로 주문이 있다면 1, 없다면 0으로 PIVOT 해주세요. user_id를 행(Row)으로, order_date를 열(Column)로 만들고 주문을 많이 해도 1로 처리합니다SELECT user_id , MAX(IF(order_date = '2023-05-01', 1, 0)) AS `2023-05-01` , MAX(IF(order_date = '2023-05-02', 1, 0)) AS `2023-05-02` , MAX(IF(order_date = '2023-05-03', 1, 0)) AS `2023-05-03` , MAX(IF(order_date = '2023-05-04', 1, 0)) AS `2023-05-04` , MAX(IF(order_date = '2023-05-05', 1, 0)) AS `2023-05-05` FROM advanced.orders GROUP BY user_id ORDER BY user_id 4) 앱 로그 데이터 배열 PIVOT하기SELECT user_id , event_date , event_name , user_pseudo_id , MAX(IF(params.key = 'firebase_screen', params.value.string_value, NULL)) AS firebase_screen , MAX(IF(params.key = 'food_id', params.value.int_value, NULL)) AS food_id , MAX(IF(params.key = 'session_id', params.value.string_value, NULL)) AS session_id FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date = '2022-08-01' GROUP BY ALL 3. 퍼널 분석 WITH step1 AS ( SELECT event_date , event_timestamp , event_name , user_id , user_pseudo_id , MAX(IF(params.key = 'firebase_screen' , params.value.string_value , NULL)) AS firebase_screen , MAX(IF(params.key = 'session_id' , params.value.string_value , NULL)) AS session_id , platform FROM advanced.app_logs CROSS JOIN UNNEST(event_params) AS params WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY ALL ), step2 AS ( SELECT * EXCEPT(event_timestamp) , CONCAT(event_name, '-', firebase_screen) AS event_name_with_screen , DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime FROM step1 ), step3 AS ( SELECT * , CASE WHEN event_name_with_screen = 'screen_view-welcome' THEN 1 WHEN event_name_with_screen = 'screen_view-home' THEN 2 WHEN event_name_with_screen = 'screen_view-food_category' THEN 3 WHEN event_name_with_screen = 'screen_view-restaurant' THEN 4 WHEN event_name_with_screen = 'screen_view-cart' THEN 5 WHEN event_name_with_screen = 'click_payment-cart' THEN 6 END AS step_number FROM step2 -- 1) 각 퍼널별 유저 수 집계 ), step3_1 AS ( SELECT event_name_with_screen , step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM step3 GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY step_number ) -- 2) 일자별 각 퍼널별 유저 수 집계 , step3_2 AS ( SELECT event_date , event_name_with_screen , step_number , COUNT(DISTINCT user_pseudo_id) AS cnt FROM step3 GROUP BY ALL HAVING step_number IS NOT NULL ORDER BY event_date , step_number ) 3) 2) 데이터를 PIVOTSELECT event_date , MAX(IF(event_name_with_screen = 'screen_view-welcome', cnt, NULL)) AS `screen_view-welcome` , MAX(IF(event_name_with_screen = 'screen_view-home', cnt, NULL)) AS `screen_view-home` , MAX(IF(event_name_with_screen = 'screen_view-food_category', cnt, NULL)) AS `screen_view-food_category` , MAX(IF(event_name_with_screen = 'screen_view-restaurant', cnt, NULL)) AS `screen_view-restaurant` , MAX(IF(event_name_with_screen = 'screen_view-cart', cnt, NULL)) AS `screen_view-cart` , MAX(IF(event_name_with_screen = 'click_payment-cart', cnt, NULL)) AS `click_payment-cart` FROM step3_2 GROUP BY event_date ORDER BY event_date 

안녕하세요.좋은 강의 잘 보고 있습니다. 13:43초 쯤에 말씀하신 JPAQueryFactory를 스프링 빈으로 등록하는 방법은 테스트 코드를 작성하는 데 귀찮아진다고 말씀하셨습니다. 테스트 클래스(MemberJpaRepositoryTest)를 수정하지 않고도 통과가 되는데, 어떤 점이 귀찮아진다는 말씀이신가요? @SpringBootTest를 사용하지 않고 순수 자바로 테스트할 때, EntityManager와 JPAQueryFactory를 2개 생성해야 해서 귀찮아진다고 하신 것일까요? 

1. ARRAY, STRUCT 연습문제문제 1) SELECT movie_id, title, genre FROM advanced.array_exercises CROSS JOIN UNNEST(genres) AS genre;문제 2)SELECT title, aa.actor, aa.character FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS aa;문제 3) SELECT title, aa.actor, aa.character, gg AS genre FROM advanced.array_exercises AS ae CROSS JOIN UNNEST(actors) AS aa CROSS JOIN UNNEST(genres) AS gg;문제 4) 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; 2. PIVOT 연습문제문제 1) SELECT order_date, SUM(IF(user_id = 1, amount, 0 )) AS user_1, SUM(IF(user_id = 2, amount, 0 )) AS user_2, SUM(IF(user_id = 3, amount, 0 )) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date;문제2 )SELECT user_id, 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) 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) 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. 퍼널 연습문제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 ), 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 1, 3 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 main GROUP BY ALL ORDER BY all;

1. 자료형: Array, Struct & 퍼널 분석주요 학습 Point기존 SQL 작성할 때 없던 개념: Array와 Struct 및 Unnest(Flatten) 개념파악Pivot 등 퍼널 분석 기본 쿼리 작성 ✍🏼 및 목적에 맞는 퍼널분석 방법론 비교 1-1. 자료형: Array & Struct연습문제 1-4/* 연습문제 1 - 테이블: array_exercises - Row 기준: 각 영화 Title 별 - Column 기준: 장르 Unnest */ select title , genre from advanced.array_exercises cross join unnest(genres) as genre -- genre가 array 형태임 ; /* 연습문제 2 - 테이블: array_exercises - Row 기준: 각 영화 Title 별 - Column 기준: actor, character */ select title , actor from advanced.array_exercises cross join unnest(actors) as actor ; /* 연습문제 3 - 테이블: array_exercises - Column 기준: title, actor, character, genre */ select title , actor , genre from advanced.array_exercises cross join unnest(actors) as actor cross join unnest(genres) as genre limit 100 ; /* 연습문제 4 - 문제: app_logs 배열 풀기 - 주어진 배열: event_params(>key, string_value or int_value) */ select event_date , event_timestamp , event_name , param.key , param.value.string_value , param.value.int_value , user_id , user_pseudo_id , platform from advanced.app_logs cross join unnest(event_params) as param limit 10 ; 1-2. Pivot 쿼리 작성Pivot 연습문제/* 연습문제 1 - 테이블: orders - Row 기준: order_date - Column 기준: user_id 별 - value 기준: sum(amount) */ select order_date , sum(if(user_id = 1, amount, 0)) as user_1 , sum(if(user_id = 2, amount, 0)) as user_2 , sum(if(user_id = 3, amount, 0)) as user_3 from advanced.orders group by order_date order by order_date ; /* 연습문제 2 - 테이블: orders - Row 기준: user_id - Column 기준: order_date 날짜 별 - value 기준: sum(amount) */ 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 `2024-05-04` , sum(if(order_date = '2023-05-05', amount, 0)) as `2023-05-05` from advanced.orders group by user_id ; /* 연습문제 3 - 테이블: orders - Row 기준: user_id - Column 기준: order_date 날짜 별 - value 기준: 주문 있으면 1 없으면 0 (Indicator) */ 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 `2024-05-04` , max(if(order_date = '2023-05-05', 1, 0)) as `2023-05-05` from advanced.orders group by user_id ; /* 연습문제4 - 테이블: app_logs - Pivot - where 기준: user_id = 32888, event_name: click_cart */ with tmp as ( select user_id , event_date , event_name , user_pseudo_id , param.key , param.value.string_value , param.value.int_value from advanced.app_logs cross join unnest(event_params) as param where 1=1 and user_id = 32888 and event_name = 'click_cart' and key = 'food_id' ) select int_value from tmp limit 10 ; 1-3. 퍼널 분석퍼널 연습문제/* 문제1 - 테이블: app_logs - 문제: 각 퍼널 유저수 집계 - where 기준: 2022-08-01 ~ 2022-08-18 */ WITH tmp 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 screen_name, CONCAT(event_name, '-', MAX(IF(param.key = 'firebase_screen', param.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs cross join UNNEST(event_params) AS param 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 tmp 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 - 테이블: app_logs - 문제: 일자별 퍼널 유저수 집계 - where 기준: 2022-08-01 ~ 2022-08-18 */ WITH tmp 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 screen_name, CONCAT(event_name, '-', MAX(IF(param.key = 'firebase_screen', param.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs cross join UNNEST(event_params) AS param 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 tmp 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 - 테이블: app_logs - 문제: 집계한 데이터 pivot - where 기준: 2022-08-01 ~ 2022-08-18 */ WITH tmp 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 screen_name, CONCAT(event_name, '-', MAX(IF(param.key = 'firebase_screen', param.value.string_value, null))) AS event_name_with_screen FROM advanced.app_logs cross join UNNEST(event_params) AS param WHERE event_date BETWEEN '2022-08-01' AND '2022-08-18' GROUP BY 1,2,3,4,5 ), tmp2 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 tmp 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 , sum(if(step_number = 1, cnt, 0)) as `screen_view-welcome` , sum(if(step_number = 2, cnt, 0)) as `screen_view-home` , sum(if(step_number = 3, cnt, 0)) as `screen_view-food_category` , sum(if(step_number = 4, cnt, 0)) as `screen_view-restaurant` , sum(if(step_number = 5, cnt, 0)) as `screen_view-cart` , sum(if(step_number = 6, cnt, 0)) as `click_payment-cart` from tmp2 group by event_date order by 1 ; Q1. 질문사항pivot 쿼리 연습문제 2번에서, 모든 날짜를 기입하는 방식으로만 Pivot화 할 수 있는지 궁금합니다. 예를 들어, 날짜가 20개 있는 경우라면 일일이 날짜 기입하지 않고 일반화해서 pivot할 수 있는 방법이 있을지 여쭙습니다.

ARRAY, STRUCT 연습문제--#1 SELECT title, genre FROM `advanced.array_exercises` CROSS JOIN UNNEST(genres) AS genre; --#2 SELECT title, actor_info.actor as actor, actor_info.character as character FROM `advanced.array_exercises` CROSS JOIN UNNEST(actors) as actor_info; --#3 SELECT title, actor_info.actor as actor, actor_info.character as character, genre FROM `advanced.array_exercises` CROSS JOIN UNNEST(actors) as actor_info CROSS JOIN UNNEST(genres) as genre; --#4 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; PIVOT 연습문제#1 SELECT order_date, SUM(IF(user_id=1,amount,0)) AS user_1, SUM(IF(user_id=2,amount,0)) AS user_2, SUM(IF(user_id=3,amount,0)) AS user_3 FROM advanced.orders GROUP BY order_date ORDER BY order_date; #2 SELECT user_id, 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-05`, SUM(IF(order_date="2023-05-04",amount,0)) AS `2023-05-05`, FROM advanced.orders GROUP BY user_id ORDER BY user_id; #3 SELECT user_id, MAX(IF(order_date="2023-05-01",1,0)) AS `2023-05-01`, MAX(IF(order_date="2023-05-02",1,0)) AS `2023-05-02`, MAX(IF(order_date="2023-05-03",1,0)) AS `2023-05-03`, MAX(IF(order_date="2023-05-04",1,0)) AS `2023-05-04`, MAX(IF(order_date="2023-05-05",1,0)) AS `2023-05-05` FROM advanced.orders GROUP BY user_id ORDER BY user_id; #4 SELECT user_id ,event_date ,event_name ,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 WHERE event_name = "click_cart" AND event_date = "2022-08-01" AND user_id = 32888 GROUP BY user_id,event_date,event_name,user_pseudo_idFUNNEL 연습문제WITH base_tab AS ( SELECT event_date, event_name, user_id, user_pseudo_id, event_timestamp, 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 ) , filter_tab 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_tab WHERE event_name IN ('screen_view','click_payment') ), event_cnt 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_tab 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 event_cnt GROUP BY ALL ORDER BY event_date

게시판에 글을 길게 쓰고 업로드 할 경우 게시판 글 리스트에서 글의 처음 일부만 보여주고 나머지 글자들은 ... 처리를 하고 싶은데 어떻게 해야 할 지 모르겠습니다. (구분 선 아래로 글을 먹는 오류가 생깁니다.)   

제가 오라클 버추얼박스로 따로 실습하고 있는 게 있는데, 도커를 설치한 이후로 버추얼박스 vm이 구동되지 않는 것을 확인했습니다. 그러다 버추얼박스와 도커가 WSL2로 인해 충돌이 있다는 것을 알게 되었고, Hyper-V 관련 설정을 제거하였더니 버추얼박스 vm이 다시 구동되었습니다. 그런데 아니나 다를까 도커는 실행이 안되더라구요 참고로 버추얼박스 실습하는것도 호환성 문제때문에 구버전을 쓰고있고 업데이트가 불가한 상황인데 혹시 도커말고도 sql 실습환경을 구축할수 있는 방법이 있을지 혹시나싶어 문의드립니다..!!

백프레셔 latest 전략으로 코드를 돌려보니 버퍼가 가득찼는데 새로 데이터가 들어오면 기존에 버퍼에 있던 데이터들이 모두 사라지는 것처럼 보여서 reactor 공식문서를 찾아보니 Discard Support: Each time a new element comes in (the new "latest"), this operator discards the previously retained element. 라고 하는걸로봐서 버퍼가 가득 찬 상태에서 새로 데이터가 들어오면 버퍼에 기존에 있던것들 다 비워버리고 최신 데이터를 버퍼에 넣는 것 같아요