인프런 커뮤니티 질문&답변

탐색함수 연습문제

문제 1. user들의 다음 접속 월과 다다음 접속 월을 구하는 쿼리를 작성해주세요.

SELECT
	*,
	LEAD(visit_month) 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 nnext_visit_month
FROM advanced.analytics_function_01

LEAD가 다음!!!! 은근 헷갈린다 정말. L, E, A, D. D로 끝나니까 다음이라고 생각해야겠다.

문제2. user들의 다음 접속 월과 다다음 접속 월, 이전 접속 월을 구하는 쿼리를 작성해주세요.

SELECT
	*,
	LEAD(visit_month) 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 nnext_visit_month,
	LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS previous_visit_month
FROM advanced.analytics_function_01

• LAG함수를 쓴 컬럼에서 NULL이 나온다? → 아 이 row가 처음이다.

• LEAD 함수를 쓴 컬럼에서 NULL이 나온다? → 아 이 row가 마지막이다.

문제3. 유저가 접속했을 때, 다음 접속까지의 간격을 구하시오.

#답 x, after_visit_month에서 에러남
SELECT
	*,
	LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) AS after_visit_month,
	after_visit_month - visit_month
FROM advanced.analytics_function_01

→SELECT 절은 가장 마지막에 실행되기 때문에 LEAD함수를 실행하면서 after_visit_month라 이름 붙인 것이다. 따라서 아직 SELECT 절에서 after_visit_month라는 것을 인식하지 못한다.

#물론 쿼리가 짧을 때에는 이것도 가능. 
#하지만 이러한 쿼리가 굉장히 많아지면 복잡해지고, 실수하기 좋다.
#중복된 쿼리는 줄이는 것이 좋다.
SELECT
	*,
	LEAD(visit_month, 1) OVER (PARTITION BY user_id ORDER BY visit_month) - visit_month AS diff_visit_month,
FROM advanced.analytics_function_01

→ 윈도우 함수를 이렇게 쓰는 것이 좋을까? 중복된 쿼리는 줄이는 것이 좋다.

→ 서브쿼리 사용하는 것이 더 낫다. 서브쿼리나 WITH문과 같은 CTE에서는 윈도우함수를 여러 개 쓰더라도 같은 정렬기준과 파티션 기준을 갖는다면 한 번의 데이터 스캔을 거친다.

→ 쿼리문 수정시에도 좋다. 만약 쿼리를 수정해야할 때 서브쿼리 내의 쿼리문만 수정하면 되기 때문이다.

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
)

문제 4. 이 데이터셋을 기준으로 user_id의 첫번째 접근 월을 구하는 쿼리를 작성해주세요.

SELECT
	*,
	FIRST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS first_month
	LAST_VALUE(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS last_month
FROM advanced.analytics_function_01

Frame 연습문제

문제 1. 우리 회사의 모든 주문량은?

SELECT
	*,
	SUM(amount) OVER (ORDER BY order_date, order_id #OVER 안에 아무것도 안 들어가도 됨
	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS amount_total
FROM advanced.orders

문제 2. 특정 주문 시점에서 누적 주문량은?

SELECT
	*,
	SUM(amount) OVER (ORDER BY order_date, order_id) AS cumulative_sum
FROM advanced.orders

문제 3. 고객별 주문 시점에서 누적 주문량은?

SELECT
	*,
SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date, order_id
) AS cumulative_sum_by_user

문제 4. 최근 직전 5개의 평균 주문량은?

SELECT
	*,
	AVG(amount) OVER (ORDER BY order_date, order_id 
	ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_orders_avg_amout

총정리 연습문제

문제 1. 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요

SELECT
	*,
	COUNT(query_date) OVER (PARTITION BY user ORDER BY user) AS total_query_cnt
FROM advanced.query_logs

문제 2. 주차별로 팀 내에서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요.

WITH table 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 ORDER BY query_cnt) AS team_rank
FROM table
QUALIFY team_rank = 1

문제 3. (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요.

WITH table 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) OVER (PARTITION BY user ORDER BY week_number) AS prev_week_query_cnt
FROM table

LAG(어떤 이전 값이 들어가야 하는지 = 1주 전 쿼리실행수)

PARTITION BY로 쓸 기준은 user : 왜냐면 user 단위로 1주 전 쿼리실행수를 가져오기 때문

ORDER BY로 쓸 기준은 week_number : 1주 전 쿼리이기 때문에 날짜 관련으로 정렬

문제 4. 시간의 흐름에 따라 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요.

WITH query_count_table 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) AS cululative_query_count
FROM query_count_table

처음에는 PARTITION BY에 query_date도 같이 넣었다가 결과가 날짜를 기준으로 구분되고 있는 것이 보여서 아차 하고 query_date를 뺐더니 정답이 되었다…..자꾸 문제에서 말하는 “일자 별로” 와 같은 말 때문에 PARTITION BY에 어떤 컬럼이 와야 하는지 헷갈리는 것 같다.


문제 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 UNION ALL
)

SELECT
	*,
	LAST_VALUE(number_of_order IGNORE NULLS) OVER (ORDER BY date) AS last_value_orders
FROM raw_data

LAST_VALUE()는 원래 NULL을 포함해서 연산하기 때문에 위에서 그냥 쓰면 NULL이 마지막 값으로 인정돼서 들어오지만, IGNORE NULLS를 사용하면 NULL값은 제외하고 값이 있는 것만 마지막값으로 생각하고 가져오기 때문에 쓰는 것.

문제 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. 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
	*,
	#이전 이벤트 시간과 현재 이벤트시간의 간격을 SECOND 초단위로 구하기 / second_diff를 기반으로 새로운 세션의 시작일지 아닐지 판단
	DATETIME_DIFF(event_datetime, prev_event_datetime, SECOND) AS second_diff
FROM
	(
	SELECT
		*,
		# 직전 이벤트 시간을 prev_event_datetime으로 만들기
		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 NULL
			END AS session_start
	FROM diff_data
)
ORDER BY event_datetime