SQL 3주차

Subuquery

쿼리안에 다른 쿼리가 있는것을 의미한다.

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

가장 많이 쓰는 유형
1. where

//카카오페이로 결제한 유저만 테이블에서 출력한다면..
select * from users u
where u.user_id in (select o.user_id from orders o 
					where o.payment_method = 'kakaopay');

실행 순서 from->Subquery-> where 절
2. 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;

실행 순서 밖의 쿼리문 출력과정에서 subquery 실행.
3. from

// 유저 별 포인트를 보고싶다면.
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

실행 순서 서브쿼리 -> 메인 쿼리

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

with를 사용하여 임시 테이블을 형성해 긴 쿼리를 짧게 만드는 방법이다.

실전에서 많이 쓰이는 문법

이메일에서 아이디만 가져오기.
이메일에서 이메일 도메인만 가져오기.

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

문자열 일부만 출력하기.

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

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

CASE

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 lv, count(*) as cnt from table1
group by lv

case를 사용하면 각 값에 따라서 통계를 만들어 낼수있음.