SQL 복습 - 4

1. Subquery

select 문을 통째로 구문에 넣는 기능
where, select, from 절에서 사용

2. where 절에서 쓰이는 subquery

where in (subquery)

카카오페이로 결제한 유저들만 유저 테이블에서 출력

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

3. select 절에서 쓰이는 subquery

select , , (subquery) from ..

'오늘의 다짐' 좋아요 수가 본인이 평소에 받았던 좋아요 수랑 비교하고 싶다.

먼저 평균 구하기

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

user_id 값에 checkins 테이블의 user_id 값 넣기

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;

4. From 절에 들어가는 subquery

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

유저 별 좋아요 평균

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

5.SQL 문법

1) SUBSTRING_INDEX(문자열, 기준, 위치)

• 이메일에서 id 만 가져오기

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

• 이메일에서 도메인만 가져오기

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

2) SUBSTRING(문자열, 출력하고 싶은 첫 글자의 위치, 텍스트 길이)

• order 테이블에서 날짜까지 출력해보기

select order_no, created_at, substring(created_at,1,10) as date from orders

3) Case : 경우에 따라 원하는 값을 새 필드에 추가

case 
when 조건 1 then 결과
else 조건 2 then 결과
end as 나머지 조건의 결과

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

4) with

select 문이 너무 길어서 구문이 지저분해졌을 때 select 문을 요약할 수 있는 문법

with 테이블명 as (select 문) 

with table1 as (
  select pu.point_user_id, pu.point, 
  case
  when pu.point > 10000 then '1만 이상' 
  when pu.point > 5000 then '5천 이상'
  else '5천 미만'
  END as lv
  from point_users pu
)
select table1.lv, count(*) as cnt from table1
group by lv