[SQL] Subquery

 

Subquery 쿼리 안의 쿼리라는 의미입니다.

하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해져요!

즉, Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 파워풀한 기능입니다.

 

• kakaopay로 결제한 유저들의 정보 보기 → 우선, 이렇게 볼 수 있겠죠? users 와 orders 의 inner join으로!

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 user_id, name, email from users u 
where user_id in (	
	SELECT user_id  FROM orders o 
	WHERE payment_method = 'kakaopay'
);

1) Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다.

where 필드명 in (subquery) 

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

👉실행순서 

(1) from 실행: users 데이터를 가져와줌

(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌

(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌

(4) 조건에 맞는 결과 출력

 

2) Select 에 들어가는 Subquery

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

// '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지
SELECT  c.checkin_id, 
		c.user_id, 
		c.likes,
		(
		SELECT avg(likes) FROM checkins 
		WHERE user_id = c.user_id 
		) as avg_likes_usesr
FROM checkins c;

 

3) From 에 들어가는 Subquery (가장 많이 사용되는 유형!)

👉 From은 언제 사용하면 좋을까요? 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱이겠죠!

- 유저 별 좋아요 평균 

/* (열심히 활동한 사람들)포인트가 많은 사람들은 like 많이 받았을까?*/
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 c 
	group by user_id
) a on pu.user_id = a.user_id

 

[연습문제] Where 절에 들어가는 Subquery 연습해보기

 

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

힌트! → point_users 테이블을 이용해서 avg를 구하고, 다시 point_users와 조인하세요!

👉 포인트가 평균보다 많은 사람들의 데이터를 추출해보자! *참고: 평균 포인트는 5380점

 

/* 평균이 시간대별로 바뀔 수 있으니 아래와 같이 작성 */
SELECT * FROM point_users pu 
WHERE point > (
	SELECT round(avg(point),2) FROM point_users
)

 

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

 👉 이씨 성을 가진 유저들의 평균 포인트보다 더 많은 포인트를 가지고 있는 데이터를 추출해보자! *참고: 이씨 성을 가진 유저들의 평균 포인트는 7454점

힌트! → 위 구문의 서브쿼리 내에서 users와 inner join을 해보세요!

/* 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기 */
SELECT * from point_users pu 
WHERE point > (
	select avg(point) FROM point_users pu 
	inner join users u on pu.user_id = u.user_id
	where u.name like '이%'
)

SELECT * from point_users pu 
WHERE point > (
	select avg(point) FROM point_users pu 
	WHERE user_id in (
		SELECT user_id from users where name = "이**"
	)
)

 

 

[연습문제] Select 절에 들어가는 Subquery 연습해보기

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

/* checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기 */
select  c.checkin_id, 
		c.course_id, 
		c.user_id, c.likes,  
		(
			select avg(likes) from checkins c 
			where course_id = c.course_id
		) as course_likes
FROM  checkins c

2) checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

/* checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기 */
select  c.checkin_id,  
		c2.title, 
		c.user_id, 
		c.likes,
		(
			select round(avg(likes),1) from checkins
			where course_id = c.course_id
		) as course_avg,
		c2.*
FROM checkins c
inner join courses c2 on c.course_id = c2.course_id;

 

[연습문제] From 절에 들어가는 Subquery 연습해보기

1) course_id별 유저의 체크인 개수를 구해보기!

checkins 테이블을 course_id로 group by 하면 되겠죠! 너무 쉽다! 그리고 distinct로 유저를 세면 되겠네요!

 

2) course_id별 인원을 구해보기!

orders 테이블을 course_id로 group by 하면 되겠죠! 너무 쉽다!

 

3) [진짜 하고 싶은 것] course_id별 checkin개수에 전체 인원을 붙이기

1) ,2) inner join 하면 됩니다!

 

4) [한 걸음 더] 퍼센트를 나타내기  👉 전체 중 얼마나 checkin를 하는지 알아보면 좋겠죠?

5) [반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!

 

/* course_id별 유저의 체크인 개수를 구해보기! */
select course_id, count(distinct(user_id)) as cnt_checkins FROM checkins c 
group by course_id;

/* course_id별 인원을 구해보기! */ 
select course_id, count(*) as cnt_total  from orders 
group by course_id 

/*  course_id별 checkin개수에 전체 인원을 붙이기  */
select a.course_id, 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 c 
			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  a.course_id, 
		c.title,
		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
inner join courses c on a.course_id = c.course_id;

 

with절 연습하기

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

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 절로 깔끔하게 정리해보기 

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 문법 (문자열, Case)

1) 문자열 데이터 다뤄보기

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

 

(1) 문자열 쪼개보기

이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!

• SUBSTRING_INDEX 라는 문법을 사용하면 됩니다. 바로 쿼리를 볼까요?

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

select user_id, email, SUBSTRING_INDEX(email, '@', 1)  FROM  users;

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

/* @기준으로 뒤 부분 */
select user_id, email, SUBSTRING_INDEX(email, '@', -1)  FROM  users;

 

 

(2) 문자열 일부만 출력하기

orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!

/* 1번부터 10글자 나오기  */
SELECT order_no, SUBSTRING(created_at,1,10) as date FROM orders;
/* 일별로 몇개씩 주 */
SELECT  SUBSTRING(created_at,1,10) as date, count(*) FROM orders
group by date;

 

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

SELECT * FROM point_users pu ;
/* 포인트를 구간별로 표시 */
SELECT pu.user_id, pu.point, 
	   (case when pu.point > 10000 then '잘 하고 있어요!'
	   else '조금만 더 파이팅!' end ) as msg
from point_users pu

/* 구간별로 몇개 씩인지 확인 */
SELECT a.lv, count(*) 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

++) with 절로 정리

with table1 as (
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 
) 
SELECT a.lv, count(*) from table1 a
group by a.lv

 

퀴즈풀기

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

[힌트!] CASE 문법 사용, CASE 안에서 Subquery로 평균을 계산하여 비교!

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

2) 이메일 도메인별 유저의 수 세어보기

SELECT SUBSTRING_INDEX(u.email,'@', -1) as domain , count(user_id) as cnt   
FROM users u
group by domain


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

3) '화이팅'이 포함된 오늘의 다짐만 출력해보기

SElect * FROM checkins
WHERE comment like '%화이팅%'

 

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

select a.enrolled_id, a.done_cnt, b.total_cnt from (
	SELECT enrolled_id, count(*) as done_cnt FROM enrolleds_detail ed  
	WHERE done = 1
	group by enrolled_id
) a 
inner join (
	SELECT enrolled_id, count(*) as total_cnt FROM enrolleds_detail ed  
	group by enrolled_id
) b on a.enrolled_id = b.enrolled_id


/* with문 */
with table1 as (
	SELECT enrolled_id, count(*) as done_cnt FROM enrolleds_detail ed  
	WHERE done = 1
	group by enrolled_id
), table2 as (
	SELECT enrolled_id, count(*) as total_cnt FROM enrolleds_detail ed  
	group by enrolled_id
)
select a.enrolled_id, 
	   a.done_cnt, 
	   b.total_cnt 
from table1 a 
inner join table2 b on a.enrolled_id = b.enrolled_id

 

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

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

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

 

6) 그러나, 더 간단하게 만들 수 있지 않을까! 

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

 

> chatGPT

SELECT enrolled_id,
       SUM(IF(done = 1, 1, 0)) AS done_cnt,
       COUNT(*) AS total_cnt,
       ROUND(SUM(IF(done = 1, 1, 0)) / COUNT(*), 2) AS progress_ratio
FROM enrolleds_detail
GROUP BY enrolled_id;

이 버전의 쿼리는 IF 함수를 사용하여 done 값이 1인 경우 1을, 그렇지 않은 경우 0을 반환하여 done_cnt를 계산합니다. 나머지 부분은 이전에 설명한 것과 동일합니다. 이렇게 수정된 쿼리를 실행하면 MySQL에서 원하는 결과를 얻을 수 있을 것입니다.