[MySQL] SQL로 맛보는 데이터 전처리 분석(4-2)

본 내용은 [멀티캠퍼스] 데이터 분석&데이터 엔지니어링 취업캠프 28회차에서 실시한 수업 내용 중 일부입니다.

 

교재 홍보 (본 과정의 SQL 교재)

교재 구매: https://www.yes24.com/Product/Goods/86544423

SQL로 맛보는 데이터 전처리 분석 - 예스24

 

이전 포스팅: https://hsyhrae.tistory.com/35

[MySQL] SQL로 맛보는 데이터 전처리 분석(4-1)

 

ERD

위 3개 테이블에 주목

 

가정된 상황: 회사 업무 중 요청된 사항 처리하기

 

그룹별 구매 지표 구하기

 

요청사항1: 국가별, 도시별 매출액

국가 데이터는 customers 테이블 country컬럼이고,

도시 데이터는 customers 테이블 city컬럼이다,

매출액 데이터는 orderdetails 테이블의 quantityordered와 priceeach 컬럼을 참고하면 된다.

orderdetails테이블과 cutomers테이블을 직접 연결할 수 없어 orders 테이블을 활용하여 3테이블을 조인한다.

 

본격적인 쿼리문을 작성하는 데에 앞서 조인문을 작성하여 확인

SELECT *
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT 
JOIN customers C
ON A.customernumber = C.customernumber
;

GROUP BY를 활용하여 국가별, 도시별 매출액 조회

SELECT 
    C.country
    , C.city
    , SUM(B.priceeach * B.quantityordered) AS 매출액
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT 
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1, 2 
;

 

ORDER BY를 활용하여 가독성 있게 구조화

SELECT 
    C.country
    , C.city
    , SUM(B.priceeach * B.quantityordered) AS 매출액
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT 
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1, 2 
ORDER BY 1, 2
;

 

요청사항2: 북미 VS 비북미 매출액 비교

국가 데이터 중에서 북미 국가는 USA, Canada가 있고, 나머지는 전부 비북미 국가이다.

이를 구분하기 위해서 필요한 것은 CASE WHEN 구문이다. 

 

우선, 북미, 비북미 국가로 나누어서 조회해보자.

SELECT 
    CASE WHEN country IN ('USA', 'Canada') THEN '북미' 
    ELSE '비북미' END country_grp
    , country
FROM customers;

 

이전의 국가별, 도시별 매출액 쿼리문에서 ORDER BY절만 약간 수정했다.

매출액 순서로 조회된다. 이 쿼리를 살짝 수정하면 북미, 비북미 매출액을 조회할 수 있다.

SELECT 
    C.COUNTRY
    , C.CITY
    , SUM(B.priceeach * B.quantityordered) 매출액
FROM orders A
LEFT 
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1, 2
ORDER BY 3 DESC
;

 

country, city 컬럼 대신 CASE WHEN 구문을 활용하여 북미, 비북미 국가 매출액을 조회

SELECT 
    CASE WHEN C.country IN ('USA', 'Canada') THEN '북미'
    ELSE '비북미' END country_grp
    , SUM(B.priceeach * B.quantityordered) 매출액
FROM orders A
LEFT 
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1
ORDER BY 2 DESC
;

 

요청사항3: 매출 Top5 국가 및 매출

윈도우 순위 함수를 사용하여 매출 상위 국가 확인

윈도우 순위 함수 사용

SELECT 
    quantityordered
    , RANK() OVER(ORDER BY quantityordered DESC) AS 'RANK'
    , DENSE_RANK() OVER(ORDER BY quantityordered DESC) AS 'DENSE RANK'
    , ROW_NUMBER() OVER(ORDER BY quantityordered DESC) AS 'ROW_NUMBER'
FROM orderdetails;

윈도우 순위 함수에 대한 더 자세한 내용은 아래 링크 참고:

https://hsyhrae.tistory.com/29

[MySQL] 윈도우 함수 알아보기(Feat: ROW_NUMBER(), RANK(), DENSE_RANK())

 

위에서 작성한 국가별 매출액을 새로운 테이블로 작성

CREATE TABLE stat AS
SELECT 
    C.country
    , SUM(priceeach * quantityordered) 매출액
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
LEFT
JOIN customers C
ON A.customernumber = C.customernumber
GROUP BY 1
ORDER BY 2 DESC
;

 

확인

SELECT * FROM stat;

 

DENSE_RANK() 이용 순위 매기기

SELECT 
    country 
    , 매출액
    , DENSE_RANK() OVER(ORDER BY 매출액 DESC) RNK
FROM stat;

 

상위 5개국만 조회하기

SELECT 
    A.*
FROM (
	SELECT 
	    country 
	    , 매출액
	    , DENSE_RANK() OVER(ORDER BY 매출액 DESC) RNK
	FROM stat
) A
WHERE RNK BETWEEN 1 AND 5
;

 

교재에서는 서브쿼리만을 사용하여 조회하는 방법을 사용했지만 새로 작성한 테이블을 사용하는 방법이 편리하여 새로운 테이블 활용하여 조회함.