오늘의 다짐 이벤트 : 오늘의 다짐을 남겨준 10명 추첨해서 기프티콘 지급

문제 발생 !

'오늘의 다짐' 이벤트 당첨자를 추첨하기 위해서는, 이름과 연락처 등의 정보를 알아야 하는데 여기에는 user_id라는 정보만 있다.

Join

(0) 실무에서 가장 많이 쓰인다.
(1) '기준' 설정
(2) Key 이해

한 테이블에 모든 정보를 담을 수도 있겠지만, 불필요하게 테이블의 크기가 커져 불편해진다. 그래서, 데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요한 경우 연결할 수 있도록 만들어놓는다.

예를 들면, users와 checkins 테이블에 동시에 존재하는 user_id,
이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 부른다.

Inner Join & Left Join 이해

• Left Join : 왼쪽 (A) 테이블을 기준으로 (B) 테이블을 조인시킨다.
  
• Inner Join : 두 테이블의 교집합
  

먼저,

SELECT * FROM users ;
SELECT * FROM point_users;

위 쿼리문을 통해 user_id가 공통 컬럼임을 확인할 수 있다.

Inner Join

SELECT * FROM users u
# 교집합
Inner join point_users p 
on u.user_id = p.user_id;

Inner Join 이 더 쉽고 많이 쓰일 가능성이 높다.

1. orders 테이블에 users 테이블 연결해보기
   주문을 하기 위해서는 회원정보가 있어야 할테니, orders 테이블에 담긴 user_id는 모두 users 테이블에 존재하겠죠?

SELECT * FROM orders o 
Inner Join users u 
on o.user_id = u.user_id 

2. checkins 테이블에 users 테이블 연결해보기
   연결의 기준이 되고싶은 테이블을 from 절에,
   기준이 되는 테이블에 붙이고 싶은 테이블을 Join 절에 위치해 놓습니다.

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 ;

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

---

Inner Join 퀴즈

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

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 ;

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

SELECT p.user_id, u.name, u.email,p.point  
FROM point_users p

inner join users u
on p.user_id = u.user_id 
order by p.point DESC ;

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

SELECT u.name, COUNT(*) as CNT from orders o 
inner join users u 
on o.user_id = u.user_id 
WHERE o.email like '%naver.com'
group by u.name 

• 위 쿼리가 실행되는 순서: from → join → where → group by → select
• Join의 실행 순서는 항상 from 과 붙어다닌다고 생각

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

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

SELECT o.payment_method as 결제수단, 
ROUND(AVG(pu.point)) as 평균  FROM orders o 
inner join point_users pu 
on o.user_id = pu.user_id 
group by o.payment_method 

SELECT * FROM point_users pu ;

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

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

SELECT u.name, COUNT(*) as CNT FROM enrolleds e
inner join users u 
on e.user_id = u.user_id 
WHERE is_registered = 0
group by u.name 
order by CNT DESC ; 

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

4. 웹개발, 앱개발 종합반의 week 별 체크인 수

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

SELECT c.title, c2.week, COUNT(*) as CNT 
FROM courses c
inner join checkins c2
on c.course_id = c2.course_id 
group by c.title, c2.week
order by c.title, c2.week

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

join 할 테이블: courses에, checkins 를 붙이고 +checkins 에, orders 를 한번 더 붙이기!

SELECT c.title, c2.week, COUNT(*) as CNT
FROM courses c

inner join checkins c2
on c.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 c.title, c2.week  
order by c.title, c2.week 

Left Join

# 별칭 제공
SELECT * FROM users u
left join point_users p 
on u.user_id = p.user_id;

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

# is NULL , is not NULL 처리
SELECT 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 not NULL 

Left Join 퀴즈

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

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'

Union

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

아래와 같은 모양으로 어떻게 만들까 ?

(
	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' # 8월 미만
	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
	
)

Project

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
order by CNT DESC