1. 작년 대비 동월 매출 비교. 작년 동월 대비 차이/비율/매출 성장 비율 추출
•
step 1: 상품 카테고리 별 월별 매출액 추출
•
step 2: step 1의 집합에서 12개월 이전(작년) 매출 데이터를 가져와서 현재 월과 매출 비교.
SQL 쿼리 작성
위 시각화 차트 구성
WITH MonthlySales AS (
SELECT
DATE_TRUNC('month', order_date)::date AS month,
SUM(amount) AS curr_amount
FROM
nw.orders a
JOIN nw.order_items oi ON a.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
DATE_TRUNC('month', order_date)::date
),
LaggedSales AS (
SELECT
month,
curr_amount,
LAG(curr_amount, 12) OVER (ORDER BY month) AS prev_amount_1years
FROM
MonthlySales
)
SELECT
month,
curr_amount,
prev_amount_1years,
curr_amount - prev_amount_1years AS difference,
(curr_amount - prev_amount_1years) / NULLIF(prev_amount_1years, 0) * 100 AS growth_rate
FROM
LaggedSales
ORDER BY
month;
SQL
복사
2. 작년 대비 동분기 매출 비교. 작년 동분기 대비 차이/비율/매출 성장 비율 추출
SQL 쿼리 작성
시각화 차트 구성 (1반과 동일)
WITH quarterlySales AS (
SELECT
DATE_TRUNC('quarter', order_date)::date AS quarter,
SUM(amount) AS curr_amount
FROM
nw.orders a
JOIN nw.order_items oi ON a.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
DATE_TRUNC('quarter', order_date)::date
),
LaggedSales AS (
SELECT
quarter,
curr_amount,
LAG(curr_amount, 4) OVER (ORDER BY quarter) AS prev_amount_1years
FROM
quarterlySales
)
SELECT
quarter,
curr_amount,
prev_amount_1years,
curr_amount - prev_amount_1years AS difference,
(curr_amount - prev_amount_1years) / NULLIF(prev_amount_1years, 0) * 100 AS growth_rate
FROM
LaggedSales
ORDER BY
quarter;
SQL
복사
3. 카테고리 별 기준 월 대비 매출 비율 추이(aka 매출 팬 차트)
•
step 1: 상품 카테고리 별 월별 매출액 추출
•
step 2: step 1의 집합에서 기준 월이 되는 첫월의 매출액을 동일 카테고리에 모두 복제한 뒤 매출 비율을 계산
SQL 쿼리 작성
시각화 차트 구성
WITH MonthlySales AS (
SELECT
DATE_TRUNC('month', order_date)::date AS month,
c.category_name,
SUM(oi.amount) AS sales_amount
FROM
nw.orders a
JOIN nw.order_items oi ON a.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
month, c.category_name
),
FirstMonthSales AS (
SELECT
category_name,
MIN(month) AS first_sale_month
FROM
MonthlySales
GROUP BY
category_name
),
CategorySalesWithBase AS (
SELECT
ms.month,
ms.category_name,
ms.sales_amount,
fms.first_sale_month,
FIRST_VALUE(ms.sales_amount) OVER (PARTITION BY ms.category_name ORDER BY ms.month) AS base_sales_amount
FROM
MonthlySales ms
JOIN FirstMonthSales fms ON ms.category_name = fms.category_name AND ms.month >= fms.first_sale_month
)
SELECT
month,
category_name,
sales_amount,
base_sales_amount,
CASE
WHEN base_sales_amount > 0 THEN (sales_amount / base_sales_amount) * 100
ELSE NULL
END AS sales_growth_percentage
FROM
CategorySalesWithBase
ORDER BY
category_name, month;
SQL
복사
4. 매출 Z 차트
Z차트
•
월별 매출
•
매출 누계
•
이동년계
-- 월별 매출 --
WITH monthly_amount AS (
SELECT
DATE_TRUNC('month', order_date)::date AS month,
EXTRACT(YEAR FROM a.order_date) AS year,
SUM(amount) AS monthly_amount
FROM
nw.orders a
JOIN nw.order_items oi ON a.order_id = oi.order_id
GROUP BY
DATE_TRUNC('month', order_date)::date, EXTRACT(YEAR FROM a.order_date)
),
--매출 누계 & 이동년계--
calc_index AS (
SELECT
year,
month,
monthly_amount,
-- 매출 누계--
sum(CASE WHEN year = '1997' Then monthly_amount END) OVER(ORDER BY year, month ROWS UNBOUNDED PRECEDING) AS agg_amount,
--이동년계--
SUM(monthly_amount) OVER (Order by year, month ROWS BETWEEN 11 PRECEDING and CURRENT Row) AS year_avg_amount
FROM monthly_amount
ORDER BY year, month
)
--1997년 z차트 표시--
SELECT
month, monthly_amount AS sum_amount, agg_amount AS acc_amount, year_avg_amount AS year_ma_amount
From calc_index
Where year = '1997'
ORDER BY month ;
SQL
복사
SQL 쿼리 작성
위 시각화 차트 구성
Web Analytics 스키마 구조
5. 이전 주문이후 걸린 기간의 Histogram 구하기
SQL 쿼리 작성
위 시각화 차트 구성 (bin 간격을 10으로 설정)
•
floor(days_since_prev_order/10.0)*10 as bin
메타베이스 모델 사용하기
모델 쿼리를 이용해서 바로 히스토그램 시각화 차트 구성