[SQL] Subquery, With, 문자열, Case

1. Subquery: 원하는 데이터를 더 쉽게 얻어보기

쿼리 안의 쿼리라는 의미. 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해짐

쪼개서 생각하자. 둘다 만든 다음에 합치면 된다.

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

-- 이걸 다른 방법으로 나타낼 수 있음! 우선 kakaopay로 결제한 user_id를 모두 구해보기
select u.user_id from orders
where payment_method = 'kakaopay'

-- 그 후에, user_id가 괄호 안에 있는 유저들만 골라보기 -> 서브쿼리. 할때 줄 잘 맞춰주기!!
select u.user_id, u.name, u.email from users u
where u.user_id in (
       select user_id from orders
       where o.payment_method = 'kakaopay'
)

​

- 자주 쓰이는 Subquery 유형

Where 에 들어가는 Subquery : where 필드명 in (subquery) 이런 방식. 큰 쿼리 안에 괄호 값이 있으면 사용

-- 예) kakaopay로 결제한 유저들의 정보 보기

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

 

Select 에 들어가는 Subquery : select 필드명, 필드명, (subquery) from ..

 

-- 예) '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금

-- 먼저 평균을 구한다. user_id='4b8a10e6' 를 예시로
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 c2
        where c2.user_id = c.user_id -- 하나하나 돌아가면서 실행한다는 뜻
       ) as avg_like_user
 from checkins c;

 

From 에 들어가는 Subquery (가장 많이 사용되는 유형!) Select와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱

-- 예) 유저별 좋아요 평균과 유저별 포인트의 상관관계를 보고 싶음. 

-- 우선 유저별 좋아요 평균을 구함
select user_id, round(avg(likes),1) as avg_like from checkin
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

-- 연습 course_id별 유저의 체크인 개수와 course_id별 인원을 한 테이블로 나타내기
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 할때 별칭을 사용해서 나타내야함!
-- 서브쿼리에서 명시된 테이블 명이 있어야 밖의 쿼리에도 사용 가능.

-- 여기에 비율과 강의 제목까지 나타내고 싶다면?
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

 

2. with절 

서브쿼리가 붙으면, inner join 안쪽이 너무 헷갈림! → 그 때 쓰는 것이 with 절! 결과는 같은데 훨씬 보기가 좋음

-- 예) 코스제목별 like 개수, 전체, 비율
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 (
	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

 

3. 실전에서 유용한 SQL 문법 (문자열, Case)

- 문자열 쪼개기

SUBSTRING_INDEX 사용 : email에서 @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!

-- 예) 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶음
-- asdjn12@naver.com
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users


-- 예)이메일에서 이메일 도메인만 가져와보기
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
-- @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

- 문자열 일부만 출력하기

SUBSTRING : 문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지

-- 예) orders 테이블에서 created_at을 날짜까지만 출력하기
select order_no, created_at, substring(created_at,1,10) as date from orders

-- 예) 일별로 몇 개씩 주문이 일어났는지 살펴보기
-- date로 묶어주고 갯수 확인

select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date

- CASE : 경우에 따라 원하는 값을 새 필드에 출력해보기

-- 예) 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시
select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;