스파르타 SQL 3주차 내용 정리

학습 목표

1. 여러 테이블의 정보를 연결하는 Join을 이해한다.

2. 연결된 정보를 바탕으로 보다 풍부한 데이터 분석을 연습한다.

3. 아래 위로 결과를 연결하는 Union을 공부한다

 

Join 맛보기

• *오늘의 다짐 이벤트: 오늘의 다짐을 남겨준 10명 추첨해서 기프티콘 지급하는 이벤트 를 진행하고자 함.
• '오늘의 다짐' 이벤트 당첨자를 추첨하기 위해서는, 이름과 연락처 등의 정보를 알아야 하는데 여기에는 user_id라는 정보만 있다.
• users 테이블의 user_id 필드와, checkins 테이블의 user_id 필드의 이름이 같다는 사실 발견

이런 방법으로 진행한다면 되지 않을까요?

1. checkins 테이블의 user_id를 복사

2. users 테이블에서 해당 user_id를 갖는 데이터를 가져오기

3. 작성자를 조회하고 싶은 '오늘의 다짐' 개수대로 1, 2 과정을 반복

 

Tip

• 한 테이블에 모든 정보를 담을 수도 있겠지만, 불필요하게 테이블의 크기가 커져 불편해진다
• 그래서, 데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요한 경우 연결할 수 있도록 만들어놓습니다.
• 예를 들면, users와 checkins 테이블에 동시에 존재하는 user_id 처럼. 이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 부른다.

 

Join이란?

• 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블 처럼 보는 것을 의미.
• ex) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보기

두 테이블을 연결해서 보고 싶을 때를 대비해서

무언가 연결된 정보가 있을 때, user_id 처럼 동일한 이름과 정보가 담긴 필드를 두 테이블에 똑같이 담아놓는다.

이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'

 

 

Join의 종류 : LEFT Join, INNER Join

1) LEFT Join

 

 

• 여기서 A와 B는 각각의 테이블을 의미
• 둘 사이의 겹치는 부분은, 테이블 A와 B의 key 값이 연결되는 부분일 것이다.

 

[유저 데이터로 LEFT Join 이해해보기]

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

 

• 어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있다.
• 꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
• 비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우

• 비어있는 데이터의 경우, 회원이지만 수강을 등록/시작하지 않아 포인트를 획득하지 않은 회원!

 

2) 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를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문.

* Left Join을 했을 때 빈 필드가 없는 데이터의 개수와, Inner Join을 했을때의 전체 데이터의 개수가 같은지 확인해보면...

Inner join이 빈 필드의 개수가 없기 때문에 차이가 난다.

---

JOIN 본격 사용해보기

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

• INNER Join을 사용해서 주문 정보에, 유저 정보를 붙여서 보기

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

• 주문을 하기 위해서는 회원정보가 있어야 할테니, orders 테이블에 담긴 user_id는 모두 users 테이블에 존재한다.

 

일단, 공통적인 것을 찾아야 하기 때문에

1) select * from orders

    select * from users  를 써서 각각 본다.

2) 그리고 user_id 가 공통으로 들어간다는 것을 파악.

3) 그러면, user_id로 이으면 되겠구나!!! 깨달으면 됨.

 

 

 

 

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

• Inner Join을 사용해서 '오늘의 다짐' 테이블에, 유저 테이블을 붙여서 보기

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

• 연결의 기준이 되고싶은 테이블을 from 절에, 기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치시킴

작성 순서

1) select * from checkins c

2) inner join users u on c.user_id = u.user_id;

 

 

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

• Inner Join을 사용해서 '수강 등록' 테이블에, 과목 테이블을 붙여서 보기

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 *

: 붙여진 모든 데이터를 출력합니다.

 

항상 from에 들어간 테이블을 기준으로, 다른 테이블이 붙는다고 생각하면 수월하다.

 

LEFT Join은 A에다가 B를 붙여라와 같이 순서가 매우 중요한데 비해

INNER Join은 순서가 별로 중요하지 않다.

 

---

배웠던 문법을 Join과 함께 사용해보기

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

- 오늘의 다짐' 정보에 과목 정보를 연결해 과목별(-> group by) '오늘의 다짐' 개수를 세어보기

 

select * from checkins c1

select * from checkins c2 로 공통분모 확인 course_id

 

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

 

작성 순서 편집 필요

1) select * from checkins c1
2) inner join courses c2 on c1.course_id = co.course_id
3) group by c1.course_id 

4) 1수정 select c1.course_id, c2.title, count(*) as cnt from checkins c1

 

 

 

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

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

 

select * from point_users

select * from users           로 공통분모 확인 => user_id

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

작성 순서

1) select * from point_users pu

2) inner join users u     on pu.user_id = u.user_id

3) order by pu.point desc

4) 1수정... select pu.user_id, u.name, u.email, pu.point from point_users pu

 

 

 

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

- 주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중(WHERE) , 성씨별 주문건수 세기

 

select * from orders

select * from users  로 확인 => user_id

 

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

<작성 순서>

1) select * from orders o

2) inner join users u on o.user_id = u.user_id

3) where u.email like '%naver.com'

4) group by u.name

5) 1수정 select u.name, count(u.name) as count_name from orders o

 

 

위의 쿼리가 실행되는 순서

• 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 필드를 기준으로 뭉쳐진 개수를 세어서 출력해줍니다.

 

Join의 실행 순서는 항상 from 과 붙어다닌다고 생각하면 편하다.

 

---

본격 쿼리 작성해보기

[퀴즈] Join 연습 1 

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

• join할 테이블: point_users에, orders를 붙이기

select * from point_users

select * from orders         ===> 공통분모 user_id

 

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

 

<작성 순서>

1) select * from point_users pu

2) inner join orders o  on p.user_id = o.user_id

3) group by o.payment_method // 결제수단별

4) 1수정... select o.payment_method, round(AVG(p.point), 2) from point_users pu // 소수점 두번째 자리까

 

 

[퀴즈] Join 연습2

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

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

select * from enrolleds

select * from users     ===> 공통분모 : user_id

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

 

<작성 순서>

1) select * frin enrolleds e

2) inner join users u  on  e.user_id = u.user_id

3) where is_registered = 0

4) group by u.name //성씨별로

5) 1수정... select u.name, count(*) as cnt from enrolleds e

6) order by cnt desc // 내림차순 정

    혹은 order by count(*) desc

 

 

 

[퀴즈] Join 연습3

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

• 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

<작성 순서>

1) select * from courses c

2) inner join enrolleds e on c.course_id = e.course_id

3) where is_registered = 0

4) group by c.course_id

5) 1수정 select c.course_id, c.title, count(*) as cnt_notstart from course c

 

 

 

[퀴즈] Join 연습4

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

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

select * from courses

select * from checkins ===> course_id

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

<작성 순서>

1) select * from courses c1

2) inner join checkins c2 on c1.course_id = c2.course_id

3) group by c1.title, c2.week

4) 1수정... seldec c1.title, c2.week, count(*) as cnt from course c1

5) order by c1.title, c2.week

 

 

 

 

*****[퀴즈] Join 연습5

Q) 연습4번에서, 8월 1일 이후에 구매한 고객들만 추려내기

• join할 테이블: courses에, checkins를 붙이고 + checkins에, orders를 한번 더 붙이기!
• tip : 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

<작성 순서>

1) select * from courses c1

2) inner join checkins c2 on c1.course_id = c2.course_id

3) inner join orders o c2.user_id = o.user_id

4) where o.created_at >= '2020-08-01'

5) group by c1.title, c2.week

6) 1수정... select c1.title, c2.week, count(*) as cnt from course c1

7) order by c1.title, c2.week

 

---

LEFT Join 복습하기

users 테이블과 point_users 테이블을 left join 해보기

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

여기서 NULL 인 것만 취해보기

=> 회원인데 point가 없는 사람만 취해봅시다.

where pu.point_user_id is NULL 

group by u.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 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

 

최종

select u.name, count(*) as cnt from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by u.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'

<작성 순서>

1) select * from users u

2) left join point_users pu on u.user_id = pu.user_id

3) where u.created_at between '2020-07-10' and '2020-07-20' // 날짜 기준으로 짜르

4) 1수정... select count(pu.point_user_id) as pnt_user_cnt, count(u.user_id) as tot_user_cnt from users_u //user_id 세기(자동으로 null 빼고 세짐)

5) 1수정.. select count(pu.point_user_id) as pnt_user_cnt count(u.user_ud) as tot_user_cnt, 

                 round(count(pu.point_user_id) / count(u.user_i, as ratio 2)

 

---

결과물 합치기, UNION

• Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우

이 경우 노란색과 하늘색의 필드명이 같아야 한다.

 

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

 

그런데, 내부 정렬을 사용하면 내부 정렬이 작동하지 않는다.(order by 불가)

해결책은 서브쿼리

 

---

마지막 숙제

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

 

조건

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

2) 조인해야 하는 테이블: enrolleds , enrolleds_detail

3) 조인하는 필드: enrolled_id

 

select * from enrolleds e

select * from enrolleds_detailed ==>  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

1) select * from enrolleds e

2) inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id

3) where ed.done = 1

4) group by e.enrolled_id, e.user_id // 같이 묶었음.

5)) 1수정... select e.enrolled_id, e.user_id count(*) as cnt // 숫자 세기

6) order by cnt desc