문제

1. 총 주문수, 주문고객수(unique), 주문빈도 구하기

코드 1:

select
  count(distinct ord.order_id) as ord_cnt,
  count(distinct cust.customer_unique_id) as cust_cnt,
  SAFE_DIVIDE(count(distinct ord.order_id),count(distinct cust.customer_unique_id)) as frequency
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
inner join zerobase-olist-440106.olist.olist_order_items as item
on ord.order_id = item.order_id;

코드 2:

select
  count(distinct ord.order_id) as ord_cnt,
  count(distinct cust.customer_unique_id) as cust_cnt,
  SAFE_DIVIDE(count(distinct ord.order_id),count(distinct cust.customer_unique_id)) as frequency
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id;

위처럼 inner join의 유무로 2가지 코드를 써봤음.
코드 1은 order_items 테이블과 inner join을 하므로 실제 구매가 발생한 주문에 한하여 데이터를 수집함
코드 2는 전체 주문에 대하여 데이터를 수집함

(만약 취소된 주문이나 구매가 완료되지 않은 주문도 빈도 계산에 포함하려면 2번 코드를, 실제 구매 데이터에만 집중하고 싶다면 1번 코드를 사용하기)

2. 1번 문제를 주차별로 집계하기

내 코드

with result as (select
  TIMESTAMP_TRUNC(ord.order_approved_at, WEEK(MONDAY)) as ord_week,
  count(distinct ord.order_id) as ord_cnt,
  count(distinct cust.customer_unique_id) as cust_cnt,
  SAFE_DIVIDE(count(distinct ord.order_id),count(distinct cust.customer_unique_id)) as frequency
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
group by TIMESTAMP_TRUNC(ord.order_approved_at, WEEK(MONDAY)))

select
  result.ord_week,
  EXTRACT(WEEK(mONDAY) from result.ord_week) as week,
  result.ord_cnt,
  result.cust_cnt,
  result.frequency
from result
order by result.ord_week;

3. 날짜 정보 없이 총 합계 구하기

내 코드

with result as (select
  TIMESTAMP_TRUNC(ord.order_approved_at, WEEK(MONDAY)) as ord_week,
  count(distinct ord.order_id) as ord_cnt,
  count(distinct cust.customer_unique_id) as cust_cnt,
  SAFE_DIVIDE(count(distinct ord.order_id),count(distinct cust.customer_unique_id)) as frequency
from zerobase-olist-440106.olist.olist_orders as ord
left join zerobase-olist-440106.olist.olist_customers as cust
on ord.customer_id = cust.customer_id
group by TIMESTAMP_TRUNC(ord.order_approved_at, WEEK(MONDAY)))

, tb as(
select
  result.ord_week,
  EXTRACT(WEEK(mONDAY) from result.ord_week) as week,
  result.ord_cnt as ord_cnt,
  result.cust_cnt  as cust_cnt,
  result.frequency as freq
from result
order by result.ord_week)

select
  sum(tb.ord_cnt) as total_ord_cnt,
  sum(tb.cust_cnt) as total_cust_cnt,
  sum(tb.freq) as total_frequency
from tb

4. 1번과 3번의 결과 비교하기

값이 다름!!!

종합 지표와 주차별 집계 후 합산한 지표 값이 다른 이유는?

예를 들어서, 철수가 1월 첫째주에도 주문하고, 둘째주에도 주문했다고 가정하자

전체적으로 보면 철수 한 명이 주문한 것이지만,
주차별로 쪼개어 보면 첫째주=1명, 둘째주=1명, 총합=1+1=2명으로 중복되어 합산되기 때문!!!