[4주차] SQL 4주차

스파르타코딩클럽 내일배움단 '엑셀보다 쉬운 SQL'

주요 내용: subquery, with, case

 

[subquery]

- 하나의 SQL 쿼리 안에 또 다른 SQL 쿼리 넣기

- where, select, from 절에서 유용하게 사용됨

(1) where

> where 필드명 in (subquery)

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

> TAB으로 줄 구분 잘 해주기

(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

> 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

 

[with]

- 복잡한 쿼리문을 깔끔하게 정리 가능

- inner join 할 때 유용

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

> select와 위의 괄호 사이의 틈이 있으며 에러가 난다. 붙여!

 

[case]

- SUBSTRING_INDEX: 문자열 쪼개기

(1) 쪼개는 문자 기준 앞

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

(2) 쪼개는 문자 기준 뒤

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

- SUBSTRING: 문자열 일부만 출력하기

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

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

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

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