[SQL] 이동평균을 통해 매출 추이 살펴보기

 

1. 이동평균이란?
N일 동안의 평균 변화의 추이를 보기 위한 값

💡 ROWS BETWEEN lower_bound AND upper_bound

 

예를 들어, ‘ROWS BETWEEN 6 PRECEDING AND CURRENT ROW’는

현재 행 포함 이전 6개의 행, 즉 7개의 행을 의미한다.

따라서, 7일 동안의 매출액의 평균 추이를 보기 위해서는

날짜 별로 GROUP BY를 해준 후, 구매량의 합을 평균내어

이를 날짜 순으로 정렬하여 7일씩 끊어서 보면 된다.

 

<purchase_log 테이블>

 

<7일간 매출의 이동 평균 구하는 방법>

SELECT dt      
      ,SUM(purchase_amount)      
      ,AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND 
       CURRENT ROW) AS seven_day_avg     
FROM purchase_log      
GROUP BY dtORDER BY dt ;

 

<7일간 매출의 이동 평균 구하는 방법 - 계산할 6일이 없는 경우 제외하는 ver.>

SELECT dt      
      ,SUM(purchase_amount)      
      ,AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND 
        CURRENT ROW) AS seven_day_avg      
      ,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
 FROM purchase_logGROUP BY dt
 ORDER BY dt ;

 

 

❓정수 나누기 문제 해결법 (1.0 곱해주기)

SQL에서 정수 나누기를 하면 결과가 정수로 반환된다.

예를 들어, 다음의 결과는 정수가 될 수 있다. 

COUNT(DISTINCT CASE WHEN signup_action = 'Confirmed' THEN user_id END) / COUNT(DISTINCT user_id)

 

 이 경우 소수점 이하 부분이 버려져서 정확한 비율을 얻을 수 없다.

 

암시적 형변환을 통한 소수점 표현:

COUNT(DISTINCT CASE WHEN signup_action = 'Confirmed' THEN user_id ELSE NULL END) * 1.0

정수에 실수 1.0을 곱하여 실수 결과를 얻음으로써 정수 나누기 문제를 해결하고

소수점 이하 부분까지 정확한 비율을 구할 수 있다.

 

---

 

오늘 SQL 챌린지 세션 들은 내용 정리