SQL_Aplied4.sql

# 4주차(09/23) SQL 수업
USE classicmodels

# 1. 문자열 함수

# LIKE
SELECT * FROM products
where productName like 'Ferrari%' # Ferrari로 시작하는 문자

SELECT * FROM products 
WHERE productName LIKE '_____'; #  5글자 짜리 문자만

# 2. 문자열의 정규 표현식
# REGEXP
## 이메일 주소 패턴, 전화번호 패턴, 정형화되지 않은 커스텀되는 패턴 (문자열 추출) 일 때 많이 사용

### 정규 표현식 종류
	### ^ : ^s(s^) s로 시작하는(끝나는) 것만 추출
	### $ : 0$ 0로 끝나는 것만 추출
	### . : 아무 문자나 1개
	### * : 0회 이상 반복
	### + : 1회 이상 반복
	### ? : 0회 또는 1회 
    
	### [abc] -> a,b,c 중에 하나 
	### [^abc] -> a,b,c 제외 
	### [0-9] -> 숫자 
	### [A-Za-z] -> 알파벳 대소문자
    
## 2-1. 예시) 이러한 정규표현식들로 쿼리를 추출한다면?
select productCode from products
where productCode REGEXP '^S[0-9]{2}_[0-9]{4}$'; # (S+숫자2개)_(숫자4개) 



## 2-2. KPI 테이블 만들기(1)
## 목표 : 문자열 데이터를 사용하기 위해서 + 우리 배운 것들 + KPI 실무까지 잡아
## 실무에서 이렇게 쓸 수 있다 이해하고 접근해보자

### th1. 테이블 별, 속성 살펴보기
### products 테이블에서의 buyPrice : 공급업체에서 사들인 원가
### MSRP : 소비자 판매가, 권장 소비자 가격
### orderdetails 테이블에서의 priceEach, quantityOrdered : 실제 판매 가격, 수량

### th2. 속성들에서 얻어낼 수 있는 것 뭐가 있을까?
### 실부 KPI 쿼리
### 제품별 마진율 계산
### 권장가 대비 실제 판매가 ... 정말 할인 한건지 -> 할인율 계산

### th3. (Ford 차량에 대한)KPI 테이블 설계를 위한 정리 - 뭐가 필요하지?
### 1) 제품에 대한 월간 마진율 : 월마다 마진이 나올거야
### 2) MSRP 대비 실제 판매가의 할인율
### -> 손실 여부 (마진이 마이너스인가, 플러스 인가) 
### => 가격 정책에 대한 인사이트를 줄 수 있다.
### => 임직원 별로의 가격에 대한 손실 여부 볼 수 있다. -> 체계적인 관리 가능

### th4. th3에서 생각한 필요한 것들 계산 어떻게 하지? (수식)
			### products의 buyPrice : 공급업체에서 사들인 원가
			### MSRP : 소비자 판매가, 권장 소비자 가격
			### orderdetails의 priceEach, quantityOrdered : 실제 판매 가격, 수량
### 1) 마진율 : (판매한 제품가 - 공급업체가 사들인 원가) / (판매한 제품가)
###    마진율 = sum((priceEach - buyPrice)) / sum(priceEach)
### 2) MSRP 대비 할인율 => sum((MSRP - priceEach)) / sum(MSRP)
		select * from products
		where productCode = 'S12_1099'; # MSRP(권장 소비자 가격) = 194.57
		select * from orderdetails
		where productCode = 'S12_1099'; # PriceEach(실제 판매 가격) = 165.38, 161.49, 171.22 등
### 3) 손익 플래그 => sum((priceEach - buyPrice)) < 0, -1 else 0


## 2-3. KPI 테이블 만들기(2)
select * from products;

## [1] Ford 차량 에 대한 테이블 CTE
with ford_products as
(	
	select 
		p.productCode,
		p.productName,
		p.buyPrice,
		p.MSRP
	from products as p
	where p.productName LIKE '%Ford%'
)
select * from ford_products;


## [2] 각 order 에 대한 테이블 CTE : priceEach, orderNumber, orderDate

## orderdetails과 orders 테이블의 조인
### orderdetails(od)에서, 각 orderNumber 별 priceEach 
### orders(o)에서, 각 orderNumber의 orderDate(날짜)
select
	o.orderNumber,
    o.orderDate,
    date_format(o.orderDate, '%Y-%m') as ym,
    od.productCode,
    od.quantityOrdered,
    od.priceEach
from orders as o
join orderdetails as od
	using (orderNumber); #on o.orderNumber = od.orderNumber


## [3] "Ford"차량[1]에 대한 '각 order 별 데이터 테이블 CTE[2]' : sales 테이블
with 
ford_products as # [1] "Ford"차량의 CTE
(	
	select 
		p.productCode,
		p.productName,
		p.buyPrice,
		p.MSRP
	from products as p
	where p.productName LIKE '%Ford%'
),
sales as 
(
	select
		o.orderNumber,
		o.orderDate,
		date_format(o.orderDate, '%Y-%m') as ym,
		od.productCode,
		od.quantityOrdered,
		od.priceEach
        
	from orders as o
	join orderdetails as od using (orderNumber) # [2] 테이블과
    
	join ford_products as fp # [1]테이블의
		using (productCode) # join
	
    where o.status not in ('Cancelled') # order의 status(상태)가 cacelled(취소)된 경우는 제외
)
select * from sales;


## [4] 마진(priceEach - buyPrice) 구하는 CTE
with 
ford_products as # [1] "Ford"차량의 CTE
(	
	select 
		p.productCode,
		p.productName,
		p.buyPrice, # 공급업체에서 사들인 원가
		p.MSRP # 권장 소비자 가격 
	from products as p
	where p.productName LIKE '%Ford%'
),
sales as # [3] "Ford" order에 대한 데이터 CTE
(
	select
		o.orderNumber,
		o.orderDate,
		date_format(o.orderDate, '%Y-%m') as ym,
        
		od.productCode,
		od.quantityOrdered,
		od.priceEach # 판매 가격
        
	from orders as o
	join orderdetails as od using (orderNumber) 
    
	join ford_products as fp 
		using (productCode) 
	
    where o.status not in ('Cancelled') # order의 status(상태)가 cacelled(취소)된 경우는 제외
),
calc as # 월별 마진율
(
	select
		s.ym,
        s.productCode,
        f.productName,

        sum(s.quantityOrdered) as qty_sold,
        sum(s.quantityOrdered * s.priceEach) as gross_sales,  # 총 판매수익
        sum(s.quantityOrdered * f.buyPrice) as buy_cost, # 포드 구매 시 총 비용
        sum(s.quantityOrdered * (s.priceEach - f.buyPrice)) as gorss_profit, # 총 마진
        
		### 1) 마진율 : (판매한 제품가 - 공급업체가 사들인 원가) / (판매한 제품가)
		###    마진율 = sum((priceEach - buyPrice)) / sum(priceEach)
        case
			when sum(s.quantityOrdered * s.priceEach) = 0 then NULL
            else sum(s.quantityOrdered * (s.priceEach - f.buyPrice)) / sum(s.quantityOrdered * s.priceEach)
		end as margin_rate,
		### 2) 권장가 대비 할인율 => sum((MSRP - priceEach)) / sum(MSRP)
		case
			when sum(s.quantityOrdered * f.MSRP) = 0 then NULL
            else sum(s.quantityOrdered * (f.MSRP - s.priceEach)) / sum(s.quantityOrdered * f.MSRP)
		end as discount_vs_MSRP,
        ### 3) 손실에 대한 확인
        case
			when sum(s.quantityOrdered * (s.priceEach - f.buyPrice)) < 0 then 1 else 0
		end as is_negative
        
	from sales as s
    join ford_products as f
		using (productCode)
	
    group by s.ym, s.productCode, f.ProductName ## 월별 마진율(priceEach - buyPrice)
)
select * from calc;



# 정답

with ford_products as (
	select 
		p.productCode,
		p.productName,
		p.buyPrice,
		p.MSRP
	from products as p 
	where p.productName  LIKE '%Ford%'
    ),
sales as (
	select 
		o.orderNumber,
		o.orderDate,
		Date_format(o.orderDate, '%Y-%m') as ym,
		od.productCode,
		od.quantityOrdered,
		od.priceEach
	from orders as o 
	join orderdetails as od using(orderNumber)
    join ford_products fp using(productCode)
    where o.status not in ('Cancelled')
    ),
calc as (
	select
    s.ym,
    s.productCode,
    f.productName,
    sum(s.quantityOrdered) as qty_sold,
    sum(s.quantityOrdered * s.priceEach) as gross_sales,
    sum(s.quantityOrdered * f.buyPrice) as buy_cost,
    sum(s.quantityOrdered * (s.priceEach - f.buyPrice)) as gross_profit,
    
    ## 마진율 : 이익/매출
    case
		when sum(s.quantityOrdered * s.priceEach) = 0 then NULL
        else sum(s.quantityOrdered * (s.priceEach - f.buyPrice)) / sum(s.quantityOrdered * s.priceEach) 
	end as margin_rate,
    
    ## 권장가 대비 할인율 (MSRP - 실제가) / MSRP
	case
		when sum(s.quantityOrdered * f.MSRP) = 0 then null
        else sum(s.quantityOrdered * (f.MSRP - s.priceEach)) / sum(s.quantityOrdered * f.MSRP) 
	end as discount_vs_msrp,
    
	## 손실에 대한 확인
    case 
		when sum(s.quantityOrdered * (s.priceEach - f.buyPrice)) < 0 then 1 else 0
	end as is_negative
    from sales as s 
    join ford_products as f using (productCode)
    group by s.ym, s.productCode, f.productName
	)
SELECT
  ym,
  productCode,
  productName,
  qty_sold,
  gross_sales,
  buy_cost,
  gross_profit,
  ROUND(margin_rate * 100, 2)           AS margin_pct,             -- %
  ROUND(discount_vs_msrp * 100, 2) AS discount_vs_msrp_pct,   -- %
  is_negative
FROM calc
ORDER BY ym, productName;