[엑셀보다 쉬운 SQL] 3주차 강의노트

Join

- 두 테이블의 공통의 변수(key)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것

- join은 엑셀의 vlookup과 동일한 기능

- 실행 순서 : from -> join -> select 

ex) users 테이블에 point_users 테이블 연결하기

select * from users u
left join point_users p #별칭을 각각 u,p로 설정
on u.user_id = p.user_id #users 테이블의 user_id와 point_user 테이블의 user_id를 같다고 보고 연결해라

 

join의 종류

1) left join

- 왼쪽에다 붙이는 것

- 한 쪽에는 있는데 한 쪽에는 없는 걸 가지고 통계낼 때 사용

• 포인트가 없는 사람(=시작하지 않은 사람)을 성씨별로 통계내기

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 NULL #NULL은 값이 없다는 의미! '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_user_cnt 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' #부등호 쓰면 안 되고 between으로!

- 줄정렬을 해 주면 훨씬 깔끔하고 보기 좋다!

select count(pu.point_user_id) as pnt_user_cnt, 
       count(u.user_id) as tot_user_cnt 
  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' #부등호 쓰면 안 되고 between으로!

- 마지막으로 pnt_user_cnt와 tot_user_cnt를 이용해 ratio를 구한다!

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 '2020-07-10' and '2020-07-20'

 

2) inner join

- 교집합만 남기는 것

- 쿼리는 left join과 동일. left -> inner

- left보다는 inner를 더 많이 사용함. 이걸 기본값으로 사용하기

 

문법 응용

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

• '오늘의 다짐' 정보(checkins)에 과목 정보(courses) 연결해 과목별 '오늘의 다짐' 갯수를 세어보자

- c1의 course_id와 c2의 course_id는 동일값이지만, group by를 하기 위해서는 명확하게 c1인지 c2인지 짚어줘야 한다. 

select c1.course_id, count(*) as cnt from checkins c1 #반점을 꼭 적어줘야 함!! -> and의 의미
inner join courses c2 on c1.course_id = c2.course_id 
group by c1.course_id

- as ~는 변수의 이름 바꿀 때 사용

- 만약 옆에 변수값을 추가하고 싶으면 select c1.course_id, c2.title, count(*)로 이어주면 됨

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

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

2) point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기

• 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!

select * from point_users p
inner join users u on p.user_id = u.user_id
order by p.point desc

- 정렬할 때는 order by 사용. 오름차순이면 그대로 두고 내림차순이면 desc

- user_id, name, email, point만 남기고 싶다면 select를 쓰자

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

 

3) orders 테이블에 users 테이블 연결해서 통계치 내보기

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

- 특정 이메일을 사용하는 유저를 골라내려면 where절을 사용!

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

 

쿼리 연습

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

- point_users 에 orders 붙이기

- 포인트가 많은 순으로 정렬하려면 order by 하면 됨!

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

 

- payment_method는 orders에만 있기 때문에(중복 데이터가 아니기 때문에) o.payment_method라고 안 하고 payment_method라고만 입력해도 되긴 됨. 다만, 헷갈릴 수 있으니까 변수를 붙이도록 하자

 

2) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기

- enrolleds에 users를 붙이기

- 결제수단이 카카오페이인 것만 빼낼 때 [ where payment_method = "kakaopay" ]  **where절 형태 까먹지 않기!!

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 u.name
order by cnt_name desc

- where 절에서 0은 숫자니까 " " 쓰지 않아도 됨

 

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

- 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 e.is_registered = 0
group by c.course_id

 

4) 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!

- courses에 checkins을 붙이기

select cr.title, ch.week, count(*) as cnt from checkins ch #checkins와 courses바꿔도 괜찮
inner join courses cr on ch.course_id = cr.course_id
group by ch.week, cr.title
order by cr.title, ch.week #titler과 week의 순서를 바꾸면 week별로 정렬됨

group by cr.title, ch.week

group by ch.week, cr.title

5) 연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!

- 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' #8월 1일 이후니까 포함
group by c1.title, c2.week

- inner join을 두 번 써서 세 개의 테이블을 연결할 수 있다.

- where절에서 날짜 조건인 경우 부등호로! [ where o.created_at >= '2020-08-01' ]

 

union

- 테이블을 합치는 기능

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

- 괄호 위치와 들여쓰기(tab) 조심하기

- union을 사용하면 내부 정렬이 먹지 않으므로 SubQuery(서브쿼리)를 이용한다.