[SQL로 배우는 데이터 전처리 분석] 3. 자동차 매출 데이터를 이용한 리포트 작성 (1) - 구매 지표 추출

천 대리님, 이제 SQL에 능숙해지신 것 같습니다. 이번에 자동사 제조사에서 데이터 분석 요청 건이 들어왔는데, 천 대리님이 TFT와 함께 참여해 프로젝트를 진행해 주셨으면 합니다.

이번 포스팅은 자동사 제조사 데이터를 가지고 구매 지표를 추출해보겠습니다.  

데이터는 https://www.mysqltutorial.org/mysql-sample-database.aspx 오픈 데이터를 사용했습니다.

 

---

ERD

 

구매 지표 추출

1) 매출액(일자별, 월별, 연도별)

a) 일별 매출액 조회

일별 매출액을 조회하려면 주문의 주문일과 매출액이 필요합니다. ERD를 보면 주문 일자(orderdate)는 orders 테이블에 존재하고, 판매액(priceEach * quantityOrdered)은 orderdetails에 존재합니다.
그래서 orders 테이블의 주문 정보에 orderdetails의 주문 상품 가격을 결합하고 일별 매출액을 조회합니다.

SELECT A.ORDERDATE,
B.PRICEEACH*B.QUANTITYORDERED
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER;

ORDERDATE로 그룹핑한 뒤, 매출액의 합을 집계하면 일별 매출액을 계산합니다.

SELECT A.ORDERDATE,
SUM(B.PRICEEACH*B.QUANTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
GROUP BY 1
ORDER BY 1

 

b) 월별 매출액 조회

판매일(orderdate)은 'yyyy-mm-dd'의 형태로 구성되어 있습니다. 'yyyy-mm'을 가져와서 처리하면 월별 매출액을 조회할 수 있을 것입니다.
문자열에서 원하는 부분만 가져오는 방법 : SUBSTR(칼럼, 위치, 길이) (ex. SUBSTR('ABCDE', 2, 3) -> BCD)

SELECT SUBSTR(A.ORDERDATE,1,7) MM,
SUM(B.PRICEEACH * B.QUANTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;

C) 연도별 매출액 조회

월별과 똑같이 SUBSTR을 사용해서 구할 수 있습니다.

SELECT SUBSTR(A.ORDERDATE,1,4) YY,
SUM(B.PRICEEACH * B.QUANTITYORDERED) SALES
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;

 

2) 구매자 수, 구매 건수

 ERD를 보면 ORDERS 테이블에 판매일(ORDERDATE), 구매 고객 번호(CUSTOMERNUMBER)가 존재합니다. 그래서 판매일로 그룹핑한 후 고객 번호를 COUNT 해주면 됩니다.
주의할 점은 구매자 수, 구매 건수를 산출할 때는 보통 UNIQUE 하게 필드를 COUNT 해줘야 합니다.

아래는 일자 별 구매자 수, 구매 건수이지만 SUBSTR을 이용하면 월별, 연별 구매자 수와 구매 건수를 구할 수 있습니다.

SELECT ORDERDATE,
COUNT(DISTINCT CUSTOMERNUMBER) N_PURCHASE,
COUNT(ORDERNUMBER) N_ORDER
FROM CLASSICMODELS.ORDERS
GROUP BY 1
ORDER BY 1;

 

 

3)인당 매출액(연도별)

기간별로 평균 인당 고객 매출액을 비교하면, 고객 1명이 우리의 서비스에 얼마를 지불하는지 그 변화를 파악할 수 있습니다.
연도별 매출액에서 구매자 수로 나누면 연도별 인당 매출액을 알 수 있습니다.

SELECT SUBSTR(A.ORDERDATE,1,4) YY,
COUNT(DISTINCT A.CUSTOMERNUMBER) N_PURCHASE,
SUM(B.PRICEEACH * B.QUANTITYORDERED) SALES,
SUM(B.PRICEEACH * B.QUANTITYORDERED)/ COUNT(DISTINCT A.CUSTOMERNUMBER) AMV
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;

 

4) 건당 구매 금액(ATV, Average Transaction Value)(연도별)

ATV : 1건의 거래는 평균적으로 얼마의 매출을 발생시키는 가입니다.
매출을 구매자 수가 아닌 구매 건수로 나누면 건당 구매 금액을 쉽게 구할 수 있습니다.

SELECT SUBSTR(A.ORDERDATE,1,4) YY,
COUNT(DISTINCT A.ORDERNUMBER) N_PURCHASE,
SUM(B.PRICEEACH * B.QUANTITYORDERED) SALES,
SUM(B.PRICEEACH * B.QUANTITYORDERED) / COUNT(DISTINCT A.ORDERNUMBER) ATV
FROM CLASSICMODELS.ORDERS A
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER = B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;