연습문제(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로 바꾸고 확인하는 부분에 대한 연습이 필요 쿼리 작성 시, 예약어를 대문자로 사용하는 것이 좋다고 알고 있는데, 아직 이 부분이 익숙하지 않아 연습이 필요하다.코드를 작성하면서 문장을 그대로 쿼리로 옮기는 편이라서 그런지 서브 쿼리를 사용하는 경우가 많다. 단순히 문장을 쿼리로 옮기기 보다 어떻게 하면 더 간결하고 가독성 높은 쿼리를 작성할 수 있을 지 고민하는 습관을 들여야 겠다.함수 이름 정하는 거 중요한 건 아는데, 잘 안되는 것 같다. 사용 기능을 이용하여 함수 이름 짓는 습관을 지어야겠다.

안녕하세요 강의를 재밌게 수강하던 도중 궁금증이 생기어 질문 드립니다. 질문select 의 row 매개변수의 순서와 FilmWithActor의 생성자 매개변수의 순서가 각 테이블 타입에 매칭되지 않을 경우 mapping이 정상적으로 이뤄지지 않는 현상이 있어별도의 해결 방법이나 대안이 있는지 여쭙고 싶습니다. 정상 케이스(Film - FilmActor - Actor)dslContext.select( DSL.row(FILM.fields()), DSL.row(FILM_ACTOR.fields()), DSL.row(ACTOR.fields()) ).from(FILM) .join(FILM_ACTOR) .on(FILM.FILM_ID.eq(FILM_ACTOR.FILM_ID)) .join(ACTOR) .on(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID)) .offset((page - 1) * pageSize) .limit(pageSize) .fetchInto(FilmWithActor.class); public class FilmWithActor { private final Film film; private final FilmActor filmActor; private final Actor actor; public FilmWithActor(Film film, FilmActor filmActor, Actor actor) { this.film = film; this.filmActor = filmActor; this.actor = actor; } // ... 문제 케이스(Film - Actor - FilmActor)dslContext.select( DSL.row(FILM.fields()), DSL.row(FILM_ACTOR.fields()), DSL.row(ACTOR.fields()) ).from(FILM) .join(FILM_ACTOR) .on(FILM.FILM_ID.eq(FILM_ACTOR.FILM_ID)) .join(ACTOR) .on(FILM_ACTOR.ACTOR_ID.eq(ACTOR.ACTOR_ID)) .offset((page - 1) * pageSize) .limit(pageSize) .fetchInto(FilmWithActor.class); public class FilmWithActor { private final Film film; private final FilmActor filmActor; private final Actor actor; public FilmWithActor(Film film, Actor actor, FilmActor filmActor) { this.film = film; this.filmActor = filmActor; this.actor = actor; } // ...     

 강의를 들으면서 문제에 대한 답은 이해했습니다! 그런데 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 기능 신기하네요.. 

CLUSTERED INDEX의 내용을 학습하다 보니, PRIMARY KEY가 단순히 CLUSTERED INDEX 세팅과 NOT NULL, UNIQUE의 옵션 설정을 한번에 해주는 것 뿐이라고 생각이 듭니다..제가 생각하는 것과는 달리 PRIMARY KEY만의 추가적인 차이점이 있을까요?

안녕하세요. 강의 잘 듣고 있습니다.이것 저것 실습해보다가SELECT SYSDATEFROM dual;이렇게 작성했을 때 실제 제가 하던 시간과 9시간이 차이가 나는 것을 발견했습니다. (오후 12시인데 추출값은 오전 3시로 나옴. 다른 것은 현재와 일치함.)이렇게 차이가 나는 이유가 있을까요? 수정할 수 있는 방법이 있나요?

첫 설정하고 잘 db도 연결해서 쿼리도 했는데그다음날 다시 들어가니ORA-12541: 데이터베이스에 접속할 수 없습니다. %s에 리스너가 없습니다. (CONNECTION_ID=NLVRNlPuRjCSIQBsRDyXvQ==)이런 항목이 뜹니다ㅠㅠ

환경설정에서 막혀서 이틀정도 헤매다가 chat-gpt에 검색으로 해결한 내용 공유드립니다.(Mac OS 환경에서 터미널로 작성한 내용입니다) docker compose up -d 명령으로 다운로드 완료[+] Running 13/13 ✔ sqlserver Pulled 1972.2s ✔ 9b857f539cb1 Pull complete 255.4s ✔ 2678dac613fe Pull complete 1703.6s ✔ c88a287125cd Pull complete 1965.5s ✔ a11052f2d5bd Pull complete 1966.1s ✔ oracle Pulled 2792.4s ✔ 9c4def932b31 Pull complete 594.0s ✔ d701f846bb57 Pull complete 2258.2s ✔ f62e8298f905 Pull complete 2425.8s ✔ 20f5a8cddc81 Pull complete 2543.3s ✔ f18c9ed317b9 Pull complete 2705.3s ✔ cb3a8f950dc8 Pull complete 2785.5s ✔ afaeda8e1723 Pull complete 2786.0s [+] Running 3/3 ✔ Network sqld_default Created 7.0s ✔ Container oracle Started 28.3s ✔ Container sql-server Started  만약 위의 내용처럼 정상적으로 다운로드되지 않는다면 아래 내용 차례대로 다시입력docker rm -f $(docker ps -qa) colima stop colima start --memory 4 --arch x86_64 cd .yml 파일이 있는 경로로 이동 docker compose up -d 이렇게 설치 완료후 dbeaver에 oracle 연결 시도를 하였는데, 리소스가 없음 오류발생 해결방법 : 아래의 코드를 복사하여 터미널에 한번에 입력 (oracle-xe 를 다운로드)docker run \ --restart unless-stopped \ --name oracle \ -e ORACLE_PASSWORD=1111111 \ -p 1521:1521 \ -d \ gvenzl/oracle-xe다운로드가 완료되면 dbeaver에서 xe/sqld_user/pass 로 다시 oracle 연결 -> 성공 

안녕하세요, 먼저 좋은 내용의 강의 감사합니다. 다름 아니라 강의 비디오에서'FastAPI Form 다루기 - 01'부터 녹음 된 소리가 울림이 커서 명료하지 않게 들리는 것 같습니다. 혹시 번거로우시겠지만, 좋은 강의 경험을 위해 그 전까지의 영상과 같이 오디오를 깔끔하게 변환해주실 수 있을지 문의 드립니다. 감사합니다.

안녕하세요. 열혈 수강자 이지수입니다 : )문제가 하나씩 풀리니 넘 재밌고 뿌듯합니다 ㅎㅎ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

CORS 설명 주신 부분을 보면 처음 브라우저에서 request 날릴 때에는 Origin에 아무런 값이 들어가 있지 않아서 response를 받고 그 이후에 받은 html의 javascript 쪽에서 다른 요청을 보냈을 때 기존에 요청하면서 받았던 Origin 값이 같이 들어가면서 이슈가 발생한다고 말씀주셨는데, blog json data 테스트 하는 거 보면 origin이 null인데 바로 cors 에러가 나나요 ? 

@app.get("/items") # 함수에 개별 인자값이 들어가 있는 경우 path parameter가 아닌 모든 인자는 query parameter # query parameter의 타입과 default값을 함수인자로 설정할 수 있음. async def read_item(skip: int = 0, limit: int = 2): return fake_items_db[skip : skip + limit] @app.get("/items_nd/") # 함수 인자값에 default 값이 주어지지 않으면 반드시 query parameter에 해당 인자가 주어져야 함. async def read_item_nd(skip: int, limit: int): return fake_items_db[skip : skip + limit]안녕하세요 url 차이에 관해 질문드립니다. 위에 url은 "/items" 인데, 아래 url은 "/items_nd/" 으로 끝에 /가 붙어있는데요. 둘다 query params로 구성되어 있는데 위는 /로 안끝나고, 아래는 왜 /로 끝났는지 이유가 궁금합니다.

목차과제별 작성 쿼리쿼리 작성 후 느낀점 과제별 작성 쿼리(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 내에서는 따옴표를 쓰지 않고, 컬럼끼리 연결해야겠다는 걸 알게 됐습니다.

스키마 다운 그리고 db다운그리고 select * from actor limilt 10; 후 실행하면테이블은 표는 나오는데 안에 value값은 null로 나와요뭘 잘 못한 걸까요? ㅜ이틀 통안 삭제 다시 하면서 10번은 해본거 같아요  

w3schools.com 에서 실습을 할 때 SELECT * FROM products ORDER BY price DESC LIMIT 3 으로 실행시켰는데,  Error in SQL:Syntax error in ORDER BY clause. 이런 결과가 나오는 건 왜일까요?ㅠ

ORA-12514: 데이터베이스에 접속할 수 없습니다. %s 서비스가 %s의 리스너에 등록되지 않았습니다. (CONNECTION_ID=+MvkL937Qk6z4IPwPp85vQ==) 요렇게 나옵니다..ㅠㅠ 

변수 선언해서 사용할때와 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가 나온 이유는 뭔가요?