[MySQL] [스파르타코딩클럽 SQL] Subquery, With, 문자열, Case

🔥 Subquery란?

• 쿼리 안의 쿼리
• 하위 쿼리의 결과를 상위 쿼리에서 사용한다.
• 자주 쓰이는 Subqueary 유형: Where에 들어가는 Subquery, Select에 들어가는 Subquery, From에 들어가는 Subquery
• () 안에 subquery가 들어간다.

 

---

🔥 Where 절에 들어가는 Subquery

Subquery의 결과를 조건에 활용한다.

where 필드명 in (subquery)

 

사용 예시

• 카카오 페이로 결제한 주문건 유저들만 유저 테이블에서 추출

select * from users u
where u.user_id in (select o.user_id from orders o 
                    where o.payment_method = 'kakaopay');

※ 쿼리 실행 순서: from → where절 안 subquery → where → select 
1. from users u : users의 데이터를 모두 가져온다.
2. (select o.user_id from orders o
     where o.payment_method = 'kakakopay') : 카카오 페이로 결제한 user_id 명단을 뽑는다. 
3. where u.user_id in () : subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 한다.
4. select * : 조건에 맞는 users 테이블의 모든 데이터 출력

 

• 이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터 추출

select * from point_users pu
where pu.point > (
	select round(avg(pu2.point)) from point_users pu2
	where pu2.user_id in 
	(
		select u.user_id from users u
		where u.name = '이**'
	)
)

subquery안에 subquery가 들어갈 수 있다.

 

select * from point_users pu 
where pu.point > 
	(select avg(pu2.point) from point_users pu2
	inner join users u 
	on pu2.user_id = u.user_id 
	where u.name = "이**");

subquery 해석:
point 정보는 point_users테이블 안에 있고, name 정보는 users 테이블 안에 있으므로, 두 테이블을 합친다.
그 후 합친 테이블에서 성이 이씨인 데이터들만 추출하도록 조건을 걸고, 그 데이터의 point 평균을 구한다.

 

---

🔥 Select 절에 들어가는 Subquery

Select는 결과를 출력해주는 부분.

결과를 출력할 때, 기존 테이블에 함께 보고 싶은 통계 데이터를 Subquery를 이용해 붙인다.

select 필드명, 필드명, (subquery) from ...

select * from 문에서 데이터는 한줄 한줄 출력한다. select 안의 Subquery는 매 데이터 한줄마다 실행된다.

 

사용 예시

• '오늘의 다짐'(chekins) 데이터를 보고 싶은데, course_id가 받았던 좋아요 수 평균을 checkins 테이블 course_id마다 붙인다.

select 
	checkin_id, 
	course_id, 
	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

1. select * from 문에서 데이터를 한줄 한줄 출력하는 과정에서
2. select 안의 subquery가 매 데이터 한줄마다 실행된다.
3. 그 데이터 한 줄의 course_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
4. 함께 출력한다.

 

---

🔥 From 절에 들어가는 Subquery

여러 table에서 select하고 싶을 때 from절에 subquery를 사용한다.

1. select하고 싶은 정보가 들어 있는 테이블들에서, 각자 select를 수행해 새로운 테이블이 된다.

2. subquery를 이용해 원하는 정보들이 있는(select가 수행된) 테이블들을 join 한다.

(나중에 select할 때를 위해 별칭을 사용한다.)

3. join된 각 테이블의 별칭을 이용해 원하는 데이터들을 select한다.

 

사용 예시

추출하고 싶은 데이터: course title, course별 유저의 체크인 개수, course별 인원, 퍼센트

1. course_id별 유저의 체크인 개수를 구한다.

select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

 

2. course_id별 인원을 구한다.

select course_id, count(*) as cnt_total from orders
group by course_id

 

3. 1,2번과 course의 title 정보가 있는 courses테이블을 join한다.

select *
       from
		(
			select course_id, count(distinct(user_id)) as cnt_checkins 
			from checkins
			group by course_id 
		) a
		inner join (
			select course_title, course_id, count(*) as cnt_total from orders
			group by course_id 
		) b on a.course_id = b.course_id
		inner join courses c on a.course_id = c.course_id

 

4. join된 각 테이블의 별칭을 이용해 select 하고 싶은 데이터들을 select 한다.

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

 

---

🔥 With절

with 절을 이용해 subquery를 가상 테이블명으로 사용할 수 있다.

with table1 as (subquery1), table2 as (subquery2) ....

 

사용 예시

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

SQL은 줄로 띄어져있으면 서로 다른 쿼리문이라고 생각하고 마지막 쿼리문을 실행한다.
하나의 쿼리문은 줄 띄어쓰기 없이 작성하자

또는 실행하고 싶은 쿼리문을 드래그해서 실행해도 된다! 

 

---

🔥 문자열

문자열 쪼개기

SUBSTRING_INDEX(문자열, 쪼개고 싶은 기준 문자열, 가지고 올 문자열 위치)

 

• 이메일에서 아이디만 가져오기

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

 

• 이메일에서 도메인만 가져오기

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

 

문자열 일부만 출력

substring(문자열, 가져올 문자열 첫 글자 위치, 몇개의 글자를 출력하고 싶은지)

 

• orders테이블에서 날짜까지 출력하기

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

 

---

🔥 Case

경우(case)에 따라 원하는 값을 새 필드에 출력해보기

select 필드명, 필드명,
case
when 조건1 then 출력값1
when 조건2 then 출력값2
.
.
.
else 조건n 출력값n
end as 별칭
from 테이블명

 

사용예시

• point_users테이블에서 10000점보다 높은 포인트를 가지고 있으면 '잘하고 있어요!', 평균보다 낮으면 '힘내요!' 라고 표시하고, 이 새로운 필드명은 '구분'이다.

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '힘내요!'
END as '구분'
from point_users pu;

 

• point_users 테이블에서 포인트가 5천 미만, 5천 이상, 1만 이상인 user들의 수 통계내기

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

 

---

Source

스파르타 코딩클럽 내일배움단 엑셀보단 쉬운 SQL 4주차