(SQL) 이동평균 구하기

09 Dec 2021 in 가이던스 on SQL

(SQL) 이동평균 구하기

✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크

---

1.이동평균이란?

이동평균이란 추세의 변동을 알 수 있도록 구간을 옮겨 가면서 구하는 평균을 말한다.

예를 들어 7일간 이동평균은 1~7일 평균, 2~8일 평균, 3~9일 평균 … 처럼
가장 오래된 데이터 대신에 최신의 데이터를 넣어 평균을 구하는 것을 말한다.

즉, 이동평균은 정해진 기간에서의 데이터의 평균이라고 할 수 있다.

---

2. 이동평균 구하는 구문

이동평균을 구하기 위해서, 윈도 함수인 OVER( ORDER BY ~) 구문을 활용해주어야 한다.
윈도함수를 통해 7일씩 데이터를 쪼개서 계산하는것이 원리이다.

이동평균을 구하는 구문은 다음과 같다.

SELECT 
    <날짜 칼럼>,
    AVG(SUM(구하고자 하는 칼럼)) OVER (ORDER BY <날짜 칼럼> ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) ,
    -- 최근 최대 7일동안의 이동평균
    CASE
        WHEN 7=count(*)
        OVER(ORDER BY <날짜 칼럼> ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    THEN
        AVG(SUM(구하고자 하는 칼럼))
        OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    END AS  -- 최근 7일 동안의 평균을 확실하게 계산하기
FROM <테이블명>
GROUP BY <날짜 칼럼>

;

위의 구문을 보면 이동평균 구하는 것을 2가지 방법으로 나누어 놓은 것을 볼 수 있다.

• 최근 최대 7일 동안의 이동평균
  
• 무조건 7일 동안의 평균
  
  

첫번째 방법은 7일의 기간이 충족되지 않는 1~6일도 있는 기간까지의 평균이 구해지는 방법이다.

반면 두번째 방법인 CASE 구문을 이용한 이동평균 구하기 방법은
무조건 기간이 7일이 되었을때 평균을 구하는 것이다.

---

3. 예제

다음 매출 데이터를 보자.

이 매출 데이터를 위에서 소개한 쿼리를 이용해 날짜별 매출과 7일 이동평균을 집계하는 결과물을 출력해보자

SELECT
    dt,
    SUM(purchase_amount) AS total_amount,
    AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS seven_day_avg, -- 최근 최대 7일 동안의 평균 계산하기.
    CASE
        WHEN 7=count(*)
        OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    THEN
        AVG(SUM(purchase_amount))
        OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    END AS seven_day_avg_strict -- 최근 7일 동안의 평균을 확실하게 계산하기
FROM purchase_log
GROUP BY dt
ORDER BY dt;

아래와 같이 출력 됨을 알 수 있다.

---