강의 문제 1) user들의 다음 접속월과 다다음 접속월을 구하는 쿼리를 작성해주세요. -- 출제의도: 윈도우 함수(LEAD)를 사용하여 파티션을 나눠 데이터를 탐색할 수 있는가? SELECT user_id , visit_month AS visit_month_m0 , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m1 , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m2 FROM advanced.analytics_function_01 ORDER BY user_id; 강의 문제 2) user들의 다음 접속월과 다다음 접속월, 이전 접속월을 구하는 쿼리를 작성해주세요 -- 출제의도: 윈도우 함수(LEAD)와 (LAG)을 함께 사용하여 파티션을 나눠 데이터를 탐색할 수 있는가? SELECT user_id , visit_month AS visit_month_m0 , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m1 , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_m2 , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS visit_month_p1 FROM advanced.analytics_function_01 ORDER BY user_id; 강의 문제 3) Frame 설정을 활용한 윈도우 함수 사용 SELECT order_id , order_date , user_id , amount , SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total , SUM(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum , SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_by_user , AVG(amount) OVER (ORDER BY order_date, order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount FROM advanced.orders --QUALIFY last_5_orders_avg_amount >= 150 ORDER BY order_id; 윈도우 함수 연습문제 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌, query_logs의 데이터 우측에 새로운 컬럼을 만들어주세요. -- 출제의도: 윈도우 함수의 집계 함수 중 COUNT를 사용할 수 있는가? SELECT * , COUNT(user) OVER (PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY query_date, user; 윈도우 함수 연습문제 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요. -- 출제의도: GROUP BY와 윈도우 함수(순위)를 함께 사용할 수 있는가? SELECT * , RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS team_rank FROM (SELECT EXTRACT(WEEK FROM query_date) AS week_number , team , user , COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL) QUALIFY team_rank = 1 ORDER BY week_number, team; 윈도우 함수 연습문제 3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준, 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요. -- 출제의도: GROUP BY와 윈도우 함수(LAG)를 함께 사용할 수 있는가? SELECT * , LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_count FROM (SELECT user , team , EXTRACT(WEEK FROM query_date) AS week_number , COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL) ORDER BY user, week_number; 윈도우 함수 연습문제 4) 시간 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요. -- *FRAME의 DEFAULT 값: UNBOUNDED PRECEDING ~ CURRENT ROW -- 출제의도: GROUP BY와 윈도우 함수(SUM-누계합)를 함께 사용할 수 있는가? SELECT * , SUM(query_cnt) OVER (PARTITION BY user ORDER BY query_date) AS cumulative_query_count FROM (SELECT user , team , query_date , COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL) ORDER BY user, query_date; 윈도우 함수 연습문제 5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. -- 출제의도: 윈도우 함수(LAST_VALUE)에서 IGNORE NULLS가 필요한 상황을 이해할 수 있는가? WITH raw_data AS( SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL SELECT DATE '2024-05-02', 13 UNION ALL SELECT DATE '2024-05-03', NULL UNION ALL SELECT DATE '2024-05-04', 16 UNION ALL SELECT DATE '2024-05-05', NULL UNION ALL SELECT DATE '2024-05-06', 18 UNION ALL SELECT DATE '2024-05-07', 20 UNION ALL SELECT DATE '2024-05-08', NULL UNION ALL SELECT DATE '2024-05-09', 13 UNION ALL SELECT DATE '2024-05-10', 14 UNION ALL SELECT DATE '2024-05-11', NULL UNION ALL SELECT DATE '2024-05-12', NULL ) -- SELECT -- date -- , IFNULL(number_of_orders, LAG(number_of_orders) OVER (ORDER BY date)) AS number_of_orders -- FROM raw_data -- ORDER BY date; SELECT * , LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders FROM raw_data ORDER BY date; 윈도우 함수 연습문제 6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요. -- 출제의도: 예외 값을 처리한 이후, 윈도우 함수로 이동 평균을 계산할 수 있는가? WITH raw_data AS( SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL SELECT DATE '2024-05-02', 13 UNION ALL SELECT DATE '2024-05-03', NULL UNION ALL SELECT DATE '2024-05-04', 16 UNION ALL SELECT DATE '2024-05-05', NULL UNION ALL SELECT DATE '2024-05-06', 18 UNION ALL SELECT DATE '2024-05-07', 20 UNION ALL SELECT DATE '2024-05-08', NULL UNION ALL SELECT DATE '2024-05-09', 13 UNION ALL SELECT DATE '2024-05-10', 14 UNION ALL SELECT DATE '2024-05-11', NULL UNION ALL SELECT DATE '2024-05-12', NULL ) SELECT * , ROUND(AVG(number_of_orders) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) , 1) AS moving_avg FROM( SELECT date , IFNULL(number_of_orders, LAG(number_of_orders) OVER (ORDER BY date)) AS number_of_orders FROM raw_data) ORDER BY date; 윈도우 함수 연습문제 7) app_logs 테이블에서 Custom Session을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. -- *Session은 숫자로(1,2,3…) 표시해도 됩니다. -- **2022-08-18일의 user_pseudo_id(1997494153. 8491999091)은 session_id가 4까지 나옵니다 -- 출제의도: 윈도우 함수를 웹 로그 데이터에 적용하여 활용할 수 있는가? -- Step 1. Session 정보 추출 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 , DATETIME(TIMESTAMP_MICROS(LAG(event_timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp)), 'Asia/Seoul') AS before_event_datetime FROM advanced.app_logs WHERE event_date = '2022-08-18' AND user_pseudo_id = '1997494153.8491999091' ), -- Step 2. 세션 유지 시간 및 신규 세션 여부 계산 session_info AS( SELECT * , TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff , CASE WHEN TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) >= 20 OR TIMESTAMP_DIFF(event_datetime, before_event_datetime, SECOND) IS NULL THEN 1 ELSE NULL END AS session_start FROM base ) -- Step 3. 신규 세션 id 세팅 SELECT * , SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id FROM session_info ORDER BY event_date, event_timestamp;
안녕하세요! SUBQUERY 이해 (업데이트) 강의에서 예2의 비교 부분의 쿼리문을 JOIN으로 바꿔서 표현해보려고 하는데 실행 결과 category_id가 1부터 전부 나옵니다. 어떻게 해야 5('Comedy')보다 큰 것만 추출할 수 있을까요? pdf파일 7page JOIN으로 바꿔서 표현해본 쿼리문 실행 결과
제로초님 하신대로 그대로 프로젝트를 생성하고 실행했더니 화면은 그대로 뜨는데 에러가 1개 있어서 봤더니 hydration 에러라고 뜨네요 ㅠㅠ 지금 z-com 프로젝트를 수업용 / 연습용으로 총 2개 진행중인데 수업용 프로젝트는 이런 에러가 없었는데 연습용에서 에러가 발생하네요. 아래는 에러 코드입니다 ! Console Error Hydration failed because the server rendered HTML didn't match the client. As a result this tree will be regenerated on the client. This can happen if a SSR-ed Client Component used - A server/client branch if (typeof window !== 'undefined') . - Variable input such as Date.now() or Math.random() which changes each time it's called. - Date formatting in a user's locale which doesn't match the server. - External changing data without sending a snapshot of it along with the HTML. - Invalid HTML tag nesting. It can also happen if the client has a browser extension installed which messes with the HTML before React loaded
먼저 친절하고 자세한 강의 감사합니다. 덕분에 셀레니움을 이용해서 간단하지만 1차로 원하는 부분까지 개발해 봤습니다. 캠핑장 예약을 위해 아직 더 가야 하지만 버튼 하나로 조회 결과 화면까지 이동하는 건 확인했습니다. ㅎㅎ 질문입니다. pyside6 설치 강의에 나오는 Qt for Python 확장 설치를 더이상 지원하지 않는답니다. 섹션13 의 PySide6 설치 및 사용법 강의에 나오는 Qt for Python 을 검색하니 이 확장은 더 이상 사용되지 않습니다. 대신 Qt All Extensions Pack 확장자를 사용하세요. 라는 멧세지가 나옵니다. Qt All Extensions Pack 이걸 설치해도 강의 따라가는 데 문제가 없을까요? 설치 잘 못 되었다가는 꼬일 거 같아 대기중입니다. ㅠㅠ 감사합니다.
안녕하세요 카일님, JOIN 연습문제 4번에서 작성 순서 및 효율성 관련해서 질문드리고 싶습니다. 카일님의 풀이에서는 Active, Training 의 Status 를 필터링 후 pokemon, trainer 테이블을 LEFT JOIN 하시고 마지막에 'Master' 를 필터링하셨는데, 저 같은 경우에는 각각의 테이블마다 필요한 부분을 먼저 필터링 후에 마지막에는 COUNT 만 할 수 있게 진행하였습니다. Trainer 테이블에서는 'Master' 필터 Trainer_pokemon 테이블에서는 'Active', 'Training' 필터 결과적으로 결과값은 올바르게 나오는데 카일님께서 앞선 강의에서 필터링을 먼저 하는 것이 효율적이라 하셨던 것 같아서 질문드리게 되었습니다. 감사합니다. SELECT p.type1, COUNT(tp.id) AS pokemon_cnt -- t.*, -- tp.pokemon_id, -- tp.status, -- p.type1 FROM( SELECT id, achievement_level FROM `basic.trainer` WHERE achievement_level = 'Master' ) AS t LEFT JOIN( SELECT * FROM `basic.trainer_pokemon` WHERE status IN ('Active', 'Training') ) AS tp ON t.id = tp.trainer_id LEFT JOIN `basic.pokemon` AS p ON tp.pokemon_id = p.id WHERE tp.pokemon_id IS NOT NULL GROUP BY p.type1 ORDER BY 2 DESC LIMIT 1;
윈도우 탐색 함수 연습문제 (1) 연습문제 1 -- 문제 1) USER의 다음 접속월, 다다음 접속 월 SELECT user_id, visit_month, LEAD(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_month, LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS the_month_after_next FROM `avdanced.analytics_function_01` (2) 연습문제 2 -- 문제 2) USER의 다음 접속월, 다다음 접속 월, 이전 접속 월 SELECT user_id, visit_month, LEAD(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_month, LEAD(visit_month,2) OVER(PARTITION BY user_id ORDER BY visit_month) AS the_month_after_next, LAG(visit_month,1) OVER(PARTITION BY user_id ORDER BY visit_month) AS last_month FROM `avdanced.analytics_function_01` 윈도우 함수 FRAME 연습문제 연습문제 (1~4) SELECT -- 1)모든 주문량 SUM(amount) OVER() AS amount_total, -- 2)특정주문시점에서 누적주문량 #SUM(amount) OVER(partition by order_date) AS cumulative_sum, SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum, -- 3)고객별 주문 시점에서 누적 주문량 #SUM(amount) OVER(partition by user_id) AS cumulative_sum_by_user, SUM(amount) OVER(partition by user_id ORDER BY order_id) AS cumulative_sum_by_user, -- 4) 최근 직전 5개 평균 주문량 AVG(amount) OVER(ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount, AVG(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS last_5_unbounded_orders_avg_amount, AVG(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS all_orders_avg_amount FROM `avdanced.orders` 윈도우 함수 (1) 연습문제 1 -- 연습문제1) 사용자별 쿼리 실행 횟수 WITH base AS( SELECT user, team, query_date, COUNT(*) OVER(PARTITION BY user) AS total_query_cnt, FROM `avdanced.query_logs` ) SELECT * FROM base (2) 연습문제 2 -- 연습문제2) 주차별 팀내 쿼리 실행한 수 (RANK 1만 보이도록) WITH base2 AS( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(*) OVER(PARTITION BY EXTRACT(WEEK FROM query_date) ,user ORDER BY EXTRACT(WEEK FROM query_date) ) AS query_cnt, FROM `avdanced.query_logs` ORDER BY EXTRACT(WEEK FROM query_date) ) SELECT DISTINCT *, RANK() OVER(PARTITION BY team,week_number ORDER BY total_query_cnt DESC) AS team_rank FROM base2 QUALIFY team_rank = 1 ORDER BY week_number, team 강의자료의 코드 -- 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요 -- 주차별로 개인당 실행한 쿼리 횟수 -- 위 쿼리 횟수를 기반으로 랭킹 -- 랭킹을 기반으로 필터링(랭킹=1) -- 문제의 의도 : 원본 데이터 => 1 row마다 데이터가 있고, 그걸 집계해서 사용. GROUP BY => 윈도우 함수 WITH query_cnt_by_team AS ( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(user) AS query_cnt FROM advanced.query_logs GROUP BY ALL ) SELECT *, RANK() OVER(PARTITION BY week_number, team ORDER BY query_cnt DESC) AS rk FROM query_cnt_by_team -- QUALIFY : 윈도우 함수의 조건을 설정할 때 사용 QUALIFY rk = 1 ORDER BY week_number, team, query_cnt DESC COUNT의 윈도우 함수 대신에 GROUP BY를 사용하는 풀이도 있다는 것을 알게 되었다! 너무 어렵게 생각하지 말기! (3) 연습문제 3 WITH base2 AS( SELECT EXTRACT(WEEK FROM query_date) AS week_number, team, user, COUNT(*) OVER(PARTITION BY EXTRACT(WEEK FROM query_date) ,user ORDER BY EXTRACT(WEEK FROM query_date) ) AS query_cnt, FROM `avdanced.query_logs` #QUALIFY team_rank = 1 ORDER BY EXTRACT(WEEK FROM query_date) ), base3 AS( SELECT DISTINCT *, RANK() OVER(PARTITION BY team,week_number ORDER BY query_cnt DESC) AS team_rank FROM base2 QUALIFY team_rank = 1 ORDER BY week_number, team ) -- 연습문제3) 쿼리 실행 시점 1주전 쿼리 실행 SELECT DISTINCT *, LAG(query_cnt,1) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count FROM base2 GROUP BY ALL ORDER BY user, week_number (4) 연습문제 4 --연습문제4) SELECT *, SUM(query_count) OVER(PARTITION BY user ORDER BY query_date) AS culmulative_query_count, SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2 FROM( SELECT DISTINCT *, COUNT(user) OVER(PARTITION BY query_date, user) AS query_count, FROM `avdanced.query_logs` ) ORDER BY user,query_date QUALIFY 로 조건설정을 하여 두 값이 같은 지 비교하는 법이 인상깊었던 문제 (QUALIFY cumulative_sum != cumulative_sum2) (5) 연습문제 5 나의 풀이 WITH raw_data AS ( SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL SELECT DATE '2024-05-02', 13 UNION ALL SELECT DATE '2024-05-03', NULL UNION ALL SELECT DATE '2024-05-04', 16 UNION ALL SELECT DATE '2024-05-05', NULL UNION ALL SELECT DATE '2024-05-06', 18 UNION ALL SELECT DATE '2024-05-07', 20 UNION ALL SELECT DATE '2024-05-08', NULL UNION ALL SELECT DATE '2024-05-09', 13 UNION ALL SELECT DATE '2024-05-10', 14 UNION ALL SELECT DATE '2024-05-11', NULL UNION ALL SELECT DATE '2024-05-12', NULL ) --연습문제 5) null에 이전 값 삽입 SELECT raw_data.date, IF(raw_data.number_of_orders IS NULL, LAG(raw_data.number_of_orders,1) OVER(ORDER BY date), raw_data.number_of_orders) FROM raw_data 강의풀이 WITH raw_data AS ( SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL SELECT DATE '2024-05-02', 13 UNION ALL SELECT DATE '2024-05-03', NULL UNION ALL SELECT DATE '2024-05-04', 16 UNION ALL SELECT DATE '2024-05-05', NULL UNION ALL SELECT DATE '2024-05-06', 18 UNION ALL SELECT DATE '2024-05-07', 20 UNION ALL SELECT DATE '2024-05-08', NULL UNION ALL SELECT DATE '2024-05-09', 13 UNION ALL SELECT DATE '2024-05-10', 14 UNION ALL SELECT DATE '2024-05-11', NULL UNION ALL SELECT DATE '2024-05-12', NULL ), -- LAG로 직전 값 가져오면 되지 않을까? -- number_of_orders가 null이면, before_number_of_orders를 가져와라! -- 아래 쿼리는 어려운 방법 -- 그 다음 방법 : LAST VALUE를 쓰자! => 값이 없으면 NULL이 뜬다! -- FIRST_VALUE, LAST_VALUE => NULL을 포함해서 연산 -- 출제 의도 : NULL을 제외해서 연산하고 싶으면 IGNORE NULLS을 쓰면 된다! -- SELECT -- *, -- IF(number_of_orders IS NULL, before_number_of_orders, number_of_orders) AS filled_orders -- -- Number of arguments does not match for function IF. Supported signature: IF(BOOL, ANY, ANY) at [89:3] -- -- False일 때 인자를 추가하지 않아서 생긴 오류 -- FROM ( -- SELECT -- *, -- LAG(number_of_orders) OVER(ORDER BY date) AS before_number_of_orders -- FROM raw_data -- ) filled_data AS ( SELECT * EXCEPT(number_of_orders), LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number_of_orders FROM raw_data -- Syntax error: Expected keyword DEPTH but got identifier "filled_data" at [104:6] : WITH문을 두개 작성했는데 WITH 쉼표 쓰고 구분! ) LAST_VALUE, FIRST_VALUE를 사용하는 풀이 법에 대하여 알게 됨 (6) 연습문제 6 WITH raw_data AS ( SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL SELECT DATE '2024-05-02', 13 UNION ALL SELECT DATE '2024-05-03', NULL UNION ALL SELECT DATE '2024-05-04', 16 UNION ALL SELECT DATE '2024-05-05', NULL UNION ALL SELECT DATE '2024-05-06', 18 UNION ALL SELECT DATE '2024-05-07', 20 UNION ALL SELECT DATE '2024-05-08', NULL UNION ALL SELECT DATE '2024-05-09', 13 UNION ALL SELECT DATE '2024-05-10', 14 UNION ALL SELECT DATE '2024-05-11', NULL UNION ALL SELECT DATE '2024-05-12', NULL ), null_is_lag AS( --연습문제 5) null에 이전 값 삽입 SELECT raw_data.date, IF(raw_data.number_of_orders IS NULL, LAG(raw_data.number_of_orders,1) OVER(ORDER BY date), raw_data.number_of_orders) AS number_of_orders FROM raw_data ) -- 연습문제 6) 이동평균 SELECT *, AVG(nl.number_of_orders) OVER(ORDER BY nl.date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM null_is_lag AS nl FRAME절을 사용할 때, AND를 기준으로 앞에는 뒤의 값보다 반드시 이전 행을 가리키는 구문이 와야 함! (7) 연습문제 7 -- 1. TIMESTAMP → DATETIME -- 2. SECOND_DIFF 생성 : uSER로 묶어서 - -- 3. SESSION_START생성 : USER로 묶어서 LAG(DATA,1)이 NULL이면 1, SECOND_DIFF가 20이상이면 +1 -- 4. SESSION_ID생성: SESSION_START가 1일 경우 SESSION_ID +1, NULL일 경우 LAG(DATA,1) WITH add_date AS ( -- 1. TIMESTAMP → DATETIME SELECT event_date, event_timestamp, DATETIME(TIMESTAMP_MICROS(event_timestamp)) AS event_datetime, event_name, user_id, user_pseudo_id, LAG(DATETIME(TIMESTAMP_MICROS(event_timestamp))) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS before_event_datetime FROM `avdanced.app_logs_temp` --,UNNEST(event_params) AS param -- FROM 절 안에서 UNNEST를 사용 WHERE event_date ="2022-08-18" AND user_pseudo_id = "1997494153.8491999091" ), add_diff AS ( -- 2. SECOND_DIFF 생성 : uSER로 묶어서 - SELECT *, DATE_DIFF(event_datetime, before_event_datetime,SECOND) AS second_diff, FROM add_date ), add_session AS( -- 3. SESSION_START생성 : USER로 묶어서 LAG(DATA,1)이 NULL이면 1, SECOND_DIFF가 20이상이면 +1 SELECT *, IF(second_diff IS NULL OR second_diff >=20, 1, NULL) AS session_start FROM add_diff ) -- 4. SESSION_ID생성 *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num FROM add_session ORDER BY event_datetime user_id와 user_pseudo_id는 다르다. (계정이 존재하면 USER_ID, 없어도 USER_PSEUDO_ID를 통해 활동기록이 남는다.) IF문은 행 단위로 작동, SUM은 특정 파티션에 대한 누적합을 계산함으로 아래 코드가 작동하지 않음 SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_num CTE를 사용하는 것과 서브쿼리를 사용하는 방법 중 상황에 맞게 적절히 혼용할 수 있다는 것을 알게 됨!
안녕하세요!! 페이지 별 적용을 원하는 레이아웃이 있는 경우 getLayout 함수를 컴포넌트의 메서드로 추가하여 적용하는 강의를 들었습니다. 그런 식으로 레이아웃을 설정하는 방법은 처음 봐서 매우 새롭고 신기하고 즐거운 강좌였습니다. 감사합니다! 궁금한 점이 있는데요, 그렇게 했을 때 페이지별로 최상단에 SearchableLayout 컴포넌트를 적용하는 것과 무슨 차이가 있는지 궁금했습니다. (혹은 강의에서 그렇게 설명해주신 이유가 궁금합니다!)
[1~2번]윈도우 함수(탐색 함수) 연습 문제 1번 select *, lead(visit_month, 1) over(partition by user_id order by visit_month) as next_visit_month, lead(visit_month, 2) over(partition by user_id order by visit_month) as two_next_visit_month from `advanced.analytics_function_01 2번 select *, lead(visit_month, 1) over(partition by user_id order by visit_month) as next_visit_month, lead(visit_month, 2) over(partition by user_id order by visit_month) as two_next_visit_month, lag(visit_month, 1) over(partition by user_id order by visit_month) as before_visit_month from `advanced.analytics_function_01` [1~4번] 윈도우 함수 Frame 연습 문제 1번 /* 전체 계산 할 때 왜 over()를 써야 하는지 몰랐습니다. 그러나 일반 집계 함수의 방식과 비교해보니, 사용 이유를 알게 됐습니다. 집계함수를 사용할 때 over()를 사용했다는 건 '윈도우 함수가 적용된 결과값(각 행마다 결과 값이 반환 되는 것)으로 보겠다'는 의미인 걸 알게 됐습니다. 같은 집계함수를 사용하더라도 '어떤 방식(결과를 1개 행으로만 받아볼지, 여러개 값으로 받아볼지)으로 결과 값을 받아 볼 지' 생각해 봐야 한다는 걸 알았습니다. */ select *, sum(amount) over() as amount_total from `advanced.orders` 2번 /* 처음에는 누적합을 구하는 건데 왜 over() 안에 order by를 써야하는지 이해하지 못했습니다. 그러나 누적합도 "어떤 방식으로 계산할지 '계산 방식'"을 지정해 줘야 한다는 걸 알게 됐습니다. /* select *, sum(amount) over(order by order_id) as cumulative_sum from `advanced.orders` 3번 select *, sum(amount) over(partition by user_id order by order_id) as cumulative_sum_by_user from `advanced.orders` 4번 /* over() 안에 사용하는 order by와 over() 밖에서 사용하는 order by를 명확히 이해했습니다. 윈도우 함수에서 사용하는 order by는 '계산 순서'를 정하는 것이고, 윈도우 함수 밖에서 사용한 order by는 '눈에 보이는 정렬'을 컨트롤 한다는 걸 이해했습니다. 이해할 때 '쿼리 작동 순서'를 생각했더니, 큰 도움이 됐습니다. */ select *, avg(amount) over(order by order_id rows between 5 preceding and 1 preceding) as last_5_orders_avg_amount from `advanced.orders` order by order_id [1~7번] 윈도우 함수 연습 문제 1번 select *, count(user) over(partition by user) as total_query_cnt from `advanced.query_logs` order by query_date 2번 /* - date 형식 데이터를 extract()로 주 형식으로 바꾸는 걸 알게 됐습니다 - partition by 내에 2개 이상 컬럼을 넣어도 된다는 걸 알게 됐습니다. */ with user_query_cnt as ( select extract(week from query_date) as week_number, team, user, count(user) as query_cnt from `advanced.query_logs` group by all ) select *, rank() over(partition by week_number, team order by query_cnt desc) as team_rank from user_query_cnt qualify team_rank = 1 order by 1,2 3번 /* - lag()와 함께 쓰는 over() 사용법이 헷갈렸는데, 실행 후 수정하는 방향으로 쿼리를 작성해 보라고 하신 점이 인상 깊게 와닿았습니다. */ with base as ( select user, team, extract(week FROM query_date) as week_number, count(user) as query_count from advanced.query_logs group by all ) select *, lag(query_count, 1) over(partition by user order by week_number) as pre_query_cnt from base 4번 /* qualify cumulative_query_cnt != cumulative_query_cnt2 위 쿼리로 정합성을 체크하는 방식을 배우게 됐습니다. */ ## 4번 with query_cnt as ( select *, count(user) as query_count from advanced.query_logs group by all ) select *, sum(query_count) over(partition by user order by query_date) as cumulative_query_cnt, sum(query_count) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_query_cnt2 from query_cnt -- qualify cumulative_query_cnt != cumulative_query_cnt2 5번 /* - 4번에서 배운 정합성 체크하는 쿼리를 이용해서 컬럼 내 값이 있는지 테스트 해봤습니다. */ ## 정합성 테스트 -- select -- * -- from ( -- select -- *, -- last_value(number_of_orders) over(order by date) as last_values_order -- from raw_data -- ) as test1 -- where -- number_of_orders != last_values_order select *, last_value(number_of_orders ignore nulls) over(order by date) as last_values_order from raw_data 6번 /* 처음에는 1. 평균을 구하고 2. 해당 테이블을 서브쿼리로 두고, lag(컬럼, 2)를 해야 하는줄 알았습니다. 그러나 Frame 사용 방법이 생각났고, 조건에 맞게 범위를 설정해서 문제를 해결했습니다. */ ## 6번 , base2 as ( select *, last_value(number_of_orders ignore nulls) over(order by date) as last_values_order, from raw_data ) select *, avg(last_values_order) over(order by date rows between 2 preceding and current row) as moving_avg from base2 7번 /* - 타임스탬프 데이터를 다루는 다양한 방법을 알게 됐습니다. - 실무에서는 세션 기준과 보고 싶은 유저 혹은 이벤트를 지정해서 사용해도 된다는 걸 알게 됐습니다. */ 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` where event_date = '2022-08-18' and user_pseudo_id = '1997494153.8491999091' ) select *, sum(session_start) over(order by event_datetime) as session_id from ( select *, case when second_diff >= 20 then 1 when second_diff is null then 1 else 0 end session_start from ( select *, timestamp_diff(event_datetime, before_event_datetime, second) as second_diff from ( select *, lag(event_datetime, 1) over(partition by user_pseudo_id order by event_datetime) as before_event_datetime from base ) as base2 ) as base3 ) as base4
윈도우 함수 (탐색 함수) 연습 문제 1번 SELECT user_id, visit_month, lead(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_1month, lead(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_2month, FROM advanced.analytics_function_01 ORDER BY 1,2; 2번 SELECT user_id, visit_month, lead(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_1month, lead(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month) AS next_2month, lag(visit_month, 1) OVER(PARTITION BY user_id ORDER BY visit_month) AS pre_1month FROM advanced.analytics_function_01 ORDER BY 1,2; 윈도우 함수 연습 문제 1번 SELECT user, team, query_date, count(user) over(PARTITION BY user) AS total_query_cnt FROM advanced.query_logs ORDER BY query_date, user 2번 WITH base AS ( SELECT EXTRACT(week FROM query_date) AS week_number, team, user, count(user) AS query_cnt FROM advanced.query_logs GROUP BY 1,2,3 ) SELECT *, rank() OVER(PARTITION BY week_number, team ORDER BY query_cnt desc) AS team_rank FROM base QUALIFY team_rank = 1 ORDER BY week_number, query_cnt desc; 3번 WITH base AS ( SELECT EXTRACT(week FROM query_date) AS week_number, team, user, count(user) AS query_cnt FROM advanced.query_logs GROUP BY 1,2,3 ) SELECT *, lag(query_cnt) OVER(PARTITION BY team, user ORDER BY week_number asc) AS prev_week_query_cnt FROM base ORDER BY team, user, week_number; 4번 WITH base AS ( SELECT user, team, query_date, COUNT(user) as query_count FROM advanced.query_logs GROUP BY 1,2,3 ) SELECT user, team, query_date, query_count, SUM(query_count) OVER(PARTITION BY team, user ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count FROM base ORDER BY team, user, query_date; 5번 WITH raw_data AS( SELECT DATE'2024-05-01'AS date,15 AS number_of_orders UNION ALL SELECT DATE'2024-05-02',13 UNION ALL SELECT DATE'2024-05-03',NULL UNION ALL SELECT DATE'2024-05-04',16 UNION ALL SELECT DATE'2024-05-05',NULL UNION ALL SELECT DATE'2024-05-06',18 UNION ALL SELECT DATE'2024-05-07',20 UNION ALL SELECT DATE'2024-05-08',NULL UNION ALL SELECT DATE'2024-05-09',13 UNION ALL SELECT DATE'2024-05-10',14 UNION ALL SELECT DATE'2024-05-11',NULL UNION ALL SELECT DATE'2024-05-12',NULL ) SELECT date, IF(number_of_orders is null , last_value(number_of_orders IGNORE NULLS) OVER(ORDER BY date asc), number_of_orders) AS number_of_orders_not_null FROM raw_data; 6번 WITH raw_data AS( SELECT DATE'2024-05-01'AS date,15 AS number_of_orders UNION ALL SELECT DATE'2024-05-02',13 UNION ALL SELECT DATE'2024-05-03',NULL UNION ALL SELECT DATE'2024-05-04',16 UNION ALL SELECT DATE'2024-05-05',NULL UNION ALL SELECT DATE'2024-05-06',18 UNION ALL SELECT DATE'2024-05-07',20 UNION ALL SELECT DATE'2024-05-08',NULL UNION ALL SELECT DATE'2024-05-09',13 UNION ALL SELECT DATE'2024-05-10',14 UNION ALL SELECT DATE'2024-05-11',NULL UNION ALL SELECT DATE'2024-05-12',NULL ), fill_null AS ( SELECT date, IF(number_of_orders is null , last_value(number_of_orders IGNORE NULLS) OVER(ORDER BY date asc), number_of_orders) AS number_of_orders_not_null FROM raw_data ) SELECT date, number_of_orders_not_null, avg(number_of_orders_not_null) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM fill_null; 7번 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 WHERE event_date = '2022-08-18' ), diff_date AS ( SELECT *, DATETIME_DIFF(event_datetime, pre_event_time, second) AS date_diff_sec FROM ( SELECT *, LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime asc) AS pre_event_time FROM base ) ), session_start AS ( SELECT *, CASE WHEN pre_event_time IS NULL THEN 1 WHEN date_diff_sec >= 20 THEN 1 END AS start_session FROM diff_date ) SELECT event_date, event_datetime, event_name, user_id, user_pseudo_id, date_diff_sec, SUM(start_session) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_id FROM session_start ORDER BY user_pseudo_id, event_datetime;
