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

목차

1. 당월 매출 누계 구하기

 

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

 

3. 카테고리별 매출과 소계 계산하기

 

4. ABC 분석으로 잘 팔리는 상품 판별하기

 

5. 팬 차트로 상품의 매출 증가율 확인하기

 

6. 데이터의 계층을 구하는 쿼리

 

실습에 필요한 테이블

purchase_log 테이블

purchase_detail_log 테이블

 

당월 매출 누계 구하기

• 월별로 목표로 설정하는 현장에서는 해당 월에 어느 정도의 매출이 누적되었는지를 확인할 수 있어야 한다
• 윈도 함수를 사용해서 표현한다
• 날짜별 매출과 월별 누계 매출을 동시에 집계하고자 substring 함수 사용한다
• 매월 누계를 구하기 위해 OVER 구에 PARTITION BY substiring을 사용해 월별로 파티션을 생성한다

SELECT 
	dt
	, substring(dt, 1, 7) AS year_month
	, SUM(purchase_amount) AS total_amount
	, SUM(SUM(purchase_amount))
	OVER(PARTITION BY substring(dt, 1, 7) ORDER BY dt ROWS UNBOUNDED PRECEDING) AS agg_amount
FROM 
	purchase_log
GROUP BY 
	dt
ORDER BY 
	dt;

 

 

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

• Z 차트는 ‘월차매출’ , ‘매출누계’, ‘이동년계’라는 3개의 지표로 구성되어 트렌드를 분석한다
• Z 차트 분석할 때의 정리
  • 매출누계에서 주목할 점
    • 월차매출이 일정할 경우 매출누계는 직선이 되고 가로축에서 오른쪽으로 갈수록 그래프 기울기가 급해지는 곡선이라면 최근 매출이 상승하고 있다는 의미이며, 완만해지는 곡선이면 최근 매출이 감소하고 있다는 의미이다
  • 이동년계에서 주목할 점
    • 작년과 올해의 매출이 일정하다면 이동년계는 직선이 된다. 오른쪽 위로 올라간다면 매출이 오르는 경향이 있다는 뜻이고, 오른쪽 아래로 내려간다면 매출이 감소하는 경향이 있다는 뜻이다. 그래프에 표시되지 않은 과거 1년동안 매출이 어떤 추이를 가지는지도 읽을 수 있다.
  • 매출을 파악할 때 중요한 점
    • 매출 집계만으로는 매출의 상승과 하락밖에 알 수 없다. 따라서 매출의 상승/하락에 관한 본질적인 이유를 알 수 없다. 매출이라는 결과의 원인이라 할 수 있는 구매 횟수, 구매 단가 등의 주변 데이터를 고려해야 이유를 알 수 있다. 따라서 매출 리포트가 필요하다면 주변 데이터를 함께 포함해서 리포트를 만드는 것이 좋다.
• 매출과 관련된 지표를 집계하는 쿼리
  • purchase_log 테이블 기반으로 월 단위 매출을 정리한 monthly_purchase 테이블을 만들고, 해당 테이블에 윈도 함수를 적용했다
  • 윈도 함수 없이 작성하려면, 각각의 지표마다 SELECT 구문을 만들고 최종적으로 하나의 테이블과 결합해야 한다.
  • 이는 데이터를 여러 번 읽어야 하므로 성능적으로 매우 떨어진다.

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)
	-- 12개월 전의 매출과 비교해서 비율 구하기
	, 100.0 * monthly / LAG(monthly, 12) OVER(ORDER BY year, month) AS rate
FROM 
	monthly_purchase
ORDER BY 
	year_month;

 

 

카테고리별 매출과 소계 계산하기

• ROLLUP을 사용 시 레코드 집계 키가 NULL이 되므로, COALESCE 함수로 NULL을 all로 변환해준다.
• 꼭 all이 아니어도 다른 문자로 변환해도 상관없다(Ex. 0)

SELECT 
	COALESCE(category, 'all') AS category
	, COALESCE(sub_category, 'all') AS sub_category
	, SUM(price) AS amount
FROM 
	purchase_detail_log
GROUP BY
	ROLLUP(category, sub_category);

 

 

ABC 분석으로 잘 팔리는 상품 판별하기

ABC 분석

• ABC 분석은 재고 관리 등에서 사용하는 분석 방법이고 매출 중요도에 따라 상품을 나눈다.
• A등급 : 상위 0 ~ 70%
• B등급 : 상위 70 ~ 90%
• C등급 : 상위 90 ~ 100%
• 데이터 작성 방법
  1. 매출이 높은 순서로 데이터를 정렬한다
  2. 매출 합계를 집계한다
  3. 매출 합계를 기반으로 각 데이터가 차지하는 비율을 계산하고, 구성비를 구한다.
  4. 계산한 카테고리의 구성비를 기반으로 구성비누계를 구한다.

WITH 
monthly_sales AS (
	SELECT
		category
			-- 항목별 매출 계산하기
		, SUM(price) AS amount
	FROM
		purchase_detail_log
	-- 대상 1개월 동안의 로그를 조건으로 걸기
	WHERE 
		dt BETWEEN '2015-12-01' AND '2015-12-31'
	GROUP BY 
		category
)
, sales_composition_ratio AS (
	SELECT
		category
		, amount
		-- 구성비: 100. 0 * <항목별 매출> / <전체 매출>
		, 100.0 * amount / SUM(amount) OVER() AS composition_ratio
		-- 구성비누계 : 100.0 * <항목별 구계 매출> / <전체 매출>
		, 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 *
	-- 구성비누계 범위에 따라 순위 붙이기
	, 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;

 

 

팬 차트로 상품의 매출 증가율 확인하기

팬 차트 예시

• 팬 차트는 어떤 기준 시점을 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 
	dt, 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, category ROWS UNBOUNDED PRECEDING) AS base_amount
	, 100.0 * amount / FIRST_VALUE(amount)
	OVER(PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING) AS rate
FROM 
	monthly_category_amount
ORDER BY 
	year_month, category;

 

 

데이터의 계층을 구하는 쿼리

• 최소 금액에서 최대 금액의 범위를 계층으로 분할 시, 매출 금액에서 최소 금액을 뺀 뒤 계층을 판정하기 위한 정규화 금액(diff)를 계산해야 한다.
• 첫 번째 계층의 범위(bucket_range)는 금액 범위를 계급 수로 나누어 구할 수 있다.
• 정규화 금액을 계급 범위로 나누고 FLOOR 함수를 이용해 소수 자리를 버리면, 해당 금액이 어떤 계급에 포함되는지 판정할 수 있다.
• 이를 통해 데이터 계층으로 계급 범위를 나누고 리포팅 테이블을 만듦으로써  계급간의 금액 차이를 알 수 있다.

WITH 
stats AS (
SELECT
	-- 금액의 최댓값
	MAX(price) AS max_price
	-- 금액의 최솟값
	, MIN(price) AS min_price
	-- 금액의 범위
	, MAX(price) - MIN(price) AS range_price
	-- 계층 수
	, 10 AS bucket_num
FROM 
	purchase_detail_log
)
, purchase_log_with_bucket AS (
SELECT
	price
	, min_price
	-- 정규화 금액 : 대상 금액에서 최소 금액을 뺀 것
	, price - min_price AS diff
	-- 계층 범위 : 금액 범위를 계층 수로 나눈 것
	, 1.0 * range_price / bucket_num AS bucket_range
	
	-- 계층 판정 : FLOOR(<정규화 금액> / <계층 범위>)	
	, FLOOR(1.0 * (price - min_price)/ (1.0 * range_price / bucket_num)) + 1 AS bucket
	, width_bucket(price, min_price, max_price, bucket_num) AS bucket
FROM 
	purchase_detail_log, stats
)
SELECT *
FROM 
	purchase_log_with_bucket
ORDER BY 
	amount;