스파르타_ SQL 3주차

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

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

 

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

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

 

🌈 Join의 종류: Left Join, Inner Join

     

💛 Left Join

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

 

🧡 Inner Join

 

 

 

Join 본격 사용해보기

 

🍪 orders 테이블에 users 테이블 연결해보기

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

 

🍪 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;

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

1) from enrolleds: enrolleds 테이블 데이터 전체를 가져온다.
2) inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의          course_id와 동일한 course_id를 갖는 courses의 테이블을 붙인다.
3) select * : 붙여진 모든 데이터를 출력한다.

 

 

 

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

 

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

select co.title, count(co.title) as checkin_count from checkins ci
inner join courses co
on ci.course_id = co.course_id 
group by co.title

 

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

select * from point_users p
inner join users u 
on p.user_id = u.user_id
order by p.point desc

 

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

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

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

1) from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙인다.
2) inner join users u on o.user_id = u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙인다. (*users 테이블에 u라는 별칭을 붙임)
3) where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져온다.
4) group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐줌.
5) select u.name, count(u.name) as count_name : users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력.

👉 Join의 실행 순서는 항상 from 과 붙어다닌다고 생각!

 

 

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

 

🍪 Join 연습1

결제 수단 별 유저 포인트의 평균값 구해보기
join 할 테이블: point_users에, orders를 붙이기

select o.payment_method, round(AVG(p.point)) from point_users p
inner join orders o 
on p.user_id = o.user_id 
group by o.payment_method

 

🍪 Join 연습2

결제하고 시작하지 않은 유저들을 성씨별로 세어보기
join 할 테이블: enrolleds에, users를 붙이기

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

 

🍪 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 is_registered = 0
group by c.course_id

 

🍪 Join 연습4

웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보기
join 할 테이블: courses에, checkins를 붙이기

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

 

🍪 Join 연습5

연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보기
join 할 테이블: courses에, checkins 를 붙이고! + checkins에, orders 를 한번 더 붙이기!

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

 

 

Left Join

 

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

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

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name

select name, count(*) 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 name

 

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

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

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

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

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

 

 

 

결과물 합치기! Union 배우기

⭐ Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우

      => 그러려면 한 가지 조건, 노란색과 파란색 박스의 필드명이 같아야 함

 

🍪 Union 사용해보기

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

⭐ 아래 위로 Union all을 사용해 붙여주면 끝!

(
	select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at < '2020-08-01'
	group by c2.course_id, c2.week
  order by c2.course_id, c2.week
)
union all
(
	select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at > '2020-08-01'
	group by c2.course_id, c2.week
  order by c2.course_id, c2.week
)

🔥 union을 사용하면 내부 정렬이 먹지 않음. (이 때 SubQuery(서브쿼리)를 사용함)

 

 

 

 

문제

 

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

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

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