SQL 강의 3, 4주차 (08.04)

---

SQL 강의 3주차

• 1. 여러 테이블의 정보를 연결하는 Join을 이해한다.
• 2. 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습한다.
• 3. 아래 위로 결과를 연결하는 Union을 공부한다.

---

Join

Join 이란?

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

---

Left 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 테이블에 존재하지 않는 경우

---

LEFT Join 연습

• users 테이블과 ↔ point_users 테이블을 left join 해보자

select * from users u
left join point_users pu on u.user_id = pu.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

---

Left Join 퀴즈

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

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'

---

Inner Join

여기서 A와 B는 각각의 테이블을 의미한다. 이 그림은 두 테이블의 교집합을 의미한다.

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

같은 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;

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

---

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

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

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

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

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

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

---

Inner Join 퀴즈

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

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

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

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

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

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

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

연습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

(
	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을 사용하면 내부 정렬이 되지 않는다.
이 때 SubQuery(서브쿼리)를 사용한다.

---

HW

enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_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

SQL 강의 4주차

Subquery

Subquery 란?

하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것

---

**kakaopay로 결제한 유저들의 정보 보기

• inner join

select u.user_id, u.name, u.email from users u
inner join orders o on u.user_id = o.user_id
where o.payment_method = 'kakaopay'

• Subquery

select u.user_id, u.name, u.email from users u
where u.user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
)