[TIL] SQL - Subquery, With

🛟 Subquery

 - 쿼리 안의 쿼리

 - 서브쿼리를 사용하지 않아도 원하는 데이터를 추출할 순 있지만, 더 편하고 간단하게 원하는 데이터를 추출해준다.

 

 

1. 비교하기

   inner join          VS          subquery    - 카카오페이 결제 유저들의 정보 보기

 

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

 

2) Subquery

  

 ① 카카오페이로 결제한 user_id 모두 구하기

select user_id from orders
where payment_method = 'kakaopay'

 

 ② user_id가 상단에 조회된 테이블에 속해있는 유저들만 골라보기

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

-> 결과는 같음!!

-> 서브쿼리로 작성된 쿼리가 더 직관적으로 잘 보이고 간단함.

 

 

2. 자주 쓰이는 유형

 1) Where에 들어가는 유형

 

 - where 필드명 in (subquery)

 

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

 

 

🙋‍♀️ 실행 순서

 

   from          ->          Subquery          ->          where ..in   

 

(1) from 실행: users 가져오기

(2) Subquery 실행: 해당되는 user_id 명단 뽑아준다.

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

(4) 결과 출력

 

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

 

 - 전체 유저의 포인트 평균보다 큰 유저들의 데이터 추출해줘.

 

① 평균 포인트 구하기

select avg(pu_avg.point) from point_users pu_avg

 

 ② Subquery에 넣기

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

 

 - '이**'인 유저들의 평균 포인트보다 더 많은 포인트 가지고 있는 데이터를 추출해줘.

 - 서브쿼리 내에서도 inner join이 가능하다!!

 

 ① '이**' 유저들 평균 포인트 구하기

select avg(pu2.point) from point_users pu2
inner join users u on pu2.user_id = u.user_id 
where u.name = "이**"

 

② Subquery에 추가하기

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

 

 

 2) Select에 들어가는 유형

 

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

 

 - 오늘의 다짐 데이터 보고 싶은데 오늘의 다짐 좋아요의 수가 특정 유저가(user_id='4b8a10e6') 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 비교해보기

 

 

🙋‍♀️ 실행 순서

 

 

(1) 제일 밖에 있는 select * from 문에서 데이터를 한줄씩 출력하는 과정에서

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

(3) 해당 데이터 한줄의 user_id 갖는 데이터의 평균 좋아요의 값을 서브쿼리에서 계산

(4) 결과 출력

 

 ① 특정 유저(user_id='4b8a10e6')의 평균 좋아요 수 구하기

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

 

 ② Subquery에 추가하기

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;

 

 

 

 - checkins 테이블에 checkin_id, course_id, user_id, likes 필드와 course_id 평균 like 수 필드에 별칭 붙여서 맨 마지막에 붙여 출력해보기

 

 ① checkins 테이블에 checkin_id, course_id, user_id, likes 필드 조회하기

select checkin_id, course_id, user_id, likes, 
from checkins c;

 

 ② course_id 평균 like 수 필드 구하기

select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id

 

③ 서브쿼리에 별칭(likes_avg) 추가해서 맨 마지막 필드로 붙여보기

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

 

 

2) From에 들어가는 유형

 

 - 가장 많이 사용된다.

 - 내가 만든 select와 이미 있는 테이블을 join하고 싶을 때 사용

 

 - 유저 별 좋아요 평균 구한 테이블에서 해당 유저 별 포인트 출력해보기

 

 ① 유저 별 좋아요 평균 구하기

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

 

 ② 유저 별 좋아요 평균 구한 테이블을 서브쿼리에 넣어서 유저 별 포인트 볼 수 있게 inner join 하기

    - 평균 구한 테이블에 별칭 부여 'avg_like_table'

select pu.user_id, avg_like_table.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
) avg_like_table on pu.user_id = avg_like_table.user_id

 

 

 - 코스 제목과 코스 제목별 like 개수, 코스별 유저 수, 전체 중 like의 비율까지 출력해보기

 

① course_id별 유저의 체크인 개수 구하기(중복 제거해야함)

select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

 

 ② course_id별 인원 구하기

select course_id, count(*) as cnt_total from orders
group by course_id

 

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

    - ① 별칭 'checkins_cnt_tb' / ② 별칭 'orders_total_tb'

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

 ④ 비율 나타내기

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

 

 ⑤ 강의 제목 같이 보여주기

select co.title, 
	   checkins_cnt_tb.cnt_checkins, 
	   orders_total_tb.cnt_total,
       (checkins_cnt_tb.cnt_checkins / orders_total_tb.cnt_total) as ratio 
from
(
	select course_id, count(*) as cnt_total from orders
	group by course_id
) orders_total_tb
inner join (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) checkins_cnt_tb
on orders_total_tb.course_id = checkins_cnt_tb.course_id
inner join courses co on orders_total_tb.course_id = co.course_id

 

 

 

✨ With

 - 서브쿼리를 깔끔하게 정리해줄때 사용

 - 서브쿼리를 묶어 별칭을 부여하고 별칭으로 서브쿼리를 이용할 수 있다.

with table_or as 
(
	select course_id, count(*) as cnt_total from orders
	group by course_id
)	
, table_ch as 
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
)
select co.title, 
	   checkins_cnt_tb.cnt_checkins, 
	   orders_total_tb.cnt_total,
	(checkins_cnt_tb.cnt_checkins / orders_total_tb.cnt_total) as ratio 
from table_or orders_total_tb inner join table_ch checkins_cnt_tb
on orders_total_tb.course_id = checkins_cnt_tb.course_id
inner join courses co on orders_total_tb.course_id = co.course_id