SQL강의 내용정리 3주차(feat MySQL)

본 포스팅은 스파르타코딩클럽 SQL강의를 참고하였습니다.

3주차 강의 목표

1. 여러 테이블의 정보를 연결하는 Join을 이해한다.
2. 연결된 정보를 바탕으로 보다 풍부한 데이터분석을 연습한다.
3. 아래 위로 결과를 연결하는 Union을 공부한다.

😀join까지는 어느정도는 알고있는 내용인데 Union은 처음듣는 용어라 기대되는 강의가 되겠다.

여러 테이블의 정보를 연결하는 Join

관계가 지어진 여러 테이블에서 원하는 정보들을 엮어서 데이터를 추출할 수 있게 해주는 SQL문법이 Join 이다.
Join은 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 볼 수 있게 해준다.

Join의 종류

• INNER JOIN(내부 조인)은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.

• OUTER JOIN(외부 조인)은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다. LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 모두 외부 조인에 속한다.

• CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.

• SELF JOIN(자체 조인)은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.

강의 내용에서는 LEFT OUTER JOIN과 INNER JOIN만 잘써도 원하는 대부분의 결과를 얻을 수 있어. 강의 내용에서는 LEFT OUTER JOIN과 INNER JOIN만 강의를 진행한다.

left join과 inner join

users 테이블과 point_users테이블을 users테이블을 기준으로 left join해보자

select * from users u
left join point_users pu on u.user_id = pu.user_id

SELECT * from 기준이 될 테이블
left join 연결할 테이블 ON 공통된 필드

left join은 기준 테이블의 데이터를 모두 가져오고 연관된 테이블 데이터를 붙이는데, 연관되지 않은 경우는 null필드를 추가해 가져온다.

inner join의 경우는 반드시 두 테이블이 연결지어진 데이터필드만을 가져온다.

select * from users u
inner join point_users pu on u.user_id = pu.user_id

inner Join 연습문제

1. orders 테이블에 users 테이블 연결해보기

• 각 테이블에서 관계설정된 공통된 필드를 찾는다.
  select from orders => select from users
• user_id가 양쪽에 존재하는 것을 확인하고 join문을 작성한다.
  select * from orders o
  inner join users u on o.user_id = u.user_id

2. checkins 테이블에 users 테이블 연결해보기

• 1번 문항과 같은 과정을 반복해서 테이블을 연결한다.
  select * from checkins c
  inner join users u on c.user_id = u.user_id

3. '오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자! 과목별 오늘의 다짐 갯수 세어보기.

• 오늘의 다짐 데이터가 있는 테이블과, 과목정보가 있는 테이블을 조회해서 공통된 필드를 찾는다
  show tables > select from checkins > select from courses
• couse_id가 공통된 필드인 것을 확인하고 묶는다.
  select * from checkins ch inner join courses co on ch.course_id = co.course_id
  
• 과목별로 comment의 개수를 세어야 하기 때문에 과목에 따라 묶어주고 count를 이용해서 수를 센다.
  select ch.course_id, count(*) from checkins ch
  inner join courses co on ch.course_id = co.course_id
  group by ch.course_id
  
• course_id만 보고 알아보기 힘들기 때문에 title을 같이 넣어준다.
  select ch.course_id, co.title, count(*) from checkins ch
  inner join courses co on ch.course_id = co.course_id
  group by ch.course_id
  

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

• 유저의 포인트 정보가 담긴 테이블과, 유저 정보가 담긴 테이블을 찾는다.
  show tables > select from point_users > select from users
• 두 테이블에서 공통된 필드가 되는 user_id를 찾고 묶는다.
  select * from point_users pu
  inner join users u on pu.user_id = u.user_id
  
• point 필드를 내림차순으로 정리한다.
  select * from point_users pu
  inner join users u on pu.user_id = u.user_id
  order by pu.point desc
  

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

• 주문 정보에 유저 정보를 연결한다고 하니 orders테이블과 users테이블을 보고 공통된 필드를 찾는다.
  show tables > select from orders > select from users
• 공통된 필드가 되는 user_id를 확인하고 묶는다.
  select * from orders o
  inner join users u on o.user_id = u.user_id
  
• 네이버 이메일을 사용하는 유저라는 조건이 추가되어 where절을 이용해서 이메일 사용자를 추려낸다.
  select * from orders o
  inner join users u on o.user_id = u.user_id
  where u.email like '%@naver.com'
  
• 성씨별로 묶어서 주문건수를 count한다.
  select u.name, count(u.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
  

7-04 추가

추가 예제들

1. 결제 수단 별 유저 포인트의 평균값 구해보기
   
2. 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
   
3. 과목 별로 시작하지 않은 유저들을 세어보기
   
4. 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
   
5. 연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보기!
   
6. 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 구하기!
   

결과물 합치기! Union 배우기

7월 데이터와 8월 데이터를 보려면 각각
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

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

두 번의 쿼리 실행으로 따로따로 봐야하는데 이러한 두번의 과정을 한번에 처리할 수 있는 것이 union이다.
(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 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)

두 개의 쿼리를 각각 ()로 하나의 묶음으로 묶고 중앙에 union all을 통해서 한번에 처리할 수 있도록 동작시킨다.

하지만 union을 사용하면 위의 그림과 같이 쿼리에서는 order by를 이용해 정렬을 해주었지만, 위 그림에서 정렬이 흐트러진것을 볼 수 있다. 일반적으로 union만 사용하게 되면 내부 정렬이 적용되지 않는다. 이러한 문제점을 해결할 수 있는 방법으로 subQuery(서브쿼리)가 존재하는데 다음포스팅에서 정리해 보자.

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

select e.enrolled_id,
e.user_id,
count(*)
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 count desc