SQL - 4주차

Subquery 에 대해서 알아보자.
: 쿼리 안의 쿼리

---

1. where에 들어가는 subquery

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

실행되는 순서 :

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

2. Select

: 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용

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

// 예시
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;

3. 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

순서

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

With 절

: subquery를 간단하게 묶어 주는 것.

with table1 as () , table2 as ()

// 예시
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

• inner join으로 연결해줬던 a, b 문장을 table1, table2 로 따로 빼서 보기 쉽게 만들어줌.
• 문장 뒤 a, b는 그대로 위치함.

SUBSTRING_INDEX

: 문자열 데이터를 다룸

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

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

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

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

3) orders 테이블에서 날짜까지 출력

select order_no, created_at, substring(created_at,1,10) as date from orders  
// 1~10번째의 문자열 출력, 여기선 첫번째가 1

4) 일별로 몇 개씩 주문이 일어났는지

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

Case

: 경우에 따라 원하는 값을 새 필드에 출력

select a.lv, 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 lv
	from point_users pu
) a
group by a.lv

// 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 lv
	from point_users pu
)

select level, count(*) as cnt from table1
group by a.lv