# join review : sqldb
use sqldb;
# --> 구매 이력 // 고객 정보
# --> 어제 : 가기 테이블에서만 정보를 추출/ 변형, 가공
# 오늘 : "양쪽 테이블에 정보를 같이" 활용~~~
select * from usertbl;
desc usertbl;
-- userID : PK --> usertbl의 PK
select * from buytbl;
desc buytbl;
-- num : PK --> buytbl의 PK
# 이 지금 DB에서는 usertbl & buytbl의 연결 명시==> FK
# ==> 이 2개의 테이블의 정보를 연결 기준 : U.userID = B.userID
# 공교롭게도 연결을 할 기준의 컬럼명이 같을 뿐!!!!
# Q) 고객 정보 테이블에서,,,,구매 이력이 있는 고객들의 정보만 보고 싶다!!
# ==> 볼 항목 : 고객 정보, 구매 이력
# ==> 양쪽 정보가 모두 있는 inner join
select * from usertbl U inner join buytbl B
on U.userID = B.userID;
# ==> 1:1 관계 보장이 안 되어 있는 상황!!!
# 1명의 고객이 여러 건의 구매 이력을 가질 수 있기에,,1:n
# *** 꼭~~~ 합칠 때 연결에 대한 1:1 vs 1:n vs n:m 잘 생각!! ***
# Q) 구매한 고객들의 정보만 보자!!!
# (구매이력이 없는 고객의 정보는 보여주지 마세요!!!)
# --> 볼 항목 : 고객의 id, 이름, 구매한 상품, 연락처full, 주소
select * from usertbl;
#
select * from usertbl U
inner join buytbl B on U.userID = B.userID;
#
select U.userID, U.name, concat(U.mobile1, U.mobile2) `연락처`, U.addr, B.prodName
from usertbl U
inner join buytbl B on U.userID = B.userID;
# +++ 위의 결과들을 구매 이력을 중심으로 순서대로 정렬해주세요!!!
# ( 정렬 기준 : 눈에 안 보이는 B--> num)
from usertbl U
inner join buytbl B on U.userID = B.userID
order by B.num asc;
#
select U.userID, U.name, concat(U.mobile1, U.mobile2) `연락처`, U.addr, B.prodName
from usertbl U
inner join buytbl B on U.userID = B.userID
order by B.num desc;
# Q) 조건을 설정을 해보고 싶다!! where vs having
# ==> userID가 JYP인 사람의 구매한 물품과 그 고객의 기본 정보만 보자!!!
# : 콜센터 --> 그 사람의 --> 특정 요청에 대한 정보를 확인하고,,도와!!
# ==> 볼 항목 : userID, 이름, 상품명, 전체핸드폰
select * from usertbl where userID="JYP";
#
select * from buytbl where userID="JYP";
#
select * from usertbl U
inner join buytbl B on U.userID = B.userID
where U.userID="JYP";
#
select U.userID, U.name, concat(U.mobile1, U.mobile2) `Mobile`, B.prodName
from usertbl U
inner join buytbl B on U.userID = B.userID
where U.userID="JYP";
# Q) 쇼핑몰에서 ... 1번이라도 구매한 고객들에게,,,감사 문자를 보내자!!
# ==> 고객id, 이름, 핸드폰번호
select * from usertbl U
inner join buytbl B on U.userID = B.userID;
#
select U.userID, U.name, concat(U.mobile1, U.mobile2) `Mobile`
from usertbl U
inner join buytbl B on U.userID = B.userID;
# ==> 고객 정보 & 구매 이력
# 단, 이 과정에서 1:1이 아니라, 1:n관계 형성!!!
# 중복 데이터가 추출!!!!
# 중복 : distinct!!!!!
select distinct U.userID, U.name, concat(U.mobile1, U.mobile2) `Mobile`
from usertbl U
inner join buytbl B on U.userID = B.userID;
# Q) 우리 사이트에 회원가입은 했는데,,,
# 전혀~~~구매 이력이 없으신 고객들에게... 쿠폰!!!
# ==> 신규구매하시면~~~10% 할인 쿠폰!!(단, 신규 구매 한정)
# : 아이디, 이름, 전화번호
# --> inner join : XXX
# left/right : 이 중에서,,,,관심사가 정보가 없는 녀석이 관심!
select * from usertbl;
#
select * from usertbl U
left join buytbl B on U.userID = B.userID;
#
select * from usertbl U
left join buytbl B on U.userID = B.userID
where B.num is null;
#
select U.userID, U.name, concat(U.mobile1, U.mobile2) `M`
from usertbl U
left join buytbl B on U.userID = B.userID
where B.num is null;
#### 기타 필요한 부분들~~~~ ####
# *** case when ==> 내가 원하는 기준을 가지고 변형!!!
# ==> 단순 집계이상으로 이럴때~저럴때 ~~다양하게 할 때
# Q) buytbl에서 누적 금액(price*amount) 기준으로 고객 등급 구별!!
# --> 테이블 값 ==> 집계처리 ==> (+필터링)
# ==> 고객등급을 구별!!!!
# 1500 이상 : 최우수고객
# 1000~1500 : 우수 고객
# 1~1000 미만 : 일반고객
# 없음 : 유령고객
select * from buytbl;
#
select userID, price, amount from buytbl;
#
select userID, sum(price * amount) from buytbl;
# ---> userID 는 단순 처음 userID 단순 출력
# sum~~~ : 전체 매출에 대한 총합 : 회사 총 매출
# --> 고객별로 묶어서 고객별 누적금액을
select userID, sum(price * amount) `누적금액`
from buytbl
group by userID;
#
select userID, sum(price * amount) `Total`
from buytbl
group by userID
order by `Total` desc;
#
select userID, sum(price * amount) `Total`
from buytbl
group by userID
order by 2 desc;
# ==> 이런 구매관련 정보를,,,,고객 정보 기반으로 봐야 함!!!
select U.userID, U.name,sum(B.price * B.amount) `Total`
from buytbl B
left join usertbl U on B.userID =U.userID
group by B.userID
order by `Total` desc;
# ---> 애매한 점 : 구매 이력이 없는 고객들은 안 나옴!!!!
#
select U.userID, U.name,sum(B.price * B.amount) `Total`
from buytbl B
right join usertbl Uon B.userID =U.userID
group by B.userID
order by `Total` desc;
# ==> 이렇게 1차 가공한 Total을 기준으로,,,VVIP/VIP/Basic/Ghost
# case by case로 부여하자!!!
# ==> 그냥 내가 설정한 기준대로,,하나하나 쿼리에 작성하면 됨!!! case when
# case when 조건1 then 그 때의 출력값
# when 조건2 then 그 때의 출력값,,,,,
# ....
# else 그 때의 출력값
# end
select U.userID, U.name,sum(B.price * B.amount) `Total` ,
case when sum(B.price * B.amount) > 1500 then "VVIP"
when sum(B.price * B.amount) > 1000 then "VIP"
when sum(B.price * B.amount) > 0 then "Basic"
else "Ghsot"
end `Class`
from buytbl B
right join usertbl U on B.userID =U.userID
group by B.userID
order by `Total` desc;
# 위의 문제의 기본
select U.userID, U.name,sum(B.price * B.amount) `Total`
from buytbl B
right join usertbl U on B.userID =U.userID
group by B.userID
order by `Total` desc;
#
select * from (select U.userID, U.name,sum(B.price * B.amount) `Total`
from buytbl B
right join usertbl U on B.userID =U.userID
group by B.userID
order by `Total` desc) A;
#
select *, case when Total>1500 then "VVIP"
when Total>1000 then "VIP"
when Total>0 then "Basic"
else "Ghost"
end `Class`
from (select U.userID, U.name,sum(B.price * B.amount) `Total`
from buytbl B
right join usertbl U on B.userID =U.userID
group by B.userID
order by `Total` desc) A;
# ==> 원본 테이블의 정보를 바탕으로 1차 가공 : 누적 금액!!
# 1차 가공물 누적금액을 바탕으로 2차 가공 : 고객 등급 부여!!
# ==> 명확하게 2차 가공의 기준이 실존하는 값이 아님!!---> 다시 재활용!!
# 그래서 1차 가공물을 마치 진짜 1개의 테이블처럼 인식시키 위해서 포장!!
# + mysql 특성상 참조 달아두어야 함!!!
select U.userID, U.name,sum(B.price * B.amount) `Total` ,
case when `Total` > 1500 then "VVIP"
when `Total` > 1000 then "VIP"
when `Total` > 0 then "Basic"
else "Ghsot"
end `Class`
from buytbl B
right join usertbl U on B.userID =U.userID
group by B.userID
order by `Total` desc;
# *** 비슷한 쿼리인데,,누군 되고, 누군 안 되는지에 대해서 정확히 체크!!
# ==> DB상 그대로 있는 원재료값인지,,
# 1차 가공한 값인지,,,
# 1차 가공을 기준으로 또 처리해야하는 2차 가공물인지,,,,
# +++ 임시테이블 만드는 방법도 있는데,, skip!~!
# ==> 기존의 값들을 바탕으로 내가 원하는 기준으로 변경할 때!!!
# case when~~ then
# when~~ then
# ...
# else
# end
# ==> 나이 : 십대/이십대/삼십대,,,,,
# 주소 : 수도권, 비수도권,,,
