스파르타 코딩 SQL 개발일지 3주차 (복습)

02. 여러 테이블을 연결해보자: Join 이란?

테이블을 붙일 때 기준이 필요하다

 

3) Join 이란?

두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요.

 

4) Join의 종류: Left Join, Inner Join

 

left join

select * from users u
left join point_users pu
on u.user_id = pu.user_id

inner join

select * from users u
inner join point_users pu
on u.user_id = pu.user_id

 

 

03. Join 본격 사용해보기

5) Join 함께 연습해보기

[실습] orders 테이블에 users 테이블 연결해보기

select * from orders o
inner join users u
on o.user_id = u.user_id

위의 SQL 쿼리가 실행되는 순서

from: order 테이블 가져온다.

innjoin: users 테이블을 user_id 기준으로 붙인다

select: 전체 테이블을 선택해서 보여준다. 

 

[실습] checkins 테이블에 users 테이블 연결해보기

select * from checkins c
inner join users u
on c.user_id = u.user_id

 

[실습] enrolleds 테이블에 courses 테이블 연결해보기

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

04. 배웠던 문법을 Join과 함께 사용해보기

7) 배웠던 문법 Join과 함께 연습해보기

checkins 테이블에 courses 테이블 연결해서 통계치 내보기 

'오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자!

select title, count(*) as cnt from checkins ch
inner join courses c
on ch.course_id = c.course_id
group by ch.course_id

point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!

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

orders 테이블에 users 테이블 연결해서 통계치 내보기

주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!

select u.name,count(*) as cnt from orders o
inner join users u
on o.user_id = u.user_id
where u.email like '%naver.com'
group by name

위 쿼리가 실행되는 순서: from → join → where → group by → select

 

05. 이제는 실전! 본격 쿼리 작성해보기

9) [퀴즈] Join 연습1

결제 수단 별 유저 포인트의 평균값 구해보기 (어느 결제수단이 가장 열심히 듣고 있나~)

join 할 테이블: point_users 에, orders 를 붙이기

select o.payment_method ,round(avg(pu.point),0) as point_avg from point_users pu
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method

10) [퀴즈] Join 연습2

결제하고 시작하지 않은 유저들을 성씨별로 세어보기 (어느 성이 가장 시작을 안하였는가~)

join 할 테이블: enrolleds 에, users 를 붙이기

select u.name, count(*) as cnt from enrolleds e
inner join users u
on e.user_id = u.user_id
where e.is_registered = 0
group by name
order by cnt desc

11) [퀴즈] Join 연습3

과목 별로 시작하지 않은 유저들을 세어보기

join 할 테이블: courses에, enrolleds 를 붙이기

select c.course_id,c.title, count(*) as cnt_notstart from courses c
inner join enrolleds e
on c.course_id = e.course_id
where e.is_registered = 0
group by c.course_id

06. 이렇게 끝내면 아쉽죠? 한번 더 총복습!

12) [퀴즈] Join 연습4

웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!

join 할 테이블: courses에, checkins 를 붙이기

select c.title,ch.week, count(*) as cnt from courses c
inner join checkins ch
on c.course_id = ch.course_id
group by ch.week, c.title
order by c.title, ch.week

위 처럼 group by, order by를 두개씩 해줄 때 

group by는 어느것을 먼저 묶어줘도 상관이 없으나

order by는 먼저 정렬 된 것이 기준이 되서 순서가 중요한다. 

 

13) [퀴즈] Join 연습5

연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!

join 할 테이블: courses에, checkins 를 붙이고! + checkins 에, orders 를 한번 더 붙이기!

select c.title, ch.week, count(*) as cnt from courses c
inner join checkins ch on c.course_id = ch.course_id
inner join orders o on ch.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c.title, ch.week
order by c.title, ch.week

 

 

07. Left Join - 안써보니까 섭섭했죠?

 left join은 어디에 → 뭐를 붙일건지, 순서가 중요하답니다!

예를 들면 모든 유저가 포인트를 갖고 있지를 않을 수 있잖아요!

 

유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계!

속닥속닥) is NULL , is not NULL 을 함께 배워보아요!

 

is NULL (포인트가 없는 사람, 시작하지 않은 사람 성씨별로 숫자 세기)

select u.name, count(*) as cnt 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 cnt desc

 

is not NULL (포인트가 있는 사람, 시작한 사람 성씨별로 숫자 세기)

select u.name, count(*) as cnt from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point is not NULL
group by u.name
order by cnt desc

16) [퀴즈] 여기서 퀴즈! - 막해보기

7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!

아래와 같은 결과를 보고 싶다면 어떻게 해야할까요?

힌트1 → count 은 NULL을 세지 않는답니다!

힌트2 → Alias(별칭)도 잘 붙여주세요!

힌트3 → 비율은 소수점 둘째자리에서 반올림!

select count(pu.point) as pnt_user_cnt, 
	   count(*) as tot_user_cnt, 
	   round(count(pu.point)/count(*),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'

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'

 

08. 결과물 합치기! Union 배우기

17) Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우, 있을걸요!

근데, 그러려면 한 가지 조건이 있어요!

노란색과 파란색 박스의 필드명이 같아야 한답니다. 🙂 (당연하겠죠?)

18) Union을 이용해서 아래와 같은 모습을 만들어볼까요?

우선, 'month'를 붙여줘야겠네요!

없는 필드를 만들어서 안에 내용까지 첨부하는 법!

' ' as ~

select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1

 

(
	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
)

전체 선택해서 실행해야 union all 이 적용되고

그냥 아무데나 마우스 찍어서 실행하면 찍은 쪽의 테이블만 실행된다.

 

원래 안에 있던 order by 한것이 다 깨졌다.

union all 하면 원래 그렇다. 

그러나 안에 있는 order by를 지우고 가장 바깥에 order by를 다시 넣어주면 적용된다. 

근데 그걸 subquery라는 것으로 해야한다 .저번에 배웠으나 까먹었다. 

 

09. 끝 & 숙제 설명

숙제: enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기.

user_id도 같이 출력되어야 한다.

힌트!

조인해야 하는 테이블: enrolleds, enrolleds_detail

조인하는 필드: enrolled_id

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

처음에는 group by를 user_id로만 해주었다.

근데 질문 자체가 enrolled_id 별이니까 group by에서 enrolled_id가 빠지면 안된다.

enrolled_id가 무엇을 의미한 것인지 몰라서 둘이 같겠거니 해서 뺐는데

그러나 user_id 하나에 enrolled_id 두개인것도 있을 걸 보아하니 강의 신청을 하면 enrolled_id가 더 생기는 듯 하다.