[MySQL] Subquery 서브쿼리

Subquery란??

간단하게 서브 쿼리란 하나의 SQL 문 내에 있는 또 다른 SQL문을 말한다.

서브쿼리는 위치에 따라 명칭이 다르다. FROM절에 사용하는 인라인 뷰(Inline view), SELECT문에 사용하는 스칼라 서브쿼리(Scala Subquery), 일반적으로 WHERE절에 사용하는 것을 서브쿼리(Subquery)라고 한다. 하지만 SQL문내 또다른 SQL문을 배치하는 경우 통일하여 서브쿼리라고 부르는 것 같다.

---

1. select

기존 테이블에 함께 보고싶은 데이터를 붙이는 것에 사용
select 필드명, 필드명, (subquery) from ..

상당히 햇갈렸다. 차분하게 필요한 데이터가 어느 테이블에 있는지 체크하고,
어떤 데이터가 필요한지, 어떤 쿼리문으로 추출 할 수 있는지 체크해야 한다.

연습 문제를 많이 접해봐야겠다.

#Select에 들어가는 
#SubQuery를 사용하는 경우
#'오늘의 다짐'(comment) 데이터를 보고 싶은데 
#'오늘의 다짐' 좋아요(likes)의 수가, 
#본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금할 수 있겠죠?
#그럼, 평균을 구해보자.
show tables;
select * from checkins;
##comment, likes 는 checkins 테이블에 있다.
#user_id별 평균 좋아요 갯수는 다음과 같다.
select user_id, round(avg(likes),1) as avg_likes from checkins
	group by user_id;

#포인트 많이 받은 사람들은 like도 많이 받았을까?
#user_id별 포인트는 다음과 같다. 
select user_id, point from point_users;

#그럼 user_id가 공통된다. 따라서 포인트별로 좋아요 갯수와의 연관을 찾아보는 select문을 만들 수 있다. 
select ck.user_id, ck.avg_likes, pu.`point` from point_users pu 
	inner join (select user_id, round(avg(likes),1) as avg_likes from checkins group by user_id) ck
	on pu.user_id = ck.user_id
	order by pu.point desc;

하나씩 어떤 정보를 필요로하는지, 어디에 있는지 순서대로 접근해봤다.

#####연습
#전체 유저의 포인트의 평균보다, 큰 유저들의 데이터 추출
#전체 유저의 포인트의 평균부터
#1.포인트 데이터 위치를 찾는다.
select * from users;
show tables;
#2.데이터를 찾았다.
select * from point_users;
#3.포인트의 평균부터 구한다.
select round(avg(`point`),1) as avg_point from point_users;

#4.유저들의 데이터를 찾는다.
show tables;
#5.유저들의 이름, 이메일 등 데이터를 찾았다.
select * from users;

#6.원본 테이블 내 공통분모를 찾는다.
#7. user_id로 합쳐서 볼 수 있다는 것을 파악했다.
select * from users;
select * from point_users;

#8.이제 필요한 데이터의 조건을 생각해본다.
##avg_point보다 큰 유저들의 정보를 알고 싶다.
#avg_point는 point_users pu테이블에 있고,
#user_id별 포인트는 pu테이블
#user_id별 정보(이름)은 users u테이블에 있다. 

#select pu.point_user_id , u.user_id , u.name ,u.email, pu.`point`  from point_users pu
select * from point_users pu
	inner join users u on pu.user_id = u.user_id 
			and point > 
			(
			select round(avg(`point`),1) as avg_point from point_users pu
			inner join users u
				on pu.user_id = u.user_id
				where u.name = '이**'
			)
order by pu.point desc;

 

여기서도 select 데이터를 어떤걸 찾아야 될 지 마지막에 길어질 수록 햇갈렸다. 올려서 문제를 다시 보니 ~보다 큰 유저 데이터라 해서 컬럼들은 상관없었던걸로 *로 변경했다. 문제를 잘 정리해야 한다.
좀 더 연습 문제를 풀어보는 중이다.

###연습문제
#checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
select round(avg(likes),1) as avg_likes from checkins;
select *, 
	   (
	   select round(avg(likes),1) from checkins c2 
	   where c.course_id=c2.course_id
	   ) as avg_likes
from checkins c;

#과목명별 평균 like수 붙여보기
#과목명은 courses.title
#likes는 checkins.likes

select * from courses cs;
select * from checkins ck1;
select * from checkins ck2;

select cs.title , (select round(avg(ck2.likes),1) from checkins ck2 where ck2.course_id=ck1.course_id) as course_avg 
	from checkins ck1
	inner join courses cs
	on ck1.course_id = cs.course_id​

 

2. where

where문에 서브쿼리를 사용하는 경우
- where field in 이후 조건의 "조건" 추가한다는 생각
- 조건을 좀 더 세분화 할 수 있다는것
- where FIELD in (subquery)

where는 select보다 이해하기가 편했다. 약간 그대로 해석되는 느낌도 있다. 아무래도 where field in(...)으로 구분되기 때문인지 조건을 좀 더 강화하는 느낌으로 접근하니 편했다. 하지만 이 부분도 그렇지만 순서가 중요하다. 
####FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY 이 순서를 잊지 말자.

#Where에 들어가는 
#SubQuery를 사용하는 경우
#Where은 조건문이므로 Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용
#### where FIELD in (subquery) 
#카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때
#where 필드명 in (subquery) 이런 방식
select * from users;
select * from orders;
########이 부분이 작동 되는지 확인한다.########
select user_id from orders
	where payment_method ='kakaopay';
######## where field in 이후 조건의 조건 추가한다는 생각으로.
#Subquery 문
select * from users u
	where user_id in(
		select user_id from orders
		where payment_method = 'kakaopay'
	);

 

3. From

from문에 서브쿼리를 사용하는 경우
- from자체가 ~테이블에서 라는 의도기 때문에
- 내가 만든 Select로 정렬된 임시테이블 이미 있는 테이블을 Join하려 할 때 사용

From 에 들어가는 Subquery (가장 많이 사용되는 유형이다.)
우선 임시 테이블 자체가 작동하는지 잘확인하고 join과 함께 사용하면 된다. alias가 여러개 붙기 때문에 명칭 구분을 좀 더 알아보기 쉽게 연습할 필요가 있다. 또한 sql문 자체가 규모가 커지면서 나조차도 알아보기 어려워져서 들여쓰기를 통해서 정렬을 좀 잘해보고 싶은데 정렬법이 어느정도 기준이 있는지 찾아봐야 겠다.

	####연습 문제
#course_id별 유저의 체크인(수강 시작) 개수를 구해보기!
show tables;
select * from courses;
select * from checkins;
select * from users;

	
#1. course_id별 수강 시작 인원
select * from checkins c;
select c.course_id ,count(DISTINCT (user_id)) from checkins c
	group by c.course_id ;

#2.1. course_id별 전체 인원(수강료 지불 인원) orders
select * from orders o ;
select o.course_id, count(user_id) from orders o 
	group by o.course_id  ;

#2.2. course_id별 수강 시작 인원을 전체 인원에 붙이기

select  a.course_id,
		c.title,
		a.cnt_ck,
		b.cnt_usrs,
		round(((a.cnt_ck/b.cnt_usrs)*100),2) as Ratio 
	FROM 
		(
		select c.course_id ,count(DISTINCT (user_id)) cnt_ck from checkins c
		group by c.course_id 
		) as a 
	inner join 
		(
		select o.course_id, count(user_id) cnt_usrs from orders o 
		group by o.course_id
		) as b
		on a.course_id = b.course_id
	inner join
		courses c
		on a.course_id = c.course_id;

 

4. with

WITH 절로 윗 from의 join 대상인 a, b를 마치 대명사처럼 위에서 선언할당 하는 느낌이 있다.
마치 Subquery문 한덩이를 table1처럼 대명사로 지정하는 것과 같다. 구분하기 쉽다. 잘 활용해봐야겠다.

with table1 as(
	select c.course_id ,count(DISTINCT (user_id)) cnt_ck from checkins c
	group by c.course_id 
), table2 as(
	select o.course_id, count(user_id) cnt_usrs from orders o 
	group by o.course_id
)
select  a.course_id,
		c.title,
		a.cnt_ck,
		b.cnt_usrs,
		round(((a.cnt_ck/b.cnt_usrs)*100),2) as Ratio 
	FROM 
		table1 as a 
	inner join 
		table2 as b
		on a.course_id = b.course_id
	inner join
		courses c
		on a.course_id = c.course_id;
select * from checkins ck;