Subquery의 select, where, from 절 비교 예제, With절 예제

 

1. Subquery란 무엇인가?

쿼리문 안에 들어가 있는 쿼리를 subquery라고 합니다.

 

 

예를 들어, users테이블과 orders테이블이 있다고 가정합시다.

2개의 테이블을 이용해서 kakaopay로 결제한 유저들의 정보 찾아볼게요.

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'

위에서 join으로 만든 쿼리를 아래처럼 서브쿼리를 이용해서 바꿀 수 있습니다

select user_id, name, email
from users
where user_id In 
(
    select user_id
    from orders
    where payment_method = 'kakaopay'
)

서브쿼리로  kakaopay로 결제한 유저들의 id값을 찾아서 where절 안에 in으로 찾아 줄 수 있습니다.

가장 안에 있는 서브쿼리부터 시작해서 결과를 만들어주기 때문입니다.

select user_id, name, email
from users
where user_id In ('3b3eac0f', 'afb35ce0'...)

 

서브쿼리에는 where절, select절, from절에 있을 수 있습니다.

 

 

2. 자주 쓰이는 Subquery 유형 알아보기

 

  1) Where에 들어가는 Subquery (위의 예시 참고)

Where은 조건문 : Subquery의 결과를 조건에 활용하는 방식으로 사용

where 필드명 in (subquery)

 

 

[연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터를 추출하기

select *
from point_users
where point >
(
	select avg(point)
	from point_users
)

 

[연습] 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

-- 첫번째 방법
select *
from point_users
where point >
(
    select avg(point)
    from points_users pu join users u
    on pu.user_id = u.user_id
    where u.name like '이**'	
)

-- 두번째 방법 ( 서브쿼리 안에 서브쿼리 )
select *
from point_users
where point >
(
    select avg(point) 
    from point_users pu
    where user_id in 
    (
        select user_id 
        from users 
        where name = '이**'
    )
)

 

 

  2) Select에 들어가는 Subquery

Select는 결과를 출력 : 기존 테이블과 함께 보고 싶은 통계 데이터를 붙일때 사용

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

select가 될때마다 하나하나에 대해서 다 서브쿼리가 실행되는 것입니다. 아례 예시를 들어서 살펴보겠습니다.

 

4b8a10e6 유저가 받은 좋아요 수들의 평균을 구했다고 가정해볼게요.

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

만일 평균을 구해놓은 쿼리를 다른 데이터를과 함께 보여주고 싶다면 select문에 subquery를 붙이면 됩니다.

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

 

바깥 select문에서 checkin_id, user_id, likes~ 이런 순으로 찾는데, 찾은 user_id를 활용해서 또 다시 서브쿼리를 돌립니다.

다시 말해서 user_id가 한줄한줄 나오게 되는데, 나올때마다 서브쿼리를 실행하게 됩니다.

그래 아래 결과처럼 동일한 유저가 나오고 좋아요수를 몇개씩 받았는지, 평균은 몇인지도 나옵니다.

 

[연습] checkins 테이블에 courser_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

[연습] checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

select c.checkin_id
	   cs.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 cs
on c.course_id = cs.course_id

 

 

 

  3) From에 들어가는 Subquery

 

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

 

유저 별 좋아요 평균을 구해보겠습니다

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

위에 만든 내용을 마치 이미 존재하는 테이블처럼 사용할 수 있습니다

 

유저가 포인트가 많은 사람들은 평균적은 좋아요도 많이 받았을까?

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
    group by user_id
) a on pu.user_id = a.pu.user_id

 

 

[준비1] course_id별 유저의 체크인 개수를 구해보기!

--count(*)이 아니라  count(distinct(user_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별 check in 개수에 전체 인원을 붙이기

select a.course_id, b.cnt_checkins, a.cnt_total 
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

[한 걸음 더] 퍼센트를 나타내기

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

[두 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!

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

 

 

3. With절 알아보기

from 절에 들어가는 서브쿼리를 굉장히 보기 좋게 작업할 수 있습니다

 

바로 위에서 만든 쿼리문은 계속 서브쿼리가 붙어서 inner join안쪽이 너무 헷가립니다. 이걸 with절로 수정해보겠습니다.

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

 

---

 

👉 [실습예제-1] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

• 살펴볼 테이블: enrolled_details
• done_cnt는 들은 강의의 수(done=1)
  • (done 칼럼 : 들었을경우 1, 안들었을경우 0)
• total_cnt는 전체 강의의 수

with lecture_done as (
	select enrolled_id, count(*) as cnt_done
    from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
), lecture_total 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 lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id

👉 [실습예제-2] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

• 진도율 = (들은 강의의 수 / 전체 강의 수)

with lecture_done as (
	select enrolled_id, count(*) as cnt_done
    from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
), lecture_total 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 lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id

👉 [실습예제-3] 그러나, 더 간단하게 만들 수 있지 않을까!

done 칼럼이 boolean타입으로 1과 0의 데이터만 갖고 있어서 sum()함수로 대체 가능

select enrolled_id,
       sum(done) as cnt_done,
       count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id