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

cg0630님의 프로필 이미지
cg0630

작성한 질문수

[빠짝스터디 2주차 과제] 윈도우 함수(연습문제) - 탐색함수 / Frame / 총정리

작성

·

68

0

탐색함수 연습문제

문제 1.

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

문제2.

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

문제3.

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

문제 4.

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

총정리 연습문제

문제 1.

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

문제2.

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.

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

문제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

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

SELECT
	*,
	LAST_VALUE(number_of_order IGNORE NULLS) OVER (ORDER BY date) AS last_value_orders
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
), 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.

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

답변 1

0

안녕하세요, 인프런 AI 인턴입니다.
cg0630님께서 주신 윈도우 함수와 관련된 SQL 연습문제들을 잘 보았습니다. 윈도우 함수는 데이터베이스 내에서 연속된 데이터 행을 기준으로 집계, 순위를 매기는 데 사용됩니다. 문제로 주어진 SQL 쿼리문들도 주로 데이터의 순서를 기준으로, 또는 특정 조건을 만족하는 데이터를 찾기 위한 다양한 윈도우 함수 활용을 볼 수 있습니다.

  1. LEAD와 LAG 함수는 데이터 행의 상대적 위치에서 값을 가져오는 데 주로 사용됩니다. 이를 통해 이전 혹은 이후의 데이터 값을 비교하거나 계산할 수 있습니다.

  2. SUM이나 AVG 함수에 윈도우 함수를 결합하여, 누적 합계 또는 이동 평균을 계산할 수 있습니다. 다양한 ‘ROWS BETWEEN’ 조건을 통해 윈도우의 크기를 조정할 수 있다는 것이 특징이지요.

  3. RANK, COUNT 등의 함수는 특정 파티션 내에서 데이터를 정렬하고, 정렬된 기준에 따라 순위를 매기거나 빈도수를 집계하는 데 사용됩니다.

  4. LAST_VALUE와 IGNORE NULLS 옵션을 활용하면, NULL 값을 제외하고 마지막 값을 가져올 수 있어 데이터 확인시 유용합니다.

이와 같은 기능들을 활용하여 복잡한 데이터 분석에 적합한 쿼리를 구성할 수 있습니다. SQL 윈도우 함수에 익숙해지시면 더 효과적으로 데이터를 다룰 수 있을 것입니다. 열심히 연습하시고 많은 도움이 되길 바랍니다!

궁금하신 점이나 더 배우고 싶으신 내용이 있으면 언제든지 질문해주세요.
https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#repositories.limit-query-result

cg0630님의 프로필 이미지
cg0630

작성한 질문수

질문하기