데이터 분석 28일차
문제 #3 (중간)
네이버 블로그의 게시글 데이터를 분석하여 3일 이동 평균(rolling average)을 계산하세요.
각 사용자의 블로그 게시글 수에 대한 3일 이동 평균을 계산하여 user_id, post_date, rolling_avg_3d 값을 출력하는 SQL 쿼리를 작성하세요.
결과는 소수점 둘째 자리까지 반올림하여 출력하세요.
결과는 고객 ID, 게시글 작성 날짜 기준으로 오름차 정렬하세요.
📌 이동 평균(rolling average) 정의
- 이동 평균은 시계열 데이터를 분석할 때 일정 기간 동안의 평균을 계산하는 기법입니다.
- 이 문제에서는 각 사용자의 3일 이동평균(3-day rolling average)을 구해야 합니다.
- 즉, 각 날짜의 게시글 수와 그 이전 2일간의 게시글 수 평균을 계산해야 합니다.
- 데이터가 3일보다 적으면 가능한 범위 내에서 평균을 구합니다.
🔍 설명
- 2022-06-01: 해당 날짜 하나뿐이므로 평균 = 2.00
- 2022-06-02: (2 + 1) / 2 = 1.50
- 2022-06-03: (2 + 1 + 3) / 3 = 2.00
- 2022-06-04: (1 + 3 + 4) / 3 = 2.67
- 2022-06-05: (3 + 4 + 5) / 3 = 4.00
with add_row_num as(select *, row_number() over(partition by user_id order by post_date desc) row_num
from blog_posts
)
select *,
avg(post_count) over(PARTITION BY user_id ORDER BY user_id, post_date desc ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) avg_post_cnt
from add_row_num✅ ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
-
뜻: 현재 행을 기준으로 이전 2행부터 현재 행까지를 포함한 범위를 설정
-
사용 위치: OVER (...) 구문 안
📌 구성
| 키워드 | 의미 |
|---|---|
| ROWS | 행 단위 범위 지정 (지정된 순서 기준) |
| 2 PRECEDING | 현재 행의 바로 앞에서 두 번째 행까지 포함 |
| CURRENT ROW | 현재 행 포함 |
🧪 예시 (ORDER BY 기준)
| row_num | 값 | 계산 대상 (2 PRECEDING ~ CURRENT) |
|---|---|---|
| 1 | 10 | [10] |
| 2 | 20 | [10, 20] |
| 3 | 30 | [10, 20, 30] |
| 4 | 40 | [20, 30, 40] |
| 5 | 50 | [30, 40, 50] |
📝 요약 문장
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW는 윈도우 함수에서 이전 2개 행과 현재 행을 포함하는 범위로 평균, 합계 등 집계함수를 계산할 때 사용된다.
