[항해99 SQL] 3주차

join

테이블이 서로 분리되어 있을 때, 어떻게 table을 join할 것인지.

두 테이블의 공통된 정보 값을 기준으로 테이블을 연결 짓는 것.

 

join의 종류: Left Join, Inner Join

1) Left Join

a 와 b table이 있을 때, a table을 기준으로 붙이는 것.

select * from users u

left join point_users p

on u.user_id = p.user_id

 

왼쪽을 기준으로 붙이는 것을 의미함.

 

2)Inner Join

select * from users u

inner join point_users p

on u.user_id = p.user_id

 

교집합을 의미. NULL인 부분이 있으면 포함시키지 않고 진행.

(u 와 p로 별칭을 만들어준 것을 확인할 수 있다)

교집합이니까 순서가 바뀌어도 상관이 없다.

---

 

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

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

 

웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보자!

select c.title, ch.week, count(*) as cnt from courses c

inner join checkins ch

on c.course_id = ch.course_id

group by c.title, ch.week

order by c.title, ch.week

 

위에 것 + 8월 1일 이후에 구매한 고객들!

select c.title, ch.week, count(*) from courses c

inner join checkins ch on c.course_id = ch.course_id

inner join orders o on ch.user_id = o.user_id

where o.created_at like '2020-08%'

group by c.title, ch.week

order by c.title3

 

 

left join 의 경우 null인 경우가 생기게 되는데 이 경우는

where ~~~ is NULL 또는 is not NULL 을 이용해주면 된다.

count의 경우 NULL을 자동으로 제외하고 세준다.

 

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

 

7월 10일 ~ 7월 19일에 가입한 고객 중,

포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보여줘라

select count(pu.user_id) as pnt_user_cnt,

count(u.user_id) as tot_user_cnt,

round(count(pu.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 배우기

 

union all은 위 쿼리랑 아래 쿼리를 이어 준다.

* 유의 해야할 점: 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

)

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주차 숙제: Join/Subquery을 사용하여 유저별로 수강완료 한 강의 수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해 보기

 

select ed.enrolled_id, e.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 ed.enrolled_id

order by count(*) desc

 

 

결과 화면