SQL 2주차 Subquery 연습하기

Subquery 문법

• 쿼리 안의 쿼리
• 더 편하고 간단하게 원하는 데이터를 얻을 수 있다.
• 실무에서의 데이터는 아주 복잡하기 떄문에, 서브쿼리를 통해 주어진 데이터를 원하는 유의미한 정보로 만들 수 있다.
• 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것
• Subquery 유형 : where절, select절, from절
• where절: Subquery의 결과를 조건에 활용하는 방식으로 유용하다. 
• where  필드명  in  (Subquery) 이렇게 쓰인다.
• select절: 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙여준다.
• select  필드명, 필드명, (subquery) from 이렇게 쓰인다.
• from절: 내가 만든 select와 이미 있는 테이블을 join할 때 유용하다. <= 가장 많이 쓰임

예1)  where절과 join문법 비교

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';

56개의 데이터가 나온다.

Subquery where절을 사용하여 구하면,

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

51개의 데이터가 나온다.

출력되는 데이터의 개수가 다른 이유는?

• 첫 번째 쿼리문은 테이블을 합친 뒤에 값들을 필터링하여 payment_method가 kakaopay인 값들을 남기는 것이고
• 두 번째 쿼리문은 테이블을 합치기 전 값들을 필터링한 뒤 테이블로 합치는 방식
• 첫 번째 쿼리문은 A라는 유저가 a를 구매한 것, A라는 유저가 b를 구매한 것 모두 테이블로 합쳐서 A 유저 데이터에 2번 들어간다.
• 두 번째 쿼리문은 A라는 유저가 카카오페이로 결제한 경우 (a,b)가 orders 테이블에 있으므로 A 유저를 하나 테이블에 넣는 순서이다.

Subquery where절 쿼리가 실행되는 순서:

1. from 실행: 테이블 데이터 가져옴
2. Subquery 실행: 해당되는 필드명 데이터 가져옴
3. where ... in 절에서 Subquery의 결과에 해당되는 조건으로 필터링
4. 조건에 맞는 결과 출력

예2) select절 : '오늘의 다짐'  좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 확인해보자.

select c.checkin_id , 
	c.user_id , 
	c.likes ,
	( SELECT avg(likes) from checkins 
	  WHERE user_id = c.user_id)
	as avg_likes_user
	from checkins c ;

Subquery select 절 쿼리가 실행되는 순서:

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

예3) from절 : 유저 별 좋아요 평균과 유저 별 포인트를 보자 (포인트와 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;

좋아요 평균과 유저 별 포인트는 상관관계가 없다!

Subquery from 절 쿼리가 실행되는 순서:

1. 서브쿼리의 select가 실행
2. 이것을 테이블처럼 여기고 밖의 select가 실행

이제 그럼 Subquery를 이용한 문제들을 더 풀어보자 :)

 

예4) where절: 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select user_id ,point from point_users 
where point > (
	select round(avg(point)) as avg_point from point_users);

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

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

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

같은 말, 다른 코드.. 두 가지 방식으로 join할 수 있음!

예6) select 절: checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select c.checkin_id ,
	   c.course_id , 
	   c.user_id ,
	   c.likes,
	   (select round(avg(likes),1) from checkins 
		WHERE course_id = c.course_id)
	   as course_avg
	   from checkins c ;

예7) select 절: checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

select c.checkin_id, 
	   c2.title , 
	   c.user_id, 
	   c.likes, 
	   (select round(avg(c3.likes),1) from checkins c3
	   where c.course_id=c3.course_id)
	   as course_avg
	   from checkins c
	   inner join courses c2 
	   on c.course_id = c2.course_id
	   order by c2.title;

예8) from 절: 강의 제목별 checkin개수, 전체인원, 체크인 비율 나타내기

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 ;

 

서브쿼리까지 배워봤다..!

이제 조금 더 깔끔하게 쿼리문을 정리하는 법을 배워보자~