스파르타코딩클럽 SQL 강의 3주차 정리노트
2주차까지는 어떻게 내가 원하는 table과 field를 선택하고, 내가 원하는 데이터값을 산출해내느냐를 배웠다면,
3주차부터는 table들까지 원하는대로 편집하는 법을 배운다. 대표적으로 많이 사용되는 명령어가 바로 'Join'이다.
내가 원하는 정보가 두 개 이상의 테이블에 흩어져있을 때, 그 테이블들을 합쳐 내가 데이터를 산출하기 좋은 형태로 만드는 과정에 join 명령어가 필수적이다.
Join이란, 두 테이블의 공통된 정보 (key값)을 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미한다.
Join의 종류: Left Join & Inner Join
-
Left Join example
Select * from users u
Left join point_users pu
on u.user_id = pu.user_id -
Inner join example
Select * from users u
Inner join point_users pu
on u.user_id = pu.user_id
Join 쿼리문 연습하기
ex) Select * from orders o
Inner join users u on o.user_id = u.user_id
ex) Select * from enrolleds e
Inner join courses c on e.course_id = c.course_id
2주차까지의 쿼리를 Join과 함께 실행해보기
-
Checkins 테이블에 courses 테이블 연결해서 통계치 내보기
Select c1.course_id, c2.title, count(*) as cnt from checkins c1
Inner join courses c2 on c1.course+id = c2.course_id
Group by c1.course_id -
Point_users 테이블에 Users테이블 연결해서 Point의 순서대로 정렬해보기.
Select * from point_users pu
Inner join users u on pu.user_id = u.user_id
Order by pu.point desc -
Orders 테이블에 Users 테이블 연결해서 네이버 이용자를 이름 별로 통계내기.
Select u.name, count(*) from orders o
Inner join users u on o.user_id = u.user_id
Where o.email like '%naver.com'
Group by u.name
Join 쿼리 연습 퀴즈
- 결제수단 별 유저 포인트의 평균값을 구해보기
Select o.payment_method, avg(pu.point) as avg.point from point_users pu
Inner join users u on pu.user_id = u.user_id
Group by o.payment_method
- 결제를 시작하지 않은 유저들을 성씨별로 세어보기
Select u.name, cnt(*) 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(*) as cnt_not_start from courses c
Inner join enrolleds e = c.course_id = e.course_id
Where e.is_registered = 0
Group by c.course_id
Order by cnt desc
- 웹개발, 앱개발 종합반의 Week별 체크인 수를 세어보기.
Select c1.title, c2.week, cnt(*) from courses c1
Inner join checkins c2 on c1.course_id = c2.course_id
Group by c1.title, c2.week
Order by c1.title, c2.week
(Group by나 Order by 를 여러 범주에 적용하고 싶으면, 위와 같이 각각의 범주를 ','로 연결해주면 된다. )
- 연습 4번에서, 8월 1일 이후에 구매한 고객만 추출하기.
Select c1.title, c2.week, 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
Left Join 연습
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
라고 하면, 유저들 중 포인트를 가지고 있지 않은 사람들의 정보는 Null로 나타나게 된다. 포인트를 가지고 있지 않은 유저를 가려내고 싶을 때 활용하면 누가 NULL로 뜨는지 쉽게 볼 수 있는 것이다.
예2) 7월 10일 - 7월 19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 고객의 숫자, 그리고 그 두 데이터의 비율을 보고싶다면,
(비율을 구하는 방법 명령문 - '실험군' + '/' + '모집군')
Select count(pu.point_user_id) as pnt_user_cnt, count(user_id) as total_user_cnt,
round(count(pu.point_user_id)/count(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' 명령문 사용하기
'Union'쿼리는 내가 보고싶은 결과 차트를 이어 붙여서 보고싶을 때 사용한다. 예를 들어 7월과 8월의 앱개발, 웹개발 종합반의 주차별 등록자 수를 이어붙여서 보고싶다면,
-
8월에 신청한 사람들의 정보를 먼저 선별:
Select 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 -
8월 이전, 즉 7월에 신청한 사람들 정보를 선별:
Select 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
그리고 각각의 쿼리문에 8월과 7월을 명시해준다 - (이런 것도 가능하네...?)
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
그리고,
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
라고 입력하면, 위에서 선별한 8월 수강생, 7월 수강생 차트 앞에 각각 '8월'. '7월'이라는 데이터를 입력할 수 있게 된다.
마지막으로 이어주는 코드, 'union'을 사용하면 두 결과 차트를 이어붙일 수 있게 된다.
즉,
(
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
)
union all
(
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
)
라고 입력하면 된다.
Quiz!
Enrolled_id별 수강완료한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. User_id도 함께 출력되어야 한다.
Select e1.enrolled_id, e1.user_id, count(*) as cnt from enrolleds e1
Inner join enrolleds_detail e2 on e1.enrolled_id = e2.enrolled_id
Where done = 1
Group by e1.enrolled_id, e1.user_id
라고 입력하면 끝이다.
