[SQL 분석][Ch2] 분석지표 만들기

큰 지표에서 작은 지표로

총 거래액과 매출

▶ olist도 셀러가 있는 형태이기 때문에 매출은 수수료로 보는 것이 맞지만, 자사몰로 가정하고 판매된 총 금액(총 거래액)을 매출로 사용

---

분석지표

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

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 ( 쿼리 ) : 임시 테이블 만드는 명령어
  
  

테이블 사용 시 유의사항

• orders(주문 정보) > customer_id는 매 주문건마다 새로 생성됨 (고객 수 판단 불가)
  • 주문 테이블 내 주문건수 = 고객번호 수
  • customer_id가 달라도 같은 고객일 수 있음
  • 고객정보 테이블에 customer_unique_id 있음
• order_items(주문 상품 정보) > order_item_id는 주문건 내 동일 상품이 있는 경우 증가
  • order_item_id를 모두 더하면 안된다 > 각각 행을 count
• 별칭 등 컬럼명을 한글로 할 경우 백틱(``)으로 묶어주기
  
  
• 주문건수 = 고유 order_id 수 세기
• 총 판매 상품 수 = 판매 품목(order_item_id) 수 세기
• 매출 = 판매 가격 모두 더하기

---

원하는 지표 합치기

• 주문당 정보를 나타내는 테이블 생성 후 단계별로 진행

step 1) 주문 1건당 주문금액, 판매 상품 수 구하기

/* 1) 주문 1건당 주문금액, 판매 상품의 수 구하기 */
WITH tb AS (
  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
)

step 2) 주문 정보 테이블에 1번 결과와 고객 unique ID 붙이기

tb 테이블과 ord 테이블을 inner join 하는 이유
: olist.olist_orders 테이블에서 주문 미완료 or 주문이 취소되는 경우 olist.olist_order_items 테이블에 정보가 없는 경우 有

/* 2) 주문 정보 테이블에 1번 결과와 고객 unique ID 붙이기 (=> 테이블 조인 2번)*/
, base AS (   -- 임시 테이블 여러 개 이어서 사용 가능
  SELECT
    ord.order_id,
    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
)

step 3) 2번 결과 집계

/* 3) 2번 결과 집계 */
SELECT
  SUM(ord_amt) AS `총매출`,
  COUNT(DISTINCT order_id) AS `총주문수`,
  SUM(prd_cnt) AS `총판매상품수`,
  SUM(ord_amt) / COUNT(DISTINCT order_id) AS `주문당평균가격`,
  SUM(prd_cnt) / COUNT(DISTINCT order_id) AS `평균판매상품수`,
  SUM(ord_amt) / SUM(prd_cnt) AS `제품개당평균가격`,
  COUNT(DISTINCT customer_unique_id) AS `주문고객수`,
  COUNT(DISTINCT order_id) / COUNT(DISTINCT customer_unique_id) AS `주문빈도`
FROM base
;

쿼리 결과