[SQL][개인과제] ECOMMERCE 데이터 분석(1)_세팅~초급 문제
[ECOMMERCE 활용 데이터]
[기초 세팅]
1. Dbeaver에서 MySQL 연결하기
+) MySQL 설치는 여기 참고 : HERE spartacoding2@
> 순서
0) Dbeaver 접속
1) 새 데이터베이스 연결(Ctrl + Alt + N)
2) MySQL 클릭 > next
3) MySQL 설치하면서 '자신이 설정한 Password' 입력
4) 완료
2. 파일 불러오기
> 순서
1) Databases 우클릭 Create new database
2) 이름 ECOMMERCE 생성
3) 생성된 ECOMMERCE 우클릭 -> 데이터 가져오기
4) (아래 NEXT을 누르거나) input file 클릭 -> 저장한 csv 파일 경로로 가서 불러올 데이터 클릭 -> NEXT -> NEXT
5) 완료
[참고] python의 pandas와 sqlite3을 사용해 CSV파일을 SQL 파일로 바꿔 DB 생성하는 방법도 있다 : HERE
3. 테이블 이름 / 형식 변경
> use database : 사용할 데이터베이스 설정
use Ecommerce;> 테이블 이름 알맞게 / 형식 변경
RENAME TABLE List_of_Orders TO list_of_orders;
RENAME TABLE Order_Details TO order_details;
RENAME TABLE Sales_target TO sales_target;
ALTER TABLE order_details CHANGE `Sub-Category` sub_category VARCHAR(25);
ALTER TABLE order_details CHANGE `Order ID` order_id VARCHAR(25);
ALTER TABLE list_of_orders CHANGE `Order Date` order_date VARCHAR(25);
ALTER TABLE list_of_orders CHANGE `Order ID` order_id VARCHAR(25);
ALTER TABLE sales_target CHANGE `Month of Order Date` month_of_order VARCHAR(25);(참고) 혹시나 혹시나 한 번 실행하면 테이블명&칼럼명이 변경됐기 때문에, 두 번 실행시 오류가 뜸
4. ER Diagram으로 관계 설정
- list_of_orders (order_id) → order_details (order_id) 연결
- order_details (Category) → salese_target(Category) 연결
5. 과제
문제 1: "최소 주문 날짜 찾기"
주어진 테이블 **list_of_orders**에는 여러 주문들의 날짜가 저장되어 있습니다. 주문 날짜는 'dd-mm-yyyy' 형식으로 저장되어 있습니다. 이 테이블에서 가장 이른 주문 날짜를 찾아보세요.
(참고) DATE_FORMAT 모를 때 : HERE
(참고) null값과 Empty 값 구분 : HERE
select STR_TO_DATE(order_date, '%d-%m-%Y') order_date
from ecommerce.list_of_orders
-- 데이터베이스에 여러 데이터베이스들이 있을 수 있어 데이터베이스명.테이블명 사용
where order_date != ''
order by 1 desc
limit 1;
-- 다른 방법
select min(str_to_date(order_date,'%d-%m-%Y')) order_date
from list_of_orders
where order_date <> '';
문제 2: "카테고리별 총 매출 계산하기"
order_details 테이블에는 다양한 주문들의 세부 정보가 저장되어 있습니다. 각 주문은 특정 'Category'에 속하며, 각 주문의 'Amount'는 그 주문의 매출 금액을 나타냅니다. 이 테이블을 사용하여 각 카테고리별 총 매출 금액을 계산하세요.
select Category , sum(Amount) as sum_amount
from ecommerce.order_details
group by Category ;
문제 3: "상위 5명의 고객별 총 구매액 찾기"
두 개의 테이블 **order_details**와 **list_of_orders**가 있습니다. order_details 테이블은 각 주문의 세부사항을 포함하고 있으며, list_of_orders 테이블은 주문에 대한 고객의 이름과 주문 ID를 포함합니다. 각 고객별로 총 구매액을 계산하여, 구매액이 가장 높은 상위 5명의 고객을 찾아보세요.
(참고) N개 데이터만 불러오기 : HERE
select a.customername, sum(b.amount)
from ecommerce.list_of_orders A join ecommerce.order_details B
on a.order_id = b.order_id
group by a.customername
order by 2 desc -- sum(b.amount)
limit 5;