[SQL로 배우는 데이터 전처리 분석] 4. 자동차 매출 데이터를 이용한 리포트 작성 (2) - 그룹별 구매 지표
천 대리님, 전달해 주신 구매 지표를 통해 전반적인 비즈니스 현황을 파악할 수 있었습니다. 현재 국가 및 도시별로 얼마의 매출이 발생하는지 전반적으로 확인할 필요가 있습니다. 필요한 내용을 전달해 드릴 테니 분석 부탁드리겠습니다.
이번 포스팅은 국가별, 도시별로 매출 지표를 계산하여 더 상세하게 비즈니스를 이해하고자 합니다.
ERD
그룹별 구매 지표
1) 국가별, 도시별 매출액
국가별, 도시별 매출액을 구하기 위해서 ERD를 살펴보면 해당 주문 건이 발생한 국가, 도시를 파악하기 위해서 CUSTOMERS 테이블의 COUNTRY를 이용해야 합니다.
즉, ORDERS, CUSTOMERS, ORDERDETAILS를 모두 사용해야 합니다.
먼저 ORDERS, ORDERDETAILS, CUSTOMERS 3가지 테이블을 결합해야 합니다. 각 테이블을 연결하는 필드는 ORDERNUMBER, CUSTOMERNUMBER입니다.
SELECT *
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER;
필요한 칼럼(COUNTRY, CITY, 매출액(PRICEEACH*QUANTITYORDERED)을 호출합니다
SELECT C.COUNTRY, C.CITY, B.PRICEEACH*B.QUANTITYORDERED
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER;
CUSTOMERS 테이블에서 국가(COUNTRY), 도시(CITY)를 불러오고, 2가지 칼럼으로 그룹핑을 진행합니다.
이후 ORDERDETAILS 테이블의 PRICEEACH*QUANTITYORDERED를 합해 국가별, 도시별 매출액을 구합니다.
SELECT C.COUNTRY, C.CITY, SUM(B.PRICEEACH*B.QUANTITYORDERED) as 매출액
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1, 2
ORDER BY 1, 2;
2) 북미(USA, Canada) vs 비 북미 매출액 비교
CASE WHEN은 조건에 따라 원하는 결과를 출력하는 구문인데, 이것을 이용해 북미와 비 북미 지역으로 구분합니다.
SELECT CASE WHEN COUNTRY IN ('USA', 'Canada') THEN "North America" ELSE "Others" END AS COUNTRY_GAP
FROM CLASSICMODELS.CUSTOMERS;
우리가 구해야 할 것은 북미와 비북미 지역의 매출을 구하는 것으로, 위에서 구한 매출을 구하는 쿼리를 조금만 수정하면 됩니다.
여기서 COUNTRY와 CITY를 CASE WHEN 구문으로 변경하면 북미, 비 북미의 매출을 구분회 조회하면 됩니다.
SELECT CASE WHEN C.COUNTRY IN ("USA", "Canada") THEN "North America" ELSE "Others" END AS COUNTRY_GAP,
SUM(B.PRICEEACH * B.QUANTITYORDERED) AS SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1
ORDER BY 2 DESC;
3) 매출 Top 5 국가 및 매출
먼저 국가별 매출을 모두 출력 후 매출이 높은 순위부터 등수를 매겨 상위 5개의 국가만 출력하면 됩니다. 그렇게 하기 위해 데이터의 값에 따라 등수를 매기는 함수가 필요합니다.
RANK, DENSE_RANK, ROW_NUMBER 함수가 있는데, 이들을 구분하는 점은 동률을 처리하는 방법이 다르다는 것입니다.
RANK는 동률을 모두 같은 등수로 처리한 후, 그다음 등수를 동률의 수만큼 제외하고 매깁니다.
DENSE_RANK는 RANK와 동일하게 동률을 같은 등수로 처리하지만, 그다음 등수를 동률의 수만큼 제외하지 않고 바로 다음 등수로 매깁니다.
ROW_NUMBER는 동률을 반영하지 않고, 동일한 등수는 존재하지 않고 모든 행은 다른 등수를 가집니다.
우리는 앞에서 국가별 매출액을 구했는데, 이를 다른 이름의 테이블로 생성 후 매출에 따라 RANK를 매기면 우리가 원하는 TOP 5 국가를 산출할 수 있습니다.
CREATE TABLE CLASSICMODELS.STAT AS
SELECT C.COUNTRY,
SUM(PRICEEACH * QUANTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1
ORDER BY 2 DESC;위에서 생성한 테이블을 조회합니다.
SELECT * FROM CLASSICMODELS.STAT;
생성된 테이블에서 DENSE_RANK를 이용해 매출액 등수를 매겨보겠습니다.
SELECT COUNTRY, SALES, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK
FROM CLASSICMODELS.STAT;
출력 결과를 테이블로 생성한 후 상위 5개의 국가를 추출하겠습니다.
CREATE TABLE CLASSICMODELS.STAT_RNK AS
SELECT COUNTRY, SALES, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK
FROM CLASSICMODELS.STAT;
SELECT * FROM CLASSICMODELS.STAT_RNK
WHERE RNK BETWEEN 1 AND 5;
우리가 원하는 결과가 출력하기 위해 2개의 테이블을 생성해 원하는 결과를 출력했습니다.
하지만 데이터를 조회할 때마다 이렇게 테이블을 생성한다면, DB에 금방 임시로 생성된 테이블로 가득해지고 데이터 관리가 힘들 수가 있습니다.
SUBQUERY를 이용하면 위 과정을 하나의 쿼리로 처리가 가능합니다.
SELECT * FROM
(SELECT COUNTRY, SALES, DENSE_RANK() OVER(ORDER BY SALES DESC) RNK
FROM
(SELECT C.COUNTRY, SUM(PRICEEACH*QUANTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1) A) A
WHERE RNK <= 5;