1. 데이터 생성

1) 데이터 구조 확인

MySQL Sample Database

2) ERD(Entity Relational Diagram)

customers, orders, products

2. 구매 지표 추출

* 일별 매출액 조회
    
select a.orderdate, priceEach*quantityOrdered from classicmodels.orders a 
left join classicmodels.orderdetails b
on a.orderNumber=b.orderNumber 

* 일별 매출액 조회_날짜 중복 없애기

select a.orderdate, sum(priceEach*quantityOrdered) from classicmodels.orders a 
left join classicmodels.orderdetails b
on a.orderNumber=b.orderNumber 
group by 1
order by 1;

* 월별 매출액 조회_yyyy_mm_dd / 년월 출력 2003-01

SUBSTR() : 문자열에서 원하는 텍스트만 호출하는 함수
    
SELECT SUBSTR(A.ORDERDATE,1,7) MM, SUM(PRICEEACH*QUANTITYORDERED) AS SALES
FROM CLASSICMODELS.ORDERS A 
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER= B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;

* 년도별 매출액 조회_yyyy
    
SELECT SUBSTR(A.ORDERDATE,1,4) YEAR, SUM(PRICEEACH*QUANTITYORDERED) AS SALES
FROM CLASSICMODELS.ORDERS A 
LEFT JOIN CLASSICMODELS.ORDERDETAILS B
ON A.ORDERNUMBER= B.ORDERNUMBER
GROUP BY 1
ORDER BY 1;

* 일자별 구매자수, 구매건수
    
SELECT ORDERDATE, CUSTOMERNUMBER, ORDERNUMBER FROM classicmodels.ORDERS;

DISTINCT : 중복제거
CUSTOMERNUMBER 중복 확인
    
SELECT COUNT(ORDERNUMBER) N_ORDERS, COUNT(DISTINCT ORDERNUMBER) N_ORDERS_DISTINCT
FROM classicmodels.ORDERS 

* 연도별 인당 매출액
연도별 구매자수, 매출액 확인
    
SELECT SUBSTR(A.`ORDERDATE`, 1, 4) AS YEAR,
COUNT(DISTINCT A.`CUSTOMERNUMBER`) AS N_PURCHASER,
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) AS YEAR,
COUNT(DISTINCT A.`CUSTOMERNUMBER`) AS N_PURCHASER,
SUM(B.`PRICEEACH` * B.`QUANTITYORDERED`)AS SALES,
SUM(B.`PRICEEACH` * B.`QUANTITYORDERED`) / COUNT(DISTINCT A.`CUSTOMERNUMBER`) AS AMV
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) AS YEAR,
COUNT(DISTINCT A.`ORDERNUMBER`) AS N_PURCHASER,
SUM(B.`PRICEEACH` * B.`QUANTITYORDERED`) AS SALES,
SUM(B.`PRICEEACH` * B.`QUANTITYORDERED`) / COUNT(DISTINCT A.`ORDERNUMBER`) AS ATV
FROM `CLASSICMODELS`.`ORDERS` A
LEFT JOIN `CLASSICMODELS`.`ORDERDETAILS` B ON A.`ORDERNUMBER` = B.`ORDERNUMBER`
GROUP BY 1
ORDER BY 1;

* 국가별, 도시별 매출액
필요컬럼 : country, customernumber, ordernumber, priceeach, quantityordered

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;

필요테이블 3개 조합후 국가별, 도시별 매출 확인
SELECT C.COUNTRY, C.CITY, 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,2
ORDER BY 1,2;

* 북미(USA, CANADA=NORTH AMERICA) VS 비북미 매출비교 (OTHERS)

CASE WHEN 조건 THEN 결과 END

북미, 비북미 구분
SELECT 
CASE WHEN COUNTRY IN ('USA','CANADA') THEN 'NORTH AMERICA'
ELSE 'OTHERS' END COUNTRY_GRP
FROM classicmodels.customers;

SELECT CASE WHEN COUNTRY IN ('USA','CANADA') THEN 'NORTH AMERICA' ELSE 'OTHERS' END COUNTRY_GRP, 
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;

* 매출 Top 5 국가 및 매출
row_number, rank, dense_rank
RESULT 예)


CODE 예)
SELECT NAME, GOALS, 
RANK() OVER(ORDER BY GOALS DESC) AS 'RANK',
DENSE_RANK() OVER(ORDER BY GOALS DESC) AS 'DENSE RANK',
ROW_NUMBER() OVER(order by GOALS DESC) AS 'ROW NUMBER'
FROM FOOTBALL.PLAYERS;

TOP 5 국가 산출
CREATE TABLE CLASSICMODELS.STAT AS 
SELECT C.COUNTRY, SUM(PRICEEACH*QUANTITYORDERED) SALES 
FROM classicmodels.ORDERS A 
LEFT JOIN classicmodels.orderdetails statstatstatB 
ON A.ORDERNUMBER = B.ORDERNUMBER
LEFT JOIN CLASSICMODELS.CUSTOMERS C
ON A.CUSTOMERNUMBER = C.CUSTOMERNUMBER
GROUP BY 1
ORDER BY 2 DESC;

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;

>> 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;

1장 데이터베이스와 SQL
2장 SQL문법
3장 데이터 추가, 삭제, 갱신, 데이터 정합성
4장 자동차 매출 데이터를 이용한 리포트 작성
5장 상품 리뷰 데이터를 이용한 리포트 작성
6장 식품 배송 데이터 분석
7장 UK Commerce 데이터를 이용한 리포트 작성
8장 타이타닉 호 데이터 분석
9장 R, Python 연동