[스파르타/SQL] Left join~, Inner join~, Union all

컴백 4주차 같은 3주차 후기 !

오랜만에 하니까 헷갈리더라 :(

inner join, left join은 엑셀의 match 함수 느낌이었다
식이 길어지다보니 조금 헤매긴 했다
as 등으로 네이밍을 잘 해둬야 겠다

재미있었다 :D
오늘 자기 전까지 풀완주 목표 !

#################3주차
# select * from point_users p 
select * from users u #유저테이블을 u로 네이밍
left join point_users p
 on u.user_id = p.user_id 
 #포인트 테이블을 p로 네이밍. u의 아이디와 p의 아이디 조인(왼쪽 기준임)
 #null 존재 = 매칭 안 될 때

select * from users u
inner join point_users p on u.user_id = p.user_id 
 #null 외 추출 = 교집합 = 순서무관
 

##order 테이블과 user 테이블 연결하기
select * from orders o
select * from users
 #공통필드 찾기

select * from orders o
inner join users u on o.user_id = u.user_id
 
##checkins 테이블에 users 테이블 연결
select * from checkins c 
#select * from users u
inner join users u on c.user_id = u.user_id

##enrolleds <= courses
select * from enrolleds e
#select * from courses
inner join courses c on e.course_id = c.course_id


###배웠던 문법을 조인과 함께 사용해보기
#checkins 테이블에 courses 테이블 연결해서 통계치 내기/과목별 오늘의 다짐 갯수
select * from checkins c
inner join courses cs on c.course_id = cs.course_id

select c.course_id, count(*) as cnt from checkins c #as 별칭주기
inner join courses cs on c.course_id = cs.course_id
group by c.course_id 

select c.course_id, cs.title, count(*) as cnt from checkins c #as 별칭주기
inner join courses cs on c.course_id = cs.course_id
group by c.course_id 

#point_users 테이블에 users 테이블 연결해서 순서대로 정렬/포인트순
select * from point_users pu 
inner join users u on pu.user_id = u.user_id
order by pu.point desc

select pu.user_id, name, email, point  from point_users pu 
inner join users u on pu.user_id = u.user_id
order by pu.point desc

#order 테이블에 users 연결해서 통계치 내보기/네이버 멜 사용하는 유저 성씨별 주문건수
select u.name, count(*) as count_name from orders o
inner join users u on o.user_id = u.user_id 
where o.email like '%naver.com'
group by u.name
order by count_name desc


###실전쿼리
#결제수단별 유저 포인트의 평균합
select o.payment_method, ROUND(avg(p.point)) as avg  from point_users p
inner join orders o  on p.user_id = o.user_id 
group by o.payment_method 
order by avg desc

#결제하고 시작하지 않은 유저들을 성씨별로 섹
select u.name, COUNT(u.name) as cnt  from enrolleds e 
inner join users u on e.user_id = u.user_id 
where e.is_registered = 0
group by u.name
order by cnt desc

#과목별로 시작하지 않은 유저
select c.course_id, c.title, COUNT(c.title)  from enrolleds e 
inner join courses c on e.course_id = c.course_id 
where e.is_registered = 0
group by c.title

#inner join은 교집합이라서 순서 상관없다 !!!!!1

#웹개발, 앱개발 종합반의 week별 체크인 수
select c.title, c2.week, count(*) as cnt from courses c
inner join checkins c2 on c.course_id = c2.course_id 
group by c.title, c2.week #콤마로 묶기
order by title, c2.week

#8월 1일 이후의 고객만
select c.title, c2.week, count(*) from courses c
inner join checkins c2 on c.course_id = c2.course_id 
inner join orders o on c2.user_id = o.user_id 
where o.created_at >= '2020-08-01'
group by c.title, c2.week
order by c.title, c2.week

#left JOIN 
select u.name, count(*)as cnt from users u 
left join point_users pu on u.user_id = pu.user_id 
where pu.point_user_id is NULL #가입하고 시작 안 한 사람들
GROUP BY u.name
order by cnt desc

select u.name, count(*)as cnt from users u 
left join point_users pu on u.user_id = pu.user_id 
where pu.point_user_id is not NULL #가입하고 시작 한 사람들
GROUP BY u.name
order by cnt desc

##7/10~19 가입 고객 중, 포인트를 가진 고객의 숫자, 전체 숫자, 비율
select count(pu.point) as pnt_user_cnt, 
	   count(u.user_id) as tot_user_cnt, 
	   round(count(pu.point)/count(u.user_id),2) as ratio
	from users u
    left join point_users pu on u.user_id = pu.user_id 
 where u.created_at BETWEEN '2020-07-10' and '2020-07-20'


##union 필드 다 같을 때, 이어보기 (쿼리복붙)
(
	select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at < '2020-08-01'
	group by c1.title, c2.week
)
union ALL 
(
	select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at >= '2020-08-01'
	group by c1.title, c2.week
)
#union은 order 안 먹혀서 빼줘도 됨

##숙제 / enrolled_id별 수강완료(done=1)한 강의 갯수 세고, 
##완료한 강의가 많은 순서대로 정렬, user_id 추추

select e.enrolled_id, e.user_id, count(*) as max_count from enrolleds e 
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id 
where ed.done = 1
group by e.enrolled_id 
order by max_count desc