혼자 고민해보기_ 서비스 기획/SQL

4주차_ Subquery 사용하기

nuri-story 2023. 5. 1. 22:18

Subquery 란?

하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것을 의미합니다.

 

1. kakaopay로 결제한 유저들의 정보 보기

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'

- 아래에 있는 것이 서브 쿼리!

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

Where 에 들어가는 Subquery

Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다. where 필드명 in (subquery) 이런 방식으로요! 예를 들면, 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때는 아래와 같이 표현할 수 있겠죠.

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

 

[처리 과정]

- from 실행: users 데이터를 가져와줌
- Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
- where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
- 조건에 맞는 결과 출력

 

 

1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select * from point_users pu 
where pu.point > (select avg(pu2.point) from point_users pu2);

 

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

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 = "이**");

Select 에 들어가는 Subquery 

Select는 결과를 출력해주는 부분이죠? 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용합니다. select 필드명, 필드명, (subquery) from .. 이렇게요! 앞서 보았던것처럼, '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금할 수 있겠죠?

select avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'

- 그럼, 위 처럼 평균을 먼저 구해봅시다! user_id='4b8a10e6' 를 예시로!

select c.checkin_id, c.user_id, c.likes, 
	(select avg(likes) from checkins c2
	where c2.user_id = c.user_id) as avg_like_user
from checkins c;

[처리 과정]

- 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
- select 안의 subquery가 매 데이터 한줄마다 실행되는데
- 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
- 함께 출력해준다!

 

 

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

select checkin_id, course_id, user_id, likes, 
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) 
from checkins c;

 

2. checkins 테이블에 과목명별 평균 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;

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

준비 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. course_id별 checkin개수에 전체 인원을 붙이기

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

 

추가. 전체 중 얼마나 checkin를 하는지 알아보면 좋겠죠?

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

 

추가. 강의 제목도 나타나면 좋겠네요!

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) a
inner join
(
	select 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 절 연습하기 

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) a
inner join
(
	select 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

- 이렇게 계속 서브쿼리가 붙으면, inner join 안쪽이 너무 헷갈리겠죠!

   → 그 때 쓰는 것이 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

 

실전에서 유용한 SQL 문법 : 문자열

실제 업무에서는, 문자열 데이터를 원하는 형태로 한번 정리해야 하는 경우가 많습니다.

 

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

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

- @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!

 

 

 

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

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

- @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

 

실전에서 유용한 SQL 문법 : Case

경우에 따라 원하는 값을 새 필드에 출력해보기, 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?

 

1. 포인트 보유액에 따라 다르게 표시해주기

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;

 

2. 포인트 보유액에 따라 다르게 표시해주기 - Subquery 이용

select level, count(*) as cnt from (
	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
) a
group by level

 

3. 포인트 보유액에 따라 다르게 표시해주기 - Subquery 이용,  with 절 사용

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

 

 

[응용] 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

select pu.point_user_id, pu.point,
case 
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu

 

[응용] 이메일 도메인별 유저의 수 세어보기

select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain

 

 

[응용] '화이팅'이 포함된 오늘의 다짐만 출력해보기

select * from checkins c
where c.comment like '%화이팅%'

 

 

[응용] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

with lecture_done as (
	select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
), lecture_total as (
	select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
	group by enrolled_id
)
select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id

🍯 subquery 두 개를 만들어놓고, inner join!

🍯 done_cnt는 들은 강의의 수(done=1), total_cnt는 전체 강의의 수

 

 

[응용] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

with table1 as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
), table2 as (
	select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id
)
select a.enrolled_id,
       a.done_cnt,
       b.total_cnt,
       round(a.done_cnt/b.total_cnt,2) as ratio
  from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id

- 아래 처럼 더 간단하게 할 수도 있어요!

select enrolled_id,
       sum(done) as cnt_done,
       count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id