[데이터리안 sql입문반 4주차 사전 과제] 탐색적 데이터 분석

https://datarian.io/blog/10x-data-analyst?utm_source=sql-camp&utm_medium=camp&utm_campaign=referral&utm_content=sql-basic 

매출 분석 (0) 10배 이상 뛰어난 데이터 분석가가 되는 법

https://medium.com/@_michaellin/how-to-be-a-10x-engineer-fdac2a5a1bd5

How to Be a 10x Software Engineer

In fact, 10x engineers might type at half the speed, work half the amount, and spend more time DELETING code rather than writing it.

 

They use the right tools, ask the right questions, and know how to prioritize.

---

 

https://solvesql.com/playground/

solvesql - 플레이그라운드

데이터셋: Brazilian E-Commerce Public Dataset by Olist

앞으로 아래 테이블들을 간략하게 줄여서 부르겠습니다.

• olist_orders_dataset → orders
• olist_order_payments_dataset → payments
• olist_customers_dataset → customers

 

데이터를 이리저리 뜯어보면서 olist 데이터셋 안에 어떤 테이블들이 들어있는지, 테이블 안에 어떤 값들이 들어있는지, 테이블들 간의 관계는 어떤지 둘러보는 시간을 가집시다. 본격적인 분석에 앞서 하는 이렇게 데이터를 탐색하는 작업을 '탐색적 데이터 분석'이라고 합니다.

 

• orders 테이블 안에 몇 일 치의 데이터가 들어있나요? order_purchase_timestamp 컬럼을 기준으로 데이터의 시작 시점과 끝 시점을 알려주세요.

SELECT MIN(order_purchase_timestamp) --2016-09-04
  ,MAX(order_purchase_timestamp) --2018-10-17
  ,COUNT(distinct order_purchase_timestamp) --98875
  ,COUNT(order_purchase_timestamp) --99441
FROM olist_orders_dataset

• 고객들은 어떤 주(state)에 살고 있나요? customers 테이블 안에 customer_state 컬럼을 참고하여 olist 고객들이 어떤 주에 살고 있는지 알아봅시다. 이 정보를 주문 데이터(orders)와 연결하려면 어떤 컬럼을 사용해야 하나요?

SELECT distinct customer_state
FROM olist_customers_dataset
LIMIT 10

1)  쿼리결과 > customer_state :  SP MG ES RJ RS BA CE PR MS PB SC MT PA RN PI DF GO PE RO MA SE AM AL TO AC AP RR

 

2) custmer 테이블과 orders 테이블 연결하기

→ 기준:  orders 테이블의 costomer_id 칼럼과 customers테이블의 customer_id 칼럼이 동일하다 (중복된 데이터는 없으며 총 99441개 )

 

- 여기서 근본적으로 드는 생각은 주문 데이터와 고객데이터를 결합하는 것의 효용이 무엇일까? 하는 것이다.

이를 위해 주문데이터와 고객데이터를 조금 더 면밀히 살펴보자.

<주문데이터>	<고객 데이터>
주문ID	주문 별 고객 ID
주문 별 고객 ID	고객 ID
주문상태	고객 우편번호 앞5자리
구매시각	고객 주소(도시)
주문승인시각	고객 주소(주)
배송시작시각
배송완료시각
배송예정시각

(1) 도시/주 별로 배송완료 시각과 배송 예정시각의 차이

(2) 도시/주 별 배송시각의 차이가 있는지

~ 거리에 따라 배송시간이 지연될 수도 있고, 배송시간이 오래 걸릴 수 있다. 

따라서 이에 따른 ①고객 만족도 관리 ②배송시간이 오래 걸리는 지역을 타겟으로 한 마케팅(ex. 배송료 프로모션 등) 을 기획 할 수 있다.

 

• 고객들의 결제 데이터는 어떤 테이블에 있나요? 주문 하나에 결제 데이터가 하나 붙어있는 1:1 관계인가요, 아니면 주문 하나에 결제 데이터가 여러 개 있을 수 있는 1:N 관계인가요?

→ 1:N의 관계, payment_sequential(연속 결제 횟수)를 확인할때 1~29까지 있는 것을 확인할 수 있다. 즉, 한번 결제할 때 연속 으로 29번까지 결제 했음을 알 수 있다.

SELECT  COUNT(distinct order_id) , COUNT(order_id)
FROM olist_order_payments_dataset

 

<결제 데이터>
[주문ID]

▷distint -> 99440

▷중복제거없음 -> 103886

[연속 결제 횟수]
[결제 방법]
[할부 개월 수]
[결제 금액]

 

----------------------------

<수업 시간에 배운 다른 쿼리>

SELECT order_id
  , COUNT(*) cnt -- 2번 이상 주문된 것들
FROM olist_order_payments_dataset p
GROUP BY order_id
HAVING cnt >=2

 

SELECT *
FROM olist_order_payments_dataset p
WHERE order_id = '0016dfedd97fc2950e388d2971d718c7'
ORDER BY payment_sequential

----------------------------

 

• 결제 금액은 어떤 컬럼을 보고 알 수 있나요? → payment_value
• orders 테이블과 결제 데이터는 어떤 컬럼을 기준으로 연결할 수 있나요? → order_id 칼럼 기준

 

• orders, customers, payments 테이블을 사용하여 2017년에 매출이 많이 나오는 주(customer_state)가 어디인지 알아봅시다. 2017년 매출 Top 3 주를 꼽아주세요.

→ WHERE YEAR(order_purchase_timestamp) = '2017'

→ GROUP BY customer_state

→ ORDER BY payments_value DESC / LIMIT 3

 

<orders> --- <payments> : order_id 기준

<orders> --- <customers> : customer_id 기준

 

SELECT c.customer_state
      ,SUM(p.payment_value) AS revenue
FROM olist_orders_dataset AS o
  INNER JOIN olist_order_payments_dataset AS p
      ON o.order_id = p.order_id
  INNER JOIN olist_customers_dataset AS c
      ON o.customer_id = c.customer_id
WHERE YEAR(o.order_purchase_timestamp) = '2017'
GROUP BY c.customer_state
ORDER BY p.payment_value DESC 
LIMIT 3

 

customer_state	payment_value
RJ	13664.08
MS	6929.31
SP	6726.66

여기서 의문인 것은 왜 c.customer_state로 GROUP BY 하고 sum(b.payment_value)로 하면 오류를 뜬다는 것?

주별로 2017년도에 여러번 주문을 했을 것이고 그룹별로 주문금액을 합계를 해야 2017년 총 매출을 구할 수 있을 것 같다.

 

 

<오류메세지>

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'olist.p.payment_value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

SELECT c.customer_state
      ,SUM(p.payment_value) AS revenue
FROM olist_orders_dataset  o
  INNER JOIN olist_order_payments_dataset p
      ON o.order_id = p.order_id
  INNER JOIN olist_customers_dataset c
      ON o.customer_id = c.customer_id
WHERE YEAR(o.order_purchase_timestamp) = '2017'
GROUP BY c.customer_state
ORDER BY revenue DESC --★ 
LIMIT 3

--★ GROUP BY 한 후의 출력을 SUM(payment)로 했으니 ORDER BY도 SUM(payment)를 해야한다.

 

-------------------------