[SQL] 3주차 (Join, Union)

테이블을 나눠놓는 이유
→ 한 목적에 맞는 것들만 모아두는 것이 가장 좋기 때
 
테이블을 잇는 기준
→ 같은 속성을 기준으로 매칭시킴.
 

1. Join

join
: 두 테이블의 공통된 정보(key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보이는 것을 의미
 
종류
: left join, inner join, outer join ...

 

left join

: A테이블을 기준으로 B를 붙이는 것.
어디에 뭐를 붙일건지 순서가 중요!
한 쪽에는 있는데 다른 한 쪽에는 없는 것을 가지고 통계 내고 싶을 때 사용.

왼쪽에 있는 것을 기준으로 붙인다고 해서 left join

 

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

→ users (u) 테이블과 point_users (p) 테이블의 user_id를 기준으로 조인
 

모든 회원이 포인트를 가지고 있는 것이 아니기 때문에 비어있는 데이터(NULL 값)가 있을 수 있음

 

inner join

:  A 테이블과 B테이블의 교집합 부분을 나타내는 것

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

→ checkins 테이블에 courses 테이블 연결해서 통계치 낸 것
 

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

→ 주문 정보에서 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수 센 것

 

실행 순서는 from > join > where > group by > select

---

2. 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'
	group 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
)

---

3. 퀴즈 & 과제

 

select o.payment_method, round(avg(pu.point),0) as avg_point from point_users pu 
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method

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

 

select u.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

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

 

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

>> 과목 별로 시작하지 않은 유저들 세기

 

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 asc, c2.week asc

>> 웹개발, 앱개발 종합반의 week 별 체크인 수 세기

 

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 asc, c2.week asc

>> 웹개발, 앱개발 종합반의 week별 체크인 한 사람 중 8월 1일 이후 구매 고객 세기

 

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

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

 

select e.enrolled_id, 
	   user_id, 
	   count(*) as max_count 
  from enrolleds e 
 inner join enrolleds_detail ed 
    on e.enrolled_id = ed.enrolled_id 
 where ed.done = 1
 group by enrolled_id 
 order by max_count desc

>> enrolled_id별 수강완료한 강의 갯수 세기 (완료한 강의 수가 많은 순서로 정렬)