스터디노트 26일차

olist 테이블을 활용해서 실습한 것

Select
COUNT(DISTINCT order_id) AS ord_cnt, ---------전체 주문 건수
SUM(price) AS ord_amt, ------주문 금액 총합
SUM(price) / COUNT(DISTINCT order_id) AS amout_per_order ------주문 금액 총합 / 주문건수
FROM olist.olist_order_items
;

/ 테이블 조인하기 /
SELECT
ord.order_id,
ord.customer_id,
cust.customer_unique_id
FROM olist.olist_orders AS ord
LEFT JOIN olist.olist_customers AS cust
ON ord.customer_id = cust.customer_id
;

/ 주문 고객 unique 세기 /
SELECT
COUNT(DISTINCT ord.order_id) AS ord_cnt, --주문 건수 세기
COUNT(DISTINCT ord.customer_id) AS cust_cnt, --주문 건의 고객 id 세기( 주문 건수와 같음 )
COUNT(DISTINCT cust.customer_unique_id) AS unique_cust_cnt -- 고객 고유 id 세기
FROM olist.olist_orders AS ord -- 주문 정보 테이블
LEFT JOIN olist.olist_customers AS cust -- 고객 정보 테이블
ON ord.customer_id = cust.customer_id
;
/ 주문빈도 조회 (주문 건수 / 고객 고유 id) /
SELECT
COUNT(DISTINCT ord.order_id) AS ord_cnt, --주문 건수 세기
COUNT(DISTINCT cust.customer_unique_id) AS unique_cust_cnt, -- 고객 고유 id 세기
COUNT(DISTINCT ord.order_id) / COUNT(DISTINCT cust.customer_unique_id) AS frequency --주문 빈도
FROM olist.olist_orders AS ord
LEFT JOIN olist.olist_customers AS cust
ON ord.customer_id = cust.customer_id
;

---

/ 주문 당 평균가격 , 주문 당 평균 판매 상품수 , 상품 평균 가격 /

SELECT -- 총매출 , 주문건수 집계
SUM(price) AS 총 매출 ,
COUNT(DISTINCT order_id) AS 총 주문수 ,
SUM(price) / COUNT(DISTINCT order_id) AS 주문당 평균가격, -- 주문 당 평균 가격 ( 총매출 / 주문 건수 )
FROM olist.olist_order_items AS ord
;

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

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

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
;

/ 합치기 /
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, ------주문당평균가격 = 평균판매상품수 제품개당평균가격
tb.총 판매 상품수,
tb.평균판매상품수 tb.총 주문수 AS check2, -------총 판매 상품수 = 평균판매상품수 주문건수
tb.총 매출,
tb.제품개당평균가격 tb.총 판매 상품수 AS check3, -------- 총 매출 = 제품개당평균가격 총 판매상품수
tb.총 매출,
tb.주문당평균가격 tb.총 주문수 AS check4 ---------총 매출 = 주문당평균가격 주문건수
FROM tb
;

---

/ 첫번째 - 주문 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 --집계 기준 : 주문번호 (주문번호는 모든 테이블 동일함 그래서 기준으로 사용)
)

/ 두번째 - 주문 정보 테이블에 1번 결과와 , 고객 Unique ID를 붙인다. /
, base AS(
SELECT
ord.order_id,
ord.order_status,
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
)

/ 세번쨰 - 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
;

---

-- DATE() / DATETIME() / TIMESTAMP()
SELECT
order_purchase_timestamp, -- 2017-11-25 11:10:33 UTC // 형태

DATE(order_purchase_timestamp) as ord_date, -- 결과 : 2017-11-25

DATETIME(order_purchase_timestamp) as ord_dt, -- 결과 : 2017-11-25T11:10:33

TIMESTAMP(order_purchase_timestamp) as ord_ts, -- 결과 : 2017-11-25 11:10:33 UTC
TIMESTAMP(DATE(order_purchase_timestamp)) as ord_date_ts, -- 결과 : 2017-11-25 00:00:00 UTC // DATE로 한번 묶어서 2017-11-25만 남고 거기에 TIMESTAMP를 한것이라서 뒤에는 모두 00:00:00이 된다.

TIME(order_purchase_timestamp) as ord_time -- 결과 : 11:10:33
FROM olist.olist_orders
;

---

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

SELECT
order_purchase_timestamp, --------뽑아낸 결과물중 하나 : 2017-11-26 16:13:13 UTC
EXTRACT(YEAR FROM order_purchase_timestamp) AS YEAR, -- 결과 : 2017
EXTRACT(QUARTER FROM order_purchase_timestamp) AS QUARTER, --결과 : 4
EXTRACT(MONTH FROM order_purchase_timestamp) AS MONTH, -- 결과 : 11
EXTRACT(DAY FROM order_purchase_timestamp) AS DAY, -- 결과 : 26
EXTRACT(DAYOFWEEK FROM order_purchase_timestamp) AS DAYOFWEEK, --결과 : 1
EXTRACT(WEEK FROM order_purchase_timestamp) AS WEEK, -- 결과 : 48
EXTRACT(WEEK(MONDAY)FROM order_purchase_timestamp) AS WEEK_MONDAY, --결과 : 47
EXTRACT(HOUR FROM order_purchase_timestamp) AS HOUR, -- 결과 : 16
EXTRACT(MINUTE FROM order_purchase_timestamp) AS MINUTE, -- 결과 : 13
EXTRACT(SECOND FROM order_purchase_timestamp) AS SECOND, -- 결과 13
FROM olist.olist_orders
WHERE
DATE(order_purchase_timestamp) = '2017-11-26'--일요일
;

---

-- 원하는 정보까지만 남기는 DATE_TRUNC('날짜','남기는 부분'), DATETIME_TRUNC('날짜','남기는 부분') , TIMESTAMP_TRUNC('날짜','남기는 부분')

SELECT
order_purchase_timestamp, --------뽑아낸 결과물중 하나 : 2017-11-26 16:13:13 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, YEAR) AS YEAR, -- 결과 : 2017-01-01 00:00:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp,QUARTER) AS QUARTER, --결과 : 2017-10-01 00:00:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, MONTH) AS MONTH, -- 결과 : 2017-11-01 00:00:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, DAY) AS DAY, -- 결과 : 2017-11-26 00:00:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK) AS WEEK, -- 결과 : 2017-11-26 00:00:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK(MONDAY)) AS WEEK_MONDAY, --결과 : 2017-11-20 00:00:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, HOUR) AS HOUR, -- 결과 : 2017-11-26 16:00:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, MINUTE) AS MINUTE, -- 결과 : 2017-11-26 16:13:00 UTC
TIMESTAMP_TRUNC(order_purchase_timestamp, SECOND) AS SECOND, -- 결과 : 2017-11-26 16:13:13 UTC
FROM olist.olist_orders
WHERE
DATE(order_purchase_timestamp) = '2017-11-26'--일요일
;

---

/ 첫번째 - 주문 1건당 주문금액 , 판매상품의 수를 구한다 EXTRACT 와 TRUNC 사용해서 응용 /

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 --집계 기준 : 주문번호 (주문번호는 모든 테이블 동일함 그래서 기준으로 사용)
)

/ 두번째 - 주문 정보 테이블에 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 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
)

/ 세번쨰 - 2번 결과를 집계한다. /

SELECT
ord_month_t,
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
GROUP BY ord_month_t
ORDER BY ord_month_t
;

---

SELECT *
FROM olist.olist_order_items
LIMIT 100
;

--EXCEPT

SELECT * EXCEPT(shipping_limit_date ,freight_value)
FROM olist.olist_order_items
LIMIT 100
;

--REPLACE
-- price에 * 10,000을 해주고 싶어요

SELECT REPLACE(price 10000 AS price)
FROM olist.olist_order_items
LIMIT 100
;

---

-- CAST & SAFE_CAST

SELECT
CAST('12345' AS INT64) AS cast_result,
SAFE_CAST('12345' AS INT64) AS safe_result
;

SELECT
CAST('asdf' AS INT64) AS safe_result
;

SELECT
SAFE_CAST('asdf' AS INT64) AS safe_result -- asdf는 문자라서 cast로 바꾸면 에러가 나지만 , safe_cast로 바꾸면 null로 바뀜
;

--DIVIDE & SAFE_DIVIDE

SELECT
SAFE_DIVIDE(5 , 0) -- 5/0은 에러가 나지만 safe_divide의 결과는 null로 표시됨
;

SELECT
SAFE_DIVIDE(5 , 0),
IFNULL(SAFE_DIVIDE(5,0),0) -- IFNULL은 null이면 특정값으로 바꿔주는 명령어인데 SAFE_DIVIDE로 null값으로 변경되서 특정값으로 바꼇다.
;

---

/
1. IN을 활용해 특정 상태에서만 집계 ( 'shipped' , 'delivered')
2. 일자별 지표
3. ROUND를 활용해서 소수 둘째 자리까지만 표시하고 , SAFE연산사용
4. 컬럼명 영문으로 대체
/

/ 첫번째 - 주문 1건당 주문금액 , 판매상품의 수를 구한다 EXTRACT 와 TRUNC 사용해서 응용 /

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 --집계 기준 : 주문번호 (주문번호는 모든 테이블 동일함 그래서 기준으로 사용)
)

/ 두번째 - 주문 정보 테이블에 1번 결과와 , 고객 Unique ID를 붙인다. /
, base AS(
SELECT

DATE(ord.order_approved_at) as ord_date,

ord.order_id,
ord.order_status,
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
WHERE 1=1 -- 1=1 은 그냥 TRUE라는 뜻이다. 뒤에 AND를 쭉 이어가기 용이해서 썻음
AND order_status IN ('delivered', 'shipped')
AND order_approved_at IS NOT NULL
)

/ 세번쨰 - 2번 결과를 집계한다. /

SELECT
ord_date,
ROUND(SUM(ord_amt), 2) AS ord_amt,
COUNT(DISTINCT order_id) AS ord_cnt,
SUM(prd_cnt) AS prd_cnt,
ROUND(IFNULL(SAFE_DIVIDE(SUM(ord_amt) , COUNT(DISTINCT order_id)),0), 2) AS avg_ord_amt,
ROUND(IFNULL(SAFE_DIVIDE(SUM(prd_cnt) , COUNT(DISTINCT order_id)),0), 2) AS avg_prd_cnt,
ROUND(IFNULL(SAFE_DIVIDE(SUM(ord_amt) , SUM(prd_cnt)),0), 2) AS avg_price,
COUNT(DISTINCT customer_unique_id) AS cust_cnt,
ROUND(IFNULL(SAFE_DIVIDE(COUNT(DISTINCT order_id) , COUNT(DISTINCT customer_unique_id)),0), 2) AS cust_freq
FROM base
GROUP BY ord_date
ORDER BY ord_date
;