2023/1/13

Join

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

Left join, inner join

Left join

A 와 B 테이블이 있을때 A 테이블을 기준으로 붙이는 것. 왼쪽에 있는 테이블을 기준으로 붙인다 해서 Left join

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

Inner join

null 값이 아닌 데이터만 나온다. 교집합

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

---

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

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

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

---

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

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 테이블 연결해서 많은 포인트를 얻은 순서대로 정렬해보기

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;

• orders에 users를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수 세기

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

---

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

select o.payment_method, round(avg(pu.points), 1) 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 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;

• 과목별로 시작하지 않은 유저들 세어보기(courses, enrolleds)

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

---

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

select co.title, ch.week, count(*) as cnt from courses co
inner join checkins ch
on co.course_id = ch.course_id
group by co.title, ch.week
order by co.title, ch.week;

• 위에서 8/1 이후에 구매한 고객들만.

select co.title, ch.week, count(*) ass cnt from course co
inner join checkins ch 
on co.course_id = ch.course_id
inner join orders o
on ch.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by co.title, ch.week
order by co.title, ch.week;

---

한쪽에는 있는데 한쪽에는 없는 것을 가지고 통계내고 싶을 때 - left join

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 (not) NULL
group by u.name

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

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

unnion에서는 order by가 동작하지 않는다.

합치고 나서 order by해야함.

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

---

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