SQL - Join (스파르타코딩 - 엑셀보다 쉬운 SQL 3주차)

* Join이란?

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

 

* inner join / left join

inner join : 교집합

left join : 왼쪽 테이블에 붙이기 : 다른 테이블에 없는 것을 포함해서 통계를 내고 싶을 때 사용

 

예) user_id 필드를 기준으로 users 테이블과 orders 테이블을 연결해서 한 눈에 보고 싶어요!

-- orders 테이블에 users 테이블 연결하기

select * from orders o 
    inner join users u 
    on o.user_id = u.user_id;

 

예제) 

-- checkins 테이블에 courses 테이블을 연결해서 통계치 내보기 -> 오늘의 다짐 정보에 과목 정보를 연결해 과목별 오늘의 다짐 갯수를 세어보자

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

 

 

-- 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;

 

 

• quiz

1. 결제 수단 별 유저 포인트의 평균값 구해보기 (point_users에 orders 붙이기)

2. 결제하고 시작하지 않은 유저들을 성씨별로 세어보기 (enrolleds에 users 붙이기)

3. 과목 별로 시작하지 않은 유저들을 세어보기 (courese에 enrolleds 붙이기)

4. 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보기 (courses에 checkins 붙이기) 

5. 연습 4번에서, 8월 1일 이후에 구매한 고객(orders 테이블에 정보 있음)들만 출력

6. 7월10일~7월 19일에 가입한 고객 중, 전체고객 수, 포인트를 가진 고객 수, 비율을 출력  *count는 null을 세지 않는다

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

 

-- 정답 (정렬 및 대소문자는 본인의 취향에 맞게 사용, Count() 는 NULL의 수를 포함하지 않는다)

1. 
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;

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

3. 
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. // order by, group by 의 조건을 콤마(,)로 구분하여 여러개 삽입 가능
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. case1
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;

5. case 2
select c1.title, c2.week, count(*) as cnt from courses c1 
    inner join checkins c2 
    inner join orders o
    	on c1.course_id = c2.course_id AND 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;

-- count는 NULL을 세지 않는다!
6. case 1
select COUNT(*) as tot_user_cnt, 
       COUNT(pu.user_id) as pnt_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';

6. case 2
select COUNT(u.user_id) as tot_user_cnt, 
       COUNT(pu.point_user_id) as pnt_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'

7.
select ed.enrolled_id, e.user_id, count(*) as done_count 
    from enrolleds_detail ed 
    inner join enrolleds e 
    	on ed.enrolled_id = e.enrolled_id 
    where ed.done = 1 
    group by ed.enrolled_id
    order by done_count desc;