SQL - Subquery (서브쿼리)

박영준·2023년 6월 2일
db
0

DB

목록 보기
15/41
  • 큰 SQL 쿼리문 안에 또다른 SQL 쿼리문이 들어가는 것

1. 실행순서

  1. ( ) 괄호 안의 명령어부터 실행

  2. 이후에 밖에 있는 명령어를 실행

2. 사용법

1) where 에 들어가는 Subquery

  • Subquery의 결과를 조건에 활용하는 방식

예시 1 : kakaopay로 결제한 유저들의 정보 보기

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'

Subquery를 사용할 경우

# 원본
select user_id from orders
where payment_method = 'kakaopay'

# 수정본
select u.user_id, u.name, u.email from users u
where u.user_id in (					# ( ) 괄호에 속하는 u.user_id만 가져오기
	select o.user_id from orders o
	where o.payment_method = 'kakaopay'
)
  • where 필드명 in (subquery)

예시 2 : 전체 유저의 포인트의 평균보다 큰 유저들

select * from point_users pu 
where pu.point > (									# 평균보다 큰 유저들
	select avg(pu2.point) from point_users pu2		# 전체 유저의 포인트의 평균
)
  • where 필드명 > (subquery) 형태로도 사용 가능하다.

예시 3 : 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들

# 방법 1
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 * from point_users pu
where pu.point > (
	select avg(pu2.point) from point_users pu2
	where user_id in (
    	select user_id from users
        where name = '이**'
    )
)

  • point_users 테이블과 users 테이블의 교집합을 구하는데, '이**' 조건으로 user_id 값을 맞춰서 구한다.

  • select (select ... (select ...)) 이렇게 select 안에 계속 select 를 이어나갈 수도 있다.

2) Select 에 들어가는 Subquery

  • 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용

예시 1 : '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지

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

# 수정본
select c.checkin_id,
	   c.user_id,
       c.likes, 
	   (
       	select avg(likes) from checkins
		where user_id = c.user_id
        ) as avg_like_user
from checkins c				# c 테이블

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

  • select 쿼리문이 한줄씩 실행될 때마다, () 괄호 안의 명령어가 실행된다.

예시 2 : 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들

select * from point_users pu
where pu.point > (
	select avg(pu2.point) from point_users pu2
	where user_id in (
    	select user_id from users
        where name = '이**'
    )
)
  • select (select ... (select ...)) 이렇게 select 안에 계속 select 를 이어나갈 수도 있다.

예시 3 : checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select c.checkin_id,
	   c.course_id,
       c.user_id,
       c.likes, 
	   (
        select avg(likes) from checkins		# 평균 likes수
		where course_id = c.course_id		# course_id별
       ) as course_avg
  from checkins c							# checkins 테이블에

예시 4 : checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

select c.checkin_id, 
	   c2.title, 										# 과목명별
       c.user_id, 
       c.likes, 
	   (
        select round(avg(likes),1) from checkins		# 평균 likes수
		where course_id = c.course_id
       ) as course_avg
  from checkins c										# checkins 테이블에
inner join courses c2 on c.course_id = c2.course_id		# 과목명별

3) From 에 들어가는 Subquery

  • 가장 많이 사용되는 유형

  • 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용

  • 내가 만든 select 문을 마치 원래 존재했던 테이블처럼 사용하는 방식

예시 1 : 유저 별 좋아요 평균와 포인트의 상관관계 알아보기

# 원본
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
  • a : () 괄호의 테이블 이름(즉, 괄호를 지칭함)

예시 2 : course_id별 like 개수, 전체

# 준비 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


# from : 준비 1 + 준비 2
select a.course_id, 
       b.cnt_checkins,
       a.cnt_total 
from
(
	select course_id, count(*) as cnt_total from orders			# 준비 2
	group by course_id
) a
inner join (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins		# 준비 1
	group by course_id
) b
on a.course_id = b.course_id		# a.course_id 와 b.course_id 가 같은 것으로 join한다


# from(준비 1 + 준비 2) + 퍼센트
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


# from(준비 1 + 준비 2) + 퍼센트 + 강의 제목
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		# 강의 제목

  • from : 준비 1 + 준비 2

    • a와 b를 묶는다

  • from(준비 1 + 준비 2) + 퍼센트 + 강의 제목

    • a와 b를 묶고 + 여기에 courses 를 묶는다

4) Subquery + with절

  • 서브쿼리가 붙어서 inner join 안쪽이 헷갈릴 때 사용하는 것이 with절

  • with 절을 실행할 때는 전체 명령어를 마우스로 블록 씌워서 실행해야 한다. (그렇지 않으면 일부만 실행됨)

예시 1

# 원본
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 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

  • with 테이블명1 as (...), 테이블명2 as (...) select ...

예시 2 : 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력

# 준비 1
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
where done = 1
group by enrolled_id


# 준비 2
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
group by enrolled_id


# Subquery(준비 1 + 준비 2)
select a.enrolled_id, a.done_cnt, b.total_cnt from (
	select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
) a
inner join (
	select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
	group by enrolled_id
) b on a.enrolled_id = b.enrolled_id


# Subquery(준비 1 + 준비 2) + with
with table1 as (
	select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
)  table2 as (
	select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
	group by enrolled_id
)

select a.enrolled_id, 
	   a.cnt_done, 
	   b.cnt_total 
   from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id

예시 3 : 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 + 진도율 출력

with table1 as (
	select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
)  table2 as (
	select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
	group by enrolled_id
)

select a.enrolled_id, 
	   a.cnt_done, 
	   b.cnt_total
       round(a.done_cnt/b.total_cnt,2) as ratio
   from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id


# 그러나, 더 간단히 만들면
select enrolled_id,
       sum(done) as cnt_done,		# 어차피 done은 1 또는 0 이기 때문
       count(*) as cnt_total,
       round(sum(done)/count(*),2) as ratio
   from enrolleds_detail
 group by enrolled_id

profile
박영준
개발자로 거듭나기!
이전 포스트

SQL - Join, Union

다음 포스트

SQL - 문자열, Case

0개의 댓글