[SQL] 자동차 매출 예제데이터 실습 -1

silver0·2022년 10월 16일

SQL

목록 보기

2/2

데이터 구조 파악

SQL 샘플데이터 다운로드
https://www.mysqltutorial.org/mysql-sample-database.aspx
MySQL Workbench에서 다운로드 받은 데이터 import
classicmodels의 이름인 db 생성 확인

classicmodels 테이블 구조 파악

데이터 구조를 쉽게 파악할 수 있도록 데이터 설계 시 ERD(Entity Relational Diagram)를 작성한다.
현업에서는 ERD가 존재하지 않는 경우가 다수이므로 직접 데이터를 조회하면서 파악해야 하는 경우가 많다. ERD가 존재한다면 빠르고 쉽게 데이터 구조를 파악할 수 있다.

ERD를 보고 데이터 구조 파악
- customers 테이블과 orders 테이블의 customerNumber를 이용해 2가지 데이터의 정보를 조합해 데이터를 조회할 수 있다.
- MySQL Sample Database Schema
  - customers : 고객의 데이터
  - products : 축소 모형 자동차의 목록
  - productLines : 제품 라인 카테고리 목록
  - orders : 고객이 주문한 판매 주문
  - orderDetails : 각 판매 주문에 대한 판매 주문 라인 항목
  - payments : 고객이 자신의 계정을 기반으로 결제한 금액
  - employees : 모든 직원 정보와 누가 누구에게 보고하는지와 같은 조직 구조
  - offices : 영업소 데이터를 저장

구매 지표 추출

클라이언트의 매출 및 현황에 대해 살펴보고자 한다.

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

구매자 수, 구매 건수(일자별, 월별, 연도별)

인당 매출액(AMV, Average Member Value) (연도별)

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

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

일별 매출액 조회

데이터	테이블	컬럼
주문일	orders	orderdate
판매액	oderdetails	priceEach * quantityOrdered

orders 테이블의 주문 정보와 oderdetails 테이블의 상품 금액 결합

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) AS sales 
FROM classicmodels.orders A 
LEFT JOIN classicmodels.orderdetails B  테이블과 join
ON A.orderNumber = B.orderNumber 
GROUP BY 1 
ORDER BY 1;

.SELECT문 문법 순서

SELECT - 1
FROM - 2
WHERE - 3
GROUP BY - 4
HAVING - 5
ORDER BY - 6

.SELECT문 실행 순서

FROM - 1
WHERE - 2
GROUP BY - 3
HAVING - 4
SELECT - 5
ORDER BY - 6

월별 매출액 조회

orderDate(판매일)의 포맷형식은 'yyyy-mm-dd'이다.
여기서 substr() 함수를 이용해서 'yyyy-mm'만 추출하여 월별 매출액을 조회한다.

substr()

substr(컬럼, 시작 위치, 가져올 텍스트 길이)

월별 판매일자에 판매금액의 합계 집계

SELECT substr(A.orderDate, 1, 7) MM,
	sum(B.priceEach*B.quantityOrdered) AS sales
FROM classicmodels.orders A
LEFT JOIN classicmodels.orderdetails B		
ON A.orderNumber = B.orderNumber				 
GROUP BY 1										 
ORDER BY 1;

연도별 매출액 조회

월별 매출액과 동일한 방법으로 조회

판매일자에 연도만 추출하여 판매금액의 합계 집계

SELECT substr(A.orderDate, 1, 4) YY, 
	sum(B.priceEach*B.quantityOrdered) AS sales
FROM classicmodels.orders A
LEFT JOIN classicmodels.orderdetails B		
ON A.orderNumber = B.orderNumber				 
GROUP BY 1										 
ORDER BY 1;

구매자 수, 구매 건수(일자별, 월별, 연도별)

orders 테이블
- orderDate 판매일
- customerNumber 구매고객번호

판매일로 그룹핑 후 구매고객번호 COUNT하여 구매자 수 및 구매 건수를 추출한다.

주의할 점

a라는 고객이 동일날짜에 2건을 구매한다면, orders 테이블에는 a고객의 2가지 구매건이 기록된다.

UNIQUE하게 COUNT하지 않는다면, A라는 고객은 해당 날짜에 구매자 수가 2번 COUNT되는 상황이 발생하므로

데이터를 추출할 때는 사용하는 데이터가 어떤 구조로 생성되는지, 구해야 할 것은 무엇인지 정확히 설정하고 계산해야 한다.

order 테이블의 customerNumber별 구매내역 조회

SELECT orderDate,
	customerNumber,
    orderNumber
FROM classicmodels.orders
ORDER BY 2;

중복된 customerNumber 제외하고 구매자 수 COUNT

중복 컬럼 제외: COUNT(DISTINCT 컬럼)

일자별 구매자수, 주문건수 조회

SELECT orderDate,
COUNT(DISTINCT customerNumber) n_purchasers,
COUNT(DISTINCT orderNumber) n_orders
FROM classicmodels.orders
GROUP BY 1
ORDER BY 1;

월별 구매자수, 주문건수 조회

SELECT substr(orderDate, 1, 7) MM,
COUNT(DISTINCT customerNumber) n_purchasers,
COUNT(DISTINCT orderNumber) n_orders
FROM classicmodels.orders
GROUP BY 1
ORDER BY 1;

연도별 구매자수, 주문건수 조회

SELECT substr(orderDate, 1, 4) YY,
COUNT(DISTINCT customerNumber) n_purchasers,
COUNT(DISTINCT orderNumber) n_orders	
FROM classicmodels.orders
GROUP BY 1
ORDER BY 1;

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

고객의 인당 매출액은 고객의 Loyalty를 측정하는 요인으로 사용될 수 있다. 기간별로 평균 인당 고객 매출액을 비교하면, 고객 1명이 우리의 서비스에 얼마를 지불하는지 그 변화를 파악할 수 있다.

연도별 매출액과 구매자 수 조회

SELECT substr(A.orderDate, 1, 4) YY,
	COUNT(DISTINCT A.customerNumber) n_purchasers, 
	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;

매출액 / 구매자 수 : 연도별 평균 인당 매출액

SELECT substr(A.orderDate, 1, 4) YY, 				
	COUNT(DISTINCT A.customerNumber) n_purchasers, 
	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;

건당 구매 금액(ATV) (연도별)

ATV란 1건의 거래에 대한 평균 매출액이다.

연도별 매출액과 구매건수 및 건당 구매 금액

SELECT substr(A.orderDate, 1, 4) YY, 
	COUNT(DISTINCT A.orderNumber) n_orders, 
	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;