[SQL 레시피] 시계열 기반으로 데이터 집계하기

9강. 시계열 기반으로 데이터 집계하기

분석 현장에서는 시계열로 매출 금액, 사용자 수, 페이지 뷰 등의 다양한 데이터를 집계하는 경우가 많음. 변화를 이해하기 쉽게 표현할 수 있는 리포팅 방법을 소개한다.

1. 날짜별 매출 집계하기

• dt별 count, sum, avg 확인

2. 이동평균을 사용한 날짜별 추이 보기

• 매출이 주기적으로 높아지는 날이 존재, 매출이 상승 경향이 있는지 하락 경향이 있는지 살펴보기 위해서 이동 평균을 사용함.
• 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일 동안의 평균을 확실하게 계산하기 (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
from sqlrecipe.purchase_log
group by dt
order by dt
limit 100;

💡 날짜별로 추이와 이동평균을 함께 표현하여 리포트를 만든다.

 

3. 당월 매출 누계 구하기

해당 월에 어느 정도의 매출이 누적되었는지를 동시에 확인

• 날짜별 매출과 당월 누계 매출을 집계하는 쿼리

select dt
      , substr(dt, 1, 7) AS year_month
      , sum(purchase_amount) AS total_amount
      , sum(sum(purchase_amount)) over(partition by substr(dt, 1, 7) order by dt rows unbounded preceding) AS agg_amount
from sqlrecipe.purchase_log
group by dt
order by dt
limit 100;

• 가독성을 위해 WITH구문을 사용하여 날짜별 매출을 일시 테이블로 만든다.

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 sqlrecipe.purchase_log
  group by dt
)
select dt
      , concat(year, '-', month) AS year_month
      , purchase_amount
      , sum(purchase_amount) over(partition by concat(year, '-', month) order by dt rows unbounded preceding) AS agg_amount
from daily_purchase
order by dt
limit 100;

 

💡 빅데이터 분석 SQL은 성능이 조금 떨어지더라도 가독성과 재사용성을 중시해서 작성함.

 

4. 월별 매출의 작대비 구하기

• 작년의 해당 월의 매출과 비교하는 등

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 sqlrecipe.purchase_log
  group by dt
)
select month
      , sum(case year when '2014' then purchase_amount end) AS amount_2014
      , sum(case year when '2015' then purchase_amount end) AS amount_2015
      , 100.0 * sum(case year when '2014' then purchase_amount end) / sum(case year when '2015' then purchase_amount end) AS rate
from daily_purchase
group by month
order by month;

 

💡 매출이 늘어났다고 해도 계절의 영향을 받아 늘었는지, 이벤트 등으로 인해 늘었는지 팡단할 수 없다. 이렇게 작년과 비교했을 때 계절 트렌드로 매출이 늘어난 경우라도 전년대비 떨어졌다면 성장이 둔화했다고 판단할 수 있다.

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

• 계절에 따라 매출이 변동하는 경우가 있음
• 월차매출, 매출누계, 이동년계 3개의 지표로 계절 변동의 영향을 배제하고 트렌드를 분석
• 월차매출
  • 매출 합계를 월별로 집계
• 매출누계
  • 해당 월의 매출에 이전월까지의 매출 누계를 합한 값
  • 월차매출이 일정할 경우 매출누계는 직선, 오른쪽으로 갈수록 그래프의 기울기가 급해지면 최근 매출이 상승, 완만해지면 최근 매출이 감소 (기울기 = 매출 상승폭)
• 이동년계
  • 해당 월의 매출에 과거 11개월의 매출을 합한 값
  • 작년과 올해의 매출이 일정하다면 이동년계는 직선, 오른쪽 위로 올라간다면 매출이 오르는 경향, 오른쪽 아래로 내려간다면 매출이 감소하는 경향
• Z차트를 작성하기 위한 SQL 집계
  • 구매 로그를 기반으로 월별 매출을 집계 후 계산

  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 sqlrecipe.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 year_month
        , amount
        , agg_amount
        , year_avg_amount
  from calc_index
  where year = '2015'
  order by year_month
  limit 100;
  

6. 매출을 파악할 때 중요 포인트

• 매출 상승/하락 이라는 결과의 원인이라 할 수 있는 구매 횟수, 구매 단가 등의 주변 데이터를 고려해야 ‘왜’라는 이유를 알 수 있음
• 매출 = 판매횟수 * 평균 구매액
• 판매 횟수에 변화가 있다면 방문 횟수, 상품 수, 회원 등록 수를 확인해서 판매 횟수의 상승과 하강 이유를 찾을 수 있음
• 평균 구매액에 변화가 있다면 기간 내에 판매된 상품의 내역을 확인해서 수치에 대한 근거를 파악할 수 있음

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 sqlrecipe.purchase_log
  group by dt
)
, monthly_purchase AS (
  -- 월별 매출 집계
  select year
        , month
        , sum(orders) AS orders
        , avg(purchase_amount) AS avg_amount
        , sum(purchase_amount) AS monthly
  from daily_purchase
  group by year, month
)
select concat(year, '-', month) AS year_month
      , orders
      , avg_amount
      , monthly
      , sum(monthly) over(partition by year order by month rows unbounded preceding)
      -- 12개월 전의 매출 구하기
      , lag(monthly, 12) over(order by year, month) AS last_year
      -- 12개월 전의 매출과 비교해서 비율 구하기
      , 100.0 * monthly / lag(monthly, 12) over(order by year, month) AS rate
from monthly_purchase
order by year_month
limit 100;

• 윈도함수를 사용하기 위해 WITH구문을 사용해서 일시 테이블을 만들며, 빅쿼리 등으로 데이터를 읽어 들일 때 발생되는 과금을 줄일수도 있다. 불필요한 데이터 읽어 들임을 줄여야 한다.