스파르타 코딩 SQL 개발일지 3주차

1. Join을 배우자

테이블과 테이블을 붙이는 것

실무에서 매번 쓰인다

거의 모든 query에 쓰인다.

 

애초에 테이블을 왜 나누는거지?

테이블은 애초에 한 목적에 맞는 것들만 모아두는것이 좋다.

실제 웹서비스가 동작할 때 편하다. 

그렇지 않으면 수정하고 싶을 때 문제가 생김.

 

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

 

02.

Join이란?

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

 

Join 종류

Left Join, Inner Join

 

left join

A, B는 각각의 테이블

가운데 교집합은 key 값

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

NULL값은 포인트가 아직 생성되지 않은 유저의 경우에는 point_user 테이블에 정보가 없고 그러므로  NULL로 비어있다고 나옴

 

Inner Join

이 경우에는 포인트 값이 있는 유저만 보여줌

null 값이 나오지 않음

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

03. Join 본격 사용해보기

 강사님이 일단 inner join 하라고 했는데 난 left join으로 해버렸음

inner join으로 하라고 한 이유는 누가 누구에게 붙느냐는 순서가 별로 중요하지 않기 때문이다.

 

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

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

 

 

orders 데이터수가 users 보다 적지만 orders를 기준으로 자료를 더해준것이라  null 값이 없다.

 

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

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

checkins 자료 수가 더 적지만 checkins를 기준으로 공통 된 것을 찾아서 더해주기 때문에 NULL값이 나오지 않음.

 

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

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

courses 테이블에는 딱 두줄 두가지 강좌에 대한 정보가 있는데, 

course_id 만 같으면 이 정보다 복사되어서 다 들어간다.

 

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

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

04. 배웠던 문법과 join을 같이 써보자

 

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

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

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

alias를 써줘서 잘 구별하자.  group by에서 alias를 안 해주니 어느 필드는 지칭하는지 모른다는 오류가 떴다.

 

 

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

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

select pu.point_user_id ,name,u.email, point from point_users pu
inner join users u on pu.user_id = u.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 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 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.

05. 본격 쿼리 작성하기 퀴즈

 

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

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

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

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

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

꿀팁! → order by 를 이용해서 내림차순으로 정렬하면 보기 좋겠죠?

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

 

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

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

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

select c.course_id, 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.course_id

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

 

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

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

꿀팁! → group by, order by에 콤마로 이어서 두 개 필드를 걸어보세요!

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

 

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

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

• checkins 에, orders 를 한번 더 붙이기
• 꿀팁! → orders 테이블에 inner join을 한번 더 걸고, where 절로 마무리!

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

이후, 이상을 표현 할 때는 >=를 써주었다 

 

쿼리가 달라도 같은 결과가 나올 수 있고, 모로가도 데이터만 나오면 된다. 

 

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

NULL을 이용한 통계를 내고 싶은 때 사용한다.

 

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

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

select count(pu.point) as pnt_user_cnt ,
	   count(u.user_id) as tot_user_cnt,
	   round(count(pu.point)/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 배우기

두가지 결과물을 같이 보여주고 싶을 때 

각각의 query를 괄호로 묶어준 후 중간에 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
)

이렇게 했을 때 order by 로 정렬해놨던 것이 사라진다.  이게 없어도 결과는 똑같다.

 

안되는 이유

합친다음에 정렬을 해야지 합치기 전에 정렬 하면 안된다.

합친 후에 정렬하는 것을 4주차에 배울 것임 = 서브쿼리

 

09. 끝 & 숙제 설명

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

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

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