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

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

 

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

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

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

 

 

ERD

위 3개 테이블에 주목

 

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

 

구매지표 추출

 

요청사항1: 매출액 조회

요청사항1-1: 일별 매출액 조회

일별 매출액을 조회하기 위해서는 주문일과 매출액 데이터가 필요하다. 

주문일 데이터는 orders 테이블의 orderdate 컬럼이고,

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

두 테이블을 연결하기 위해서는 JOIN 사용이 필요, 그 중에서 LEFT JOIN을 사용한다.

USE classicmodels; -- classicmodels DB 사용

SELECT 
	A.orderdate
    	, priceeach * quantityordered
FROM orders A
LEFT
JOIN orderdetails B 
ON A.ordernumber = B.ordernumber
;

해당 조회 결과는 건별로 매출이 발생한 결과이다.  일별 자료가 있긴하지면 데이터를 분석하기엔 모자르다.

따라서 일별로 종합된 데이터를 만들어야 한다. 

이때 사용되는 것은 GROUP BY이다.

 

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

집계함수인 SUM과 같이 사용하여 일별 매출액을 조회했다.

 

요청사항1-2; 월별 매출액 조회

월별로 조회 하기 위해서는 orderdate  컬럼에서 앞에서 7자리가 필요하다.

이를 위해 사용할 수 있는 함수로는 SUBSTR이 있다.

SUBSTR(컬럼, 시작위치, 길이) 

사용 예시

SELECT SUBSTR('ABCDE', 2, 2);

위 문자열 'ABCDE'의 2번째 자리에서 2개의 문자열을 조회했다.

 

이를 orderdate 컬럼에 적용해보자.

SELECT 
	SUBSTR(A.orderdate, 1, 7) MM
    , SUM(priceeach * quantityordered) AS 매출액
FROM orders A
LEFT
JOIN orderdetails B 
ON A.ordernumber = B.ordernumber
GROUP BY MM
ORDER BY MM
;

월별로 평균 매출액을 조회했다.

 

요청사항1-3: 연도별 매출액 조회

연도별은 4자리만 필요하다.

SELECT 
	SUBSTR(A.orderdate, 1, 4) YY
	, SUM(priceeach * quantityordered) AS 매출액
FROM orders A
LEFT
JOIN orderdetails B 
ON A.ordernumber = B.ordernumber
GROUP BY YY
ORDER BY YY
;

연도별로 평균 매출액을 조회했다.

 

 

요청사항2: 일자별 구매자 수, 구매 건수

일자별은 orderdate 이용

구매자수는 custormernumber 이용

구매 건수는 ordernumber 이용

 

데이터 확인

SELECT
    orderdate
    , customernumber
    , ordernumber
FROM orders;

 

한 고객이 하루에 여러 물건을 살 수 있다. 이때 단순히 COUNT()만 사용하면 한 고객이 여러번 집계된다.

따라서 중복을 제거해야한다. 

구매 건수는 중복이 없을 것으로 생각되지만 같이 확인해보자.

SELECT 
    COUNT(DISTINCT customernumber) AS 구매자수
    , COUNT(customernumber) AS 구매자수2
    , COUNT(DISTINCT ordernumber) AS 구매건수
    , COUNT( ordernumber) AS 구매건수2
FROM orders;

구매자 수는 중복이 있어 DISTINCT를 사용해야 한다.

구매 건수는  중복이 없어 그냥 사용해도 괜찮다.

 

일자별 구매자 수, 구매 건수 확인

SELECT 
    orderdate
    , COUNT(DISTINCT customernumber) AS 구매자수
    , COUNT(ordernumber) AS 구매건수
FROM orders
GROUP BY 1
ORDER BY 1
;

월별, 연도별은 SUBSTR()를 이용하면 구할 수 있다.

 

번외) 구매자수가 2명 이상인 날짜 확인

SELECT A.*
FROM (
	SELECT 
	orderdate
	, COUNT(DISTINCT customernumber) AS 구매자수
    , COUNT(DISTINCT ordernumber) AS 구매건수
FROM orders
GROUP BY 1
ORDER BY 1
) A
WHERE A.구매자수 >= 2;

 

 

요청사항3: 연도별 인당 매출액

우선, 연도별 매출액과 구매자 수를 구한다.

매출액 = 개별가격(priceeach) x 주문수량(quantityordered)

SELECT 
    SUBSTR(A.orderdate, 1, 4) AS YY
    , SUM(priceeach * quantityordered) AS 매출액
    , COUNT(DISTINCT A.customernumber) AS 구매자수
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1
;

인당 매출액 = 매출액 / 구매자 수

SELECT 
    SUBSTR(A.orderdate, 1, 4) AS YY
    , COUNT(DISTINCT A.customernumber) AS 구매자수
    , SUM(priceeach * quantityordered) AS 매출액
    , SUM(priceeach * quantityordered) / COUNT(DISTINCT A.customernumber) AS AMV
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1
;

AMV: 인당 구매 금액

 

번외) 인당 매출액이 4만 이상인 구매자수 조회

서브쿼리를 사용하여 WHERE절을 추가하여 조

SELECT *
FROM (
	SELECT 
	SUBSTR(A.orderdate, 1, 7) AS MM
    , COUNT(DISTINCT A.customernumber) AS 구매자수
    , SUM(priceeach * quantityordered) AS 매출액
    , SUM(priceeach * quantityordered) / COUNT(DISTINCT A.customernumber) AS AMV
FROM orders A
LEFT
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
ORDER BY 1
) A
WHERE A.AMV >= 40000
;

 

요청사항4: 연도별 건당 구매 금액

인당 매출액과 유사

SELECT 
    SUBSTR(A.orderdate, 1, 4) YY
    , COUNT(DISTINCT A.ordernumber) AS 구매건수
    , SUM(priceeach * quantityordered) AS 매출액
    , SUM(priceeach * quantityordered) / COUNT(DISTINCT A.ordernumber) AS ATV
FROM orders A
LEFT 
JOIN orderdetails B
ON A.ordernumber = B.ordernumber
GROUP BY 1
;

 

ATV: 건당 구매 금액