스파르타 코딩클럽 SQL 3주차

[수업 목표]

1. 여러 테이블의 정보를 연결하는 Join을 이해한다.
2. 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습한다.
3. 아래 위로 결과를 연결하는 Union을 공부한다.

01. Join

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

예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!

위의 예시와 같이, 두 테이블의 정보를 연결해서 함께 보고싶을 때가 있겠죠?

그럴 때를 대비해서 무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는답니다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 불러요.

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

2) Join의 종류

• Left Join : A 테이블에 겹치는 필드(Key)를 기준으로 B테이블을 연결
• Inner Join : A테이블과 B 테이블 모두 가지고 있는 데이터만 출력 (교집합)

3) inner Join 연습해보기

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

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

// Key :  user_id

• checkins 테이블에 users 테이블 연결해보기

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

// Key : user_id

• enrolleds 테이블에 courses 테이블 연결해보기

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

// Key : course_id

• 결제 수단 별 유저 포인트의 평균값 구해보기 (어느 결제수단이 가장 열심히 듣고 있나~) 
• Join 할 테이블: point_users 에, orders 를 붙이기

꿀팁 : round(숫자, 자릿수)를 이용해 반올림

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

// Key : user_id

• 결제하고 시작하지 않은 유저들을 성씨별로 세어보기 (어느 성이 가장 시작을 안하였는가~)
• join 할 테이블: enrolleds 에, users 를 붙이기
• 꿀팁 : is_registered = 0 인 사람들을 세고 order by 를 이용해서 내림차순으로 정렬하면 보기 좋겠죠?

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

// Key : user_id

• 과목 별로 시작하지 않은 유저들을 세어보기
• join 할 테이블: courses에, enrolleds 를 붙이기

꿀팁! → is_registered = 0 인 사람들을 세어보아요!

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

// Key : course_id

• 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
• join 할 테이블: courses에, checkins 를 붙이기
• 꿀팁! → group by, order by에 콤마로 이어서 두 개 필드를 걸어보세요!

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

// Key : course_id

• 연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!
• join 할 테이블: courses에, checkins 를 붙이고! + checkins 에, orders 를 한번 더 붙이기!
• 꿀팁! → orders 테이블에 inner join을 한번 더 걸고, where 절로 마무리!

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

// key : 1) course_id   2) user_id

4) Left Join

1. Left join 언제쓸까요?

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

유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계! [  is NULL , is not NULL  ]을 함께 배워보아요!

 

2. Left join 연습해보기

• 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요! 아래와 같은 결과를 보고 싶다면 어떻게 해야할까요?
• 힌트1 → count 은 NULL을 세지 않는답니다!
• 힌트2 → Alias(별칭)도 잘 붙여주세요!
• 힌트3 → 비율은 소수점 둘째자리에서 반올림!

select count(pu.user_id)as pnt_user_cnt,
           count(u.user_id)as tot_user_cnt,
           round(count(pu.user_id)/count(*),2) as ratio 
from users u 
left join point_users pu on pu.user_id = u.user_id 
where u.created_at BETWEEN "2020-07-10" and "2020-07-20"

03. 결과물 합치기! Union !

1. 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
)
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
)

 

04. 3주차 숙제

• 숙제: enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.

• 힌트!
  • 조인해야 하는 테이블: enrolleds, enrolleds_detail
  • 조인하는 필드: enrolled_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

답안 코드

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