개발지식/MySQL

Subquery, with절 사용법과 SQL 문자열, case

다오__ 2023. 5. 1. 20:24

내일배움캠프의 엑셀보다 쉬운 SQL 수업 4주차 바탕으로 작성하였습니다.

1. Subquery

서브쿼리란, 쉽게 말해 쿼리 안의 쿼리라는 의미입니다. 하위 쿼리의 결과를 상위 쿼리에서 사용하면 SQL 쿼리가 훨씬 간단해집니다.
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'
)

해당 구문의 의미는 '결제 수단이 카카오페이인 유저 아이디를 주문테이블에서 가져와 해당 조건에 맞는 유저 아이디,이름,이메일을 유저테이블에서 가져와라' 라는 의미입니다.

2. 자주 쓰이는 Subquery 유형

- Where에 들어가는 Subquery

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

의미는 '카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해 가져와라'라는 의미입니다.
서브쿼리의 결과물을 조건에 활용해 사용하는 것을 볼 수 있습니다.

- Select에 들어가는 Subquery

Select는 결과를 출력하는 부분입니다. 기존 테이블에 함께 보고싶은 '통계 데이터를 붙이는 것'에 사용합니다.

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 (가장 많이 사용되는 유형)

From은 내가 만든 Select와 이미 있는 테이블을 join하고 싶을 때 사용합니다.

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

해당 구문의 의미는 '유저 아이디별 좋아요수 평균을 구한 테이블과 포인트테이블을 조인하여 유저아이디, 좋아요 수 평균, 포인트를 함께 출력해 가져와라'라는 의미입니다.

3. with절 사용하기

  • 서브쿼리를 그룹화하여 단순하게 만들 수 있습니다.
  • with절은 서브쿼리를 정리하는데 큰 도움이 됩니다. 실제로 다수의 서브쿼리를 사용하게 될 때 with절을 사용하여 가시성을 높일 수 있습니다.

기존의 코드

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

4. 실전에서 유용한 SQL 문법에 대해서 알아봅니다.

- SUBSTRING_INDEX

특정 값을 기준으로 잘라 앞부분 혹은 뒷부분을 인덱스로 표현해 가져올 수 있습니다.

예시)
이메일에서 아이디만 가져와 보기

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

문자 '@'를 기준으로 자른 후 앞의 값을 가져옵니다.

'@'뒤의 도메인만 가져와 보기

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

문자 '@'를 기준으로 자른 후 뒤의 값을 가져옵니다.

- SUBSTRING

인덱스를 기준으로 해당 부분을 잘라 가져옵니다.

orders테이블에서 전체가 아닌 일부분(날짜)까지만 표시하게 해보기

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

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

아래처럼 응용해볼 수 있습니다.

일별로 몇개씩 주문이 일어났는지 살펴보기

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

- Case

조건식이 여러개 필요할 때 유용합니다.

예시)
포인트 보유액에 따라 다르게 표시해주기

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

이렇게 with절과도 사용할 수 있습니다.

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 level
    from point_users pu
)
select level, count(*) as cnt from table1
group by level