[SQL 입문] Week3

3주차 

노랑이 = 배운 것 

핑크 = 내가 모르거나 틀린 것

 

1. join - 한 눈에 두 개의 테이블을 연결해서 볼 수 있음

두 테이블의 공통된 정보 key값 - 기준에서 테이블을 연결해서 보는 것

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

checkin 테이블에는 user_id는 있지만 name 없음.

user 테이블에는 사람이름이 있지만 오늘의 다짐 없음.

이때 중요한 것이 <기준>이다

이 경우에는 user_id 가 두개의 테이블에 다 있는 정보. 그 정보를 매칭하면 됨! 

 

엑셀의 vlookup의 기능과 같다! 

 

join의 종류는 left join과 inner join이 있다. 

 

(1) left join : A와 B가 교집합일때, A테이블을 기준으로 붙인다. (A + A와B가 합쳐지는 부분) 

 

일단 2개의 테이블의 결과값을 확인 원하는 key값이 공통되게 있는지 보기

select * from users

 

select * from user_point

--> user_id가 공통되는 것 확인

 

그 다음에 

select * from users u

left join point_users p on u.user_id = p.user_id

 

NULL로 있는 것은 유저아이디는 있지만 포인트 아이디가 없는 사람도 있기 때문 

 

(2) inner join은 교집합 부분만!

select * from users u

inner join point_users p on u.user_id = p.user_id

 

포인트가 있는 것만 나온다! 

 

---

(3) join 연습해보자

 

orders테이블에 users 테이블 연결해보기 (이너 조인)

select * from orders o

inner join users u on o.user_id = u.user_id

 

checkins테이블에 users테이블 연결

select * from checkins c

inner join users u on c.user_id = u.user_id

 

enrolleds 테이블에 courses 테이블 연결 

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

 

---

SQL 쿼리가 실행되는 순서

from -> join -> select 

---

배운 다른 문법과 join 써보자

 

(1) checkins테이블에 courses 테이블 연결해서 통계치 내기

과목별 오늘의 다짐 갯수 세어보기 

select c1.course_id, count(*) as cnt from checkins c1

inner join courses c2 on c1.course_id = c2.course_id

group by c1.course_id

 

과목명을 보려면

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) point_users 테이블에 users 테이블 연결 

많은 포인트별로 정리

select * from point_users pu

inner join users u on pu.user_id = u.user_id

order by pu.point desc

 

select pu.user_id, u.name, u.email, pu.point from point_users pu

inner join users u on pu.user_id = u.user_id

order by pu.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 o.email like '%naver.com'
group by u.name

---

쿼리가 실행되는 순서 

from -> join -> where -> group by -> select 하면서 카운트~

---

퀴즈풀기 

 

(1) 결제 수단 별 유저 포인트의 평균값 구하기 (어느 결제수간이 가장 열심히 듣고 있나~) 

조인테이블 - point_users 에 orders 

 

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

 

(2) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기(어느 성씨가 가장 시작을 안하였는가~~)

조인테이블 enrolleds에 users 

 

시작하지 않은 사람 is_registered = 0

 

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

 

(3) 과목 별로 시작하지 않은 유저들 세어보기

조인할 테이블 course 에다 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 e.is_registered = 0
group by c.course_id

 

(4) 웹개발, 앱개발 종합반의 week별 체크인 수를 보기 좋게 정리

조인 - course, checkins 

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

 

(5) 위에4번에서 8월 1일 이후에 구매한 고객들만 발라 내세요

조인할 테이블 course - checkins

checkins 에 orders 

팁 : 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 >= 20200801
group by c1.title, c2.week 
order by c1.title, c2.week

 

---

left join 써보기

포인트 없는 

select u.name, count(*) from users u 
left join point_users pu on u.user_id = pu.user_id 
where point_user_id is NULL
group by u.name

 

퀴즈

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

 

count는 NULL을 세지 않는 답니다

Alias 도 잘 붙여주세요

비율은 소수점 둘째자리 까지 

 

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 20200710 and 20200720

 

---

2. 결과물 합치기 Union 

필드가 같은데 7월 / 8월 따로 있을 때 붙이는 것. 

 

Union 안에서는 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 >= 20200801
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 >= 20200801
group by c1.title, c2.week 
)

 

---

숙제하기

 

enrolled_id 별 수강완료(done=1)한 강의 갯수 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기

user_id도 같이 출력되어야 한다

 

select ed1.enrolled_id, ed1.user_id, sum(ed2.done) as max_count from enrolleds ed1 
inner join enrolleds_detail ed2 on ed1.enrolled_id = ed2.enrolled_id 
where ed2.done = 1
group by ed1.enrolled_id
order by max_count desc