25.02.16 Olist데이터 SQL 쿼리

Prologue

여러 개의 CSV로 되어 있는 Olist 데이터로 SQL 쿼리를 연습한 과정을 적고자 한다.

튜터님 파일 읽기

---

• dbeaver에서 sql 파일 가져오면 데이터베이스에 연결되지 않는 이슈

• use 써도 연결이 없다는 에러가 뜸
  

  

  

주문 상태별 주문 수 알기

-- 1. 기본 데이터: 오더 데이터셋의 주문 상태
SELECT order_status
FROM olist_orders_dataset;

-- 2. 주문 상태 열을 그룹화
SELECT order_status
FROM olist_orders_dataset
GROUP BY order_status;

-- 3. 그룹별 개수 세기
SELECT order_status, COUNT(*) as order_count
FROM olist_orders_dataset
GROUP BY order_status;

고가의 상품 조회

-- 1. 필요 데이터: 제품 가격, 제품 이름
SELECT product_id, price 
FROM olist_order_items_dataset ooid;

-- 2. 가격 필터링
SELECT product_id , price 
FROM olist_order_items_dataset ooid
WHERE price > 1000
ORDER BY price DESC;

-- 3. 정렬
SELECT order_id, product_id, price
FROM olist_order_items_dataset
WHERE price > 1000
ORDER BY price DESC;

배송 지연 주문 확인

-- 배송 지연 주문을 확인하고 싶다
-- 실제 배송일이 예상 배송일보다 늦은 주문만 필터링!!
-- 1. 기본 데이터: 실제 및 예상 배송일 
SELECT order_id,
       order_estimated_delivery_date,
       order_delivered_customer_date
FROM olist_orders_dataset;


-- 2. 지연 배송 필터링
SELECT order_id,
       order_estimated_delivery_date,
       order_delivered_customer_date
FROM olist_orders_dataset
WHERE order_delivered_customer_date > order_estimated_delivery_date;

고객별 주문 금액 확인

-- 필요 데이터: 고객, 주문금액

SELECT c.customer_unique_id, c.customer_city, c.customer_state
FROM olist_customers_dataset c

-- 고객별 결제 금액
SELECT 
    ocd.customer_id
    ,ocd.customer_city
    ,ocd.customer_state
    ,sum(oopd.payment_value) AS total_payment 
FROM olist_customers_dataset ocd 
JOIN olist_orders_dataset ood ON ood.customer_id = ocd.customer_id 
JOIN olist_order_payments_dataset oopd  ON oopd.order_id = ood.order_id
GROUP BY ocd.customer_id , ocd.customer_city , ocd.customer_state;

-- 가장 결제금액이 높은 고객들
SELECT 
    ocd.customer_id
    ,ocd.customer_city
    ,ocd.customer_state
    ,sum(oopd.payment_value) AS total_payment 
FROM olist_customers_dataset ocd 
JOIN olist_orders_dataset ood ON ood.customer_id = ocd.customer_id 
JOIN olist_order_payments_dataset oopd  ON oopd.order_id = ood.order_id
GROUP BY ocd.customer_id , ocd.customer_city , ocd.customer_state
ORDER BY total_payment desc;

그룹으로 묶을 때 다른 테이블의 고유열이 있으면 안된다.

-- 오류 난 고객별 결제 금액
SELECT 
    ocd.customer_id
    ,ocd.customer_city
    ,ocd.customer_state
    ,ood.order_id #다른 테이블의 열이 있음 
FROM olist_customers_dataset ocd 
JOIN olist_orders_dataset ood ON ood.customer_id = ocd.customer_id 
JOIN olist_order_payments_dataset oopd  ON oopd.order_id = ood.order_id
GROUP BY ocd.customer_id , ocd.customer_city , ocd.customer_state;

카테고리별 리뷰 점수 평균과 리뷰 수

필요 데이터: 카테고리, 리뷰 평균, 리뷰 수

-- 카테고리 정보 연결
SELECT pt.product_category_name_english, p.product_id
FROM olist_products_dataset p
JOIN product_category_name_translation pt
    ON p.product_category_name = pt.product_category_name;

-- 리뷰 정보 연결(연결을 위한 오더 정보 연결) 및 평균 점수와 개수 집계
SELECT 
    pcnt.product_category_name_english 
    ,count(oord.review_id) cnt_review
    ,avg(oord.review_score) avg_review_score
FROM olist_products_dataset opd 
JOIN product_category_name_translation pcnt 
    ON opd.product_category_name = pcnt.product_category_name 
JOIN olist_order_items_dataset ooid 
    ON ooid.product_id = opd.product_id 
JOIN olist_order_reviews_dataset oord 
    ON oord.order_id = ooid.order_id
GROUP BY pcnt.product_category_name_english
HAVING cnt_review > 10
ORDER BY cnt_review DESC, avg_review_score DESC;

코딩문제

각 도시별로 평균 주문금액보다 높은 주문을 한 고객 조회

-- 필요 데이터: 고객, 도시, 주문금액
-- 고객, 도시
SELECT *
FROM olist_customers_dataset ocd 

-- 주문금액
SELECT *
FROM olist_order_payments_dataset oopd 

-- 연결을 위한 테이블
SELECT *
FROM olist_orders_dataset ood 

-- 주문-오더 연결
SELECT *
FROM olist_customers_dataset ocd 
JOIN olist_orders_dataset ood 
    ON ood.customer_id = ocd.customer_id 
JOIN olist_order_payments_dataset oopd
    ON oopd.order_id = ood.order_id;

-- 도시별 평균 주문금액 계산

SELECT 
    ocd.customer_city
    ,avg(oopd.payment_value) avg_payment
FROM olist_customers_dataset ocd 
JOIN olist_orders_dataset ood
    ON ood.customer_id = ocd.customer_id 
JOIN olist_order_payments_dataset oopd
    ON oopd.order_id = ood.order_id
GROUP BY ocd.customer_city ;


-- 고객의 모든 결제금액 집계
-- 고객 모든 합, 도시 평균 정보 조인
-- 모두 조인한 테이블에 비교 조건 걸기
SELECT 
    a.customer_id
    ,a.customer_city 
    ,a.avg_cus_payment
    ,a.avg_city_payment
FROM (
SELECT 
    ocd.customer_id
    ,ocd.customer_city 
    ,SUM(oopd.payment_value) avg_cus_payment
    ,c.avg_city_payment
FROM olist_customers_dataset ocd 
JOIN olist_orders_dataset ood
    ON ood.customer_id = ocd.customer_id 
JOIN olist_order_payments_dataset oopd
    ON oopd.order_id = ood.order_id
JOIN (SELECT 
    ocd.customer_city
    ,avg(oopd.payment_value) avg_city_payment
FROM olist_customers_dataset ocd 
JOIN olist_orders_dataset ood
    ON ood.customer_id = ocd.customer_id 
JOIN olist_order_payments_dataset oopd
    ON oopd.order_id = ood.order_id
GROUP BY ocd.customer_city) c
    ON c.customer_city = ocd.customer_city 
GROUP BY ocd.customer_id, ocd.customer_city # city 정보도 포함하기 위해서
) a
WHERE avg_cus_payment > avg_city_payment 

같은 도시에 사는 고객의 쌍 만들기

-- 기본 데이터: 고객, 도시
SELECT *
FROM olist_customers_dataset ocd

-- 도시가 같은데, 고객이 다른 데이터 연결
SELECT ocd.customer_id, ocd2.customer_id , ocd.customer_city
FROM olist_customers_dataset ocd 
JOIN olist_customers_dataset ocd2 
ON ocd.customer_city = ocd2.customer_city 
AND ocd.customer_id <> ocd2.customer_id 

각 도시별로 가장 많은 주문을 한 고객 3명 조회

질문 dense rank 안의 파티션 기준 열이
group by 안에 있어야 하나? 윈도우 함수는 따로 진행되는 것 아닌가?

dense_rank() over (partition by x order by y)
: 공동 순위가 있을 때도 차례 대로 1,2,3.. 순위를 매겨주는 함수

rank() 함수는 공동 1등이 2명 있다면
1, 1, 3, 4 로 순위를 매겨줌

row_number() 함수는 공동 순위가 있어도 무시하고
차례대로 1,2,3,4로 매긴다.