D-36-SQL분석-BigQuery

1. 날짜
   

2. 날짜 일부분 추출
   
   
   

3. 날짜에서 원하는 정보까지만 남기기
   
   

select
 order_purchase_timestamp,
  extract(year FROM order_purchase_timestamp) as year,
  timestamp_trunc(order_purchase_timestamp, year) as year2,

  extract(month FROM order_purchase_timestamp) as month,
  timestamp_trunc(order_purchase_timestamp, month) as month2,

from`olist.olist_orders`
;

->

4. EXCEPT / REPLACE
   
   
   

5. CAST / SAFE_CAST
   
   
   

-- divide & safe_divide

select
 safe_divide(10,0),
 ifnull(safe_divide(10,0),0)
 ;

->

6. IN / NOT IN
   
   

7. 순위/행 번호 매기는법
   
   

8. 

9. CASE
   

/*
고객들의 월별 구입금액에 기반해서 그룹을 나누고,
각 그룹별 비중이 변화하는지/유지되는지를 확인하려고 합니다.
월 – 고객 unique ID – 해당 월의 구매금액 – 그룹 순서로 보여주세요.
300 BRL 이상은 A, 150 BRL 이상 300 BRL 미만은 B, 그 외 C입니다.
*/

SELECT
  DATE_TRUNC(DATE(ord.order_approved_at), MONTH) AS ord_month, 
  cust.customer_unique_id, 
  SUM(item.price) AS ord_amt, 
  CASE WHEN SUM(item.price) >= 300 THEN 'A' 
    WHEN SUM(item.price) >= 150 AND SUM(item.price) < 300 THEN 'B'
    ELSE 'C' END AS level
FROM `olist.olist_orders` AS ord
INNER JOIN `olist.olist_order_items` AS item
  ON ord.order_id = item.order_id 
LEFT JOIN `olist.olist_customers` AS cust
  ON ord.customer_id = cust.customer_id
WHERE 1=1 
  AND order_approved_at IS NOT NULL 
GROUP BY 1, 2
ORDER BY ord_month ASC, ord_amt DESC
;