2024-04-17

• Today

1. 프로젝트 목차 설정 및 자료 수집
2. SQL 코드카타
 

---

• Today I Learned

SQL

* 쿼리 문이 좀 복잡해질 수 있는 조건이 있는 경우 어떤식으로 풀이를 해야할지 궁금해서 팀원분께 여쭤봤다.
보통 메인쿼리에서 쓰기에는 복잡한 조건들의 경우

서브쿼리 또는 with 문을 사용하여 먼저 테이블을 만들어 놓고,

그 후에 간단한 조건들은 메인쿼리에서 뽑아내어 사용한다.
순서로 보자면
서브쿼리 or with 문 > 메인쿼리
이렇게 알고리즘을 생각하고 있으면 될 듯!
 
 
대여기간까지는 구했으나 대여금액을 구하지 못하여 다른 쿼리를 참고!
FROM 절에서 사용을 했길래 WITH 문으로 바꾸어 짜보려고 했으나 실행결과가 다르게 나왔다 
코드는 동일하게 작성이 된 것 같은데, 이유를 알 수 없어 튜터님께 여쭤보았다.
정확한 이유는 찾지 못하였으나 아마 NULL 값이 포함되지 않아, 7일미만이 제외되어 다른 결과가 나온 것으로 예상함

WITH rental_date AS
    (
    SELECT history_id,c.car_id, car_type, daily_fee, end_date, start_date
         , CASE WHEN DATEDIFF(end_date,start_date)+1 BETWEEN 7 AND 29 THEN '7일 이상'
                WHEN DATEDIFF(end_date,start_date)+1 BETWEEN 30 AND 89 THEN '30일 이상'
                WHEN DATEDIFF(end_date,start_date)+1 >= 90 THEN '90일 이상'
                ELSE null END duration_date
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h JOIN CAR_RENTAL_COMPANY_CAR c USING(car_id)
    )

SELECT history_id
     , round(daily_fee  * (datediff(end_date, start_date) + 1) * coalesce(1 - (discount_rate * .01),1),0) as fee
FROM rental_date d left JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN p ON d.car_type=p.car_type AND d.duration_date=p.duration_type
-- 위 구문에서 car_type 과 duration_type 만 연결이 되어 7일미만은 제외 됨
where p.car_type = '트럭'
order by fee desc, history_id desc;

 
* 튜터님께서 알려주신 다른 코드로 내일 뜯어볼 예정!
 
 

---

프로젝트

 
 
어제에 이어서 진행!
 

회의 내용

진행 목차

1. 분석목적

- Technology 기반의 사업체로 어떤 제품을 판매할 것인지

 

2. 수집 및 전처리(1~2p)

- 수집: 캐글자료

- 전처리 (현수님 코드 등으로 추가)

: 결측치(0건)와 이상치(6건 이상) 처리 

: sales 컬럼이 단가였는지, 매출인지 확인한 방법           

 

3. 데이터분석 ( 왜 이런 분석을 했는지 간단 설명 )

- 기초 통계량 분석

: 총 매출액, 판매량, 수익, 수익률, category별, 월별 등

- 시각화를 통한 데이터 탐색

: category 와 sub-category 로 도넛차트 등으로 보여주기

 

4. 결과해석 및 결론

- 표, 그래프 등으로 시각화 하여 보여주며 프로젝트 요약

- 결론: Accessories 를 선정

- 향후 예상 수익 등 

- 한계점

: 1년동안의 수치밖에 없었기 때문에 분석에 한계가 있었다.

> 월별로 세그먼트를 내어 확인을 하고 싶었으나 데이터양의 한계가 있어, 1년 평균 또는 합계의 순위로 보고, 상품을 정함

> 지금 당장은 분석의 한계였지만 최소 2~3년 이상의 데이터가 누적된다면 조금더 정확한 추세를 확인해볼 수 있다.

: 기업명 등의 정보가 없어, 동종업계의 동향을 분석하기에 한계가 있었다.

 

5. 참고자료(발표에서는 제외~/ 우리가 이런걸 참고했다)

- Power BI(시각화 예시)

- ChapGPT(분산결과정리)

 

금일 계획

- ppt 디자인 레퍼런스 탐색 및 공유

- 기존 코드 수정, 시각화 자료 확인

 
 
 
3. 정가를 파악하고 가격대별로 Segmentation 을 하였을 때 가장 많이 팔리는 가격대 알아보기
 
Accessories 내의 제품종류에 대한 기초통계량

# 재분류한 카테고리별 수익 기준 기초통계량
WITH price AS (
	SELECT  SubCategory, Product_ID, Product_Name, Sales, Quantity, Discount,
		(Sales / Quantity) sales_price, # 할인 먹인 개당 가격 
		(Sales / Quantity / (1 - Discount)) org_price, # 정가
		(Profit / Quantity) per1_profit # 개당 수익 
	FROM US2020
	WHERE SubCategory = 'Accessories'
),
categorised AS (
	SELECT 
	(case 
		WHEN Product_Name LIKE "%key%" or Product_Name LIKE "%trackball%" or Product_Name LIKE "%mouse%" or Product_Name LIKE "%gameboard%" THEN "키보드&마우스"
		WHEN product_name LIKE "%drive%" or Product_Name LIKE "%card%" or Product_Name LIKE "%Data Traveler%" THEN "스토리지"
		WHEN product_name LIKE "%dvd%" or Product_Name LIKE "%CD%" or Product_Name LIKE "%disc%" THEN "DVD"
		WHEN product_name LIKE "%head%" or Product_Name LIKE "%speak%" THEN "음향기기"
		WHEN product_name LIKE "%pad%" THEN "단말기"
	ELSE "etc."  end) acs_type, 
	org_price, 
	sales_price,
	per1_profit,
	Quantity
	FROM price
)
SELECT 
	acs_type,
	MIN(per1_profit) AS 'MIN',
	SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(per1_profit ORDER BY per1_profit SEPARATOR ','),',', 5/100 * COUNT(*) + 1), ',', -1)  AS `5TH PER`,
	SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(per1_profit ORDER BY per1_profit SEPARATOR ','),',', 25/100 * COUNT(*) + 1), ',', -1)  AS `1IQR`,
	SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(per1_profit ORDER BY per1_profit SEPARATOR ','),',', 50/100 * COUNT(*) + 1), ',', -1)  AS `MEDIAN`,
	AVG(per1_profit) AS 'MEAN',
	SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(per1_profit ORDER BY per1_profit SEPARATOR ','),',', 75/100 * COUNT(*) + 1), ',', -1)  AS `3IQR`,
	SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(per1_profit ORDER BY per1_profit SEPARATOR ','),',', 95/100 * COUNT(*) + 1), ',', -1)  AS `95TH PER`,
	MAX(per1_profit) AS 'MAX',
	SUM(Quantity) AS 'Quantity' 
FROM categorised
GROUP BY 1
ORDER BY AVG(per1_profit) DESC;

 
Accessories 내의 제품종류에 대한 월별 매출액 및 수익금 추이

# acs_type별 월별 매출액 및 월별 수익금 추이 파악 (pivot table)
WITH price AS (
	SELECT  Order_Date, SubCategory, Product_ID, Product_Name, Sales, Quantity, Discount,
		(Sales / Quantity) sales_price, # 할인 먹인 개당 가격 
		(Sales / Quantity / (1 - Discount)) org_price, # 정가
		(Profit / Quantity) per1_profit # 개당 수익 
	FROM US2020
	WHERE SubCategory = 'Accessories'
),
categorised AS (
	SELECT 
	(case 
		WHEN Product_Name LIKE "%key%" or Product_Name LIKE "%trackball%" or Product_Name LIKE "%mouse%" or Product_Name LIKE "%gameboard%" THEN "키보드&마우스"
		WHEN product_name LIKE "%drive%" or Product_Name LIKE "%card%" or Product_Name LIKE "%Data Traveler%" THEN "스토리지"
		WHEN product_name LIKE "%dvd%" or Product_Name LIKE "%CD%" or Product_Name LIKE "%disc%" THEN "DVD"
		WHEN product_name LIKE "%head%" or Product_Name LIKE "%speak%" THEN "음향기기"
		WHEN product_name LIKE "%pad%" THEN "단말기"
	ELSE "etc." end) acs_type, 
	Product_ID,
	Product_Name,
	Order_Date,
	org_price, 
	sales_price,
	per1_profit,
	Quantity
	FROM price
)
SELECT 
	MONTH(Order_Date) MONTH,
	SUM(IF (acs_type = '키보드&마우스', sales_price * quantity, 0)) '키보드&마우스 월 매출액',
	SUM(IF (acs_type = '스토리지', sales_price * quantity, 0)) '스토리지 월 매출액',
	SUM(IF (acs_type = '음향기기', sales_price * quantity, 0)) '음향기기 월 매출액',
	SUM(IF (acs_type = 'DVD', sales_price * quantity, 0)) 'DVD 월 매출액',
	SUM(IF (acs_type = 'etc.', sales_price * quantity, 0)) 'ETC 월 매출액',
	SUM(IF (acs_type = '키보드&마우스', per1_profit * quantity, 0)) '키보드&마우스 월 수익금',
	SUM(IF (acs_type = '스토리지', per1_profit * quantity, 0)) '스토리지 월 수익금',
	SUM(IF (acs_type = '음향기기', per1_profit * quantity, 0)) '음향기기 월 수익금',
	SUM(IF (acs_type = 'DVD', per1_profit * quantity, 0)) 'DVD 월 수익금',
	SUM(IF (acs_type = 'etc.', per1_profit * quantity, 0)) 'ETC 월 수익금'		
FROM categorised
GROUP BY 1
ORDER BY 1;

 
 
 
위와 같은 코드로 진행한 이유는
Tech > Accessories > 키보드&마우스, 스토리지 등 6개의 분류  > 마지막으로 분류된 상품을 금액대로 나누기
 
: 어떤 상품을 어떤 금액대로 팔았을 때 이익이 날 것인지를 예측해보기 위함이다.
 
 

4. 해당 제품의 월 별 매출/할인/판매량/수익/수익률 확인
키보드&마우스로 선택하여 분석> 음향기기로 변경

# 1. 키보드&마우스 타입의 상품을 금액대별로 segmentation 
# 2. 금액대별 물건 1개 판매 시 매출액, 수익, 수익률을 파악
SELECT
	(CASE WHEN sales_price > 60 THEN 'high'
	WHEN sales_price <= 60 AND sales_price > 40 THEN 'medium'
	ELSE 'cheap' END) merch_type, 
	AVG(sales_price),
	AVG(per1profit),
	AVG(profit_ratio), 
	SUM(Quantity)
FROM (
	SELECT 
		(case 
			WHEN Product_Name LIKE "%key%" or Product_Name LIKE "%trackball%" or Product_Name LIKE "%mouse%" or Product_Name LIKE "%gameboard%" THEN "키보드&마우스"
			WHEN product_name LIKE "%drive%" or Product_Name LIKE "%card%" or Product_Name LIKE "%Data Traveler%" THEN "스토리지"
			WHEN product_name LIKE "%dvd%" or Product_Name LIKE "%CD%" or Product_Name LIKE "%disc%" THEN "DVD"
			WHEN product_name LIKE "%head%" or Product_Name LIKE "%speak%" THEN "음향기기"
			WHEN product_name LIKE "%pad%" THEN "단말기"
		ELSE "etc." end) acs_type,
		Product_ID, 
		(Sales / Quantity) sales_price,
		(Profit / Quantity) per1profit,
		(Profit / Sales) profit_ratio,
		Sales,
		Profit,
		Quantity
	FROM US2020
)tbl
WHERE acs_type = '키보드&마우스'
GROUP BY 1;

 
* 키보드&마우스를 선택한 사유에 대해서 추가적인 보완 필요
> 매출액, 매출량, 수익,수익률, 분산
 
 
 
 
5. 이윤을 남기기 위해 어떤 방법을 사용할 것인지 결정
6. 1년 동안의 매출/판매량/수익/수익률 예측
7. 해당 카테고리에 대한 분석결과를 토대로 우리 기업이 가장 많은 이윤을 추구할 수 있는 계획 수립
 
 
* 새로 알게된 사실!
CASE WHEN 으로 하나씩 월을 계산하는 방식이 아니라

CONCAT(CAST(MONTH(Order_Date) AS char), "월") month

이렇게 사용하면 한줄로 끝낼 수 있다!
 
 
* 현업에서 Segmentation 을 하는 방법은?
: 시중에서 판매되고 있는 가격대를 기준으로 해서 분류를 한다.
: 그 기준에서 세부적으로 나눈다면 min, max, median 등을 보고 나누는 방법이 있다.
: 그룹별로 같은 양의 갯수를 주거나, 갯수와는 상관 없이 금액대를 맞추고 싶다는 등의 기준을 정해주면 된다.
 
 

---

• Next

1. 프로젝트 준비(발표자료준비)
2. SQL, 파이썬 코드카타