TIL)SQL Join, Union

Join

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

예) 오늘의 다짐 이벤트 담청자를 선정하여 기프티콘을 지급하는 이벤트

      users_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고싶을 때

 

데이터 테이블을 나누는 이유? 실제로 웹서비스가 동작하는데 간편함

종류: Left Join, Inner Join

Left Join : A 와 B 중 교집합 + A쪽

Inner Join : 교집합

NULL값 : 매칭이 안됨, 존재하지 않는 값 

 

별칭을 줘야함.

예)

select * from orders o

inner join users u on o.user_id = u.user_id       -> orders 테이블에 users 테이블을 users_id로 잇는다. 

 

1. 과목별 오늘의 다짐 갯수 세어보기 쿼리 checkins-> c1 courses -> c2

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. 유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!

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 (내림차순)

 

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

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'      >> 문자는 '%' 숫자는 where = n

group by u.name

 

위 쿼리의 진행 순서 : from > join > where > group by > select

 

연습문제

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

select o.payment_method, round(avg(pu.point),0) from point_users pu

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

group by o.payment_method 

 

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

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 name

order by count(*) desc      <<내림차순 정렬

*count(*) 사이 띄우지 말기!!!! 

 

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

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

inner join enrolleds e on e.course_id = c.course_id

where e.is_registered = 0

group by e.course_id

 

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

select c1.title, c2.week, count(*) 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일 이후에 구매한 고객들만 발라내어 보세요!

select c1.title, c2.week, count(*) 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

 

Left Join

어디에 뭐를 붙일건지, 순서가 중요

언제쓰나? 한쪽에는 있는데 한쪽에 없는 것으로 통계 내고 싶을 때

 

1. 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!  (어려움. 반복학습)

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-19'

 

Union

같은 필드의 다른 값들을 위 아래로 붙이고 싶을 때 -- 7월, 8월, 9월....

(

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에서는 order by 안 먹힘. 지워도 무방 

)

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

)

 

3주차 숙제

enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력.

 

select e1.enrolled_id, 

            e1.user_id, 

            count(done) as max_count 

from enrolleds e1

inner join enrolleds_detail e2 on e1.enrolled_id = e2.enrolled_id 

where done = 1

group by e1.enrolled_id

order by max_count desc

 

3주차 Feedback 

숙제를 해설 없이 해내서 좋다. 이해가 되어가는 느낌.