SQL 3주차

Join

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

 

두 테이블의 정보를 연결해서 함께 보고싶을 경우 무언가 연결된 정보가 있을 때 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는다. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 부른다.

 

Left Join

A에 B를 붙이든, B에 A를 붙이든 어느 하나를 기준으로 붙이는 것으로 A와 B 순서가 중요할 수 있다. (한쪽 테이블에는 있고 다른 한 테이블에는 없을때)

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

> user의 별칭 u

> point_users의 별칭 p

>> left join 으로 u.user_id = p.user_id

> 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

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

> is NULL , is not NULL 을 사용해서 정보를 골라내기

 

Q. 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 조회

• count 은 NULL을 세지 않음
•  Alias(별칭)도 잘 붙여주기
• 비율은 소수점 둘째자리에서 반올림

select * 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'

>  우선 7월10일 ~ 7월19일에 가입한 고객을 발라내고

---

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

---

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

---

소수점 둘째자리에서 반올림

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

---

Inner Join

교집합으로 이해하면 좋으며, 값이 있는 것만 나타내준다.

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

---

예제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 테이블에 courses 테이블 연결해보기

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

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 * : 붙여진 모든 데이터를 출력합니다.
4. 위 쿼리가 실행되는 순서: from → join → select

---

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

예제1. '오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보기

select * from checkins c1
inner join courses c2 on c1.course_id = c2.course_id

▼

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

---

여기서 count의 별칭을 주고, courses의 title도 함께 보고 싶다면

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

---

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

예제2. 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보기

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

---

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

---

orders 테이블에 users 테이블 연결해서 통계치 내보기

예제3. 주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보기

select u.name, count(*) from orders o
inner join users u on o.user_id = u.user_id 
where u.email like '%naver.com'
group by u.name

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 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해줍니다.
6. 위 쿼리가 실행되는 순서: from → join → where → group by → select

---

Q1. 결제 수단 별 유저 포인트의 평균값 구해보기

• join 할 테이블 :  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

 

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

• 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 
= count 별칭 안했을 경우
= order by count(*) desc

 

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

• join 할 테이블: courses에, enrolleds 를 붙이기
• is_registered = 0 인 사람들을 세어보기

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

 

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

• join 할 테이블: courses에 checkins 를 붙이기
• group by, order by에 콤마로 이어서 두 개 필드를 걸어보기

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

> group by, order by 각각에 두 개의 필드를 콤마로 이어서 내걸 수 있다

 

Q5. 4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보기

• join 할 테이블: courses에, checkins 를 붙이고!
• + checkins 에, orders 를 한번 더 붙이기!
• orders 테이블에 inner join을 한번 더 걸고, where 절로 마무리!

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

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

---

Union

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월이 생성됨

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

---

(
	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 all로 두 개를 묶어줄 수 있다.

> union에서는 order by가 적용되지 않는데, 합쳐진 전체에서 정렬을 해주어야지 정렬을 하고 합치면 그 정렬은 깨지게 되는 것이다. 이 때 유용한 방법은 SubQuery(서브쿼리) 이다.

---

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

 

힌트

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

SELECT e.enrolled_id, e.user_id, 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, e.user_id
order by count(*) desc