엑셀보다 쉬운 SQL 3주차
순식간에 하다보니 벌써 3주차가 되었다. 코딩에 '코'자도 몰랐지만 SQL로 코딩입문을 하면서 재미를 느끼는중.. 무언가를 새로 배운다는 것은 항상 즐겁다.😀
3주차에서는 여러 테이블의 정보를 연결하는 join에 대해서 다룬다. 이것을 통해 아래 위로 결과를 연결하는 union에 대해서 공부한다.
-
join이란? 두 테이블의 공통된 정보(key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미한다.
예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶은 경우.이럴 때를 대비해서 무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'🔑라고 부른다.
- join을 사용해서 key값으로 두 테이블 연결해보기
select * from point_users
left join users
on point_users.user_id = users.user_id
이렇게 하면 point_users 테이블과 user 테이블의 있는 user_id를 한눈에 볼 수 있다!
-
join의 종류: Left join, Inner join
-
Left join
- SQL에서의 Join은 두 집합 사이의 관계와 같다.
여기서 각각 A와 B는 테이블을 의미하고 A와 B의 교집합은 key값이 연결되는 부분을 말한다.
select * from users u
left join point_users p
on u.user_id = p.user_id;
이런식으로 비어있는 필드가 있는 경우가 있다.
- 꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
- 비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우
비어있는 데이터의 경우, 회원이지만 수강을 등록/시작하지 않아 포인트를 획득하지 않은 회원인 경우이다!
- Inner join
여기서 각각 A와 B는 테이블을 의미하고 A와 B의 모두 가지고 있는 데이터를 말한다.
< 유저 데이터로 Inner join 이해하기 >
select * from users u
inner join point_users p
on u.user_id = p.user_id;
여기서는 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문에 비어있는 필드가 있는 데이터가 없다.
- join 연습하기
orders테이블에users테이블 연결해보기
select * from orders o 테이블 먼저 보기 > user_id가 있다!
select * from users u 테이블 먼저 보기 > 공통으로 user_id가 있다!
select * from orders o
inner join users u
on o.user_id = u.user_id;그러면 orders와 users 테이블이 user_id로 하나의 테이블로 묶이게 된다.
checkins테이블에users테이블 연결해보기
select * from checkins c
inner join users u
on c.user_id = u.user_id;팁! 연결의 기준이 되고싶은 테이블을 from절에, 기준이 되는 테이블에 붙이고 싶은 테이블을 join절에 위치해 놓는다.
- SQL 쿼리가 실행되는 순서
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 * : 붙여진 모든 데이터를 출력한다.
- 네이버 이메일을 사용하는 유저의 성씨별 주문건수 세어보기
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
-
from orders o: orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙인다. -
inner join users u on o.user_id= u.user_id : users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙인다. (*users 테이블에 u라는 별칭을 붙인다.) -
where u.email like '%naver.com': users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져온다. -
group by u.name: users 테이블의 name값이 같은 값들을 뭉쳐준다. -
select u.name, count(u.name) as count_name: users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해준다.
- [퀴즈] join 연습1
< 결제 수단 별 유저 포인트 평균값 구해보기 >
join 할 테이블: point_users 에, orders 를 붙이기
select payment_method, round(avg(pu.point)) as avg_point
from point_users pu
inner join orders o on pu.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 courses c1
inner join checkins c2 on c1.course_id = c2.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 
- 결과물 합치기: union
연습5 쿼리에서 이걸 만들어 주기 위해서는,
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 우선 month를 붙여준다.
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 '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을 사용하면 내부 정렬은 적용되지 않는다! 이때는 다음주차에 배울 SunQuery(서브쿼리)를 써줘야 한다.
- [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
