0. 개요
기존에 시계열분석이라 함은, 학부, AI 과정에서는 회귀 모델/딥러닝을 활용한 요인분석, 계수 추정, 예측값 계산이라는 틀 내에서 이루어졌는데, SQL 실무 도서인 "데이터 분석을 위한 SQL 레시피" 4장, 매출 파악을 위한 데이터 추출 파트를 공부하며 "시계열 데이터를 어떻게 잘 집계하여 쉽게 활용할 것인가?" 에 대해서 고민해볼 수 있어서 좋았다.
<다루는 내용>
시계열 집계 / Z차트(매출 추이 분석) / ABC 분석 & 펜차트(상품 카테고리별 매출 분석) / 히스토그램
매출분석을 위해 매출금액, 사용자수, 페이지뷰 등 다양한 시계열 데이터를 집계하는 경우가 많다. 이는 웹사이트 또는 서비스 현 상황을 파악하는데 굉장히 중요하고 유의미한 수치 정보를 제공한다.
특히, 이번에 정리하는 분석법들은, 단순히 매출을 꺾은선 그래프로 나타냈을때는 찾기 힘든, 시계열 추이 변화를 명확하게 확인할 수 있는 분석법들이라고 소개하고 있고, 실제로 봤을때도 굉장히 좋은 방법이라는 생각이 들어, 이러한 분석 방법을차후에 다른 분석 툴을 그대로 적용해도 좋을 것 같다는 생각이 든다.
(++ 책에서는 SQL을 통한 쿼리 작성 뿐 아니라, 이 데이터를 가지고 tableau로 시각화 한 모습도 보여주고 있다. 따라서 나도 가능하면 sql 문법 학습 이후에 데이터를 활용해 BI 툴로 직접 시각화해보고자 한다..)
1. 시계열 집계
(1) 매출 집계
매출 집계는 간단하게 , 일별 매출과, 일별 평균 구매액을 계산하는 쿼리문이다. 이를 활용하여 간단하게 일별 매출 추이를 그래프화 할 수 있다.
아래는 예제에서 사용된 로그데이터로 상위 10개만을 추출하였다.
select * from purchase_log limit 10;
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 ;
(2) 이동평균 추이
이동평균값은, 매출이 주기적으로 등락이 있는 경우에도 유언하게 추세를 반영하여 보여준다. 예를들어 매출이 주말 2일간이 평일 5일보다 매출이 압도적으로 높은 경우, 등락 패턴이 유사하게 나타나 매출이 증가하고있는지 시각적으로 구분하기 여려울 수 있다.
이때 '7일 이동평균'을 기준으로 비교한다면, 어느 날이든, 그 기준일로부터 최근 7일간, (6일 전 - 오늘)의 매출 평균값을 계산하게 되어 추세를 구분하기 용이해진다..
아래는 크게 두가지 케이스를 모두 고려한다. 참고할 만한 6일 전까지의 데이터가 없더라도, 확보가능한 날의 매출 평균값을 계산해 모든 날의 이동평균을 계산할 수 있고, 좀 더 명확하게, 기준일로부터 7일 이동평균을 계산할 수 있는 경우에만 월별 매출액을 계산하는 방식도 있다.
1. 최근 7일간 평균 일별 매출액 (seven_day_avg)
-> over(order by dt between 6 preceding and current row) : 기준열과 이전 6개 열 값을 가져와 평균
2. 명확한 최근 7일간 평균 월별 매출액 (seven_day_avg_strict)
-> case when 7 = count(*) over(~) 를 활용해 최근 7일 값이 모두 존재하는 경우만 평균, 이 외는 null 처리
select * from purchase_log;
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 purchase_log
group by dt
order by dt ;
(3) 작년 동월 대비 매출액 비 (작대비)
다음으로, 작년 대비 동월 매출액 비율을 계산하는 쿼리이다. 2014년, 2015년 구매 로그 데이터가 주어졌을때, with 문으로 substring을 활용하여 dt를 년/월/일 형식으로 분리, 월별 누적 매출액을 계산한 daily_purchase 테이블을 생성한 이후, 이를 활용하여 2014년 대비 2015년 매출액 비를 계산하는 컬럼 rate를 계산한다.
여기서 with 절이 처음으로 나오는데 이는 with 문에서 자체적으로 subquery를 직접 정의한 이후 이를 곧바로 활용할 수 있게 해주는 구문이다.
with
daily_purchase as (
select
dt,
substring(dt, 1, 4) as year,
substring(dt, 6, 2) as month,
substring(dt, 9, 2) as date,
sum(purchase_amount) as purchase_amount,
count(order_id) as orders
from 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 * 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
;
2. Z 차트 분석
Z차트는 월차매출, 매출누계, 이동년계 3가지 지표를 한눈에 확인할 수 있는 그래프이다. 3개의 지표는 아래와 같은 의미를 갖는다.
- 월차매출 : 월별 매출 합계 - 월별 매출액 추이를 한눈에 살펴봄
- 매출누계 : 해당 월까지의 매출 누적값(1월 > 1월 매출, 2월 > 1월 매출 + 2월 매출, ..)
- 이동년계 : 해당 월로부터 11개월 이전까지의 누적값 (= 12개월 이동평균)
아래는 실제 월별 매출이 감소하고 있는 한 서비스의 z차트라고 하자.
이러한 세 가지 지표를 직접 해석해보며 이 그래프가 왜 필요한지 알아보자.
- 월별 매출의 하향, 매출 누계가 위로 굽은 형태를 보임을 봤을 때, 22년 기준 월 매출은 꾸준히 햐향하고 있다.
- 이동합계 역시 4.5억에서 2억까지 크게 감소, 즉, 작년 대비 매출액이 2배 이상 크게 감소하 있다.
즉, Z 차트 하나만으로, 월 매출 추이, 총 매출액 추이, 작년 대비 매출 감소율을 한번에 확인할 수 있다.
(1) Z 차트 쿼리 작성
제공 데이터는 2014년 2월 부터 2015년 12월 까지의 구매 로그 데이터를 바탕으로, 15년도의 월별 매출(amount), 매출 누계(agg_amount), 이동합계(year_avg_amount) 를 계산한다.
쿼리 작성 단계는 총 3단계로,
(1) 구매 로그 데이터로 일별 누적매출 계산 + 누적 계산을 위해 년/월/일 컬럼 분리 서브쿼리 작성 (daily_purchase)
(2) 로그 데이터를 바탕으로 월별 누적 매출계산 서브쿼리 작성 (monthly_amount)
(3) 14.02-15.12 데이터를 바탕으로 15년도 매출 누계, 이동합계 컬럼 계산 (calc_index)
계산이 잘 되었다면, 15년 Z 형태로, 15.01 월 매출과 매출 누계, 15.12 매출누계와 이동합계가 아래와 같이 동일한 값을 가지게 된다.
(+) window 절 OVER ~ ROWS, RANGE 사용 시 BETWEEN ~ AND CUREENT ROW <- 구문은 생략 가능.
with
daily_purchase as (
select
dt,
substring(dt, 1, 4) as year,
substring(dt, 6, 2) as month,
substring(dt, 9, 2) as date,
sum(purchase_amount) as purchase_amount
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,
-- 15년 매출 누계(누적) 계산
sum(case when year = '2015' then amount end)
over(order by year, month rows unbounded preceding)
as agg_amount,
-- 당월과 11개월 이전까지 12개월 이전까지의 매출합계(이동년계)
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
)
-- 15년 데이터만 출력
select
concat(year, '-', month) as year_month_,
amount,
agg_amount,
year_avg_amount
from
calc_index
where
year = '2015'
order by
year_month_ ;
3. ABC 분석
ABC분석은, 매출 비중에 따른 카테고리별 매출등급을 A, B, C로 구간화하는 것으로, 아래와 같은 3가지 기준을 가진다.
- A 등급 : 상위 0~70% (인기 상품군)
- B 등급 : 상위 70~90%
- C 등급 : 상위 90~100% (비인기 상품군)
이를 파악하면 서비스 내에서 중요 상품군에 대해 상품별 매출 증요도를 파악하고 그에 맞는 전략을 세울 수 있다. 예를들자면 재고관리라던지 품목 배치에 활용할 수도 있다. 제작 단계도 간단하게 파악 가능하다.
- 카테고리별 매출 합계 집계
- 매출이 높은 순서대로 카테고리 정렬
- 각 카테고리별 판매비중을 계산하고 이를 누적한 구성비누계를 계산 (차후 등급 분류에 용이)
이때, 등급 분류 기준은 사용 목적에 따라 달라질 수 있으므로, SQL로는 누적 매출비(구성비누계) 까지만 계산하고, 등급 분류는 보고서 단계에서 이에맞는 리포트 툴 선에서 계산하는것이 더 효율적이다.
그러나 예제에서는 ranking 구간화 단계까지 구현한 것을 보면, 아마 집계/정렬/누적/구간화 까지 모두 연습하기 위해 이런식으로 구성해 둔 듯 하다.
(+) sum 등의 계산함수를 쓰기위해 group by 가 필수적이므로 아래와 같이 over() 문을 꼭 작성해야됨.
(+) between 0 and 70 은 0 이상 70이하 이므로 이론상 누계함수가 딱 70이라면 이는 A,B 두 개 등급에 모두 포함된다. 이를 피하려면 WHEN cumulative_ratio >= 0 AND cumulative_ratio < 70 THEN 'A' 와 같이 작성하자
with
monthly_sales as (
select
category,
sum(price) as amount
from purchase_detail_log
group by
category
)
, sales_composition_ratio as(
select
category,
amount,
-- 구성비
100.0 * amount / sum(amount) over() as composition_ratio,
-- 구성비누계
100.0 * sum(amount) over(order by amount desc
rows between unbounded preceding and current row)
/ sum(amount) over() as cumulative_ratio
from
monthly_sales
)
select * from sales_composition_ratio;
select
* ,
-- rank A,B,C 매기기
case
when cumulative_ratio between 0 and 70 then 'A'
when cumulative_ratio between 70 and 90 then 'B'
when cumulative_ratio between 90 and 100 then 'C'
end as abc_rank
from
sales_composition_ratio
order by
amount desc
;
3. 펜 차트
.펜차트는 특정 비교 시점의 수치(카테고리별 판매액, 판매량 등)를 100%로 두고, 이후 시간에 따른 변동을 백분율로 표시하는 방법이다. 단순 카테고리별 매출 추이를 비교하는 방식보다 훨씬 시각적으로 비교가 쉽고 좋은 방법이라 생각하여 정리하게 되었다.
- 이점 : 제품 카테고리별 추세 비교가 쉬워짐. (시작점 동일, 비율을 기준으로 하므로 변동 폭 비교 용이)
- 단점 : 매출 규모와 전반적인 판매 추세 파악의 어려움. 비율 계산으로 인한 모수 정보 손실
다만, 예제 퍄일이 수정된 것인지, 책과 동일한 데이터셋이 없어 SQL 쿼리만 작성하자면 아래와 같다.
이때, 새롭게 등장하는 first value 함수에 대해서 주의깊게 볼 필요가 있는데, 이는 각 카테고리별로, 시작 시점값을 기준으로 100% 비율을 계산하므로 계산 편의를 위해 기준이 되는 시작 시점값을 갖는 컬럼이다.
with
daily_category_amount as (
select
dt,
category,
substring(dt, 1, 4) as year,
substring(dt, 6, 2) as month,
substring(dt, 9, 2) as date,
sum(price) as amount
from purchase_detail_log
group by
year, month, category
)
, monthly_category_amount as (
select
concat(year, '-', month) as year_month_,
category,
sum(amount) as amount
from
daily_category_amount
group by
year, month, category
)
select
year_month,
category,
amount,
-- 기준이 되는 시작 시점의 매출액
first_value(amount)
over(partition by category order by year_month_, categroy rows unbounded preceding)
as base_amount,
-- 비율 변화 계산
100.0 * amount / first_value(amount)
over(partition by category order by year_month_, categroy rows unbounded preceding)
as rate
from
monthly_category_amount
order by
year_month_, category
;
'My SQL' 카테고리의 다른 글
| [MySQL] 데이터 분석을 위한 SQL 레시피 - part5 유저 액션 /벤다이어그램/지속률 및 정착률/RFM (0) | 2024.06.21 |
|---|---|
| [데이터 분석을 위한 SQL 레시피] 데이터 분석 목적과 분석 방향 정리 (1) | 2024.06.18 |
| My SQL "데이터 분석을 위한 SQL 레시피" - part3 테이블 구조 변경, 다중 테이블 조작 (0) | 2024.03.22 |
| My SQL "데이터 분석을 위한 SQL 레시피" - part2 연산 및 그룹화 (0) | 2024.03.11 |
| MySQL "데이터 분석을 위한 SQL 레시피" - part1 값 조작하기 (0) | 2024.02.27 |