[제로베이스 데이터 분석 취업 스쿨]Day21 SQL 분석5

종합 쿼리

아래와 같은 결과를 쿼리로 만든다. 

• WITH Table  AS -> 임시 테이블로 만들기
• 이어주려면 , TABLE AS 를 사용한다.

 

/* 첫 번째 : 주문 1건당 주문금액, 판매상품의 수를 구합니다. */
WITH tb AS(
SELECT
  item.order_id,
  SUM(item.price) AS ord_amt,
  COUNT(item.order_item_id) AS prd_cnt
FROM `olidy.olist_order_items` AS item
GROUP BY item.order_id
),

/*두 번째 : 주문 정보를 테이블에 1번 결과와, 고객 unique ID를 붙입니다.*/
base AS(
  SELECT
    ord.order_id,
    ord.customer_id,
    cust.customer_unique_id,
    tb.ord_amt,
    tb.prd_cnt,
  FROM `olidy.olist_orders` AS ord
  LEFT JOIN `olidy.olist_customers` AS cust
    ON ord.customer_id = cust.customer_id
  INNER JOIN tb
    ON ord.order_id = tb.order_id
)

/* 세 번째 : 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

 

날짜 다루기 실습 

 

날짜별로 지표 뽑기

 

Extract/Truc

• EXTRACT : 추출하는 함수이다.
• TRUNC : 숫자를 절삭할 때 사용하는 함수이다.
• TIMESTAMP_TRUNC : BigQuery에서 시간 추출할 떄 주로 사용하는 함수이다. 연도와 시간만 가져온다.

 

대시보드용 데이터 준비