데이터분석? 처음부터 한 걸음씩 - SQL #4

Subquery

쿼리문 안에 들어가는 쿼리문

ex) kakaopay로 결제한 유저들의 id, name, email정보 보기

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'

그런데 이것을 서브쿼리를 활용해 다르게 표현할 수 있다.

1) 우선 kakaopay로 결제한 user_id를 모두 구해보기 → K 라고 해보자

select user_id from orders
where payment_method = 'kakaopay'

2) 그 후에 user_id가 K에 있는 유저들만 골라 id, name, email 보기(name, email은 oders에 없고 users에 있음) → 이게 바로 서브쿼리!

원래는

select * from users u
where u.user_id in (aaa, bbb, ccc, ...) 

처럼 1)에서 나온 user_id 'aaa', 'bbb', 'ccc'등의 아이디를 하나씩 다 넣어줘야 하지만 서브쿼리를 이용하면

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 유형 알아보기

1) Where에 들어가는 Subquery

where 필드명 in (subquery)

ex) 카카오페이로 결제한 유저들만 테이블에서 출력하고 싶을 때

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

쿼리가 실행되는 순서
(1) from 실행: users 데이터를 가져와줌
(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
(4) 조건에 맞는 결과 출력

2) Select에 들어가는 Subquery

select 필드명, 필드명, (subquery) from ..

ex) '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금해 비교하려고 할 때
우선 특정 아이디의 평균을 구하는 방식은

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;

→ 각각의 아이디가 하나 출력될 때 마다 맨 오른쪽에 그 아이디의 평균 likes 값도 나옴

쿼리가 실행되는 순서
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력해준다!

3) From에 들어가는 Subquery(가장 많이 사용되는 유형!)

From은 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱이다!(내가 만든 테이블을 원래 있는 테이블처럼 사용하는 것)

ex) 우선 유저 별 좋아요 평균을 먼저 구해보면

select user_id, round(avg(likes),1) as avg_like 
from checkins
group by user_id

이후 여기에서 해당 유저 별 포인트를 보고 싶다면?

select pu.user_id, pu.point, a.avg_like 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

쿼리가 실행되는 순서
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!

설명
나는 유저 id, 유저 평균 포인트, 유저 평균 좋아요 수를 보고싶어.
근데 유저별 평균 좋아요 수를 먼저 구해야 하니 group by로 묶은 테이블 하나 만듬.
이후에 inner join을 써서 a라는 별칭으로 함.
근데 이때 동일한 필드명으로 매칭 시켜줘야 하니까 group by에서 출력할 필드명 설정할 때 user_id 도 넣어줘야지 inner join 이후에 user_id로 매칭시켜줄 수 있음(pu.user_id = a.user_id)

with절

with절을 통해 from절에 들어간 Subquery를 더 깔끔하게 정리할 수 있다!

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

실전에서 유용한 SQL 문법(문자열)

문자열 쪼개기(SUBSTRING_INDEX)

SUBSTRING_INDEX(필드명, '기준', 1 or- 1)

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

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

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

이메일에서 도메인만 가져와보기

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

@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!

문자열 일부만 출력하기(SUBSTRING)

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

orders 테이블에서 날짜까지 출력하기

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

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

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

실전에서 유용한 SQL 문법(Case)

특정 조건에 따라, 데이터를 구분해서 정리해주고 싶을 때 사용

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

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

Subquery를 이용해 이런 통계를 낼 수 있다.

1) 테이블을 하나 만들어주고

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

2) 서브쿼리를 이용해 group by로 통계를 낸다.

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

참고) 서브쿼리를 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