2. 구매 지표 추출
1) 매출액 조회
* 일별 매출액 조회
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;
2) 구매자수, 구매건수
* 일자별 구매자수, 구매건수
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;
3) 구룹별 구매지표
* 국가별, 도시별 매출액 필요컬럼 : 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 연동