3/21 SQL 강의

3주차

여러 테이블의 연결

• join

  • 두 테이블의 공통된 key값을 기준으로 테이블을 연결해서 보는 방법
  • left join, inner join 이 존재
    • left join → select ~ from ~ left join (붙이고 싶은 테이블) on (두 테이블의 공통된 키) → 기준 테이블의 정보를 토대로 붙이고 싶은 테이블과 공통된 키 값을 기준으로 붙여서 출력 (비어있는 값이 존재할 수 있음) → 순서가 중요!!
      
    • inner join → select ~ from ~ left join (붙이고 싶은 테이블) on (두 테이블의 공통된 키) → 기준 테이블과 붙이고 싶은 테이블의 교집합만 출력(비어있는 값 생략)
      
  • 두 테이블의 공통된 key 값이 무엇인지 먼저 찾고 join을 사용
  • where 절 또는 group by / order by와 함께 쓰기위한 쿼리 실행 순서
    • from → join → where → group by → select
      • select에 뽑고자 하는 key값이 어느 테이블의 key 값인지 alias를 꼭 붙여야 한다.
  • example

    • 웹 개발, 앱 개발 종합반의 week 별 체크인 수 세기

      → group by에 두개의 필드를 걸어서 사용

      SELECT c.title, c2.week, count(*) from courses c 
      inner join checkins c2 on c.course_id = c2.course_id 
      GROUP by c.title, c2.week
      ORDER by c.title, c2.week

    • 위 예제에서 추가 → 8월 1일 이후에 구매한 고객들만 발라내기
      - inner join으로 checkins 에 orders 한번 더 붙이기

      SELECT c.title, c2.week, count(*) from courses c 
      inner join checkins c2 on c.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 c.title, c2.week
      ORDER by c.title, c2.week

    • inner join에서 묶을 key 값 주의!!!

• UNION

  • example

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

4주차

join 대신 subquery 사용하기

• where 절에 사용하는 subquery

→join example

select u.user_id, u.name, u.email from users u 
inner join orders o on u.user_id = o.user_id
where o.payment_method = 'kakaopay'

→subquery examplt

select user_id, name, email from users 
where user_id in (
	select user_id from orders 
	where payment_method = 'kakaopay'	
)

*subquery 사용시 tab 주의!!

• select 절에 사용하는 subquery

→ example : checkins 테이블에 course_id 별 평균 likes 수 필드 우측에 붙여보기 + title 도 붙이기

select c.checkin_id,
		c.user_id,
		c.likes,
		(
			select round(avg(likes), 1) from checkins
			where course_id = c.course_id 
		) as avg_like,
		**c2.***
from checkins c
**inner join courses c2 on c.course_id = c2.course_id**

• *** from 절에 사용하는 subquery ***

  • 코스 제목별 like 개수, 전체, 비율

• 11) From 절에 들어가는 Subquery 연습해보기

  • [준비1] course_id별 유저의 체크인 개수를 구해보기! 👉 checkins 테이블을 course_id로 group by 하면 되겠죠! 너무 쉽다! 그리고 distinct로 유저를 세면 되겠네요!
  • [준비2] course_id별 인원을 구해보기! 👉 orders 테이블을 course_id로 group by 하면 되겠죠! 너무 쉽다!
  • [진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기 👉 쉽죠? 준비1과 준비2를 inner join 하면 됩니다!
  • [한 걸음 더] 퍼센트를 나타내기 👉 전체 중 얼마나 like를 하는지 알아보면 좋겠죠?
  • [반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!

• with 절 subquery

  • 위 subquery를 보기 쉽게 변경

    → 각각의 subquery를 table에 저장하여 사용

with table1 as (
	select course_id, count(DISTINCT(user_id)) as cnt_checkins from checkins
	group by course_id
), table2 as (
	select course_id, count(*) as cnt_total from orders
	group by course_id	
)

select a.course_id,
		a.cnt_checkins,
		b.cnt_total,
		(a.cnt_checkins/b.cnt_total) as ratio, 
		c.title
from table1 a
inner JOIN table2 b on a.course_id = b.course_id
inner join courses c on c.course_id = b.course_id

[DISTINCT : 중복 제거 명령어]

*실행 시 모두 선택하여 실행시키기!!!

실전에 유용한 SQL 문

• 이메일에서 아이디만 가져오기 (문자열 쪼개기)

  • SUBSTRING_INDEX

    select user_id, email, SUBSTRING_INDEX(email, '@', 1)  from users

    [email 에서 @ 기점으로 1은 시작, -1 끝]

• 날짜에서 일부분만 가져오기 (문자열 일부만 출력하기)

  • SUBSTRING

    select order_no, SUBSTRING(created_at, 1, 10) as date
    from orders

    [key 값에서 1은 시작 포인트 다음은 몇 글자인지]

• Case

  • 포인트 구간 별로 msg 남기기

    select pu.user_id, pu.`point`,
    		(CASE when pu.point > 10000 then '잘 하고 있어요!'
    		else '조금만 더 파이팅!' end
    		) as msg
    from point_users pu

  • subquery 활용

    select a.lv, count(*) as cnt from (
    	select pu.user_id, pu.`point`,
    			(CASE when pu.point > 10000 then '1만 이상'
    				when pu.`point` > 5000 then '5천 이상'
    				else '5천 미만' end
    			) as lv
    	from point_users pu
    ) a
    group by a.lv