[Ch2] 빅쿼리에서 날짜 다루기

DATE() / DATETIME() / TIMESTAMP() / TIME()

---

• TIMESTAMP()에서는 시간대 설정 가능 (참고. 한국 시간대 : ‘Asia/Seoul’)

-- DATE() / DATETIME() / TIMESTAMP()

SELECT
  order_purchase_timestamp,

  DATE(order_purchase_timestamp) ord_date,

  DATETIME(order_purchase_timestamp) ord_dt,

  TIMESTAMP(order_purchase_timestamp) ord_ts,
  TIMESTAMP(DATE(order_purchase_timestamp)) ord_date_ts,

  TIME(order_purchase_timestamp) ord_time
FROM `olist.olist_orders`
;

EXTRACT

---

• 날짜에서 일부분만 추출하고 싶을 때
• EXTRACT(‘추출할 부분’ FROM ‘날짜 컬럼’)
  

-- 필요한 값만 추출하는 EXTRACT

SELECT
    order_purchase_timestamp,
    EXTRACT(YEAR FROM order_purchase_timestamp) AS YEAR,
    EXTRACT(QUARTER FROM order_purchase_timestamp) AS QUARTER,
    EXTRACT(MONTH FROM order_purchase_timestamp) AS MONTH,
    EXTRACT(DAY FROM order_purchase_timestamp) AS DAY,
    EXTRACT(DAYOFWEEK FROM order_purchase_timestamp) AS DAYOFWEEK,
    EXTRACT(WEEK FROM order_purchase_timestamp) AS WEEK,
    EXTRACT(WEEK(MONDAY) FROM order_purchase_timestamp) AS WEEK_MONDAY,
    EXTRACT(HOUR FROM order_purchase_timestamp) AS HOUR,
    EXTRACT(MINUTE FROM order_purchase_timestamp) AS MINUTE,
    EXTRACT(SECOND FROM order_purchase_timestamp) AS SECOND
FROM
    `olist.olist_orders`
WHERE
    DATE(order_purchase_timestamp) = '2017-11-26' -- 일요일
;

TRUNC

---

• DATE_TRUNC(‘날짜’, ‘남기는 부분’)
• DATETIME_TRUNC(‘날짜’, ‘남기는 부분’)
• TIMESTAMP_TRUNC(‘날짜’, ‘남기는 부분’)
  

-- 원하는 정밀까지만 남기는 DATE/DATETIME/TIMESTAMP_TRUNC

SELECT
    order_purchase_timestamp,
    TIMESTAMP_TRUNC(order_purchase_timestamp, YEAR) AS YEAR,
    TIMESTAMP_TRUNC(order_purchase_timestamp, QUARTER) AS QUARTER,
    TIMESTAMP_TRUNC(order_purchase_timestamp, MONTH) AS MONTH,
    TIMESTAMP_TRUNC(order_purchase_timestamp, DAY) AS DAY,
    TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK) AS WEEK,
    TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK(MONDAY)) AS WEEK_MONDAY,
    TIMESTAMP_TRUNC(order_purchase_timestamp, HOUR) AS HOUR,
    TIMESTAMP_TRUNC(order_purchase_timestamp, MINUTE) AS MINUTE,
    TIMESTAMP_TRUNC(order_purchase_timestamp, SECOND) AS SECOND
FROM `olist.olist_orders`
WHERE
    DATE(order_purchase_timestamp) = '2017-11-26' -- 일요일
;

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`
WHERE
    DATE(order_purchase_timestamp) = '2017-11-26' -- 일요일
;

종합쿼리에 날짜추가

• 주문당 정보를 나타내는 테이블 만드는 쿼리에 날짜추가

/* 첫 번째 : 주문 1건당 주문금액, 판매상품의 수를 구합니다 */

WITH tb AS (
  SELECT
    item.order_id,
    SUM(item.price) AS ord_amt,
    COUNT(DISTINCT item.order_item_id) AS prd_cnt
  FROM `olist.olist_order_items` AS item
  GROUP BY item.order_id
)
/* 두 번째 : 주문 정보 테이블에 1번 결과와, 고객 unique ID를 붙입니다 */
, base AS (
  SELECT 
    ord.order_approved_at,
    DATE(ord.order_approved_at) as ord_date,
    DATETIME(ord.order_approved_at) as ord_dt,
    TIMESTAMP(ord.order_approved_at) as ord_ts,

    -- Extract / Trunc
    -- EXTRACT
    EXTRACT(YEAR FROM ord.order_approved_at) as ord_year,
    EXTRACT(MONTH FROM ord.order_approved_at) as ord_month,
    
    -- TRUNC
    TIMESTAMP_TRUNC(ord.order_approved_at, YEAR) as ord_year_t,
    TIMESTAMP_TRUNC(ord.order_approved_at, MONTH) as ord_month_t,

    ord.order_id,
    ord.order_status,
    ord.customer_id,
    cust.customer_unique_id,
    tb.ord_amt,
    tb.prd_cnt
  FROM `olist.olist_orders` ord
  LEFT JOIN `olist.olist_customers` cust
    ON ord.customer_id = cust.customer_id
  INNER JOIN tb
    ON ord.order_id = tb.order_id
)
/* 세 번째 : 2번 결과를 집계합니다 */

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

• GROUP BY 했을 때 NULL값이 있는 이유 : olist_orders 원본에서 일부 행의 날짜가 NULL값이기 때문!