[D+22][SQL] 이동 평균을 활용한 날짜별 매출 평균 구하기 - AVG() OVER (), ROWS UNBOUNDED PRECEDING

 

✏️ STUDY INDEX 
01. 이동 평균 개념
02. 이동 평균 관련 함수 및 구문 

 

01. 이동 평균 개념 

---

이동 평균(Moving Average)은 시계열 데이터의 노이즈를 줄이고 데이터의 패턴을 이해하는 데 사용되는 통계적 기법이다. 일정한 기간 동안의 데이터의 평균을 계산하여 각 시점에서의 값을 구합니다.

이동 평균은 주로 다음과 같은 목적으로 사용됩니다.
1. 데이터 스무딩: 시계열 데이터의 변동성을 줄여서 패턴을 더 명확하게 보기 위해 사용됩니다.
2. 추세 분석: 데이터의 장기적인 추세를 파악하는 데 사용됩니다.
3. 주식 및 금융 시장 분석: 주식 가격의 움직임을 분석하여 매수 및 매도 시점 결정에 도움을 줍니다.
ChatGPT  이동평균'에 대한 답변 일부 발췌 (2024-05-29)

 

 

요약하자면, 이동 평균(Moving Average)은 데이터의 추세와 패턴을 파악하기 위해 사용되는 기법이에요!

실무에서는 단순히 날짜별 매출 리포트를 작성하면 매출 변동을 파악하기 어렵기 때문에, 7일 평균 매출을 기반으로 이동 평균을 사용한다고 합니다. 오늘은 Mysql로 이동 평균을 활용한 날짜별 매출 평균을 구해보겠습니다!

 

 

02. 이동 평균 관련 함수 및 구문 -  윈도우 함수, ROWS

---

SQL에서 이동평균을 구하기 위해서 윈도우 함수와 ROWS UNBOUNDED PRECEDING을 사용합니다. 

평균을 구하는 것이기 때문에 윈도우 함수는 AVG() OVER (ORDER BY 칼람 ROWS ...)로 작성합니다.

 

ROWS UNBOUNDED PRECEDING은 주로 윈도우 함수와 함께 활용하고 특정 범위의 행을 지정할 때 사용해요. 저는 이번에 처음 알게 됐는데 누적 계산할 때 자주 사용한다고 하니 익혀두세요!

기본 문법 구조와 활용법은 아래와 같아요. 

기본 문법	의미
ROWS BETWEEN lower_bound AND upper_bound	lower_bound 부터 upper_bound까지 행 포함
UNBOUNDED PRECEDING	현재 행 이전 모든 행 포함
n PRECEDING	현재 행 이전 n개 행 포함
CURRENT ROW	현재 행 포함
n FOLLOWING	현재 행 이후 n개 행 포함
UNBOUNDED FOLLOWING	현재 행 이후 모든 행 포함

# 7일 이동 평균 구하기(1) 
SELECT date, 
	SUM(sales) AS total_sales,
    AVG(SUM(sales)) OVER (ORDER BY date ROWS BETWEEN PRECEDING 6 AND CURRENT ROW) AS moving_avg
    # 날짜 기준 정렬, 6일전부터 현재까지의 행 사용
FROM 테이블 명
GROUP BY date 
ORDER BY date;

# 7일 이동 평균 구하기(2) - 7일이 아닌 데이터는 null로 반환하여 제외해주기

SELECT date, 
	SUM(sales) as total_sales, 
    AVG(SUM(sales) OVER (ORDER BY date ROWS BETWEEN PRECEDING 6 AND CURRENT ROW) AS moving_avg, 
    CASE WHEN 7 = COUNT(*)
    	THEN AVG(SUM(sales)) OVER (ORDER BY date ROWS BETWEEN PRECEDING 6 AND CURRENT ROW)
        ELSE NULL END AS moving_avg_strinct
FROM 테이블명 
GROUP BY date 
ORDER BY date;

 

ChatGPT에게 1번 쿼리를 실행할 수 있게 데이터를 만들어 달라고 했는데, 집계한다면 우측처럼 결과가 나올 거예요. 1~6일까지는 평균을 순차적으로 계산하기 때문에 01-07 이후부터가 정확한 7일간의 이동 평균이라고 할 수 있겠네요! 해당 데이터를 활용해서 그래프를 그린다면 매출 추세나 증감률을 더 직관적으로 확인할 수 있을듯합니다. 

---

[ 2024. 5. 29 Today I Learned ] 오늘은 SQL을 활용해서 이동평균을 구하는 방법을 복습했습니다. 기본적인 원리는 이해했지만, 간단한 예제가 아닌 활성화 유저 비율, 증감률 등을 계산한다면 훨씬 어렵고 복잡할 것 같아요. 많은 복습이 필요할 듯 합니다. 그래도 SQL 챌린지 세션을 들으면서 쿼리를 응용해서 원하는 형태의 데이터를 추출하는 법을 배울 수 있어 너무 좋습니다!🍀 

 

*스파르타코딩클럽 [KDT 데이터 분석가 양성 과정] 중 SQL 챌린지 세션 2회 차 자발적 복습 포스팅입니다.