SQL 3주차

3-1. 배울 것 : join

• table은 한 목적에 맞는 것들만 모아두는 것이 가장 좋음(회원정보끼리, 결재정보끼리, 체크인정보끼리...) -> 서비스가 동작 하는데에 훨씬 효율적
• join 하려면 기준(key값: 두 table의 공통된 정보)이 있어야 한다.

3-2. left join / inner join

• (outer join)
• left join : [NULL]도 추출 ([NULL] : 값이 비어있다, 값이 없다)
  • select * from users u left join point_users pu on u.user_id = pu.user_id

ex)
select count(*) from users u : 498
select count(*) from orders o : 286
select count(*) from users u left join orders o on u.user_id = o.user_id : 509
select count(*) from orders o left join users u on o.user_id = u.user_id : 286
select count(*) from users u inner join orders o on u.user_id = o.user_id :286

• NULL 사용 : is NULL (= NULL이 아님)

• inner join : [NULL] 제외하고 추출 (교집합)
  • select * from users u inner join point_users pu on u.user_id = pu.user_id

• inner join 연습
  • select * from orders o inner join users u on o.user_id = u.user_id
  • select * from checkins c inner join users u on c.user_id = u.user_id
  • select * from enrolleds e inner join courses c on e.course_id = c.course_id
  • 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
  • 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
  • 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' group by u.name order by u.name desc
• 실행 순서 (join) : from > join > where > group by > select

3-3. join 퀴즈 풀기

• 퀴즈4)

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

  • 여러개로 group by 할 수 있다 : group by A, B (순서 상관 없음)
  • order by 도 동시에 여러개 쓸 수 있다 (순서 상관 있음)
    : order by A, B (A 먼저 정렬하고, 그다음 B 정렬 -> 각각 desc 붙여서 정렬 가능)

• 퀴즈5)

  
  		select c2.title, c1.week, count(*) as cnt  from checkins c1
  		inner join courses c2 on c1.course_id = c2.course_id 
  		inner join orders o on c1.user_id = o.user_id 
  		where o.created_at like '2020-08%'
  		group by c2.title, c1.week 
  		order by c2.title, c1.week

  • inner join을 한번 더 할 수 있다.
  • 8월 1일 이후에 구매한 고객 : >= '2020-08-01' 또는 like '2020-08%'

3-4. left join

• left join은 순서가 중요하다.
• NULL과 관련한 통계를 내고 싶을 때 (is NULL, is not NULL)
  ex) 회원가입 했는데, 포인트가 없는 사람들을 성씨별로 분류.

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

• 퀴즈) count는 NULL을 세지 않는다.

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

3-5. union all

• 이어보고 싶을 때 붙여주기. (위아래로 붙이기)
• order by가 안먹힘 (합친 것에서 order by 해야 함, order by 한 상태에서 합치는 건 안먹힘)
  • '7월' as month : 필드를 만들어 '7월'로 채우고, 이름은 month

    (
    	select '8월' as month, c2.title, c1.week, count(*) as cnt from checkins c1
    	inner join courses c2 on c1.course_id = c2.course_id 
    	inner join orders o on c1.user_id = o.user_id 
    	where o.created_at >= '2020-08-01'
    	group by c2.title, c1.week 
    )
    union all 
    (
    	select '7월' as month, c2.title, c1.week, count(*) as cnt from checkins c1
    	inner join courses c2 on c1.course_id = c2.course_id 
    	inner join orders o on c1.user_id = o.user_id 
    	where o.created_at < '2020-08-01'
    	group by c2.title, c1.week 
    )

3-6. 숙제

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