[데이터 분석을 위한 SQL 레시피] 9강 - 시계열 기반으로 데이터 집계하기

목차

1) 날짜별 매출 집계하기

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

3) 당월 매출 누계 구하기

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

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

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

 

1) 날짜별 매출 집계하기

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

날짜별 매출과 평균 구매액 등을 집계할 땐, GROUP BY 날짜 해주면 됨

 

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

토욜이나 일욜과 같은 주말에 매출이 크게 변동하는 서비스의 매출 리포트로는 매출이 상승하는 경향이 있는지, 하락하는 경향이 있는지 판단하기 어려움.

이러한 경우, 7일 동안의 평균 매출을 사용한 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일분의 데이터를 추출할 수 없는 첫번쨰 6일간에 대해 해당 6일만을 가지고 평균을 구하고 있음

--최근 7일 동안의 평균을 확실하게 계산하기   # 7일의 데이터가 모두 있는 경우에만 7일 이동평균을 구하고자 한다면 seven_day_avg_strinct 사용.
, 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) 당월 매출 누계 구하기

월별로 목표 설정하는 현장에서는, 날짜별로 매출뿐만 아니라, 해당 월에 어느 정도의 매출이 누적되었는지를 동시에 확인할 수 있어야함.

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

SELECT
dt
--'연-월' 추출하기
,substring(dt,1,7) AS year_month
,SUM(purchase_amount) AS total_amount
,SUM(SUM(purchase_amount))  #GROUP BY dt로 날짜별로 집계한 합계금액에 SUM 윈도함수 적용
OVER(PARTITION BY substring(dt,1,7) ORDER BY dt ROWS UNBOUNDED PRECEDING) AS agg_amount #매월 누계 구하기 위해 over 구에 partition by 추가해 월별로 파티션 생성.
FROM
purchase_log
GROUP BY dt
ORDER BY dt
;

 

** substring(컬럼명, 시작인덱스(1부터), 시작인덱스로부터 몇개뽑을지) = substring(string, start, length)

반복해서 나오는 SUM과 substring을 WITH구문으로 외부로 빼서 해보기

#9-4, 9-5 날짜별 매출을 일시 테이블로 만들고, 그 테이블에 대해 당월 누계 매출을 집계하는 쿼리

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
	dt
	,concat(year,'-',month) AS year_month
	,purchase_amount
	, SUM(purchase_amount)  #9-3에서처럼 sum 두번 된거임. 위에 with 구문안에 sum 한채로 purchase_amount로 저장해놨으니까
			OVER(PARTITION BY year,month ORDER BY dt ROWS UNBOUNDED PRECEDING) AS agg_amount
FROM daily_purchase
ORDER BY dt
;

 

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

월별 매출 추이를 추출해서 작년의 해당 월의 매출과 비교

#9-6 월별 매출과 작대비를 계산하는 쿼리

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 #dt 별로 purchase amount더하고, month 별로 한번 더 더함.
	,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)  # 2015년의 월별 매출을 2014년의 월별 매출로 나누어서 비율(작대비)를 계산.
		AS rate
FROM
	daily_purchase
GROUP BY month
ORDER BY month
;

 

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

월차매출, 매출누계, 이동년계 라는 3개의 지표로 구성된 Z차트.

월차매출 = 매출 합계를 월별로 집계

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

이동년계 = 해당 월의 매출에 과거 11개월의 매출을 합한 값 (ex. 2016년 1월의 이동년계 → 2015년 2월 ~ 2016년 1월)

 

• Z차트를 분석할 때의 정리
  • 매출누계에서 주목할 점

월차매출이 일정할 경우 매출누계는 직선이 됨.

가로축에서 오른쪽으로 갈수록 그래프의 기울기가 급해지는 곡선(아래로 봉긋) → 최근 매출이 상승하고 있음 

완만해지는 곡선(위로 봉긋) → 최근 매출이 감소하고 있음

• • 이동년계에서 주목할 점

작년과 올해의 매출이 일정하다면 이동년계가 직선이 됨.

오른쪽 위로 올라간다면 매출이 오르는 경향이 있다는 뜻

오른쪽 아래로 내려간다면 매출이 감소하는 경향이 있다는 뜻.

 

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

# 9-7 2015 매출에 대한 Z차트를 작성하는 쿼리

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
)
, 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 PREDEDING 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
;

** rows unbounded preceding = rows between unbounded preceding and current row

: between 안쓰면 자동으로 현재 행 기준으로 계산하기 때문에 이 둘은 같은 기능

 

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

매출 집계만으로는 매출의 상승과 하락밖에 알 수 없음. 매출의 상승/하락에 관한 본질적인 이유를 알 수 없음.

결과의 원인이라 할 수 있는 구매 횟수, 구매 단가 등의 주변 데이터를 고려해야 ‘왜’라는 이유를 알 수 있음.

→ 주변 데이터 함께 포함하여 리포트 만들기

ex. 판매 횟수에 변화가 있다면 방문 횟수, 상품 수, 회원 등록 수를 확인해서 판매 횟수의 상승과 하강 이유 찾을 수 있을 것

# 9-8 매출과 관련된 지표를 집계하는 쿼리

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
)
, 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)
		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
;