[4주차]subquery

• where절에 넣기 : where 필드명 in (subquery)

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

결제수단이 카페인 유저들의 아이디, 이름, 이메일 

subquery = 큰 쿼리 안에 작은 쿼리 넣기(작은 쿼리부터 해석해서 점점 밖에 있는 쿼리를 해석해나감)

 

• select절에 넣기 : select 필드명, 필드명, (subquery) from ..

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;

 

• from절에 넣기 

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

원래 있던 select절을 from절에 넣어버린다고 생각.

두 개 절 따로 만들고 subquery로 이어버리는 게 편함.

 

• from에 넣고 inner join 해버리기(총 3개 테이블을 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

 

• with절로 정리하기(with로 table을 일종의 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

 

• 문자열 쪼개기

select user_id, email, SUBSTRING_INDEX(email,'@',1) from users

 

• 문자열 일부만 표시하기

select order_no, created_at, SUBSTRING(created_at,1,10) from orders

 

• case

select pu.point_user_id, pu.point,
    case 
    when pu.point > 10000 then '잘 하고 있어요!'
    else '조금 더 달려주세요!'
    END as '구분'
from point_users pu;