[SQL] 스파르타코딩클럽 내일배움단 "엑셀보다 쉬운 SQL" 3주차 개발일지 (내일배움카드 SQL)

3주차

이번 주가 벌써 SQL 종강이다. 오늘 내일 한강씩 듣고 완강하자.

3주 차도 가보자고!

 

1. 오늘 배울 것

• 오늘은 Join, 즉 테이블과 테이블을 붙이는 법을 배워볼 것이다.
• 굉장히 실무에서 많이 쓰인다. 거의 모든 쿼리에 Join이 들어간다고 보면 됨.
• 활용 예시) 오늘의 다짐을 보고 기프티콘을 주고 싶은데, 오늘의 다짐 테이블에는 회원 이름은 없고 user ID만 있다. 이름이 들어 있는 테이블이랑 합쳐서 쓰면 한 판에 연결해서 볼 수 있다.
• Q1) 근데 애초에 왜 테이블을 다 나눠 놓나요? 한 목적에 맞는 것만 모아두는 것이 가장 편하고 효율적이기 때문이다.
• Q2) 그럼 이 두 테이블을 뭘 기준으로 잇나요? checkin과 user 테이블 모두 user_id를 갖고 있다. 이걸 기준으로 잇는 것!

 

 

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

• 두 테이블의 공통된 정보, 즉 key값을 기준으로 테이블을 연결해 한 테이블처럼 보는 것.
• 엑셀의 vlookup과 동일하다. 사실 SQL의 join이 Excel의 vlookup보다 쉽다.
• Join의 종류: Left join과 Inner join. (outer join이란 것도 있긴 한데 거의 쓸 일이 없다)

 

Left Join

A(왼쪽 테이블)을 기준으로 + B(오른쪽)과의 교집합까지 같이 붙여 나타내는 것.

 

사용하는 법

우선 DBeaver를 열고 테이블들을 쭉 다 펼쳐 보자. users 테이블에다 point_users 테이블을 붙일 것이다.

그리고 따라 쓴다:

select * from users
select * from point_users

지난 시간에 배운 alias, 별칭을 써보자. 별칭은 짧게.

select * from users u
select * from point_users p

left join 함수 작성. on: 뭘 기준으로

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

엔터 치면 결과가 뜬다. 이때 Null값은? 정보값이 없는 데이터들임. 포인트가 없는 유저들도 있으니까.

 

Inner Join

A, B 두 테이블의 교집합만 나타내는 것.

 

left를 inner로만 바꾸면 포인트의 값 있는 데이터들만, 즉 교집합이 나온다.

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

inner가 left보다 좀 더 쉽고 활용도가 높을 것이라 생각됨. (left는 순서가 중요하기 때문에 좀 더 어렵다)

일단 시작하는 단계에서는 inner join으로 먼저 연습하는 게 좋다.

 

실습

1. orders 테이블에 users 테이블 연결해 보기

일단 두 테이블을 봐야 함.

select * from orders

select * from users

왔다 갔다 하면서 뭐가 겹치는지 파악. user_id임 -> 별칭을 준다. -> inner join, on 작성.

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

 

2. checkins 테이블에 users 테이블 연결해 보기

user id가 겹친다.

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

 

3. enrolleds 테이블에 courses 테이블 연결해 보기

course_id가 겹치네.

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

*여기서 쿼리 실행 순서: from -> join -> select

enrolleds 테이블을 먼저 읽고, join을 하면서 courses 테이블을 읽는다. 테이블이 붙은 상태에서 select가 이루어진다.

 

join과 그동안 배웠던 문법을 함께 사용해 보기

1. checkins 테이블에 courses 테이블 연결해서 통계치 내보기
-> 과목별 오늘의 다짐 개수 세어보기 (~별: group by!)

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

 

2. point_users 테이블에 users 테이블 연결해서 순서대로 정렬해 보기
-> 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자! (포인트 정보가 담긴 테이블에 유저 정보를 연결)

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

보려는 것만 발라서 좀 더 깔끔하게 보려면-

select pu.users_id, name, 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

 

3. orders 테이블에 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.com'
group by u.name

위 쿼리의 실행 순서: from -> join -> where -> group by -> select

 

 

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

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

(point_users에 orders 붙이기, 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

 

카카오페이로 결제한 유저들이 포인트를 많이 받았네..?로 확인할 수 있음.

 

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

(enrolleds에 users 붙이기 / enrolleds의 is_registered가 시작하였으면 1, 아니면 0으로 뜬다.)

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 u.name
order by cnt desc

 

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

(courses에 enrolleds를 붙이기)

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

 

 

4. 한번 더 총 복습

퀴즈 4. 웹개발, 앱개발 종합반의 week별 체크인 수를 세어서, 보기 좋게 정리해 보기

(courses에 checkins를 붙이기 / group by, order by에 콤마로 이어서 두 개 필드를 걸어 보기)

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

 

퀴즈 5. 퀴즈 4번에서 8월 1일 이후에 구매한 고객만 발라내서 보기

(courses에 checkins를 붙이기 / checkins에 orders를 한번 더 붙이기 / orders에 inner join을 한 번 더 걸고, where절로 마무리)

select * from courses c
inner join checkins ch
on c.course_id = ch.course_id

일단 여기서 먼저 출발!

 

select c.title, ch.week, count(*) 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 like '2020-08%'
group by c.title, ch.week
order by c.title, ch.week

밑줄 친 부분을 수업에서는 >= '2020-08-01'로 작성했다.

생각해 보니 내가 쓴 쿼리로는 2021년 이후 데이터는 포괄할 수 없으니 수업대로 쓰는 것이 맞을 듯!

 

"모로 가도 데이터만 나오면 된다" 명언쓰..

SQL은 같은 결과에 도달할 수 있는 방법이 여러 가지다. 강사님도 말씀하셨지만 강사님의 쿼리가 꼭 정답은 아니며,

어느 누구도 한 번에 위에서부터 쭉 줄줄줄 쿼리를 작성해 실행시켜 음 결과가 나왔군 이런 사람은 없음.

따라서 "모로 가도 데이터만 나오면 된다"는 마음으로 작업해도 무방하다. (이런 따스한 격려 눈물줄줄..!🥺)

 

 

5. Left Join

레프트 조인은 어느 한쪽을 기점으로 다른 쪽을 붙이는 것.

이렇게 하면 결과에 null이 잔뜩 나오는데 null인 것만 뽑을 수가 있다. "필드이름 is NULL" 이렇게 작성.

이렇게 하면 회원가입 했는데 포인트가 없는 사람들을 색출해(?) 낼 수 있다.

반대로 "필드이름 is not NULL"로 하면 그 여집합도 볼 수 있지.

 

즉 left join은 없는 것들(null값)을 포함해 통계를 내고 싶거나 할 때 쓸 수 있다!

 

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

(힌트: count는 null을 세지 않는다, null이 아닌 것만 자동으로 세서 나옴 / alias를 잘 붙이기 / 비율은 소수점 둘째 자리에서 반올림하기)

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

여기서 시작!

select count(pu.point) as cntpoint,
           count(*) as cnttotal,
           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'

19일까지 포함하고 싶으니까 between 뒤의 값이 07-20인 점 유의하기!

 

 

6. 결과물 합치기 - Union 배우기

Union은 필드가 다 똑같을 때, 두 필드를 붙여서 이어서 볼 수 있게 해 주는 기능.

 

4번의 퀴즈 5 코드를 그대로 복사해 와서 활용:

select c.title, ch.week, count(*) 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

 

여기 첫 줄 select 뒤에 '8월' as month를 끼워 넣고 실행시키면 결과에 8월로 된 왼쪽 필드가 생긴다.

그다음에 전체를 위에 한 번 더 복붙한 다음 7월로 바꿔줌:

 

select '7월' as month, c.title, ch.week, count(*) 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

select '8월' as month, c.title, ch.week, count(*) 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

 

이 둘을 잇는 것이다. union all이라는 문법을 사용할 것. "위에 있는 애와 밑에 있는 애를 union all 합니다"

(
select '7월' as month, c.title, ch.week, count(*) 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
)
union ALL 
(
select '8월' as month, c.title, ch.week, count(*) 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
)

 

근데 이렇게 하면 order가 1,2,3 순서대로 안 나오고 깨진다. 사실 union에서는 order by가 안 먹는다. 그래서 빼줘도 동일하다.

왜 안먹게? 둘을 합쳐서 전체가 나왔으니까, 합친 것에서 다시 order를 해야지 order 한 상태로 합칠 수는 없다는 것.

그래서 일단 합치기를 다 한 다음에 마지막으로 order by 한다. 이것은 4주 차의 subquery 개념에서 다룰 것.

 

 

7. 끝 & 숙제 설명

숙제

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 
order by cnt DESC 

 

join을 완료했으니 SQL의 정수까지 온 것이다. 다음 시간에는 마지막으로 배워야 하는 기능들과 연습해 보고 종강할 것!