테이블 붙이기 join
테이블을 붙일때 기준이 필요
Join이란? 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미한다.
Join의 종류: Left Join, Inner Join
left join예제
select * from users u
left join point_users p
on u.user_id = p.user_id;
설명:u.user_id와 p.user_id를 기준으로하여 이어진다.
Inner Join 예제
select * from users u
inner join point_users p
on u.user_id = p.user_id
inner join은 포인트가 있는것들만 나온다. 즉 교집합이다.
[실습] orders 테이블에 users 테이블 연결해보기
select * from orders o
inner join users u
on o.user_id = u.user_id;
[실습] checkins 테이블에 users 테이블 연결해보기
select * from checkins c
inner join users u
on c.user_id = u.user_id;
[실습] enrolleds 테이블에 courses 테이블 연결해보기
select * from enrolleds e
inner join courses c
on e.course_id = c.course_id;
SQL 쿼리가 실행되는 순서: from → join → select
- from enrolleds: enrolleds 테이블 데이터 전체를 가져옵니다.
- inner join courses on e.course_id = c.course_id: courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙입니다.
- select * : 붙여진 모든 데이터를 출력합니다.
항상 from에 들어간 테이블을 기준으로, 다른 테이블이 붙는다고 생각하면 편합니다!
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
여기서 as checkin_count가 없으면
as checkin_count가 추가되면
필드명이 바뀐다.
많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
select * from point_users p
inner join users u
on p.user_id = u.user_id
order by p.point desc
orders 테이블에 users 테이블 연결해서 통계치 내보기
네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기
select u.name, count(u.name) as count_name from orders o ⇒ users 테이블안에 있는 name 과 users안에있는 name 개수를 출력하는 동시에 as 를 이용하여 필드 이름을 count(u.name)로부터 ⇒count_name로 바꾼다.
inner join users u ⇒ 윗줄에 orders 테이블과 users 테이블을 붙여놓는다.(교집합을 이용하여 공통점을 찾는다.)
on o.user_id = u.user_id ⇒ 두 테이블은 user_id라는 공통 필드이름을 갖고있다. 이두 필드로 붙여놓는다.
where u.email like '%naver.com' ⇒ 네이버 이메일을 이용하는 유저만 가져와줘!
group by u.name ⇒ users테이블 안에있는 name 필드를 묶는다.
Where 절은, Select 쿼리문으로 가져올 데이터에 조건을 걸어주는 것을 의미해요.
예1) orders 테이블에서 결제수단이 카카오페이인 데이터만 가져와줘!
예2) point_users 테이블에서 포인트가 5000점 이상인 데이터만 가져와줘!
예3) orders 테이블에서 주문한 강의가 앱개발 종합반이면서, 결제수단이 카드인 데이터만 가져와줘!
SQL 쿼리가 실행되는 순서
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의 실행 순서는 항상 from 과 붙어다닌다고 생각하면 편해요!
[퀴즈] Join 연습1
결제 수단 별 유저 포인트의 평균값 구해보기 (어느 결제수단이 가장 열심히 듣고 있나~) join 할 테이블: point_users 에, orders 를 붙이기
꿀팁! → round(숫자,자릿수) 를 이용해서 반올림을 해볼까요?
select o.payment_method, round(AVG(p.point)) from point_users p
inner join orders o
on p.user_id = o.user_id
group by o.payment_method
여기서 round(AVG(p.point)) 중 round: 반올림을 뜻하고 AVG:평균값을 뜻한다.
즉 point_users 테이블에서 평균포인트값을 세고 반올림 해준다.
[퀴즈] Join 연습2
결제하고 시작하지 않은 유저들을 성씨별로 세어보기
(어느 성이 가장 많이 시작을 안하였는가~)
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 ⇒ neme으로 묶고
order by cnt_name desc ⇒ cnt_name 내림차순으로 정렬하라.
[퀴즈] Join 연습3
과목 별로 시작하지 않은 유저들을 세어보기
join 할 테이블: courses에, enrolleds 를 붙이기
꿀팁! → is_registered = 0 인 사람들을 세어보아요!
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
만약에 c.course_id 와 c.title문자열의 위치가 바뀌면 excel의 렬도 바뀐다.
[퀴즈] Join 연습4
웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기! join 할 테이블: courses에, checkins 를 붙이기
꿀팁! → group by, order by에 콤마로 이어서 두 개 필드를 걸어보세요!
select c1.title, c2.week, count(*) as cnt from checkins c2
inner join courses c1
on c2.course_id = c1.course_id
group by c1.title, c2.week
order by c1.title, c2.week
[퀴즈] Join 연습5
연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!
join 할 테이블: courses에, checkins 를 붙이고! + checkins 에, orders 를 한번 더 붙이기!
꿀팁! → orders 테이블에 inner join을 한번 더 걸고, where 절로 마무리!
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
Left join (복습)
users 테이블과 ↔ point_users 테이블을 left join 해보기
유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계!
is NULL , is not NULL
select 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 name
[퀴즈]
7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 추출하라!
힌트1 → count 은 NULL을 세지 않는답니다!
힌트2 → Alias(별칭)도 잘 붙여주세요!
힌트3 → 비율은 소수점 둘째자리에서 반올림!
select count(point_user_id) as pnt_user_cnt,
count(*) as tot_user_cnt,
round(count(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'
숙제: enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.
답안:
select e1.enrolled_id, user_id, count(e1.enrolled_id) as max_count from enrolleds e1
inner join enrolleds_detail e2
on e1.enrolled_id = e2.enrolled_id
where done=1
group by e1.enrolled_id
order by max_count desc
전체화면:
답안을 알려주는 힌트.ㅎㅎ
덕분에 아주 쉽게 배운듯한 느낌!
'sparta coding club .kr' 카테고리의 다른 글
| SQL 4주차 개발일지 (0) | 2021.10.30 |
|---|---|
| SQL 하루만에 완성하기 - 실전 데이터 분석 (0) | 2021.10.24 |
| SQL 2주차 개발일지 (0) | 2021.10.24 |
| SQL 1주차 개발일지 (0) | 2021.10.24 |
| 로그인 페이지를 만들면서 How I felt while creating the login page (0) | 2021.09.20 |