D-35-SQL분석-BigQuery

1. 주문건당 평균가격

select
 SUM(price) as ord_amt, -- 총매출
 count(distinct order_id) as ord_cnt, -- 주문건수
 SUM(price)/count(distinct order_id) as amount_per_order -- 주문 1건당 평균 가격
from `olist.olist_order_items`
;

2. 주문빈도

select 
count(distinct ord.order_id) as `주문건수`, -- 주문건수세기
count(distinct cust.customer_unique_id) as `주문고객수`, -- 고객 고유 id 세기
count(distinct ord.order_id)/count(distinct cust.customer_unique_id) as `주문빈도`
from `olist.olist_orders` as ord
left join `olist.olist_customers` as cust
on ord.customer_id = cust.customer_id
;

3. 테이블 유의사항
   

4. 건당 주문 금액
   

5. 주문 건당 판매 상품 수
   

6. 상품 평균 가격
   

7. with

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 check_2,
tb.`총매출`,
tb.`제품개당평균가격` * tb.`총판매상품수` as check_3
from tb
;