SQL_ Subquery, with

SubQuery

하나의 쿼리 안에 또 다른 쿼리가 있는 것

where에 subquery

• where 필드명 in (서브쿼리)

select u.user_id, u.name, u.email from users u
inner join orders o on u.user_id = o.user_id
where payment = 'card'

//위 아래의 쿼리 결과는 같다.

select user_id, name, email from users
where user_id in (
	select user_id from orders
	where payment = 'card'
)

• 연습

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

select에 subquery

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

//먼저 랜덤으로 특정 아이디를 넣어 평균 likes 구해보기
select avg(likes) from checkins
where user_id = 'asdf'

//select 필드로 넣어주기
select c.checkin_id, 
       c.user_id,
       c.likes
       (
        select avg(likes) from checkins
         where user_id = c.user_id
       )
  from checkins c

• 연습
  checkins 테이블에 과목명(course title)별 평균 likes를 나타내라

//순서1) checkins 테이블에 필드 select
select ch.check_id,
       ch.course_id,
       ch.user_id,
       ch.likes,
  from checkins ch
  
//순서2) 랜덤으로 특정 아이디를 넣어 평균 likes 구해보기
select avg(likes) from checkins
where course_id = '랜덤아이디'

//순서3) subquery 만들기
select ch.check_id, ch.course_id, ch.user_id, ch.likes,
       (
        select round(avg(likes),1) from checkins
        where course_id = ch.course_id
       ) as course_avg
  from checkins ch
  
  
//순서4) inner join
select ch.check_id, ch.course_id, ch.user_id, ch.likes,
       (
        select round(avg(likes),1) from checkins
        where course_id = ch.course_id
       ) as course_avg
  from checkins ch
  inner join courses co on ch.course_id = co.course_id

//순서5) 'co.*' : co의 모든 필드가 잘 이어졌는지 확인
select ch.check_id, ch.course_id, ch.user_id, ch.likes,
       (
        select round(avg(likes),1) from checkins
        where course_id = ch.course_id
       ) as course_avg,
       co.*
  from checkins ch
  inner join courses co on ch.course_id = co.course_id

//순서6) course_id가 아닌 과목명(타이틀)을 보여주자.
select ch.check_id, co.title, ch.user_id, ch.likes,
       (
        select round(avg(likes),1) from checkins
        where course_id = ch.course_id
       ) as course_avg,
  from checkins ch
  inner join courses co on ch.course_id = co.course_id

from에 subquery

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.user_id

• 연습
  강의별 유저의 전체 인원 대비 체크인의 비율을 알고싶다.

//1) 과목별로 user_id 중복 없이 한번이라도 출석한 갯수
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

//2) 주문테이블에서 과목별 수강생 갯수 가져오기
select course_id, count(*) as cnt_total from orders
group by course_id

//3) 위 두 쿼리를 합친다.
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(*) as cnt_total from orders
  group by course_id
) b on a.course_id = b.course_id

//4) ratio 나타내기
select a.course_id, 
       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

//5) 강의 과목 이름을 붙여라 (courses table join)
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

쿼리를 깔끔하게 정리한다.

• 사용법 : ()안에 쿼리를 넣는다.

with table1 as (), table2 as()
//일종의 Alias 처럼 사용 가능해진다.

• 연습

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