2024-04-18

• Today

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

 

---

• Today I Learned

 

SQL

자동차 대여 기록 별 대여 금액 구하기

https://lyj-01.tistory.com/99

자동차 대여 기록 별 대여 금액 구하기

FROM 절에서 쓴 구문을 WITH 구문으로 바꾸고 싶어 

여러가지 방법을 써봤는데, 결과값이 다르게 나왔다

튜터님과 여러가지 방면으로 고민을 해보았는데, 

아마 NULL 값이 포함되지 않아 다른 결과가 나온 것으로 예상된다..

 

조금더 고민을 해보아야겠다.

 

 

 

---

프로젝트

 

1. ppt 만들기

2. 발표영상촬영

3. 발표자료(대본)

4. 브랜드 추천

 

4-1

 문자열을 바꾸고, 첫번째 인덱스 뽑아주기

ex) Imation�USB 2.0 Swivel�Flash Drive�USB�flash drive  4 GB - Pink

SELECT substring_index(REPLACE(REPLACE(Product_Name,'�',' '),'?',' '),' ', 1) Product_Name 
FROM us2020 u 
WHERE SubCategory = 'Accessories'

'Accessories' 중에서 Product_Name 안에 특수 문자 등으로 잘못 구분이 되어있는 데이터들이 있었는데

해당 데이터를 바꾸고, 브랜드명을 뽑는 작업을 해주었다.

 

# REPLACE(컬럼,문자열선택,바꿀문자열)

REPLACE 함수는 한번에 하나만 지원하기 때문에 여러개를 바꾸고 싶다면

 

REPLACE(REPLACE( 컬럼,문자열선택,바꿀문자열),문자열선택,바꿀문자열)

이렇게 진행해주어야 한다.

 

# SUBSTRING_INDEX('문자열','구분자',구분자index)

구분자를 기준으로 문자열을 잘라오는 기능

 

 

 

4-2

*브랜드의 매출액, 수익, 판매량, 수익률

SELECT
	SUBSTRING_INDEX(Product_Name, ' ', 1),
	SUM(Sales),
	SUM(Profit),
	sum(quantity),
	sum(profit)/sum(sales)*100 pf
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,
		REPLACE(REPLACE(Product_Name, '?', ' '), '�', ' ') Product_Name,
		(Sales / Quantity) sales_price,
		(Profit / Quantity) per1profit,
		(Profit / Sales) profit_ratio,
		Sales,
		Profit,
		Quantity
	FROM US2020
    WHERE subcategory = 'Accessories'
)tbl
WHERE acs_type = '음향기기'
GROUP BY 1
order by 4 desc

 

 

 

제품 선정 사유에 대해서 시각화자료 준비

> 총 매출액, 총 판매량, 총 수익금, 수익률, 월별 수익률 분산

 

 

추가 보안을 위한 acs_type 의 월별 매출액, 수익, 수익률 추이

# (line chart 제작용) acs_type별 월별 매출액 및 월별 수익금 추이 파악 
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,
	acs_type,
	SUM(sales_price * Quantity) monthly_sales,
	SUM(per1_profit * Quantity) monthly_profit
FROM categorised
GROUP BY 1, 2
ORDER BY 1 ,4 DESC;

 

 

 

 

---

• Next

1. 프로젝트 발표

2. 알고리즘 코드카타