엑셀보다 쉬운 SQL - Subquery -

Subquery 

쿼리 안의 쿼리가 있는 것

자주 쓰이는 서브쿼리 유형

Where에 들어가는 Subquery : where 필드명 (subquery)

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

서브 쿼리에서 반환하는 필드값이 where절에서 비교하는 필드값과 같아야함

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

쿼리 실행 순서

from 실행: users테이블 데이터 전체를 가져온다.

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

where 실행 : where.. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링하고

select 실행 : 결과 출력

 

Select에 들어가는 Subquery : select 필드명, (subquery) from..

'오늘의 다짐' 데이터를 볼 때, '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지

select c.checkin_id, c.user_id, c.likes,
	   (select avg(likes) from checkins h
	    WHERE h.user_id = c.user_id) as avg_likes
FROM checkins c

쿼리 실행 순서

밖의 select * from 문에서 데이터를 한 줄씩 출력하는 과정에서

select 안의 서브쿼리가 매 데이터 한 줄마다 실행

그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 서브쿼리에서 계산해 출력.

 

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

유저 별 좋아요 평균

select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

여기에 해당 유저 별 포인트 확인

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

 

쿼리 실행 순서: 서브쿼리의 select가 실행되고 이것을 테이블처럼 여기고 밖의 select가 실행

서브쿼리 연습

Where 절에 들어가는 Subquery 연습

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(pu.point) from users u
inner join point_users pu 
on u.user_id = pu.user_id
where u.name = '이**')

 

Select 절에 들어가는 Subquery 연습

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

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

 

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

select 테이블명.* 하면 모든 필드를 볼 수 있다

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
 from checkins c
 inner join courses c2 on c.course_id = c2.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

course_id별 like 개수에 전체 인원을 붙이기

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

퍼센트(전체 중 얼마나 like 했는지) 나타내기

(a.cnt_checkins/b.cnt_total) as ratio

강의 제목(courses테이블에 있음)도 나타내보기

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(*) 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