미리보기
패션 온라인 쇼핑몰 데이터를 바탕으로 주문 데이터 분석 진행
- 2021년 6월 1일 하루 동안의 가상의 패션 이커머스 데이터 분석
실습
0. 데이터 불러오기
테이블 4개 (csv파일 4개) 불러오기
- 테이블명, as 별칭 이름 등 네이밍 할 때는 SQL 문법에 쓰이는 문구와 겹치지 않게 해줍니다.
- order, year, user 등 추천 X
- 또한 데이터 불러오기 할 때, Configure 등으로 파일 형태를 확인해줘야 합니다.
- 각 컬럼 형태에 따라 intiger 숫자형, varchar 문자형 등 확인
숫자형으로 되어야 하는 컬럼이 varchar로 되어있으면 intiger로 수정
1. 데이터 탐색
unitsold 판매수량
gmv 거래액 (unitsold * price)
product_profit 상품 이익
total_profit 총 이익 (product_profit - discount 할인액)
--주문 테이블 확인
select *
from online_order
--상품 테이블 확인
select *
from item
--카테고리 테이블 확인
select *
from category
--유저 테이블 확인
select *
from user_info
테이블간 관계도
테이블간 관계도를 생각해보며, 서로 어떻게 연결을 해야 좋을지 생각해봅니다.
테이블이 여러개이고 단순 join을 통해 진행하기 어려운 경우,
디비버의 기능을 통해 진행할 수도 있긴 합니다.
2. 상품명별 매출액
-- 주문테이블에서 상품별 매출액 집계 후, 매출액 높은 순으로 정렬 (주문테이블의 아이템id + 그에 해당하는 매출액)
-- 상품은 itemid , 매출액은 gmv
select itemid , sum(gmv) as gmv
from online_order
group by 1
order by 2 desc
-- 상품테이블의 아이템명과 상품별 매출액 집계를 한눈에 보기 (상품테이블의 아이템name + 그에 해당하는 매출액)
-- 주문테이블의 아이템id와 상품테이블의 아이템id는 동일합니다.
-- 아이템id를 join시켜 그에 해당하는 아이템name을 불러와줍니다.
select item_name , sum(gmv) as gmv
from online_order
join item on online_order.itemid = item.id
group by 1
order by 2 desc
-- 위의 내용을 동일하지만, 테이블명을 좀 더 간단하고 짧게 수정
select item_name , sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
group by 1
order by 2 desc 
3. 카테고리별 매출액
-- online_order.userid = item.id --> item.cateogry_id = category.id (총 3번 JOIN)
select c.cate1 , c.cate2 , c.cate3, sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join category c on i.category_id = c.id
group by 1,2,3
order by 4 desc
4. 성별 & 연령별 매출액
select ui.gender, ui.age_band , sum(gmv) as gmv
from online_order oo
join user_info ui on oo.userid = ui.userid
group by 1, 2
order by 1, 2
5. 남성구매에 한하여 상품명별 매출
-- 성별 user_info.gender
-- 상품명 item.item_name
-- 상품번호 item.id = online_order.itemid
-- 매출 online_order.gmv
select item_name, sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join user_info ui on oo.userid = ui.userid
where gender = 'M'
group by 1 
되짚어보기
- JOIN 순서
Select컬럼명From테이블명1 명칭1JOIN테이블명2 명칭2On명칭1.컬럼명 = 명칭2.컬럼명Where조건절Group by그룹별Having그룹별 조건절Order by정렬기준
정리
-- 주문 데이터 분석
-- 2021년 6월 1일 하루 동안의 가상의 패션 이커머스 데이터 분석
--------------------------------------------------- 1. 데이터 탐색
--주문 테이블 확인
select *
from online_order
--상품 테이블 확인
select *
from item
--카테고리 테이블 확인
select *
from category
--유저 테이블 확인
select *
from user_info
--------------------------------------------------- 2. 상품별 매출 확인 (상품명과 매출액)
-- 주문테이블에서 상품별 매출액 집계 후, 매출액 높은 순으로 정렬 (주문테이블의 아이템id + 그에 해당하는 매출액)
-- 상품은 itemid , 매출액은 gmv
select itemid , sum(gmv) as gmv
from online_order
group by 1
order by 2 desc
-- 상품테이블의 아이템명과 상품별 매출액 집계를 한눈에 보기 (상품테이블의 아이템name + 그에 해당하는 매출액)
-- 주문테이블의 아이템id와 상품테이블의 아이템id는 동일합니다.
-- 아이템id를 join시켜 그에 해당하는 아이템name을 불러와줍니다.
select item_name , sum(gmv) as gmv
from online_order
join item on online_order.itemid = item.id
group by 1
order by 2 desc
-- 위의 내용을 동일하지만, 테이블명을 좀 더 간단하고 짧게 수정
select item_name , sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
group by 1
order by 2 desc
--------------------------------------------------- 3. 카테고리별 매출액
-- online_order.userid = item.id --> item.cateogry_id = category.id (총 3번 JOIN)
select c.cate1 , c.cate2 , c.cate3, sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join category c on i.category_id = c.id
group by 1,2,3
order by 4 desc
--------------------------------------------------- 4. 성별 & 연령별 매출액
select ui.gender, ui.age_band , sum(gmv) as gmv
from online_order oo
join user_info ui on oo.userid = ui.userid
group by 1, 2
order by 1, 2
--------------------------------------------------- 5. 남성이 구매 한하여, 상품명과 매출
-- 성별 user_info.gender
-- 상품명 item.item_name
-- 상품번호 item.id = online_order.itemid
-- 매출 online_order.gmv
select item_name, sum(gmv) as gmv
from online_order oo
join item i on oo.itemid = i.id
join user_info ui on oo.userid = ui.userid
where gender = 'M'
group by 1
복습
강의 한 번에 이해가 되면 좋겠지만, 사실 이해가 1도 되지 않습니다!
이제 보지 않고, 각 테이블과 코드를 모두 손으로 그어가며 복습해보겠습니다.
-- 주문 데이터 분석
-- 2021년 6월 1일 하루 동안의 가상의 패션 이커머스 데이터 분석
--------------------------------------------------- 1. 테이블 확인
--------------------------------------------------- 2. 상품별 매출 확인 (상품명과 매출액)
--------------------------------------------------- 3. 카테고리별 매출액
--------------------------------------------------- 4. 성별 & 연령별 매출액
--------------------------------------------------- 5. 남성이 구매 한하여, 상품명과 매출
--------------------------------------------------- 1. 테이블 확인
select * from category c
select * from item i
select * from online_order oo
select * from user_info ui
--------------------------------------------------- 2. 상품별 매출 확인 (상품명과 매출액)
select item.item_name, sum(gmv) as gmv
from online_order
join item on item.id = online_order.itemid
group by 1
order by 2 desc
--------------------------------------------------- 3. 카테고리별 매출액
select c.cate3, c.cate2, c.cate1, sum(gmv) as gmv
from online_order oo
join item i on i.id = oo.itemid
join category c on c.id = i.category_id
group by 1,2,3
order by 4 desc
--------------------------------------------------- 4. 성별 & 연령별 매출액
select gender , age_band , sum(gmv) as gmv
from online_order
join user_info on user_info.userid = online_order.userid
group by 1,2
order by 3 desc
--------------------------------------------------- 5. 남성이 구매 한하여, 상품명과 매출
select item_name, sum(gmv) as gmv
from online_order
join user_info on user_info.userid = online_order.userid
join item on item.id = online_order.itemid
where user_info.gender = 'M'
group by 1
order by 2 desc
다행히 다 맞았습니다! 야호!
이론
위에 사용했던 join은 사실 Inner Join 을 뜻합니다.
NUll값이 있는데 JOIN?
Null값이 있으면 보통은 NULL값이 삭제된 채로 JOIN 됩니다.
Null값이 있는 테이블A (from) 과 테이블B (join) 을 하게 되었을 때,
그냥 join하게되면 Null값을 삭제됩니다.
left join 하게되면 테이블A 기준으로 Null값까지 모두 나오게됩니다.
JOIN 할 때 실수!
중복값 확인 (듀플리케이션 = 듑이 일어난다)
중복값이 있은 채로 JOIN하게 되면 다른 에러 창이 나오지 않아 잘 모르고 지나가는 경우가 있으니 주의합니다.
Where 조건절 확인
Where 조건절에 Where을 빼고 실행했을 경우
SQL에서는 문법적 오류가 없다면 에러가 나오지 않기 때문에 주의해야합니다.
JOIN 뒤에 = 필수
Join On 다음에 = 가 없을 때 에러가 납니다.
Join Key 여러 개 가능합니다.
데이터기반 스토리텔링을 통해 인사이트를 얻습니다.