데이터 분석을 위한 SQL 레시피
4장 9강: 시계열 기반으로 데이터 집계하기
이 글은 위의 교재를 참고하여 빅쿼리를 이용해서 학습한 내용입니다.
매출을 단순하게 수치로만 확인했을 때 단점
-> 장기적인 관점에서 어떤 경향이 있는지 알 수 없다.
하지만!
시계열로 매출 금액을 집계한다면?
1. 규칙성을 찾을 수 있다.
2. 어떤 기간과 비교했을 때 변화폭을 확인할 수 있다.
1. 날짜별 매출 집계하기
-- 코드 9-1: 날짜별 매출과 평균 구매액을 집계하는 쿼리 --
SELECT
dt,
count(*) AS purchase_count,
SUM(purchase_amount) AS total_amount,
AVG (purchase_amount) AS avg_amount
FROM ch04.purchase_log
GROUP BY dt
ORDER BY dt
;
2. 이동평균을 사용한 날짜별 추이 보기
책에서 제공하는 데이터는 주말에 매출이 크게 변동하고 있다.
주기적으로 매출이 높아지는 데이터라면 이동평균을 사용하여 매출의 상승/하락 경향을 판단한다.
*이동평균 = 지난 얼마간의 합계 / 이동평균에서 사용된 기간 수
찾아보니 이동평균도 가중치를 두는 법에 따라 종류가 다양한 것 같았다. 여기서는 단순이동평균을 사용했다.
일정한 크기를 정해 이동하며 평균을 계산하여 평균값의 흐름을 알 수 있다.
이번에는 7일 동안의 이동평균을 구해볼 것이다.
-- 코드 9-2: 날짜별 매출과 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 ch04.purchase_log
GROUP BY dt
ORDER BY dt
;
<윈도 프레임 지정>
: 6행 앞에서부터 현재행까지
윈도 프레임을 지정해서 7일 동안의 평균을 계산했다.
- seven_day_avg는 앞에 행이 6개보다 적어도, 즉 7일의 데이터가 없는 경우에도 평균을 집계한다.
- seven_day_avg_strict는 CASE문을 사용해서 6행 앞에서부터 현재행까지 행의 수를 세어 7개,
즉 7일의 데이터가 있는 경우에만 계산을 하여 7일 이동평균을 확실하게 계산한다.
결과로 확인해보면 이해하기 쉽다.
seven_day_avg의 값들을 보면 1행의 1월 1일 총 매출과 seven_day_avg 값이 같다.
2행은 1일과 2일의 총 매출을 더하여 평균을 낸 값이다. (24516 + 36049) / 2 = 30282.5
이렇게 6행 앞에서부터 현재행까지, 7일의 데이터가 없다면 있는 데이터 만큼의 평균을 구해준다.
seven_day_avg_strict는 6행까지 데이터가 null값이다.
CASE문으로 7일의 데이터가 있어야 평균을 계산하도록 했기 때문이다.
이동평균만으로는 날짜별 변동을 파악하기 힘들다.
-> 날짜별로 추이와 이동평균을 함께 표현한다.
3. 당월 매출 누계 구하기
날짜별로 매출뿐만 아니라 당월 매출 누계를 표현할 때 윈도 함수를 사용해야한다.
-- 코드 9-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
ch04.purchase_log
GROUP BY dt
ORDER BY dt
;
- substr(value, position, length)
substr함수는 value에서 position부터 length개의 문자를 추출한다.
position은 추출을 시작할 위치를 정하며, value의 제일 첫 번째 문자가 1로 시작한다. 마지막 문자는 -1로 지정할 수도 있다.
BigQuery에서 substring은 substr의 별칭으로 둘 다 사용가능하다.
위의 쿼리에서 substr 함수로 날짜(dt)에서 연과 월을 추출했다. (year_month)
dt가 'yyyy-dd-mm'이므로 연과 월부분까지 추출하려면 1부터 7까지 인덱스를 지정하면 된다. (yyyy-dd)
GROUP BY dt와 ORDER BY dt로 날짜별로 매출합을 구해 날짜 순서대로 정렬했다.
agg_amount는 매월 누계합을 계산한 것이다.
|
SUM(SUM(purchase_amount))
OVER(PARTITION BY substr(dt,1,7) ORDER BY dt ROWS UNBOUNDED PRECEDING)
AS agg_amount
|
-> '매월' 누계를 구하기 위해서 over구분에 partition by(dt,1,7)와 order by dt를 조합하여 월별로, 날짜 순서대로 계산하게 하고, rows unbounded preceding로 윈도우 프레임을 지정해서 해당 월 내에서 이전 행을 모두 더하여 매월 누계를 구하게 된다.
위의 코드는 가독성의 문제가 약간 있다. 반복해서 나오는 구문들 때문이다.
이를 WITH 구문을 이용해서 알아보기 쉬운 이름을 붙여 불러올 수 있다.
with구문을 이용해서 날짜별 매출을 일시 테이블로 만드는 쿼리를 작성하였다.
-- 코드 9-4: 날짜별 매출을 일시 테이블로 만드는 쿼리 --
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
FROM ch04.purchase_log
GROUP BY dt
)
SELECT *
FROM daily_purchase
ORDER BY dt
;
with로 반복되는 구문들을 묶어 별칭이 있는 서브쿼리처럼 만들 수 있었다.
날짜와 년, 월, 일을 나누고 날짜별로 합계금액을 집계하여 dail_purchase라는 임시 테이블을 만들었다.
이 임시 테이블에서 원하는 칼럼을 불러올 수 있다.
이렇게 with를 사용한다면 구문을 반복해서 불러오지 않아도 되고 정해둔 별칭으로 계속 불러와 쓸 수 있다!
이제 이 with구문으로 만든 daily_purchase 테이블을 이용해서 여러 쿼리를 만들어 볼 것이다.
-- 코드 9-5: 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
FROM ch04.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
;
위의 코드 9-4에서 만든 daily_purchase를 이용해서 당월 누계 매출을 집계했다.
year과 month를 불러와서 문자열을 합치는 concat로 년도와 월을 이었고, 날짜별 매출합을 구했던 purchase_amount를 윈도함수 sum에 불러와서 년도, 월별 이전행까지의 합을 구하고 날짜순으로 정렬했다. 즉 당월 누계 매출을 구한 것이다.
4. 월별 매출의 작대비 구하기
Join을 사용하지 않고 작대비를 계산하는 방법
-- 코드 9-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
FROM ch04.purchase_log2
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
;
월별로 그룹화한 뒤 sum 내부에 case를 이용해서 2014년, 2015년의 월별 매출 합을 구했다.
2015년 월별 매출을 2014년과 비교하기 위해 당월 매출 합에서 작년 매출 합을 나누어 비율을 구한다. (작대비)
5. Z차트로 업적의 추이 확인하기
Z차트: 월차매출, 매출누계, 이동년계라는 3가지 지표로 구성된 차트
-> 계절의 변동의 영향을 배제하고 트렌드를 분석한다.
각 지표에 대해
- 월차매출: 매출 합계를 월별로 집계
ex)
| 2021년 1월 | 1월 매출합 |
| 2021년 2월 | 2월 매출합 |
| 2021년 3월 | 3월 매출합 |
- 매출누계: 해당 월의 매출에 이전월까지의 매출 누계를 합한 값
ex)
| 2021년 1월 | 1월 매출합 |
| 2021년 2월 | 1월+2월 매출합 |
| 2021년 3월 | 1월+2월+3월 매출합 |
* 월차매출이 일정하면 매출누계는 직선이 된다. 최근 매출이 상승한다면 우상향하고, 감소한다면 우하향한다.
-> 기울기에 따라 추이를 확인 가능
- 이동년계: 해당 월의 매출에 과거 11개월의 매출을 합한 값
ex)
| 2021년 2월 | 2021년 2월 ~ 2022년 1월 매출합 |
| 2021년 3월 | 2021년 3월 ~ 2022년 2월 매출합 |
| 2021년 4월 | 2021년 4월 ~ 2022년 3월 매출합 |
* 작년과 올해 매출이 일정하면 이동년계는 직선이다.
-> 과거 1년 동안 매출 추이 확인 가능
세 지표를 차트로 그려보면
이런 Z모양의 차트가 나온다!
다음은 2015년 매출에 대한 Z차트를 작성하는 쿼리다.
-- 9-7: 2015년 매출에 대한 Z차트를 작성하는 쿼리 --
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
FROM ch04.purchase_log2
GROUP BY dt
)
, monthly_purchase AS(
select
year,
month,
SUM(purchase_amount) AS amount
FROM daily_purchase
GROUP BY year, month
)
, calc_index AS(
SELECT
year,
month,
amount,
SUM(CASE WHEN year='2015' THEN amount END)
OVER(ORDER BY year, month ROWS UNBOUNDED PRECEDING)
AS agg_amount,
SUM(amount)
OVER(ORDER BY year, month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
AS year_avg_amount
FROM
monthly_purchase
ORDER BY
year, month
)
SELECT
concat(year, '-', month) AS year_month,
amount,
agg_amount,
year_avg_amount
FROM
calc_index
WHERE
year='2015'
ORDER BY year_month
;
with로 만든 purchase_log테이블을 이용해서 여러 임시 테이블을 만들 수 있었다.
with로 두 개 이상의 테이블도 만들 수 있다.
monthly_amount는 월별 매출을 집계, calc_index는 2015년 누계 매출과 이동년계를 집계했다.
먼저 calc_index의 모든 칼럼들을 불러와보았다.
2015년의 누계매출(agg_amount)만 구했기 때문에 2014년은 null값이다.
마지막으로 where절에 year='2015'로 지정하여 2015년 데이터만 압축하여 추출한 최종 쿼리 값이다.
