[스파르타 코딩 클럽] 엑셀보다 쉬운 SQL 3주차
1. Join
테이블과 테이블을 붙이는 것 (정보 매칭)
테이블을 붙일 때는 공통된 정보인 특별한 '기준(key값)'이 필요함
엑셀의 vlookup과 동일한 기능
종류
(1) Left Join: A테이블을 기준으로 B테이블을 붙임
from 뒤에 들어간 테이블을 기준으로 다른 테이블을 붙임 (아래에서는 users가 기준)
*어디에 무엇을 붙일것인지 순서가 중요!
select * from users u left join point_users pu on u.user_id = pu.user_id
의미: 공통 부분(key 값)인 user_id가 동일한 것끼리 매칭하여 left join 실행
(2) Inner Join: 교집합인 부분만 결과가 나타남
select * from users u inner join point_users pu on u.user_id = pu.user_id
Inner Join
(예시1) orders 테이블에 users 테이블 연결
select * from users u
inner join orders o on u.user_id=o.user_id
(예시2) checkins 테이블에 users 테이블 연결
select * from checkins c
inner join users u on c.user_id=u.user_id
(예시3) enrolleds 테이블에 courses 테이블 연결
select * from enrolleds e
inner join courses c on e.course_id=c.course_id
(예시4) checkins 테이블에 courses 테이블 연결해서 통계 낸 후, 과목별 오늘의 다짐 갯수 세기
select * from checkins ch
inner join courses c on c.course_id=ch.course_id
나의 답안
select title, comment, count(*) from checkins ch
inner join courses c on c.course_id=ch.course_id group by title
영상 답안
select ch.course_id, c.title, count(*) from checkins ch
inner join courses c on c.course_id=ch.course_id group by ch.course_id
(예시5) point_users 테이블에 users 테이블 연결해서 순서대로 정렬 후, 많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
select * from point_users pu
inner join users u on pu.user_id=u.user_id
order by pu.point desc
이 중에서, user_id, name, email, point 만 보려면,
select pu.user_id, u.name, u.email, pu.point from point_users pu
inner join users u on pu.user_id=u.user_id
order by pu.point desc
(예시6) order 테이블에 users 테이블 연결 후, 네이버 이메일을 사용하는 유저의 성씨별 주문건 수 세기
select u.name, count(*) from orders o
inner join users u on o.user_id=u.user_id
where u.email like '%naver.com' group by u.name
(예시7) 결제 수단 별 유저 포인트의 평균값 구해보기
select o.payment_method, round(avg(pu.point),0) from point_users pu
inner join orders o on pu.user_id=o.user_id
group by o.payment_method
(예시8) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
select u.name, count(*) from users u
inner join enrolleds e on e.user_id=u.user_id
where e.is_registered=0
group by u.name
order by count(*) desc
(예시9) 과목별로 시작하지 않은 유저들을 세어보기
select e.course_id, count(*) from courses c
inner join enrolleds e on e.course_id=c.course_id
where e.is_registered =0
group by e.course_id
Group by 2개도 가능
(예시10) 웹개발, 앱개발 종합반의 week 별 체크인 수
select c.title, ch.week, count(*) from checkins ch
inner join courses c on ch.course_id=c.course_id
group by ch.week, c.title
이 때, 앱개발, 웹개발을 구분지으려면 order by 추가 사용
order by c.title, ch.week 추가
Join 한 표에 추가하기
inner join을 한번 더 하면 추가 연결이 가능
(예시11) 위 데이터에서 8월 1일 이후에 구매한 고객들만
select c.title, ch.week, count(*) from checkins ch
inner join courses c on ch.course_id=c.course_id
inner join orders o on o.user_id =ch.user_id
where o.created_at >='2020-08-01'
group by c.title, ch.week
order by c.title, ch.week
Left Join
(예시12) 유저 중 포인트가 없는(=시작을 안했는지) 유저를 찾고, 어떤 성씨를 가진 사람들이 주로 등록만 하고 시작을 안했는지
select u.name, count(*) from users u
left join point_users pu on u.user_id=pu.user_id
where pu.point is NULL
group by u.name
order by count(*) desc
Left Join은 주로 '없는 것들을 포함(NULL)'한 통계를 낼 때 사용
단, count 는 NULL을 세지 않음
(예시13) 7월 10~19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율
포인트를 가진 고객의 숫자
select count(*) 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'
and pu.point is not NULL
또는
select count(pu.point_user_id) 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'
count 는 NULL을 세지 않기 때문에 자동으로 포인트가 있는 유저의 수만 카운팅 된다.
포인트를 가진 고객의 숫자와 포인트가 없는 고객의 숫자를 한번에 확인
select count(pu.point_user_id) as pnt_user_cnt, count(u.user_id) as tot_user_cnt 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'
비율을 구하려면,
select count(pu.point_user_id) as pnt_user_cnt,
count(u.user_id) as tot_user_cnt,
round(count(pu.point_user_id)/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'
2. Union
select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우
(예: 7,8월 데이터를 연속으로 나타내기)
(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
order 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
order by c1.title, c2.week)
*order by를 하더라도 union all에서는 먹지 않기 때문에, order by는 맨 마지막에 해준다.
(예시14) enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기.
이 때, user_id도 함께 출력한다.
select e.enrolled_id, e.user_id, count(*) as max_cnt from enrolleds_detail ed
inner join enrolleds e on ed.enrolled_id=e.enrolled_id
where ed.done=1
group by e.enrolled_id, e.user_id
order by max_cnt desc