1. 작년 대비 동월 매출 비교. 작년 동월 대비 차이/비율/매출 성장 비율 추출
•
step 1: 상품 카테고리 별 월별 매출액 추출
•
step 2: step 1의 집합에서 12개월 이전 매출 데이터를 가져와서 현재 월과 매출 비교.
SQL 쿼리 작성
--작년 대비 동월 매출 비교. 작년 동월 대비 차이/비율/매출 성장 비율 추출
SELECT
TO_CHAR(o.order_date, 'YYYY-MM') AS month,
SUM(oi.amount) AS curr_amount,
LAG(SUM(oi.amount), 12) OVER (ORDER BY TO_CHAR(o.order_date, 'YYYY-MM')) AS prev_amount_1year,
(SUM(oi.amount) - LAG(SUM(oi.amount), 12) OVER (ORDER BY TO_CHAR(o.order_date, 'YYYY-MM'))) / NULLIF(LAG(SUM(oi.amount), 12) OVER (ORDER BY TO_CHAR(o.order_date, 'YYYY-MM')), 0) * 100 AS prev_pct
FROM nw.orders o
JOIN nw.order_items oi ON o.order_id = oi.order_id
GROUP BY TO_CHAR(o.order_date, 'YYYY-MM')
ORDER BY TO_CHAR(o.order_date, 'YYYY-MM');
SQL
복사
위 시각화 차트 구성
2. 작년 대비 동분기 매출 비교. 작년 동분기 대비 차이/비율/매출 성장 비율 추출
SQL 쿼리 작성
SELECT
EXTRACT(YEAR FROM o.order_date) AS order_year,
EXTRACT(QUARTER FROM o.order_date) AS order_quarter,
SUM(oi.amount) AS current_quarter_sales,
LAG(SUM(oi.amount), 4) OVER (ORDER BY EXTRACT(YEAR FROM o.order_date), EXTRACT(QUARTER FROM o.order_date)) AS previous_year_same_quarter_sales,
SUM(oi.amount) - LAG(SUM(oi.amount), 4) OVER (ORDER BY EXTRACT(YEAR FROM o.order_date), EXTRACT(QUARTER FROM o.order_date)) AS sales_difference,
(SUM(oi.amount) / NULLIF(LAG(SUM(oi.amount), 4) OVER (ORDER BY EXTRACT(YEAR FROM o.order_date), EXTRACT(QUARTER FROM o.order_date)), 0) - 1) * 100 AS sales_growth_rate
FROM nw.orders o
JOIN nw.order_items oi ON o.order_id = oi.order_id
WHERE EXTRACT(YEAR FROM o.order_date) BETWEEN 1997 AND 1998
GROUP BY order_year, order_quarter
ORDER BY order_year, order_quarter;
SQL
복사
시각화 차트 구성 (1번과 동일)
3. 카테고리 별 기준 월 대비 매출 비율 추이(aka 매출 팬 차트)
•
step 1: 상품 카테고리 별 월별 매출액 추출
•
step 2: step 1의 집합에서 기준 월이 되는 첫월의 매출액을 동일 카테고리에 모두 복제한 뒤 매출 비율을 계산
SQL 쿼리 작성
-- 3. 카테고리 별 기준 월 대비 매출 비율 추이(aka 매출 팬 차트)
WITH BaseSales AS (
SELECT
c.category_id,
MIN(EXTRACT(YEAR FROM o.order_date) * 100 + EXTRACT(MONTH FROM o.order_date)) AS base_year_month
FROM nw.orders o
JOIN nw.order_items oi ON o.order_id = oi.order_id
JOIN nw.products p ON oi.product_id = p.product_id
JOIN nw.categories c ON p.category_id = c.category_id
GROUP BY c.category_id
),
MonthlySales AS (
SELECT
p.category_id,
EXTRACT(YEAR FROM o.order_date) AS sale_year,
EXTRACT(MONTH FROM o.order_date) AS sale_month,
SUM(oi.amount) AS total_sales
FROM nw.orders o
JOIN nw.order_items oi ON o.order_id = oi.order_id
JOIN nw.products p ON oi.product_id = p.product_id
JOIN nw.categories c ON p.category_id = c.category_id
GROUP BY p.category_id, sale_year, sale_month
),
SalesWithBase AS (
SELECT
MS.sale_year,
MS.sale_month,
MS.category_id,
MS.total_sales,
BS.base_year_month
FROM MonthlySales MS
JOIN BaseSales BS ON MS.category_id = BS.category_id
)
SELECT
S.sale_year,
S.sale_month,
C.category_name,
S.total_sales,
(S.total_sales / FIRST_VALUE(S.total_sales) OVER (
PARTITION BY S.category_id
ORDER BY S.sale_year, S.sale_month
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) - 1) * 100 AS base_ratio
FROM SalesWithBase S
JOIN nw.categories C ON S.category_id = C.category_id
ORDER BY S.category_id, S.sale_year, S.sale_month;
SQL
복사
FIRST_VALUE 라는 윈도우 함수로 각 파티션(카테고리)의 첫 번째 값(기준 매출액)을 가져옴.
시각화 차트 구성
4. 매출 Z 차트
Z차트 : 월차매출, 매출누계, 이동년계라는 3개 지표로 구성해서 계정 변동의 영향을 배제하고 트렌드 분석하는 방법
SQL 쿼리 작성
위 시각화 차트 구성
Web Analytics 스키마 구조
참고만 하는 테이블
5. 이전 주문이후 걸린 기간의 Histogram 구하기
SQL 쿼리 작성
위 시각화 차트 구성 (bin 간격을 10으로 설정)
•
floor(days_since_prev_order/10.0)*10 as bin
메타베이스 모델 사용하기
모델 쿼리를 이용해서 바로 히스토그램 시각화 차트 구성
lag / lead
lag : 이전 행 값 리턴
lead : 행 다음거 리턴
ETL 작업 : 쿼리를 데이터로 따로 저장해놓는 작업