Q. 년도별 고객 1인의 평균 구매액을 조회해보자 !

필요한 데이터 : 

연도정보 ➜ Orders 테이블에 OrderDate 데이터

고객 수 ➜ Orders 테이블에 CustomerNumber 데이터를 중복제거 (distinct) 와 집계함수 (count) 를 사용하여 가공

구매액 ➜ OrdersDetails 테이블에 PriceEach*QuantityOrdered 데이터

1인당 평균 구매 ➜ 연도별 총매출 / 연도별 총 고객수

 

 

조회방법

 

1. 필요한 데이터들이 들어있는 테이블 Orders 와 OrderDetails 를 join 해준다.

해석
classicmodels.orders 를 A라 칭하고 classicmodels.orderdetails 는 B라고 칭한다.
A 테이블과 B 테이블을 left join 해줄 건데 이 때, orderNumber 가 같은 것끼리 묶어서 join 해주게 된다.
이 join 한 테이블의 모든 데이터 (*) 를 가져오게 된다.

 

 

2. 필요한 컬럼을 조회해준다.

해석
1번의 join 한 테이블에서 구매날짜 (A.orderDate), 자동차 가격(B.priceEach), 판매수량 (B.quantityOrdered) 의 컬럼만 조회하여준다.

 

 

3. 연도별로 그룹화 하고 연도별 총 판매액을 조회한다.

해석
2번의 내용에서 년도만을 추출하기 위해 substr() 함수를 이용해준다.
orderDate 는 YYYY-MM-DD 의 형태이기 때문에 년도만 추출하기 위해 첫번째 글자부터 4개만 출력해주면 된다.
즉, substr(A.orderDate,1,4) 가 된다.
총 판매액은 자동차가격 * 판매수량 을 이용하여 구하게 되며 년도로 그룹화를 해주었기 때문에
집계함수 sum() 을 사용해주어야 한다.

 

 

4. 연도별 구매자수와 연도별 인당 평균 매출액을 조회한다.

해석
customerNumber 데이터를 중복제거 해주고 집계함수 count() 를 사용하여 구매자수를 구해준다.
중복제거의 이유 : 한명의 고객이 여러번 구매를 했을 상황을 대비하여 중복제거 (distinct) 를 해주게 된다.
count() : 문자열, 리스트의 갯수를 세어준다.

1명당 평균 구매율을 알기 위해
총매출액 / 총고객수 'Sales of 1' 라는 이름을 붙여 조회한다.