[수업 목표]
- 여러 테이블의 정보를 연결하는 Join을 이해한다.
- 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습한다.
- 아래 위로 결과를 연결하는 Union을 공부한다.
JOIN
두 테이블을 연결한다는 의미인 Join
두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미해요.
예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!
Join의 종류: Left Join, Inner Join
left join
어떤 데이터는 모든 필드가 채워져있지만, 어떤 데이터는 비어있는 필드가 있습니다.
꽉찬 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재해서 연결한 경우
비어있는 데이터: 해당 데이터의 user_id 필드값이 point_users 테이블에 존재하지 않는 경우
inner join
앗, 여기서는 비어있는 필드가 있는 데이터가 없어요!
그 이유는, 같은 user_id를 두 테이블에서 모두 가지고 있는 데이터만 출력했기 때문이에요.
[실습] enrolleds 테이블에 courses 테이블 연결해보기
select from enrolleds e
inner join courses c
on e.course_id = c.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
많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기
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
결제 수단 별 유저 포인트의 평균값 구해보기
(어느 결제수단이 가장 열심히 듣고 있나~)
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
결제하고 시작하지 않은 유저들을 성씨별로 세어보기
(어느 성이 가장 시작을 안하였는가~)
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
inner join 은 교집합, left join 은 첫번째 원에 붙이는 것!
예를 들면 모든 유저가 포인트를 갖고 있지를 않을 수 있잖아요!
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
퀴즈
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'
Union
Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우,
(
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
)
숙제
enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.
- 조인해야 하는 테이블:
enrolleds,enrolleds_detail - 조인하는 필드:
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
