1주차

데이터베이스 + 테이블 생성해주기

mysqlsampledatabase.sql

191.2KB

위의 데이터베이스 생성 코드를 Mysqlworkbench 새 쿼리 탭에 복붙해주고 실행시켜준다.

테이블 구조, 정의, 특성 확인해보기

## 고객 데이터
select * from customers; 
## 주문 데이터
select * from orders; 
## 상품 데이터
select * from products;
## 주문 상세 데이터
select * from orderdetails;

SQL
복사

고객 데이터(5행)

주문 데이터(5횅)

상품 데이터(5행)

주문 상세 데이터(5행)

지표 설계

︎ 전사 차원에서 지표를 설계한다 총 매출, 총 주문수, 총 판매된 수량, 주문당 평균 매출, 주문당 평균 수량, 해당 날짜의 주문한 고객수

︎ 항상 필요한 일자 기준 yyyymmdd 날짜는 꼭 필요. ord_ymd, ord_ym 두 개의 컬럼을 만들자

select  
	o.orderDate, # 일별
	sum(od.quantityOrdered * od.priceEach) as total_revenue, ## 총매출
	count(distinct o.orderNumber) as total_orders, ## 주문수
	sum(od.quantityOrdered) as total_quantity_sold, ## 총 판매된 수량
	count(distinct o.customerNumber) as distinct_customers, ## 주문한 고객 수
	round(sum(od.quantityOrdered * od.priceEach) / count(distinct o.orderNumber), 2) as avg_order_value, ## 주문당 평균 매출 
	round(sum(od.quantityOrdered) / count(distinct o.orderNumber), 2) as avg_quantity_per_order ## 주문당 평균 수량
from orders as o
join orderdetails od on o.orderNumber = od.orderNumber
group by o.orderDate
order by 1
limit 5;
SQL
복사

orderDate	total_revenue	total_orders	total_quantity_sold	distinct_customers	avg_order_value	avg_quantity_per_order
2003-01-06	10223.83	1	151	1	10223.83	151.00
2003-01-09	10549.01	1	142	1	10549.01	142.00
2003-01-10	5494.78	1	80	1	5494.78	80.00
2003-01-29	50218.95	1	541	1	50218.95	541.00
2003-01-31	40206.20	1	443	1	40206.20	443.00

고객과 관련된 지표

고객 테이블 — 마스터 테이블

•

고객ID, 고객명, 고객의 총 주문 횟수, 고객의 총주문금액, 고객의 평균 주문금액, 마지막 주문 날짜, 마지막 주문 이후 경과된 일수, 고객 등급

→ 필요한 테이블 가져오기

select
	c.customerNumber,
	c.customerName,
	count(distinct o.orderNumber) as total_orders,
	coalesce(sum(od.quantityOrdered * od.priceEach),0) as total_revenue,
	coalesce(round(sum(od.quantityOrdered * od.priceEach) / nullif(count(distinct o.orderNumber),0),2),0) as avg_order_value,
	max(o.orderDate) as last_order_date,
	datediff(curdate(), max(o.orderDate)) as day_since_last_order,
	case
		when count(distinct o.orderNumber) > 20 and sum(od.quantityOrdered * od.priceEach) > 50000 then 'VIP 고객'
		when count(distinct o.orderNumber) between 5 and 20 then '일반 고객'
		when datediff(curdate(), max(o.orderDate)) > 365 then '휴면 고객'
	end as customer_seg
from 
	customers as c
left join orders as o on o.customerNumber = c.customerNumber
left join orderdetails as od on od.orderNumber = o.orderNumber
group by c.customerNumber;
SQL
복사

함수 정리

필수 과제 1

위의 2개 마트 쿼리를 검증해주세요.

명확한 검증 로직을 작성하고 → 해당 값을 검증할 수 있는 코드와 함께 정리해서 주세요.

둘 다 모두 검증해야 하고, 예시는 최소 2개 이상씩 해야합니다.

total_revenue : 총 매출

total_orders : 총 주문수

total_quantity_sold : 총 판매 수량

distinct_customers : 주문 고객 수

avg_order_value : 주문당 평균 매출

avg_quantity_per_order : 주문당 평균 수량

total_orders : 고객의 총 주문 수

total_revenue : 고객의 총 주문 금액

avg_order_value : 고객의 평균 주문 금액

last_order_date : 고객의 가장 최근 주문 날짜

day_since_last_order : 마지막 주문일로부터 경과한 날짜

필수 과제 2

고객의 세그먼트를 새롭게 나눠주세요.

5등급으로 나눠서 직접 설계해보시면 됩니다.

customerNumber	customerName	total_orders	total_revenue	avg_order_value	last_order_date	day_since_last_order	customer_seg
103	Atelier graphique	3	22314.36	7438.12	2004-11-25	7420	휴면 고객
112	Signal Gift Stores	3	80180.98	26726.99	2004-11-29	7416	휴면 고객
114	Australian Collectors, Co.	5	180585.07	36117.01	2004-11-29	7416	일반 고객
119	La Rochelle Gifts	4	158573.12	39643.28	2005-05-31	7233	휴면 고객
121	Baane Mini Imports	4	104224.79	26056.20	2004-11-05	7440	휴면 고객
124	Mini Gifts Distributors Ltd.	17	591827.34	34813.37	2005-05-29	7235	일반 고객
125	Havel & Zbyszek Co	0	0.00	0.00	NULL	NULL	NULL
128	Blauer See Auto, Co.	4	75937.76	18984.44	2004-11-05	7440	휴면 고객
129	Mini Wheels Co.	3	66710.56	22236.85	2004-11-18	7427	휴면 고객
131	Land of Toys Inc.	4	149085.15	37271.29	2004-11-15	7430	휴면 고객
141	Euro+ Shopping Channel	26	820689.54	31564.98	2005-05-31	7233	VIP 고객
144	Volvo Model Replicas, Co	4	66694.82	16673.71	2004-11-19	7426	휴면 고객
145	Danish Wholesale Imports	5	129085.12	25817.02	2005-04-15	7279	일반 고객
146	Saveley & Henriot, Co.	3	130305.35	43435.12	2004-03-02	7688	휴면 고객
148	Dragon Souveniers, Ltd.	5	156251.03	31250.21	2005-03-02	7323	일반 고객
151	Muscle Machine Inc	4	177913.95	44478.49	2004-12-01	7414	휴면 고객
157	Diecast Classics Inc.	4	104358.69	26089.67	2005-05-30	7234	휴면 고객
161	Technics Stores Inc.	4	104545.22	26136.31	2005-01-05	7379	휴면 고객
166	Handji Gifts& Co	4	107746.75	26936.69	2005-04-23	7271	휴면 고객
167	Herkku Gifts	3	97562.47	32520.82	2004-09-03	7503	휴면 고객
168	American Souvenirs Inc	0	0.00	0.00	NULL	NULL	NULL
169	Porto Imports Co.	0	0.00	0.00	NULL	NULL	NULL

어떤 목적의 세그먼트인가?

어떤 고려 요소들이 있는가?

RFM 모델

# RFM -> 5등급
with customer_orders as (
	select
		c.customerNumber,
        c.customerName,
        count(distinct o.orderNumber) as total_orders,
        coalesce(sum(od.quantityOrdered * od.priceEach),0) as total_revenue,
        coalesce(round(sum(od.quantityOrdered * od.priceEach) / nullif(count(distinct o.orderNumber),0),2),0) as avg_order_value,
		max(o.orderDate) as last_order_date,
		datediff(curdate(), max(o.orderDate)) as day_since_last_order
	from customers as c
	left join orders as o on c.customerNumber = o.customerNumber
    left join orderdetails as od on o.orderNumber = od.orderNumber
	group by c.customerNumber
),
rfm_scores as (
	select
		customerNumber,
		customerName,
		-- recency : 최근 주문 일과의 간격이 길수록 낮은 점수
		ntile(5) over (order by day_since_last_order desc) as r_score,
		-- frequency : 총 주문 수이기 때문에 높을수록 좋은 점수
		ntile(5) over (order by total_orders) as f_score,
		-- monetary : 매출액 점수, 높을수록 좋은 점수
		ntile(5) over (order by total_revenue) as m_score
	from customer_orders
)


select
	customerNumber,
    customerName,
    r_score,
    f_score,
    m_score,
    (r_score + f_score + m_score) as rfm_score,
    case
		when (r_score + f_score + m_score) >= 13 then 'Platinum'
        when (r_score + f_score + m_score) >= 10 then 'Gold'
        when (r_score + f_score + m_score) >= 17 then 'Silver'
        when (r_score + f_score + m_score) >= 5 then 'Bronze'
        else 'Standard'
	end as customer_grade
from rfm_scores;
SQL
복사