SQL로 매출 데이터를 분석하려면? _ 이동 평균,당월 매출 누계,Z차트

이 글은 책 '데이터 분석을 위한 SQL 레시피'를 참고하여 작성하였습니다.

http://sqlfiddle.com/ 이라는 사이트에서 샘플 데이터를 다운 받아 실습하면 좋습니다!

SQL Fiddle

 

이동 평균을 사용한 날짜별 매출 추이 보기

이동 평균이란 무엇일까? 왜 사용할까?

- 단순히 날짜별 매출 리포트를 작성하면, 주말에 매출이 크게 변동하는 그래프 등의 경우, 매출이 상승하는 경향이 있는지, 하락하는 경향이 있는지 판단하기 어렵기 때문에, 7일 동안의 평균 매출을 사용한 '7일 이동 평균'으로 표현하는 것이 더 좋습니다.

네이버 백과

따라서 주식을 해본 경험이 있으시다면 이런 차트를 보신 적이 있으실 것입니다.

주식에서는 '추세'가 매우 중요하기 때문이죠!

연합 인포맥스

그러니까, 7일 평균이 계속 움직인다(moving)으로 생각해 주시면 될 것 같습니다.

이걸 어떻게 SQL로 구할까요?

2023.12.01~ 12.07 / 2023.12.08~12.14 이렇게 일주일씩 끊어서 7일 평균을 구하는 것이 아니라,

12.05 이면 앞에서 부터앞에서부터 지금까지 7일간의 평균을 구하고, 12.6일 이면 앞에서부터 지금까지 7일간의 평균을 구해야 합니다.

이 앞에서 부터 지금까지 7일간의 평균은

= '6일전 부터 현재까지의 평균'과 같고, 이를 SQL WINDOW 함수로 표현하면

= ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

가 됩니다.

PRECEDING 이란 그 전의 라는 뜻입니다.

네이버 사전

따라서 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

은 자연스럽게 '6개 전과 현재 행 사이'라는 말이 되겠죠..!

예를 들어 이런 purchase_log 테이블이 있다면

sql 레시피

MS sql 기준으로

WINDOW Function을 사용하여 구할 수 있습니다.

날짜별로 GROUP BY 해주고, 구매 양의 합의 평균을 구하는데 날짜 순으로 정렬하여 이를 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW로 7일로 끊죠.

 

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 dt
ORDER BY dt ;

7일 이동 평균 구하기 ( Strict 버전 포함)

같은 7일 이동 평균이더라도, 계산할 앞의 6일이 없는 경우는 제외할 수가 있는데, CASE 구문 (seven_day_avg_strict 컬럼)이 바로 그것입니다.

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_log
GROUP BY dt
ORDER BY dt ;

 

 

여기서는 null 로 라도 처리해 주었지만, 아예 strict 한 7일 이동 평균만 출력하도록 하고 싶을 수도 있는데요. 그런 경우에는 조금 더 복잡해집니다.

 

7일 이동 평균 구하기 ( Strict만)

리트코드 1321. Restaurant Growth를 참고하여 이를 알아볼 수 있습니다.

나의 풀이

• 이 문제는 기본적으로 이동 평균을 구하는 문제..! But..

일반적인 이동 평균 쿼리와 다른 점

• 한 날짜에 두 개 이상의 행이 있다.
• strict 7 days로 count(*) rows between 6 preceding and current row = 7 인 날짜들만 출력해주어야 한다.

때문에 풀이를 하나하나씩 해보면,

1. 한 날짜에 두개 이상의 행이 있다.

• 이 점 때문에 날짜별로 하나의 행이 오도록 CTE 테이블을 만들었다.

WITH CTE AS(
    SELECT  DISTINCT visited_on
          , SUM(amount) OVER(PARTITION BY visited_on ORDER BY visited_on) AS am  
    FROM customer
)

 

2. Strict 7 days로 count(*) rows between 6 preceding and current row = 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
      ,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_log
GROUP BY dt
ORDER BY dt ;

출처 : SQL 레시피

• 원래 이런식으로 strict 하더라도 이동 평균을 null로 처리한다면 이렇게 쿼리를 칠 수 있다.
• 하지만 이번에는 null 처리될 행의 날짜들을 다 없애주어야 한다.

SELECT      visited_on 
            ,SUM(am) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS amount
            ,AVG(SUM(am)) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS average_amount
            ,COUNT(*) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS cnt
FROM CTE
GROUP BY visited_on

• 그래서 만약 이 테이블에 WHERE 절로 cnt=7을 걸게 되면 참사가 일어난다.
• FROM/JOIN -> WHERE -> GROUP BY -> HAVNG -> SELECT -> ORDER BY -> LIMIT 이 순서대로 쿼리가 되기 때문에 WHERE 절에서 이동 평균을 구할 앞의 날짜들의 데이터가 삭제된다.
• 그래서 나는 이 점을 해결하기 위해 서브 쿼리를 두었다.

SELECT visited_on 
      ,amount  
      ,ROUND(average_amount,2) AS average_amount
FROM (
        SELECT visited_on 
            ,SUM(am) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS amount
            ,AVG(SUM(am)) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS average_amount
            ,COUNT(*) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS cnt
        FROM CTE
        GROUP BY visited_on
)l 
WHERE cnt = 7 
ORDER BY visited_on

 

완성 쿼리

WITH CTE AS(
    SELECT  DISTINCT visited_on
          , SUM(amount) OVER(PARTITION BY visited_on ORDER BY visited_on) AS am  
    FROM customer
)
SELECT visited_on 
      ,amount  
      ,ROUND(average_amount,2) AS average_amount
FROM (
        SELECT visited_on 
            ,SUM(am) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS amount
            ,AVG(SUM(am)) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS average_amount
            ,COUNT(*) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS cnt
        FROM CTE
        GROUP BY visited_on
)l 
WHERE cnt = 7 
ORDER BY visited_on

 

다른 사람의 풀이는 어떨까?

SELECT
    visited_on,
    (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount,
    ROUND(
        (
            SELECT SUM(amount) / 7
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        ),
        2
    ) AS average_amount
FROM customer c
WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )
GROUP BY visited_on;

 

한 날짜에 두개 이상의 행이 있다.

• 이 점을 서브 쿼리나 WITH절을 사용하지 않고 SELECT 구문에 테이블을 끌어와
• WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on 이렇게 DATE_SUB로 날짜 문제를 해결하였다.
• strict 7 days로 count(*) rows between 6 preceding and current row = 7 인 날짜들만 출력해주어야 한다.
  • 이렇게 각자 다른 customer 테이블을 사용, DATE를 사용하여 순서에 따른 문제를 해결하였다.
  • WHERE visited_on >= ( SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) FROM customer )

 

이동 평균을 여러가지 방법으로 구했으니 다른 매출 분석 방법으로 넘어가도록 하겠습니다. 

 

당월 매출 누계 구하기

• 월별로 매출의 누계를 SQL로 어떻게 계산할 수 있을까?

ROWS UNBOUNDED PRECEDING

UNBOUNDED란? , 출처 : 네이버 사전

• ROWS UNBOUNDED PRECEDING : 무한한 행 전의
  • The purpose of the ROWS clause is to specify the window frame in relation to the current row.
  • The syntax is:
  • ROWS BETWEEN lower_bound AND upper_bound
• The bounds can be any of these five options:
  • UNBOUNDED PRECEDING – All rows before the current row.
  • n PRECEDING – n rows before the current row.
  • CURRENT ROW – Just the current row.
  • n FOLLOWING – n rows after the current row.
  • UNBOUNDED FOLLOWING – All rows after the current row.

출처: 5 Practical Examples of Using ROWS BETWEEN in SQL

SELECT dt 
       -- 연-월
      ,substring(dt,1,7) AS year_month 
      ,SUM(purchase_amount) AS total_amount
      ,SUM(SUM(purchase_amount)) OVER(PARTITION BY substring(dt,1,7)
                                      ORDER BY dt ROWS UNBOUNDED PRECEDING) AS agg_amount
FROM purchase_log
GROUP BY dt
ORDER BY dt ;

 

 

Z 차트로 업적의 추이 확인하기

• Z차트는 월별매출, 매출 누계, 이동 년계 라는 3가지 지표로 구성됩니다.  https://blog.naver.com/socialmedia/220194062598 

Z차트를 활용한 매출추이 분석

 

위에서 배운 당월 매출 누계를 구하는 방식을 활용하여 Z차트를 그리고, 매출 추이 데이터로 인사이트를 뽑아낼 수 있습니다. 

WITH daily_purchase AS ( 
  SELECT dt
        ,substr(dt,1,4) AS year
        ,substr(dt,6,2) AS month
        ,substr(dt,9,2) AS day
        ,SUM(purchase_amount) AS purchase_amount
        ,COUNT(order_id) AS orders
  FROM purchase_log
  GROUP BY dt 
  
), monthly_amount AS (
  
  --  월별 매출 집계하기   
  SELECT year
        ,month
  		,SUM(purchase_amount) AS amount
  FROM daily_purchase
  GROUP BY year,month 
  
),calc_index AS (
  SELECT 
  	year
   ,month
   ,amount
   -- 2015년의 누계 매출 집계하기 
  ,SUM(CASE WHEN year = '2015' THEN amount END)
   OVER(ORDER BY year,month ROWS UNBOUNDED PRECEDING)
   AS agg_amount
 -- 당월부터 11개월 이전까지의 매출 합계(이동 년계) 집계하기 
  ,SUM(amount) OVER(ORDER BY year,month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
  AS year_avg_amount 
  FROM monthly_amount
  ORDER BY year,month 
 )
 
 ### 마지막으로 2015년의 데이터만 압축하기
SELECT concat(year, '-' , month) AS yearandmonth
      ,amount
      ,agg_amount
      ,year_avg_amount
FROM calc_index
WHERE year = '2015'
ORDER BY yearandmonth

 

감사합니다!