데이터분석? 처음부터 한 걸음씩 - SQL #3

여러 테이블을 연결할 때 사용하는 join

join이란?

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

join의 종류 : Left join, inner join

Left join
왼쪽에 있는 테이블을 기준으로 다른 테이블을 붙이는 것

left join 테이블명 on 일치하는 필드명

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

→ users 테이블이 왼쪽에 나오고 user_id를 기준으로 오른쪽에 point_users 테이블이 나옴

inner join
교집합과 같은의미(비어있는 필드가 없이 두 테이블에서 모두 가지고 있는 데이터만 출력)

inner join 테이블명 on 일치하는 필드명

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

→ users 테이블이 왼쪽에 나오고 user_id를 기준으로 오른쪽에 point_users 테이블이 나오는데 point_users 테이블에 있는 데이터 중 user_id 필드값이 채워져 있는 테이블만 나옴.
(users에 등록된 사람중에 point를 획득하지 않아 point_users에 등록되지 않은 사람도 있는데 Left join과 달리 NULL값으로 나오지 않음)

★ SQL 쿼리가 실행되는 순서

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

위 쿼리가 실행되는 순서: from → join → select

1) from enrolleds: enrolleds 테이블 데이터 전체를 가져온다.

2) inner join courses on e.course_id = c.course_id
courses를 enrolleds 테이블에 붙이는데, enrolleds 테이블의 course_id와 동일한 course_id를 갖는 courses의 테이블을 붙인다.

3) select * : 붙여진 모든 데이터를 출력한다.
항상 from에 들어간 테이블을 기준으로, 다른 테이블이 붙는다고 생각하면 편함.

연습

1) checkins 테이블에 courses 테이블 연결해서 통계치 내보기
과목별 오늘의 다짐 갯수 세어보기

select co.title, count(co.title) as checkin_count 
from checkins ci
inner join courses co on ci.course_id = co.course_id 
group by co.title

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

특정 필드만 선택해서 보려면

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) orders 테이블에 users 테이블 연결해서 통계치 내보기
네이버 이메일 사용하는 유저의 성씨별 주문건수 세어보기

select u.name, count(u.name) as count_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

위 쿼리가 실행되는 순서: from → join → where → group by → select

1) from orders o
orders 테이블 데이터 전체를 가져오고 o라는 별칭을 붙인다.

2) inner join users u on o.user_id = u.user_id
users 테이블을 orders 테이블에 붙이는데, orders 테이블의 user_id와 동일한 user_id를 갖는 users 테이블 데이터를 붙인다. (*users 테이블에 u라는 별칭을 붙입니다)

3) where u.email like '%naver.com'
users 테이블 email 필드값이 naver.com으로 끝나는 값만 가져온다.

4) group by u.name
users 테이블의 name값이 같은 값들을 뭉쳐준다.

5) select u.name, count(u.name) as count_name
users 테이블의 name필드와 name 필드를 기준으로 뭉쳐진 갯수를 세어서 출력해준다.

퀴즈(inner join 활용)

웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
join 할 테이블: courses에, checkins 를 붙이기

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

위의 문제에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!
join 할 테이블: 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'
group by c1.title, c2.week
order by c1.title, c2.week

Left join 활용하기 → 어디에 뭐를 붙일건지 순서가 중요함

예를 들면 모든 유저가 포인트를 갖고 있지를 않을 수 있잖아요!
users 테이블과 ↔ point_users 테이블을 left join 해보면

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

is NULL , is not NULL 을 사용하기!

유저 중에, 포인트가 없는 사람(=즉, 시작하지 않은 사람들)의 통계를 알 수 있다.

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

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

퀴즈

7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요
힌트 → count는 NULL을 세지 않는다

select count(point_user_id) as pnt_user_cnt,
       count(*) as tot_user_cnt,
       round(count(point_user_id)/count(*),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 : 결과물 합치기

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

union을 사용하면 내부 정렬이 먹지 않아요.
(oder by가 적용이 안되서 없어도 됨)
이 때 유용한 방법이 있지요. 바로, SubQuery(서브쿼리) → 다음 시간에