[엑셀보다 쉬운 SQL] week3: Join

수업 목표

 

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

 

 

강의 자료 

[내일배움단] 엑셀보다 쉬운 SQL - 3주차

---

1. 3주차 오늘 배울 것

Join: 여러 정보를 한 눈에 보기

• 만약, 오늘의 다짐을 남겨준 10명 추첨해서 기프티콘을 지급해주는 이벤트를 진행한다고 하자.
• 하지만 checkins에는 users의 정보가 없다. 
• 따라서 checkins 테이블과 users 테이블을 join해주어야 한다.

 

Join을 사용한다면?

• 두 테이블을 연결해서 한 눈에 볼 수 있다.

 

그렇다면 테이블을 왜 나눌까?

• 한 목적에 맞는 것들만 모아놓은 것이 바로 테이블이다.
• 그래서 테이블을 나눠서 묶어 모아둔다.
• 그래야만 실제로 웹 서비스가 동작할 때 더 편하기 때문이다.

 

어떻게 두 테이블을 이을 수 있을까?

• 기준으로.
• users 테이블과 checkins 테이블에는 user_id가 있다. 따라서 두 값을 매칭시키면 된다. (user_id가 기준임)

 

 

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

Join이란?

Join

• 두 테이블의 공통된 정보(=key값)를 기준으로 테이블을 연결해서 하나의 테이블로 보는 것
• ex) user_id를 기준으로 users 테이블과 orders 테이블을 보고 싶은 경우

❗엑셀의 vlookup과 동일함

 

Join의 종류

Left Join

• 왼쪽 테이블(A)을 기준으로 B 테이블을 붙인다는 의미
• 즉, A + A∩B
• 한 쪽에 없는 내용을 가져올 때 사용함
• ex) uesr_id를 기준으로 users 테이블(A)과 point_users 테이블(B)를 left join하기

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

 

Inner Join

• left join에서는 users 테이블에서 기준인 user_id가 있되, point_users에서 해당 user_id가 없는 값들도 모두 나왔다면, 
• inner join에서는 users 테이블과 point_users 테이블 모두 가진 user_id 값만 나온다.
• 즉, 교집합의 값(A∩B)만 테이블에 띄운다.
• ex) uesr_id를 기준으로 users 테이블(A)과 point_users 테이블(B)를 inner join하기

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

 

Join 연습해보기

실습 1) orders 테이블에 users 테이블 연결해보기

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

 

실습 2) checkins 테이블에 users 테이블 연결해보기

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

 

실습 3) enrolleds 테이블에 course 테이블 연결해보기

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

• 특정 user가 어떤 강의를 신청했는지 알 수 있음!

 

SQL 쿼리 실행 순서

• from ➡️ join ➡️ select

 

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

Q1) checkins 테이블에 courses 테이블 연결해서 과목별 오늘의 다짐 개수 통계치 내보기

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

 

Q2) point_users 테이블에 users 테이블 연결해서, 많은 포인트를 얻은 유저 순서대로 정렬해보기

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

 

Q3) order 테이블에 users 테이블 연결해서 네이버 이메일을 사용하는 유저의 성씨별 주문건수 통계치 내보기

select u.name, count(*) as cnt from orders o 
inner join users u on o.user_id = u.user_id
where o.email like '%@naver%'
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 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

 

 

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

Quiz 1) 결제 수단 별 유저 포인트의 평균값 구해보기 (round를 이용하여 반올림도 해보기)

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

 

Quiz 2) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

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

 

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

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

 

 

4. 한번 더 총 복습

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

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

 

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

select c.title, c2.week, count(*) as cnt from courses c 
inner join checkins c2 on c.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 c.title, c2.week 
order by c.title, c2.week

 

❗SQL문은 여러 가지 정답이 있을 수 있다

 

 

5. Left Join

응용 (+ is NULL, is NOT NULL)

• NULL과 관련된 것 데이터를 통계 내고 싶은 경우
• ex 1) 가입을 해서 users는 있는데, 포인트가 없는 유저를 찾고 싶은 경우(=가입은 했지만 강의 시작을 안한 유저들). left join을 하고 where point_user_id is NULL 인 정보를 불러오면 됨.

select u.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 u.name

'이'씨와 '김'씨가 많음을 알 수 있다

• ex 2) 강의를 시작한 성씨를 통계를 낸다면

select u.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 u.name

 

퀴즈

Quiz ) 7월 10일~19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 전체 숫자, 비율을 보자.

이렇게 나오면 됨

❗hint

• 힌트1 → count 은 NULL을 세지 않는답니다!
• 힌트2 → Alias(별칭)도 잘 붙여주세요!
• 힌트3 → 비율은 소수점 둘째자리에서 반올림!

 

Answer )

1. 기간 내 가입 고객 불러오기: where u.created_at between '2020-07-10' and '2020-07-20'
2. group by, order by 할 필요 없음
3. 포인트 가진 고객의 숫자 = count(pu.point_user_id)
4. 전체 숫자 = count(*) 혹은 count(u.user_id)
5. 두 값의 비율 = round(count(pu.point_user_id)/count(*),2)

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

 

 

6. 결과물 합치기: Union

Union

언제 사용하나?

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

• ex) 노란색이 7월달, 파란색이 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월 데이터니까, 테이블 앞에 8월이라고 쓰고 month 값을 넣어주자

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

• 7월 테이블도 만들어준다.

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 '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에서는 order by가 안먹힌다. 그래서 order by를 빼자. 왜냐하면 합친 것에서 order by를 해줘야 하기 때문.

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

 

 

7. 끝 & 숙제 설명

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

❗hint

• 조인해야 하는 테이블: enrolleds, enrolleds_detail
• 조인하는 필드: enrolled_id

나와야하는 결과

 

answer )

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