[TIL] 52일차 TIL(20230419) - SQL 미니 프로젝트
[TIL] 52일차 TIL(20230419) - SQL 미니 프로젝트
📗 SQL 데이터 분석
✅ 데이터 분석 과정
1. 데이터 탐색
2. 분석 목적 설정
- 누구를 위한 분석인지, 대상을 정하고 대상에 알맞은 비즈니스 가치 창출을 위한 목적을 구체적으로 설정한다
ex) 고객 담당자, 효과적인 고객 관리 전략을 위한 데이터 분석 > 고객 세분화
3. 지표 설정 및 분석 계획
4. 데이터 추출/정제/가공/분석
5. 리포트 작성
📗 SQL 미니 프로젝트
✅ 주제 선정 과정
데이터셋을 탐색하여 목표 배송 날짜보다 늦게 도착하거나, 배송 기간이 오래걸리는 주문들이 상당수 존재한다는 사실을 알게됐다. 또 이런 주문들은 주(State)의 영향이 존재한다는 사실을 파악하였다.
그렇다면 배송이 오래걸리고, 운임비가 높지만, 매출이 일정 이상 발생하는 지역이 있다면 이 지역에 물류 창고를 설치하여 배송의 효율성을 높이고 비용은 감소하는 효과를 얻을 수 있을 것이다.
✅ 주제
지역별 판매 현황을 통해 상품 수요를 예측하고 물류 창고 신설 지역을 선정한다.
물류창고 신설 지역을 선정하는 기준은
1) 지역별 수요가 꾸준히 발생하는 지역
2) 배송이 오래걸려 배송의 개선이 필요한 지역
으로 선정했다.
✅ 사용 데이터 선정
존재하는 테이블에서 분석목적에 적합한 관련 컬럼들을 선별했다. 지역별 매출 발생량을 확인하기 위해 주문과 고객정보 데이터를 가져오고, 구체적으로 어떤 상품 혹은 카테고리에서 매출이 많이 발생하는지 살펴보기 위해 products 테이블에서 관련 정보를 가져왔다. 선정한 모든 데이터는 다음과 같다.
✅ 선정 지표
- 지역별 상품 판매 현황: 주문수량, 판매금액, 상품별 판매수량 등
- 지역별 년도별-분기별 상품 판매 현황: 주문수량, 판매금액, 상품별 판매수량 등
- 지역별 예상일차이: 예상배송일시 - 배송완료일시
✅ SQL 데이터 추출
앞서 선정한 데이터(컬럼)과 지표를 바탕으로 데이터를 추출하는 쿼리문을 작성했다.
SELECT
p.product_id,
p.product_category_name,
t.product_category_name_english 카테고리명,
o.order_id,
o.order_purchase_timestamp 구매일시,
o.order_delivered_customer_date 배송완료일시,
o.order_estimated_delivery_date 예상배송일시,
oi.shipping_limit_date 배송목표일시,
oi.price,
oi.freight_value,
c.customer_unique_id,
c.customer_zip_code,
c.customer_city,
c.customer_state,
date_part('day', o.order_delivered_customer_date - o.order_purchase_timestamp) 전체배송소요기간,
date_part('day', o.order_delivered_customer_date - o.order_estimated_delivery_date) 예상일차이,
case
when date_part('hour', o.order_delivered_customer_date - oi.shipping_limit_date) < 0 then 1
WHEN date_part('hour', o.order_delivered_customer_date - oi.shipping_limit_date) >=0 then 0
end as 제한일경과여부
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = oi.product_id
LEFT JOIN product_category_name_translation t ON p.product_category_name = t.product_category_name
WHERE p.product_category_name notnull;