[SQL] 서브쿼리

서브쿼리

• 서브쿼리란?
  - 쿼리 안의 쿼리라는 의미
  • 하위 쿼리의 결과를 상위 쿼리에서 사용

---

예시에 사용되는 테이블별 특징

• checkins: 여러분이 강의실 들어오시며 남기는 '오늘의 다짐'이 들어있어요
  - columns : checkin_id, created_at, updated_at, course_id, user_id, week, comment, likes
  

• courses: 스파르타의 개설 강좌 정보가 들어있어요
  - columns : course_id, created_at, updated_at, title, description

• enrolleds: 유저별 강좌 등록정보가 들어있어요
  - columns : enrolled_id, created_at, updated_at, course_id, user_id, is_registered

• enrolleds_detail: 유저별 들을 수 있는 영상과, 들었는지 여부가 들어있어요
  - columns : enrolled_detail_id, enrolled_id, week, current_order ,done, seen, done_date, seen_date

• orders: 주문 (수강등록) 정보가 들어있어요
  - columns : order_no, created_at, updated_at, course_id, course_title, user_id, payment_method, email

• point_users: 유저별 포인트 점수가 들어있어요
  - columns : point_user_id, created_at, updated_at, user_id, point

• users: 유저 정보가 들어있어요
  - columns : user_id, created_at, updated_at, name, email

---

사용 예시

1. where 절에서 사용되는 서브쿼리 예시

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

2. select 절에서 사용되는 서브쿼리 예시

	select c.checkin_id,
       c.user_id,
       c.likes,
       (
       select avg(likes) from checkins
       where user_id = c.user_id
       ) as avg_likes_user
  	from checkins c

3. from 절에서 사용되는 서브쿼리 예시

	select pu.user_id ,pu.point, a.avg_likes from point_users pu
	inner join (
	select user_id, round(avg(likes),1) as avg_likes from checkins
	group by user_id 
	) a on pu.user_id = a.user_id

4. 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

	select * from point_users
	where point > (
	select avg(pu.point) from point_users pu 
	where user_id in (
		select user_id from users
		where name like '이%'
		)
	)

5. checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

	select checkin_id, c3.title, user_id, likes, 
	(select round(avg(c2.likes),1) from checkins c2
	where c.course_id = c2.course_id) as course_avg
	from checkins c
	inner join courses c3 
	on c.course_id = c3.course_id;

6. course_id별 check in 개수에 전체 인원을 붙이고, 비율과 강의 제목 나타내기

	select	(
		select title from courses 
		where course_id = c.course_id 
		) as title,
		COUNT(distinct(c.user_id)) as cnt_checkins,
		a.cnt_total,
		COUNT(distinct(c.user_id))/a.cnt_total as ratio
	from checkins c
	inner join(
		select course_id, count(*) as cnt_total from orders 
		group by course_id 
	) a on c.course_id = a.course_id 
	group by c.course_id

7. 6번에 With절 사용하기

	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 c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
	from table1 a
	inner join 
	table2 b on a.course_id = b.course_id
	inner join courses c on a.course_id = c.course_id

---

문자열 다루기

사용 예시

1. 이메일에서 아이디만 가져와보기

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

2. 이메일에서 도메인만 가져와보기

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

3. 문자열 일부만 출력하기

	select SUBSTRING(created_at,1, 10) as date, count(*) from orders
	group by date 

CASE

• CASE란?
  - 경우에 따라 원하는 값을 새 필드에 출력하기 위해 사용
  

사용예시

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

---