SQL 실습 01- 지표출력

/* 하나씩 구해보기 */
SELECT
  SUM (price) AS `총 매출`,
  COUNT (DISTINCT order_id) AS `총 주문수`,
  SUM (price) / COUNT (DISTINCT order_id) AS `주문당평균가격`
FROM `olist.olist_order_items`
;

SELECT
  COUNT (DISTINCT order_item_id) AS `총 판매상품수`,
  COUNT (DISTINCT order_id) AS `총 주문수`,
  COUNT (DISTINCT order_item_id) / COUNT (DISTINCT order_id) AS `평균판매상품수`
FROM `olist.olist_order_items`
;

SELECT
  SUM (price) AS `총 매출`,
  COUNT (order_item_id) AS `총 판매상품수`,
  SUM (price) / COUNT (order_item_id) AS `제품개당평균가격`
FROM `olist.olist_order_items`
;

/* 합치기 */
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`
)
SELECT
  tb.`주문당평균가격`,
  tb.`평균판매상품수` * tb.`제품개당평균가격` AS Check_1,
  tb.`총 판매상품수`,
  tb.`평균판매상품수` * tb.`총 주문수` AS Check_2,
  tb.`총 매출`,
  tb.`제품개당평균가격` * `총 판매상품수` AS Check_3
FROM tb
;

/* 1건당 주문금액, 판매상품수 */
WITH tb AS (
SELECT
  item.order_id,
  SUM (item.price) AS ord_price,
  COUNT (item.order_item_id) AS num_of_prod
FROM `olist.olist_order_items` AS item
GROUP BY item.order_id
)

/* unique customer id 붙인 후 합치기 */
SELECT 
  ord.order_id,
  ord.customer_id,
  cust.customer_unique_id,
  tb.ord_price,
  tb.num_of_prod
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
;

집계하기 편한 임시 table 만들어서 지표 구하기

/* 1건당 주문금액, 판매상품수 */
WITH tb AS (
  SELECT
    item.order_id,
    SUM (item.price) AS ord_price,
    COUNT (item.order_item_id) AS num_of_prod
  FROM `olist.olist_order_items` AS item
  GROUP BY item.order_id
)

, base AS (
  /* unique customer id 붙인 후 합치기 */
  SELECT 
    ord.order_id,
    ord.customer_id,
    cust.customer_unique_id,
    tb.ord_price,
    tb.num_of_prod
  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
)

SELECT
  SUM (ord_price) AS `총매출`,
  COUNT (order_id) AS `총주문수`,
  SUM (num_of_prod) AS `총판매상품수`,
  SUM (ord_price) / COUNT (order_id) AS `주문당평균가격`,
  SUM (num_of_prod) / COUNT (order_id) AS `평균판매상품수`,
  SUM (ord_price) / SUM (num_of_prod) AS `제품개당평균가격`,
  COUNT (DISTINCT customer_unique_id) AS `주문고객수`,
  COUNT (order_id) / COUNT (DISTINCT customer_unique_id) AS `주문빈도`
FROM base
;

이 글은 제로베이스 데이터 분석 취업 스쿨의 강의자료 일부를 발췌하여 작성되었습니다.