15. SELECT

1. ONLINE_ORDER 주문 테이블 컬럼
    1) PRICE : 판매가격
       UNITSOLD : 판매수량
       GMV : 거래액 (PRICE * UNITSOLD)
       PRODUCT_PROFIT : 상품 이익
       DISCOUNT : 할인액
       TOTAL_PROFIT : 거래액(PRODUCT_PROFIT - DISCOUNT)

2. 데이터 탐색 결과 정리
    1) ONLINE_ORDER
        - 상품 ID만 있고, 상품명이 없음
        - USER ID만 있고, 고객 정보(성/연령)가 없음
        - ITEM ID만 있고, 카테고리 정보가 없음
        - USER ID 컬럼에 NULL값이 있음

    2) ITEM
        - 상품 (ITEM) ID에 매핑되는 상품명, 카테고리 ID가 있음
    3) cATEGORY
        - 카테고리 ID에 매핑되는 카테고리 이름이 있음
        - 카테고리 분류는 CATE1이 세부분류, CATE2가 중분류, CATE3가 대분류임
    4) USER_INFO
        - USER ID에 매핑되는 성별, 연령대 정보가 있음

3. 문자형 데이터 가공하기
    1) 특정 문자열만 잘라내기 : SUBSTR()
    2) 문자열 합치기 : A || B, CONCAT(A, B)
    3) 조건별로 그룹핑하기 : CASE WHEN ~ ELSE ~ END
    4) NULL값 치환하기 : NVL(컬럼명, ~~~)
    5) TRIM(), REPLACE(), UPPER(), LOWER(), LENGTH ...

4. 숫자형 데이터 가공하기
    1) 반올림 : ROUND()
    2) 절대값 : ABS()
    3) SIN() ...

5. 날짜 데이터 가공하기
    1) 날짜 더하기 / 빼기 
    2) 날짜에서 특정 월/주 등 추출하기

-- 할인률, 판매가, 이익률, 전체이익률 계산하기

-- 할인률
SELECT DISCOUNT / GMV AS DISCOUNT_rate
FROM ONLINE_ORDER oo ;

-- 할인률, 판매가, 이익률, 전체이익률
SELECT DISCOUNT / GMV AS DISCOUNT_rate,
		gmv - discount AS paid_amount,
		PRODUCT_PROFIT / GMV AS product_magin,
		TOTAL_PROFIT / GMV AS total_magin
FROM ONLINE_ORDER oo ;

-- 카테고리, 아이템과 연결해서 출력 (조인)
SELECT c.CATE1,
		DISCOUNT / GMV AS DISCOUNT_rate,
		GMV - DISCOUNT AS paid_amount,
		PRODUCT_PROFIT / GMV AS product_magin,
		TOTAL_PROFIT / GMV AS total_magin
FROM ONLINE_ORDER oo 
JOIN ITEM i ON oo.ITEMID = i.ID 
JOIN CATEGORY c ON i.CATEGORY_ID = c.ID ;


SELECT c.CATE1,
		sum(DISCOUNT) / sum(GMV) AS DISCOUNT_rate,
		sum(GMV) - sum(DISCOUNT) AS paid_amount,
		sum(PRODUCT_PROFIT) / sum(GMV) AS product_magin,
		sum(TOTAL_PROFIT) / sum(GMV) AS total_magin
FROM ONLINE_ORDER oo 
JOIN ITEM i ON oo.ITEMID = i.ID 
JOIN CATEGORY c ON i.CATEGORY_ID = c.ID 
GROUP BY c.CATE1 
ORDER BY 3 DESC;

-- 소수점 이하 2자리로 조정
-- 좀 더 보기 좋게 %로 조정
SELECT c.CATE1,
		ROUND(sum(DISCOUNT) / sum(GMV), 2) * 100 || '%' AS DISCOUNT_rate,
		sum(GMV) - sum(DISCOUNT) AS paid_amount,
		ROUND(sum(PRODUCT_PROFIT) / sum(GMV), 2) * 100 || '%' AS product_magin,
		ROUND(sum(TOTAL_PROFIT) / sum(GMV),2) * 100 || '%' AS total_magin
FROM ONLINE_ORDER oo 
JOIN ITEM i ON oo.ITEMID = i.ID 
JOIN CATEGORY c ON i.CATEGORY_ID = c.ID 
GROUP BY c.CATE1 
ORDER BY 3 DESC;


-- 100명의 고객이 구매를 하였고, 총 판매수량이 200개였다.
-- 인당 평균 구매수량 = 총 판매수량 / 총 고객수
-- 인당 평균 구매금액 = 총 구매금액 / 총 고객수

-- online_order에서 인당 구매수량이 높은 상품은 어떤 것인가?
SELECT i.ITEM_NAME, 
		SUM(UNITSOLD) AS UNITSOLD,
		COUNT(DISTINCT USERID) AS USER_COUNT,
		ROUND(SUM(UNITSOLD) / COUNT(DISTINCT USERID), 2) AS AVG_UNITSOLD_PER_CUSTOMER,
		ROUND(SUM(GMV) / COUNT(DISTINCT USERID)) AS AVG_GMV_PER_CUSTOMER  
FROM ONLINE_ORDER oo 
JOIN ITEM i ON oo.ITEMID = i.ID 
GROUP BY i.ITEM_NAME 
ORDER BY AVG_GMV_PER_CUSTOMER DESC ;


-- 인당 구매금액이 높은 성/연령대는 무엇인가?
SELECT ui.AGE_BAND , 
		ui.GENDER , 
		SUM(GMV) AS GMV ,
		COUNT(DISTINCT oo.USERID) AS USER_COUNT,
		ROUND(SUM(oo.GMV) / COUNT(DISTINCT ui.USERID)) AS AVG_GMV_PER_CUSTOMER 
FROM ONLINE_ORDER oo 
JOIN USER_INFO ui ON oo.USERID = ui.USERID 
GROUP BY ui.AGE_BAND , ui.GENDER 
ORDER BY AVG_GMV_PER_CUSTOMER DESC;