SQL: 시계열 기반으로 데이터 집계하기

2020-02-23

시계열로 집계를 하여 경향, 규칙성, 기간 비교를 통한 변화폭을 확인
매출을 단순한 꺾은선 그래프로 나타냈을 때는 찾아내기 힘든 변화를 명확하게 시각적으로 확인

  • 이동평균
  • YoY: 전년 (동기) 대비 증감률(수익률, 상승률)
  • QoQ: 전분기 대비 증감률
  • MoM: 전월 대비 증감률
  • YTD: 연초 대비 증감률
  • MTD: 월초 대비 증감률
  • Z 차트(월차매출, 매출누계, 이동년계): 계절 변동의 영향을 배제하고 트렌드 분석

시계열 차트

날짜별 매출과 평균 구매액을 집계
SELECT
    dt,
    COUNT(*) AS purchase_count
    SUM(purchase_amount) AS total_amount,
    AVG(purchase_amount) AS avg_amount
FROM purchase_log
GROUP BY dt
ORDER BY dt
;
날짜별 매출과 7일 이동평균 그래프

토요일, 일요일과 같은 주말에 매출이 크게 변동하는 서비스는 매출이 상승하는 경향이 있는지, 하락하는 경향이 있는지 판단하기 어려움. 이러한 경우 7일 동안의 평균 매출을 사용한 ‘7일 이동평균’으로 표현

SELECT
    dt,
    SUM(purchase_amount) AS total_amount,

    -- 최근 7일 동안의 평균 계산하기
    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 BETWEENT 6 PRECEDING AND CURRENT ROW)
    END
    AS seven_day_avg_strict
FROM purchase_log
GROUP BY dt
ORDER BY dt
;
날짜별 예약 수와 7일 이동평균 그래프-1

Image-2

날짜별 예약 수와 7일 이동평균 그래프-2

Image-1

전주 대비 증감률

Image-1

Image-2