엑셀보다 쉬운 SQL - 4주차

Subquery란?

Subquery란 쿼리안에 쿼리가 있는것을 뜻합니다.

예를들어
kakaopay로 결제한 유저들의 정보

inner join으로 쿼리문을 작성한다면

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'
로 작성됩니다.

Subquery를 이용하여 작성하면
select u.user_id, u.uname, u.email from users u where u.user_id in ( select user_id from orders where payment_method = 'kakaopay')
괄호를 이용하여 쿼리문을 작성하는 방식으로 보다 더 간단하게 쿼리문을 작성 할 수 있습니다.

Subquery는 select.from,where절에 모두 사용할 수 있습니다.

select절을 안에 Subquery

select절 안에 Subquery는 기존 테이블에 있는 정보와 함께 추가로 보여주고 싶은 통계가 있을때 select절에 Subquery를 사용합니다.

예를들어

checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select course_id, (select avg(likes) from checkins c2 where c.course_id = c2.course_id) from checkins c group by course_id

from절 안에 Subquery

from절 안에 Subquery를 사용하는 경우는 본인이 만든 Select와 이미 있는 테이블을 연결하고 싶을때 사용합니다.

예를들어

course_id별 check in 개수에 전체 인원을 붙이기

select a.course_id, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio 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

where절 안에 Subquery

where절 안에 Subquery를 사용하는 경우는 Subquery의 결과를 조건으로 사용하는 경우입니다.

예를들어

전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select * from point_users p where p.point > (select avg(p2.point) from point_users p2)

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

문자열 다루기

문자열을 다루기 위해서 SUBSTRING, SUBSTRING_INDEX에 대해 알아야합니다.

이 둘은 문자열을 쪼갠다는 공통점이 있지만 SUBSTRING은 문자열의 일부만 출력할때 사용을하는 반면 SUBSTRING_INDEX는 한 문자를 기준으로 쪼갠다는 차이점이 있습니다.

SUBSTRING의 예

orders 테이블에서 created_at을 날짜까지만 출력

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

SUBSTRING_INDEX의 예

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

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

@를 기준으로 앞의것을 가져올때는 1, 뒤에 것을 가져올때는 -1을 작성합니다.