[Sql분석] 분석지표 만들기

지표 만들기

---

큰 지표에서 작은 지표로!
분석가는 지표를 잘 사용하고 다룰 줄 알아야 한다

• 매출 → 주문수 * 건당주문금액
• 주문수 → 고객수 * 주문빈도
• 건당 주문 금액 → 판매물품수 * 물품평균가격

WITH tb AS (
SELECT
  SUM(price) AS `총 매출`,
  COUNT(DISTINCT order_id) AS `총 주문수`,
  COUNT(order_item_id) AS `총 판매상품수`,

  SUM(price) / COUNT(DISTINCT order_id) AS `주문당평균가격`,
  COUNT(order_item_id) / COUNT(DISTINCT order_id) AS `평균판매상품수`, 
  SUM(price) / COUNT(order_item_id) AS `제품개당평균가격`

FROM `olist.olist_order_items` AS ord 
)
SELECT 
  tb.`주문당평균가격`, 
  tb.`평균판매상품수` * tb.`제품개당평균가격` AS check_1, 
  tb.`총 판매상품수`,
  tb.`평균판매상품수` * tb.`총 주문수` AS check_2,
  tb.`총 매출`,
  tb.`제품개당평균가격` * tb.`총 판매상품수` AS check_3
FROM tb 
;

• with tablename as( ) : 임시테이블을 만드는 명령어

• from 절로 가져와서 서브쿼리로 사용가능

• 컬럼명을 한글로 할 경우 (``) 백틱 안에 작성

• 주문테이블내 주문건수와 고객번호수는 일치한다

• customer_id가 달라도 같은 고객일수 있다

• 고객정보테이블에 customer_unique_id가 있다

원하는 지표 합치기

---

• 주문당 정보를 나타내는 테이블을 생성하고 싶다

• 레고를 쌓는다고 생각하고 단계별로 진행하기

1. 주문 1건당 주문금액, 판매상품의 수를 구하자

SELECT
item.order_id,
SUM(item.price) as ord_amt,
COUNT(item.order_item_id) as prd_cnt
FROM `olist.olist_order_items` AS item –- 주문 상품 정보 테이블
GROUP BY item.order_id –- 집계 기준 : 주문 번호

2. 주문정보테이블에 1번결과와,고객uniqueID를 붙인다

WITH tb AS (
SELECT
item.order_id, 
SUM(item.price) as ord_amt, --order_id 당 주문금액
COUNT(item.order_item_id) as prd_cnt --order_id당 주문상품 수
FROM `olist.olist_order_items` AS item
GROUP BY item.order_id
),
SELECT
ord.order_id,
ord.order_status,
ord.customer_id,
cust.customer_unique_id,
tb.ord_amt,
tb.prd_cnt
FROM `olist.olist_orders` AS ord
LEFT JOIN `olist.olist_customers` AS cust
ON ord.customer_id = cust.customer_id
INNER JOIN tb
ON ord.order_id = tb.order_id

• tb 테이블과 inner join 하는 이유
  : olist.olist_orders 에 있는 주문이 취소되는 경우, olist.olist_order_items 에 없는 내용이 있기 때문