값 안나옴
해결됨
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
왜 답이 안나오는 걸까요?
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
shs4166
댓글 2
좋아요 0
조회수 102
묻고 답해요
172만명의 커뮤니티!! 함께 토론해봐요.
원핫 인코딩 문의
해결됨
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
0과 1이 아닌 True/False 데이터가 입력되게 됩니다. 버전 업데이트 관련 문제인가요? 시험환경에서는 0/1로 나올 거니 신경 안써도 되는건가요
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
wsyang
댓글 2
좋아요 0
조회수 121
22강 21번 문제에 관련해 질문있습니다.
미해결
[리뉴얼] 파이썬입문과 크롤링기초 부트캠프 [파이썬, 웹, 데이터 이해 기본까지] (업데이트)
mystr = ' a man goes into the rooom...' print(mystr.strip('..')) 강사님이 작성하신 mystr.strip('.')과 똑같이 위 코드를 돌렸을 때에도 점 세 개가 모두 지워지는 결과를 확인했습니다. 만일 해당 str문에서 "점 두 개"만 지워 달라는 의도를 가졌다고 가정하면 이를 어떤 식으로 해결해야 하는 지 의구심이 생겨서 해당 질문 글을 남깁니다.
- python
- 웹-크롤링
hsdanny501
댓글 1
좋아요 0
조회수 90
강의 연장
미해결
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
안녕하세요 11월 30일 실기 시험 준비를 위해 해당 강의를 시청하는데, 현재 강의 수강 만료일이 11월 5일로 예정되어 있이 학습에 어려움이 있습니다. 11월 30일까지 강의 수강 기간을 연장 가능한지 문의드립니다. 일정부분 비용 발생하더라도 상관없으니 검토 부탁드리겠습니다.
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
kdmin88
댓글 2
좋아요 0
조회수 155
작업형2 하이퍼파라미터 튜닝
해결됨
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
인프런 강의와 2025 시나공 빅데이터 분석기사 실기 책 같이 보면서 공부중입니다. 작업형2 연습문제를 풀고 있는데, 성능개선을 위해 하이퍼파라미터 튜닝 시 문제마다 적용되는 max_depth, n_estimators 값 범위가 다르더라구요. 혹시 파라미터 값 범위를 지정하는 어떤 기준이 있는건지... 어떤 식으로 값을 조정해야하는건지 궁금합니다.
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
chohs
댓글 2
좋아요 0
조회수 159
sql 설치 문제
미해결
[리뉴얼] 처음하는 SQL과 데이터베이스(MySQL) 부트캠프 [입문부터 활용까지]
sql을 설치하고 Mysql configurator를 통해서 마지막에 execute를 시도하는데 계속 Database initialization failed. 에러가 발생합니다 ㅠㅠ 처음엔 한글경로 문제인가 싶어서 이름도 모두 영어로 바꾸고 재설치도 10번은 해본거 같은데 해결이 안되네요 ㅠㅠ 혹시 해결 방법이 있을까요.. 로그 남겨둡니다.. ㅠ Beginning configuration step: Writing configuration file Saving my.ini configuration file... Saved my.ini configuration file. Ended configuration step: Writing configuration file Beginning configuration step: Updating Windows Firewall rules Adding a Windows Firewall rule for MySQL911 on port 3306. Attempting to add a Windows Firewall rule with command: netsh.exe advfirewall firewall add rule name="Port 3306" protocol=TCP localport=3306 dir=in action=allow 확인됨 Successfully added the Windows Firewall rule. Adding a Windows Firewall rule for MySQL911 on port 33060. Attempting to add a Windows Firewall rule with command: netsh.exe advfirewall firewall add rule name="Port 33060" protocol=TCP localport=33060 dir=in action=allow 확인됨 Successfully added the Windows Firewall rule. Ended configuration step: Updating Windows Firewall rules Beginning configuration step: Adjusting Windows service Attempting to grant the required filesystem permissions to the 'NT AUTHORITY\NetworkService' account. Granted permissions to the data directory. Granted permissions to the install directory. Adding new service New service added Ended configuration step: Adjusting Windows service Beginning configuration step: Initializing database (may take a long time) Attempting to run MySQL Server with --initialize-insecure option... Starting process for MySQL Server 9.1.0... Starting process with command: C:\Program Files\MySQL\MySQL Server 9.1\bin\mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 9.1\my.ini" --console --initialize-insecure=on --lower-case-table-names=1... Process for mysqld, with ID 14936, was run successfully and exited with code -1073741819. Failed to start process for MySQL Server 9.1.0. Database initialization failed. Ended configuration step: Initializing database (may take a long time)
- python
- sql
- mysql
- dbms/rdbms
- 데이터-엔지니어링
댓글 2
좋아요 0
조회수 1480
라벨 인코딩 문의
해결됨
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
라벨 인코딩에서 나타나지는 숫자들이 정해지는 기준이 무엇인가요? 예를들어, workclass 피처에서 ' State-gov'이 왜 6번이고, 'private'가 왜 3번인가요?
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
wsyang
댓글 2
좋아요 0
조회수 108
df.isull().sum() 과 df.shape() 차이
해결됨
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
처음 질문에서는 결측치를 확인할 때 df.isull().sum()를 사용하셨고 두번째 질문에서는 결측치 확인할 때 df.shape()를 사용하셨는데 두번째도 df.isull().sum()을 사용하면 안될까요? 어떤 차이가있나요?
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
김혁수
댓글 1
좋아요 0
조회수 119
문제풀이 서비스 버그 문의드립니다
미해결
Python 알고리즘 베스트 10
화면 왼쪽의 [Q1. 자격증명], [Q2. 암호문] 등의 버튼을 클릭해도 해당 문제가 있는 html페이지로 넘어가지 않습니다 chrome 외에 safari로 해도 마찬가지이고요 url 파라미터로 page=2 넣어서 직접 접속하니 넘어가기는 해서 불편한 대로 쓰고는 있는데요 https://pyalgo.co.kr/?page=2 처음 오는 분들은 이런 부분을 전혀 모르실 거고 애초 명백히 잘못된 버그이니 빨리 고쳐주시기 바랍니다 <li><a href="#" id="q1" class="btn-que success">Q1. 자격 증명</a></li> 보니까 href속성에 값이 제대로 있지 않습니다 임의로 값을 바꾸고 클릭하니 잘 넘어가지네요
- python
- 코딩-테스트
이재호
댓글 1
좋아요 0
조회수 90
변수 초기화
해결됨
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
학습 관련 질문을 남겨주세요. 상세히 작성하면 더 좋아요! 질문과 관련된 영상 위치를 알려주면 더 빠르게 답변할 수 있어요 먼저 유사한 질문이 있었는지 검색해보세요 여기서 처음처럼 20이 출력되도록 초기화?하려면 어떻게 해야 하나요? # 20 = 10 + 10 box = box + box box
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
yorabbit08
댓글 2
좋아요 0
조회수 133
데이터 핸들링을 위한 판다스 기초1 시리즈 선택 질문
해결됨
[퇴근후딴짓] 빅데이터 분석기사 실기 (작업형1,2,3)
시리즈 선택할때 저는 왼쪽처럼 대괄호 하나만 해도 데이터프레임처럼 나오는데 강의에는 오른쪽처럼 나오시더라구요 어떻게 된걸까요?
- python
- 머신러닝
- 빅데이터
- pandas
- 빅데이터분석기사
코딩바보
댓글 2
좋아요 0
조회수 101
Next.js vs React.js
해결됨
한 입 크기로 잘라먹는 Next.js
강사님, 안녕하세요. 강의를 들으면서 살짝 혼란스러운 부분이 있습니다. 사실 Next.js 도 바탕은 React 인데요. React 에서 제일 많이 언급되고 중요시되는 부분은 상태(state)관리라고 생각이 되는데 Next.js 로 SSR 위주의 페이지를 작성하게 되면 결국 상태관리는 최소화하게 되는 것인데 이게 React 의 컨셉과 맞는 것인지 살짝 이해가 되지 않습니다. (강의에서도 state 는 거의 언급이 되지 않고요) 그렇다고 상태관리를 최대한 활용하는 CSR 위주의 개발을 하게 되면 Next.js 의 장점을 거의 얻지 못하게 될 것 같기도 하고요. 저는 Next.js 가 제공하는 프레임워크 측면의 혜택을 얻고자 Next.js 로 어플리케이션 구축을 해보고 싶은데 해당 어플리케이션이 CSR 의 비중이 적지 않을 것 같아서 좀 망설여지는 부분이 있습니다. 현대 애플리케이션들은 사용자들과의 상호작용이 적을 수가 없을텐데 대다수의 앱들이 Next.js 의 SSR 위주의 개발로 충분히 커버가 가능한 것일까요?
- react
- typescript
- next.js
아리마
댓글 2
좋아요 0
조회수 607
혹시 애플 M시리즈 사용하시는 분 중에 오류
미해결
내 업무를 대신 할 파이썬(Python) 웹크롤링 & 자동화 (feat. 주식, 부동산 데이터 / 인스타그램)
PackagesNotFoundError: The following packages are not available from current channels: - python=3.8.5* 이런거 나오면서 가상환경 설치가 안되면, conda create -n crawling_class -c conda-forge python=3.8.5 이렇게 하니까 해결되네요. Mt시리즈에서는 3.8.5버전이 기본지원이 안된다고함.
- python
- 웹-크롤링
hoonnews
댓글 0
좋아요 0
조회수 163
구조체 강의 4:31 질문있습니다
해결됨
(2026 최신!) 일주일만에 합격하는 정보처리기사 실기
myCar.model[i] = '\0'; 은 세줄 위의 for에 영향을 받는 것 인가요?? 바로 위 } 에서 for문이 종료됐다고 생각했는데 myCar.model[i] = '\0'; 가 [i]에는 어떤 숫자를 넣어야할지 이해가 되지 않습니다
- python
- java
- c
- 정보처리기사
kku1804
댓글 2
좋아요 1
조회수 174
빠짝스터디 2주차 과제 윈도우 함수
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
윈도우 함수 연습문제 1 select *, count(*) over(partition by user) as total_query_cnt from `advanced.query_logs`; 윈도우 함수 연습문제 2 select *, rank() over(partition by team, week_number order by query_cnt desc) as team_rank from ( select user, week_number, team, count(*) as query_cnt from ( select *,extract(week from query_date) as week_number from `advanced.query_logs` ) group by all ) qualify team_rank=1; 윈도우 함수 연습문제3 select *, lag(query_cnt) over(partition by user order by week_number asc) as prev_week_query_cnt from ( select user, week_number, team, count(*) as query_cnt from ( select *,extract(week from query_date) as week_number from `advanced.query_logs` ) group by all ); 윈도우 함수 연습문제4 select *, sum(query_count) over(partition by user order by query_date asc) as cumulative_query_cnt from ( select user, query_date, team, count(*) as query_count from `advanced.query_logs` group by all ); 윈도우 함수 연습문제5 SELECT date, case when number_of_orders is null then lag(number_of_orders) over(order by date asc) else number_of_orders end as number_of_orders FROM raw_data; 윈도우 함수 연습문제6 select *, avg(number_of_orders) over(order by date asc rows between 2 preceding and current row) as moving_average from ( SELECT date, case when number_of_orders is null then lag(number_of_orders) over(order by date asc) else number_of_orders end as number_of_orders, FROM raw_data ) ; 윈도우 함수 연습문제7 select *, sum(session_start) over(partition by user_pseudo_id order by event_timestamp asc) as session_id from ( select *, case when time_diff is null then 1 when time_diff >= 20 then 1 else null end as session_start from ( select *, cast((event_timestamp - before_event_timestamp)/1000000 as int) as time_diff from ( select event_date, event_timestamp, event_name, user_id, user_pseudo_id, lag(event_timestamp) over(partition by user_pseudo_id order by event_timestamp asc) as before_event_timestamp from `advanced.app_logs` where user_pseudo_id='1997494153.8491999091' and event_date='2022-08-18' ) ) );
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery
권기영
댓글 1
좋아요 0
조회수 111
윈도우 함수 연습 문제
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
윈도우함수(탐색함수) 연습문제 연습문제1 -- 문제 1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요 select user_id, visit_month, lead(visit_month) over (partition by user_id order by visit_month asc) as after_visit_month, lead(visit_month, 2) over (partition by user_id order by visit_month asc) as after_visit_month from `advanced.analytics_function_01` order by user_id; 연습문제2 -- 문제2) user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요. select *, lead(visit_month) over (partition by user_id order by visit_month asc) as after_visit_month, lead(visit_month, 2) over (partition by user_id order by visit_month asc) as after_two_visit_month, lag(visit_month) over(partition by user_id order by visit_month asc) as before_visit_month from `advanced.analytics_function_01` order by user_id, visit_month; 연습문제3 -- 3번 유저가 접속했을 때 다음 접속까지의 간격을 구하시오 select *, after_visit_month - visit_month as diff from( select *, lead(visit_month, 1) over (partition by user_id order by visit_month) as after_visit_month from `advanced.analytics_function_01` ) order by user_id, visit_month; 윈도우 함수 frame 연습문제 SELECT * , SUM(amount) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total , SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum , SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user , 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 -- 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요 단, group by를 사용해서 집계하는것이 아닌 quary_log의 데이터의 우측에 새로운 컬럼을 작성해주세요 select *, count(query_date) over (partition by user) as total_query_cnt from `advanced.query_logs` order by user,query_date; 연습문제2 -- 2. 주차별로 팀 내에서 쿼리를 많이 실행한수를 구한후 실행 수를 활요해 랭킹을 구해주세요. 단 랭킹이 1등인 사람만 보여주세요. 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 rk = 1 order by week_number, team, query_cnt desc; 연습문제3 -- 3. (2번문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할수 있는 쿼리를 작성해주세요. 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 *, lag(query_cnt, 1) over(partition by user order by week_number) as prev_week_query_cnt from query_cnt_by_team ; 연습문제4 -- 4. 시간에 흐름에 따라 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요(query_date) select *, sum(query_cnt) over(partition by user order by query_date) as cumulative_sum, sum(query_cnt) over(partition by user order by query_date rows between unbounded preceding and current row) as cumulative_sum2 from( select query_date, team, user, count(user) as query_cnt from `advanced.query_logs` group by all ) order by user, query_date; 연습문제5 -- 5. 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 null로 기록됩니다.이런 데이터에서 null값이라고 되어 있는 부분은 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요 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 *, last_value(number_of_orders ignore nulls) over(order by date) as last_value_orders from raw_data; 연습문제6 -- 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 ) , 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 ) select *, avg(number_of_orders) over (order by date rows between 2 preceding and current row) as moving_avg from filled_data 연습문제7 -- 7) app_logs 테이블에서 custom session을 만들어 주세요 이전 이벤트 로그와 20초가 지나면 새로운 session을 만들어 주세요 session 숫자로 (1,2,3 ...)표시해도 됩니다. -- 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다. with base as( select event_date, 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" ), diff_Data as( select *, from( select *, lag(event_datetime, 1) over(partition by user_pseudo_id order by event_datetime) as prev_event_datetime from base ) ) select *, sum(session_start) over (partition by user_pseudo_id order by event_datetime) as session_num from( select *, case when prev_event_datetime is null then 1 when second_diff >= 20 then 1 else 0 end as session_start from diff_data ) order by event_datetime
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery
kyb6010
댓글 1
좋아요 0
조회수 103
[빠짝스터디 2주차 과제] 윈도우 함수 연습문제
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
윈도우 함수의 탐색 함수 : LEAD, LAG, FIRST_VALUE, LAST_VALUE ## 문제1) user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요. -- SELECT -- user_id -- , visit_month -- , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month -- , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_next_month -- FROM `advanced.analytics_function_01` -- ORDER BY user_id ## 문제2) user들의 다음 접속 월과 다다음 접속 월,이전 접속 월을 구하는 쿼리를 작성해주세요. -- LAG 함수를 사용할 때 NULL이 나온다 => 그 값은 처음이다! -- LEAD 함수를 사용할 때 NULL이 나온다 => 그 값은 마지막이다! -- SELECT -- user_id -- , visit_month -- , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month -- , LEAD(visit_month, 2) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_next_month -- , LAG(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS before_month -- FROM `advanced.analytics_function_01` -- ORDER BY user_id ## 3번 : 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오 -- user_id | visit_month | after_visit_month | diff_month -- SELECT -- user_id -- , visit_month -- , LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month -- , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) - visit_month AS diff_month -- FROM `advanced.analytics_function_01` -- ORDER BY user_id ## 윈도우 함수를 이렇게 쓰는게 좋을까? => 중복된 쿼리는 줄이는 것이 좋을 수 있음 -- 쿼리를 수정할 상황이 생김 => 2번 수정 => 굉장히 많아지면 복잡해지고, 실수하기 좋음 -- 쿼리가 길어지는 것을 무서워하지 말고, 쿼리를 덜 수정할 수 있는 구조를 만들면 좋겠다! -- 윈도우 함수 쓰다보면 쿼리 줄이 길어짐. 감안하고 사용하면 좋겠다 -- -- 그래서 서브쿼리로 만들어보면,, -- SELECT -- * -- , (after_month - visit_month) AS diff_month -- FROM ( -- SELECT -- * -- , LEAD(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_month -- FROM `advanced.analytics_function_01` -- ) -- ORDER BY user_id ## 추가 문제 : 이 데이터셋을 기준으로 user_id의 첫번째 방문 월, 마지막 방문 월을 구하는 쿼리를 작성해주세요 SELECT user_id , visit_month , FIRST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS first_visit , LAST_VALUE(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS last_visit FROM `advanced.analytics_function_01` ORDER BY user_id, visit_month 윈도우 함수 Frame 연습문제 -- amount_total : 전체 SUM -- cumulative_sum : row 시점에 누적 SUM -- cumulative_sum_by_user : row 시점에 유저별 누적 SUM -- last_5_orders_avg_amount : order_id 기준으로 정렬하고, 직전 5개의 주문의 평균 amount -- 집계분석함수() OVER(PARTITION BY ~~~ ORDER BY ~~~ ROWS BETWEEN A and B) SELECT * , SUM(amount) OVER() AS amount_total ## OVER 안에 아무것도 들어가지 않는 경우도 있구나! , SUM(amount) OVER(ORDER BY order_id) AS cumulative_sum , SUM(amount) OVER(PARTITION BY user_id ORDER BY order_id) AS cumulative_sum_by_user , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amount -- , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) -- , AVG(amount) OVER(ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) FROM advanced.orders ORDER BY order_id 윈도우 함수의 연습문제 ## 윈도우 함수 연습문제 ## 1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요. -- 사용자별 쿼리를 실행한 총 횟수 : COUNT() 전체 실행. -- OVER(PARTITION BY user) -- SELECT -- * -- , COUNT(query_date) OVER(PARTITION BY user) AS total_query_cnt -- FROM `advanced.query_logs` -- ORDER BY query_date ## 2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요. -- 주차별로 개인당 실행한 쿼리 횟수 -- 위 쿼리 횟수를 기반으로 랭킹 -- 랭킹을 기반으로 필터링(랭킹=1) -- 문제의 의도 : 원본 데이터 => 1 row마다 데이터가 있고, 그걸 집계해서 사용. 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(query_date) AS query_cnt -- FROM `advanced.query_logs` -- GROUP BY ALL -- ) -- QUALIFY team_rank = 1 -- ORDER BY week_number, team -- ## ## WITH 문 풀이 -- WITH query_cnt_by_team AS ( -- SELECT -- EXTRACT(week from query_date) AS week_number, -- team, -- user, -- COUNT(query_date) 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 -- -- 16주차 - AI팀의 케이피, 16주차 - 코칭팀의 카일, 16주차 - 데이터 사이언스팀의 샘 ## 3)(2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요 -- 1주 전의 쿼리 실행 수 => LAG -- WITH query_cnt_by_team AS ( -- SELECT -- EXTRACT(week from query_date) AS week_number, -- team, -- user, -- COUNT(query_date) AS query_cnt -- FROM `advanced.query_logs` -- GROUP BY ALL -- ) -- SELECT -- user, -- team, -- week_number, -- query_cnt, -- LAG(query_cnt) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt -- FROM query_cnt_by_team -- ORDER BY user -- -- ans T) -- WITH query_cnt_by_team AS ( -- SELECT -- EXTRACT(week from query_date) AS week_number, -- team, -- user, -- COUNT(query_date) AS query_cnt -- FROM `advanced.query_logs` -- GROUP BY ALL -- ) -- SELECT -- *, -- LAG(query_cnt, 1) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt -- FROM query_cnt_by_team ## 4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요. -- 누적 쿼리 : 과거의 시간(UNBOUNDED PRECEDING)부터 current row까지 -- 출제 의도 : Default Frame에 대해 알려드리고 싶었음. -- For aggregate analytic functions, if the ORDER BY clause is present but the window frame clause is not, the following window frame clause is used by default: -- SELECT -- *, -- SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_sum -- FROM ( -- SELECT -- user, -- team, -- query_date, -- COUNT(query_date) AS query_cnt -- FROM `advanced.query_logs` -- GROUP BY ALL -- ) -- -- ans T) -- SELECT -- *, -- SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_sum, -- SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2 -- -- Frame의 Default 값 : UNBOUNDED PRECEDING ~ CURRENDT ROW -- FROM ( -- SELECT -- query_date, -- team, -- user, -- COUNT(user) AS query_cnt -- FROM `advanced.query_logs` -- GROUP BY ALL -- ) -- -- QUALIFY cumulative_sum != cumulative_sum2 -- -- -- WHERE, QUALIFY 조건 설정해서 2가지 값이 모두 같은지 비교 => 모두 같으면 != 연산 결과에 반환하는 값이 없을 -- ORDER BY user, query_date ## 5) 다음 데이터는 주문횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. -- LAG로 직전 값 가져오면 되지 않을까? -- number_of_orders가 null 이면, before_number_of_orders를 가져와라! -- 아래 쿼리는 어려운 방법 -- 그 다음 방법 : LAST VALUE를 쓰자! => 값이 없으면 NULL이 뜬다 ! -- FIRST_VALUE, LAST_VALUE => NULL 을 포함해서 연산 -- 출제 의도 : NULL 을 제외해서 연산하고 싶으면 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, last_value_orders) AS numbers_of_orders -- -- FROM ( -- -- SELECT -- -- *, -- -- -- LAG(number_of_orders) OVER(ORDER BY date) AS prev_orders, ## 마지막 값 NULL !! -- -- LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS last_value_orders -- -- FROM raw_data -- -- ) -- -- -- ans T) -- 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 -- ) -- ## 6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요 (이동평균) -- -- Frame : 2일 전 => BTWEEN 2 PRECEDING AND CURRENT ROW -- -- 출제 의도 : Frame을 지정할 수 있는가? -- SELECT -- *, -- AVG(number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg -- FROM filled_data ## 7) app_logs 테이블에서 CustomSession을 만들어주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어주세요. Session은 숫자로(1,2,3…) 표시해도 됩니다. ## 2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다 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' ), base2 as ( SELECT *, LAG(event_datetime, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) as before_event_datetime FROM base ) SELECT *, SUM(session_start) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) as session_id FROM ( SELECT *, IF(second_diff is NULL or second_diff > 20, 1, NULL) as session_start FROM ( SELECT *, DATETIME_DIFF(event_datetime, before_event_datetime, second) as second_diff FROM base2 ) ) ORDER BY event_timestamp
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery
Sunny
댓글 1
좋아요 0
조회수 111
[빠짝스터디 2주차 과제] 윈도우 함수 연습 문제
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. 탐색함수 연습문제 문제 1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리 SELECT user_id, visit_month, lead(visit_month) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month, lead(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month2 FROM `advanced.analytics_function_01` ORDER BY user_id 문제2. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리 SELECT user_id, visit_month, lead(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month, lead(visit_month,2) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month2, lag(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lag_visit_month FROM `advanced.analytics_function_01` ORDER BY user_id, visit_month # LAG 함수를 사용할 때 NULL 이면 그 값은 처음, # LEAD 함수를 사용할 때 NULL 이면 그 값은 마지막 문제3. 유저가 접속했을 때, 다음 접속까지의 간격을 구하기 SELECT user_id, visit_month, lead(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month, ((lead(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) ) - visit_month) AS diff_month # 별칭쓴거는 select 안에서 못함 FROM `advanced.analytics_function_01` ORDER BY user_id, visit_month # 이 경우에는 쿼리 많은경우에 수정할 경우 헷갈리기 시작한다. 따라서 서브쿼리로 묶어서 하면 더 편하고 실수 적어짐 # 쿼리 길어진다고 해도 무서워하지 말고 쿼리 덜 수정할 수 있는 구조를 만들자. # 윈도우 함수 쓰면 줄이 쿼리 길어짐. 감안하고 쓰자. 문제 4. 이 데이터셋을 기준으로 user_id의 첫번째 접근 월을 구하는 쿼리를 작성하기 # 마지막 추가 문제 SELECT *, FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS First_Value, LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS Last_Value FROM ( SELECT *, lead(visit_month,1) OVER (PARTITION BY user_id ORDER BY visit_month) AS lead_visit_month FROM `advanced.analytics_function_01` ORDER BY user_id, visit_month ) ORDER BY user_id, visit_month 2. Frame 연습문제 문제 . 회사의 모든 주문량, 누적 주문량, 최근 직전5개 평균 주문량 구하기 SELECT * , SUM(amount) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total, # OVER 안에 아무것도 없으면 전체 출력이다! SUM(amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_by_user, AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg order by order_id 3. 연습문제 문제 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성하기. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들기 SELECT *, COUNT(user) OVER (PARTITION BY user ) AS cnt FROM advanced.query_logs ORDER BY query_date, user 문제 2. 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구하기. 단, 랭킹이 1등인 사람만 결과가 보이도록 하기 # query_date를 바탕으로 주차별로 구분하여 WITH함수로 묶기 WITH week_number_table AS ( SELECT *, EXTRACT(WEEK FROM query_date) AS week_number, # 하루에 유저별 쿼리 이용수 추출 COUNT(*) AS query_cnt, FROM advanced.query_logs GROUP BY ALL ) # 팀 내에서 유저별로 랭킹 구하기 SELECT week_number, team, query_date, query_cnt, RANK() OVER (PARTITION BY week_number,team ORDER BY query_cnt DESC) AS team_rank FROM week_number_table # 1등인 유저만 출력 QUALIFY team_rank = 1 ORDER BY week_number, team 문제 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성하기 # 2번쿼리 WITH week_number_table AS ( SELECT *, EXTRACT(WEEK FROM query_date) AS week_number, COUNT(*) AS query_cnt, FROM advanced.query_logs GROUP BY ALL ) SELECT *, # 1주전의 실행수 이므로 LAG를 이용해서 전 week_number의 실행수 구하기 LAG(query_cnt, 1) OVER(PARTITION BY user ORDER BY week_number) AS prev_week_query_count FROM week_number_table 문제 4. 시간의 흐름에 따라 일자별로 유저가 실행한 누적 쿼리 수를 작성하기 # 시간의 흐름에 따라 일자별로 쿼리수 묶기 WITH user_query AS ( SELECT *, COUNT(*) AS query_count FROM advanced.query_logs GROUP BY ALL ) # 유저별로 누적 쿼리수 구하기 SELECT *, SUM(query_count) OVER (PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_query_count FROM user_query ORDER BY user # Frame 의 Default 값은 UNBOUNDED PRECEDING ~ CURRENT ROW 이다 # 데이터 정합성 확인 할때 2가지 값이 모두 같은지 비교하면 편리하다 ( WHERE, QUALIFY절에 활용 ) 문제 5. 만약 주문 횟수가 없으면 NULL로 기록됨. 이런 데이터에서 NULL값 이라고 되어 있는 부분을 바로 이전 날짜의 값 으로 채워주는 쿼리를 작성하기 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 ) # CASE문을 이용해서 주문량 NULL 값이면 전 날짜의 주문량으로 대치 SELECT *, (CASE WHEN number_of_orders IS NULL THEN LAG(number_of_orders,1) OVER (ORDER BY date) ELSE number_of_orders END ) AS number_of_orders2 FROM raw_data # BUT 맨 마지막값 NULL이 연속2번이라 NULL이 나옴 # LAST_VALUE의 IGNORE NULLS 쓰기 SELECT *, LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders2 FROM raw_data 문제 6. 5번 문제에서 NULL을 채운 후, 2일전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성하기(이동평균) SELECT date, number_of_orders2, AVG(number_of_orders2) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_agv # NULL값 처리한 테이블 서브쿼리로 묶은 다음에 윈도우 함수써서 2틀전~현재 평균 출력하기 FROM ( SELECT *, LAST_VALUE(number_of_orders IGNORE NULLS) OVER (ORDER BY date) AS number_of_orders2, FROM raw_data ) 문제 7. app_logs 테이블에서 custom session을 만들기. 이전 이벤트 로그와 20초가 지나면 새로운 session을 만들기. session은 숫자로 (1, 2, 3 …) 표시해도 됨. WITH START AS ( SELECT event_date, # 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-16' ), # 기존 시간의 차이 구하기 DIFF_DATE AS ( SELECT *, DATETIME_DIFF(event_datetime, before_event_datetime, SECOND) AS second_diff FROM ( SELECT # 전(LAG) 시간을 불러오기 위해 서브쿼리로 묶음 *, LAG(event_datetime, 1 ) OVER ( PARTITION BY user_pseudo_id ORDER BY event_datetime) AS before_event_datetime FROM START) ORDER BY event_datetime) # session_start를 누적합 이용하여 session_number 구하 SELECT *, SUM(session_start) OVER (PARTITION BY user_pseudo_id ORDER BY event_datetime) AS session_number FROM ( SELECT *, # CASE문을 써서 처음 시작부분 (NULL) 1로 바꾸기 (CASE WHEN second_diff IS NULL THEN 1 # second_diff 가 20초 이상이면 1 아니면 0 WHEN second_diff >= 20 THEN 1 ELSE 0 END) AS session_start FROM DIFF_DATE)
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery
노앙
댓글 1
좋아요 0
조회수 107
[인프런 빅쿼리 빠짝스터디 2주차] 윈도우 함수, FRAME설정, QUALITY
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
윈도우 탐색 함수 연습문제 (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 (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 (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 (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 (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
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery
김성호
댓글 1
좋아요 0
조회수 82
[빠짝스터디 2주차 과제] 윈도우 함수 연습문제
미해결
BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)
1. --1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단 Group By를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요. select * , count(query_date) over(partition by user) as total_query_cnt from advanced.query_logs order by user, query_date 2. --2) 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단 랭킹이 1등인 사람만 결과가 보이도록 해주세요. 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 ) as base qualify team_rank = 1 order by 1,2,3 3. --3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요. select * , lag(query_cnt) over(partition by user order by week_number) as prev_week_query_cnt from ( select EXTRACT(WEEK FROM query_date) as week_number , team , user , count(user) as query_cnt from advanced.query_logs group by all ) as base order by user 4. --4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요. select * , sum(query_cnt) over(partition by user order by query_date) as cumulative_query_cnt from ( select user , team , query_date , count(user) as query_cnt from advanced.query_logs group by all ) as base order by user 5. --5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요. 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 * , ifnull(number_of_orders,last_value(number_of_orders ignore nulls) over(order by date)) as last_value_orders from raw_data 6. --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 date , number_of_orders , avg(number_of_orders) over(order by date rows between 2 preceding and current row) as moving_avg from (select date , ifnull(number_of_orders,last_value(number_of_orders ignore nulls) over(order by date)) as number_of_orders from raw_data) as base 7. --7) app_logs 테이블에서 Custom Session을 만들어 주세요. 이전 이벤트 로그와 20초가 지나면 새로운 Session을 만들어 주세요. Session은 숫자로 (1,2,3...) 표시해도 됩니다 --2022-08-18일의 user_pseudo_id(1997494153.8491999091)은 session_id가 4까지 나옵니다. 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 , lag(datetime(timestamp_micros(event_timestamp), 'Asia/Seoul')) over(partition by user_pseudo_id order by event_timestamp) as before_event_datetime from advanced.app_logs ) , diff_data as ( select * , if(second_diff > 20 or second_diff is null, 1, null) as session_start from ( select * , DATETIME_DIFF(event_datetime, before_event_datetime, second) as second_diff from base ) ) select * , sum(session_start) over(order by event_timestamp) as session_num from diff_data -- where event_date = '2022-08-18' -- and user_pseudo_id = '1997494153.8491999091' order by event_timestamp
- sql
- Google-Analytics
- firebase
- google-sheets
- bigquery
케이든
댓글 1
좋아요 0
조회수 101
인기 태그
인프런 TOP Writers
- 정현섭 8
- 닉네임 5
- eggmun(문성진) 4
- 박영준 4
- 김보노 3
- Like me black 3
- 꿈이 있는 자유 3
주간 인기글
- 🚀 IT 공부는 했는데 왜 아직도 안 될까요? | AI와 함께 실무 중심 IT 모임 🤖 NOESIS
- 🚀 IT 공부는 했는데 왜 아직도 안 될까요? | AI와 함께 실무 중심 IT 모임 🤖 NOESIS
- 🚀 끝까지 만들어 본 적 있나요? | 실서비스 완성 + 수익 창출 모임 💰 catalyst collectiv
- 🚀 IT 공부는 했는데 왜 아직도 안 될까요? | AI와 함께 실무 중심 IT 모임 🤖 NOESIS
- 🚀 끝까지 만들어 본 적 있나요? | 실서비스 완성 + 수익 창출 모임 💰 catalyst collectiv