본문 바로가기
SQL

[BigQuery] 시계열 기반으로 데이터 집계하기

by 권미정 2022. 5. 6.

<데이터 분석을 위한 SQL 레시피> 4장 9강을 참고하여 실습한 내용입니다.

 

 

#시계열로 데이터를 집계하는 이유는?

 

시계열로 매출 금액, 사용자 수, 페이지 뷰 등의 다양한 데이터를 집계하는 것은 웹사이트 또는 서비스의 상태를 파악할 때 중요한 작업이다. 시계열로 매출 금액을 집계하면 어떤 규칙성을 찾거나, 어떤 기간과 비교하여 변화폭을 확인할 수 있다.

 

#샘플 데이터

2014년의 매출 데이터를 샘플로 사용할 것이다. 빅쿼리에 아래의 코드를 작성해 매출 테이블을 생성해보자.

DROP TABLE IF EXISTS expanded-lock-349311.lec9.purchase_log;
CREATE TABLE expanded-lock-349311.lec9.purchase_log(
    dt              string(255)
  , order_id        integer
  , user_id         string(255)
  , purchase_amount integer
);

INSERT INTO expanded-lock-349311.lec9.purchase_log
VALUES
    ('2014-01-01',  1, 'rhwpvvitou', 13900)
  , ('2014-01-01',  2, 'hqnwoamzic', 10616)
  , ('2014-01-02',  3, 'tzlmqryunr', 21156)
  , ('2014-01-02',  4, 'wkmqqwbyai', 14893)
  , ('2014-01-03',  5, 'ciecbedwbq', 13054)
  , ('2014-01-03',  6, 'svgnbqsagx', 24384)
  , ('2014-01-03',  7, 'dfgqftdocu', 15591)
  , ('2014-01-04',  8, 'sbgqlzkvyn',  3025)
  , ('2014-01-04',  9, 'lbedmngbol', 24215)
  , ('2014-01-04', 10, 'itlvssbsgx',  2059)
  , ('2014-01-05', 11, 'jqcmmguhik',  4235)
  , ('2014-01-05', 12, 'jgotcrfeyn', 28013)
  , ('2014-01-05', 13, 'pgeojzoshx', 16008)
  , ('2014-01-06', 14, 'msjberhxnx',  1980)
  , ('2014-01-06', 15, 'tlhbolohte', 23494)
  , ('2014-01-06', 16, 'gbchhkcotf',  3966)
  , ('2014-01-07', 17, 'zfmbpvpzvu', 28159)
  , ('2014-01-07', 18, 'yauwzpaxtx',  8715)
  , ('2014-01-07', 19, 'uyqboqfgex', 10805)
  , ('2014-01-08', 20, 'hiqdkrzcpq',  3462)
  , ('2014-01-08', 21, 'zosbvlylpv', 13999)
  , ('2014-01-08', 22, 'bwfbchzgnl',  2299)
  , ('2014-01-09', 23, 'zzgauelgrt', 16475)
  , ('2014-01-09', 24, 'qrzfcwecge',  6469)
  , ('2014-01-10', 25, 'njbpsrvvcq', 16584)
  , ('2014-01-10', 26, 'cyxfgumkst', 11339)
;

샘플데이터 purchase_log

 

1. 날짜별 매출 집계하기 COUNT, SUM, AVG, GROUP BY

 

매출을 집계하는 업무에서는 가로 축에 날짜, 세로 축에 금액을 표현하는 그래프를 사용한다.

COUNT 함수로 구매 횟수를 추출하고, SUM 함수로 날짜별 매출을 집계하고, AVG 함수로 평균 구매액을 집계하는 쿼리를 작성해보자.

 

SELECT
   dt
   ,COUNT(*) AS purchase_count
   ,SUM(purchase_amount) AS total_amount
   ,AVG(purchase_amount) AS avg_amount
FROM expanded-lock-349311.lec9.purchase_log
GROUP BY dt
ORDER BY dt
;

날짜별 매출과 평균 구매액을 집계하는 쿼리

이렇게 날짜별로 구매 횟수, 매출, 평균구매액이 집계되었다. 이 리포트의 3일, 5일, 7일의 매출을 보면 주기적으로 매출이 높아지는 날이 있음을 확인할 수 있다. 이처럼 보통 합계와 평균으로 추이를 확인하지만, 합계와 평균 대상이 없는 경우에는 위와 같이 COUNT 함수를 통해서도 추이를 확인할 수 있다.

 

2. 이동평균을 사용한 날짜별 추이 보기 OVER(ORDER BY~)

 

위의 날짜별 매출 리포트처럼 매출이 주기적으로 높아지는 날이 있는 경우도 있지만, 매출이 상승하는 경향이 있는지 하락하는 경향이 있는지 판단하기 어려운 경우도 있다. 이러한 경우에는 OVER(ORDER BY~) 구문을 사용해 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 BETWEEN 6 PRECEDING AND CURRENT ROW)
   END
   AS seven_day_avg_strict
FROM expanded-lock-349311.lec9.purchase_log
GROUP BY dt
ORDER BY dt
;

여기서 seven_day_avg는 과거 7일분의 데이터를 추출할 수 없는 첫 번째 6일간에 대해 해당 6일만을 가지고 평균을 구하는 것이다. 7일의 데이터가 모두 있는 경우에만 7일 이동평균을 구하려면 seven_day_avg_strinct를 사용하면된다.

날짜별 매출과 7일 이동평균을 집계하는 쿼리

 

3. 당월 매출 누계 구하기 OVER(PARTITION BY~ ORDER BY~)

 

이번에는 날짜별로 매출을 집계하고, 해당 월의 누계를 구하는 리포트를 만들어보자. 이를 위해서는 윈도 함수를 사용해야 한다.

 

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 expanded-lock-349311.lec9.purchase_log
GROUP BY dt
ORDER BY dt
;

이 쿼리에서는 날짜별 매출과 월별 누계 매출을 동시에 집계하기 위해 substr 함수를 사용해 날짜에서 '연과 월' 부분을 추출했다.

이어서 GROUP BY dt로 날짜별로 집계한 합계 금액인 SUM(purchase_amount)에 SUM 윈도 함수를 적용해서 SUM(SUM(purchase_amount)) OVER(ORDER BY dt)로 날짜 순서대로 누계 매출을 계산한다.

또 매월 누계를 구하기 위해 OVER 구에 PARTITION BY substr(dt,1,7)을 추가해 월별로 파티션을 생성했다.

 

 

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

 

여기서 좀 더 가독성 있게 수정하기 위해, 반복해서 나오는 SUM(purchase_amount)과 SUBSTR(dt,1,7)을 WITH 구문으로 외부로 빼고 알기 쉽게 이름을 붙여보자. 또 substr 함수로 연과 월을 추출했던 부분을 연, 월, 일 3개로 분할하고 결합해보자.

 

WITH
daily_purchase AS(
   SELECT
    dt
    --'연','월','일'을 각각 추출하기
    ,substr(dt,1,4) AS year
    ,substr(dt,6,2) AS month
    ,substr(dt,9,2) AS date
    ,SUM(purchase_amount) AS purchase_amount
    ,COUNT(order_id) AS orders
   FROM expanded-lock-349311.lec9.purchase_log
   GROUP BY dt
)
SELECT
  *
FROM
  daily_purchase
ORDER BY dt
;

 

이 일시 테이블을 daily_purchase라고 하자. 이번에는 daily_purchase 테이블을 사용해 당월 매출 누계를 집계하는 쿼리를 작성해보자.

 

WITH
daily_purchase AS(
   SELECT
    dt
    ,substr(dt,1,4) AS year
    ,substr(dt,6,2) AS month
    ,substr(dt,9,2) AS date
    ,SUM(purchase_amount) AS purchase_amount
    ,COUNT(order_id) AS orders
   FROM expanded-lock-349311.lec9.purchase_log
   GROUP BY dt
)
SELECT
   dt 
 ,concat(year,'-',month) AS year_month
 ,purchase_amount
 ,SUM(purchase_amount)
   OVER(PARTITION BY year, month ORDER BY dt ROWS UNBOUNDED PRECEDING)
  AS agg_amount
FROM daily_purchase
ORDER BY dt
;

daily_purchase 테이블에 대해 당월 누계 매출을 집계하는 쿼리

이렇게 일시 테이블을 만들고 그것을 사용해 당월 누계 매출을 집계하는 쿼리를 작성하면, 첫 번째 쿼리와 결과는 같지만 SELECT 구문 내부에 있는 콜롬의 의미를 쉽게 이해할 수 있다!

 

4. 월별 매출의 작대비 구하기 SUM(CASE~END)

 

이번에는 월별 매출 추이를 추출해서 작년의 해당 월의 매출과 비교해보고, 매출이 어느 정도 상승하거나 하락했는지 한눈에 확인할 수 있는 리포트를 만들어보자.

 

일단 2014년 1월 데이터만 있던 기존 매출 테이블을 삭제하고, 2014년과 2015년 데이터도 있는 매출 데이터를 생성하자.

DROP TABLE IF EXISTS expanded-lock-349311.lec9.purchase_log;
CREATE TABLE expanded-lock-349311.lec9.purchase_log(
    dt              string(255)
  , order_id        integer
  , user_id         string(255)
  , purchase_amount integer
);

INSERT INTO expanded-lock-349311.lec9.purchase_log
VALUES
    ('2014-01-01',    1, 'rhwpvvitou', 13900)
  , ('2014-02-08',   95, 'chtanrqtzj', 28469)
  , ('2014-03-09',  168, 'bcqgtwxdgq', 18899)
  , ('2014-04-11',  250, 'kdjyplrxtk', 12394)
  , ('2014-05-11',  325, 'pgnjnnapsc',  2282)
  , ('2014-06-12',  400, 'iztgctnnlh', 10180)
  , ('2014-07-11',  475, 'eucjmxvjkj',  4027)
  , ('2014-08-10',  550, 'fqwvlvndef',  6243)
  , ('2014-09-10',  625, 'mhwhxfxrxq',  3832)
  , ('2014-10-11',  700, 'wyrgiyvaia',  6716)
  , ('2014-11-10',  775, 'cwpdvmhhwh', 16444)
  , ('2014-12-10',  850, 'eqeaqvixkf', 29199)
  , ('2015-01-09',  925, 'efmclayfnr', 22111)
  , ('2015-02-10', 1000, 'qnebafrkco', 11965)
  , ('2015-03-12', 1075, 'gsvqniykgx', 20215)
  , ('2015-04-12', 1150, 'ayzvjvnocm', 11792)
  , ('2015-05-13', 1225, 'knhevkibbp', 18087)
  , ('2015-06-10', 1291, 'wxhxmzqxuw', 18859)
  , ('2015-07-10', 1366, 'krrcpumtzb', 14919)
  , ('2015-08-08', 1441, 'lpglkecvsl', 12906)
  , ('2015-09-07', 1516, 'mgtlsfgfbj',  5696)
  , ('2015-10-07', 1591, 'trgjscaajt', 13398)
  , ('2015-11-06', 1666, 'ccfbjyeqrb',  6213)
  , ('2015-12-05', 1741, 'onooskbtzp', 26024)
;

 

앞에서 만든 daily_purchase 테이블로 2015년 매출의 작대비를 계산하는 쿼리를 작성해보자.

WITH
daily_purchase AS(
   SELECT
    dt
    ,substr(dt,1,4) AS year
    ,substr(dt,6,2) AS month
    ,substr(dt,9,2) AS date
    ,SUM(purchase_amount) AS purchase_amount
    ,COUNT(order_id) AS orders
   FROM expanded-lock-349311.lec9.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 '2015' THEN purchase_amount END)
    /SUM(CASE year WHEN '2014' THEN purchase_amount END)
    AS rate
FROM
 daily_purchase
GROUP BY month 
ORDER BY month 
;

대상 데이터는 2014년과 2015년 데이터를 포함해 집계하고, 월마다 GROUP BY를 적용해 매출액을 계산한다.

매출액을 계산할 때 SUM 함수 내부에 CASE 식을 사용해 2014년과 2015년 로그를 각각 압축하면, 2014년과 2015년의 월별 매출을 각각 다른 콜롬으로 출력할 수 있다.

그리고 2015년의 월별 매출을 2014년의 월별 매출로 나누어서 비율을 계산한다.

 

월별 매출과 작대비를 계산하는 쿼리

5. Z 차트로 업적의 추이 확인하기 SUM(CASE~END) OVER(ORDER BY~)

 

Z차트는 '월차매출', '매출누계', '이동년계'라는 3개의 지표로 구성되어 계절 변동의 영향을 배제하고 트렌드를 분석하는 방법이다.

 

월차매출: 매출 합계를 월별로 집계한다.

매출누계: 해당 월의 매출에 이전월까지의 매출 누계를 합한 값이다.

이동년계: 해당 월의 매출에 과거 11개월의 매출을 합한 값이다.

 

#매출누계에서 주목할 점

월차매출이 일정할 경우 매출누계는 직선이 된다. 가로축에서 오른쪽으로 갈수록 그래프의 기울기가 급해지는 곡선은 최근 매출이 상승하고 있다는 의미이고, 완만해지는 곡선은 최근 매출이 감소하고 있다는 의미이다.

 

#이동년계에서 주목할 점

작년과 올해의 매출이 일정하면 이동년계는 직선이 된다. 오른쪽 위로 올라간다면 매출이 오르는 경향이 있다는 뜻이고, 오른쪽 아래로 내려가면 매출이 감소하는 경향이 있다는 뜻이다. 그래프에 표시되지 않은 과거 1년 동안 매출이 어떤 추이를 가지는지도 읽어낼 수 있다.

 

#다양한 형태의 Z차트

1번째 차트처럼 예쁜 Z 모양은 매출이 거의 일정한 상태이다.

2번째는 기간 말에 매출이 성장한 상태로, 월별매출, 매출누계, 이동년계 모두 오른쪽 위로 상승하고 있다.

3번째는 작년에 매출이 성장했지만, 올해 성장을 멈추고 2년 전과 같은 수준이 된 상태이다. 월별매출과 매출누계는 직선 형태를 나타내지만, 이동년계가 점점 내려가고 있다.

 

#Z차트를 작성하기 위한 지표 집계하기

 

Z차트에 필요한 지표는 월 단위로 집계하기 때문에, 구매 로그를 기반으로 월별 매출을 집계하고 각 월의 매출에 대해 누계매출과 이동년계를 계산한다. 이동년계를 계산하려면 특정 월의 1년치 매출 데이터가 필요하지만 그래프를 그릴 땐 필요하지 않으므로 없는 데이터는 신경 쓰지 않아도 된다.

 

WITH
daily_purchase AS(
   SELECT
    dt
    ,substr(dt,1,4) AS year
    ,substr(dt,6,2) AS month
    ,substr(dt,9,2) AS date
    ,SUM(purchase_amount) AS purchase_amount
    ,COUNT(order_id) AS orders
   FROM expanded-lock-349311.lec9.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
;

2015년의 누계 매출을 계산하기 위해, SUM 함수 내부에서 CASE 식을 사용해 2015년의 매출만 압축하고 SUM 윈도 함수를 사용해 누계를 계산했다.

이동년계를 계산하기 위해, SUM 윈도 함수를 사용했다. 당월을 포함한 과거 11개월의 매출 합계를 구해야 하기 때문에 ROWS BETWEEN 11 PRECEDING AND CURRENT ROW를 지정해서, 현재 행에서 11행 이전까지의 데이터 합계를 구했다.

2015년 매출에 대한 Z차트를 작성하는 쿼리

 

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

 

매출 집계만으로는 매출의 상승과 하락에 관한 이유를 알 수가 없다. 따라서 매출 리포트가 필요하다면 주변 데이터를 함께 포함해서 만드는 것이 좋다. 매출 변화의 '이유'를 안다면, 관련 데이터 분석을 통해 원인을 확인하고 개선할 수 있다.

 

매출과 관련된 지표를 집계하는 쿼리를 작성해보자.

WITH
daily_purchase AS(
   SELECT
    dt
    ,substr(dt,1,4) AS year
    ,substr(dt,6,2) AS month
    ,substr(dt,9,2) AS date
    ,SUM(purchase_amount) AS purchase_amount
    ,COUNT(order_id) AS orders
   FROM expanded-lock-349311.lec9.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 
  ,SUM(monthly)
    OVER(PARTITION BY year ORDER BY month ROWS UNBOUNDED PRECEDING)
  AS agg_amount
  --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
;

purchase_log 테이블을 기반으로 월 단위 매출을 정리한 monthly_purchase 테이블을 만들고, 해당 테이블에 윈도 함수를 적용했다.

작년의 같은 달 매출을 구할 때는 LAG 함수를 사용해 12개월 전의 매출을 추출했다.

이 SQL을 사용해서 데이터를 추출하면 현장의 요구에 신속하게 대응할 수 있고, 다방면으로 활용할 수 있다!

'SQL' 카테고리의 다른 글

[BigQuery] 웹사이트에서의 행동을 파악하는 데이터 추출하기  (0) 2022.05.20
[BigQuery] 다면적인 축을 사용해 데이터 집약하기  (0) 2022.05.06
[Oracle] WHERE 절을 이용한 조건 검색  (0) 2022.04.18
[Oracle] SELECT 명령문을 사용하여 다양한 방법으로 데이터 출력하기  (0) 2022.04.15
[Oracle] 테이블에 데이터를 잘못 삭제 또는 잘못 입력했을 때!  (0) 2022.04.14

관련글

댓글

티스토리툴바