[221103] SQL(2)

스파르타 코딩클럽 엑셀보다 쉬운 SQL을 보고 작성하였습니다.

✨ Subquery

서브쿼리란, 쿼리안의 쿼리라는 의미!
하위쿼리의 결과를 상위 쿼리 안에서 사용하면, SQL 쿼리가 훨씬 간단해진다.
Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 기능이다.

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'

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

where 필드명 in (subquery)
(1) from 실행
(2) Subquery 실행
(3) where .. in 절에서 subquery의 결과에 해당되는 조건으로 필터링 해줌
(4) 조건에 맞는 결과 출력

2. select

select 필드명, 필드명, (subquery) from ..
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 조건을 갖는 데이터의 값을 subquery에서 계산해서
(4) 함께 출력해준다!

3. from

가장 많이 사용되는 유형이다.
내가 만든 Select와 이미 있는 table을 Join하고 싶을 때 사용할 수 있다.
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!

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

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

---

✨ 문자열

1.SUBSTRING_INDEX : email에서 아이디만 가져오기

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

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

1일 때 : 아이디만 가져와짐
-1일 때 : 도메인만 가져와짐
@를 기준으로 텍스트를 쪼개고, 그 중 첫번째 조각을 가져와라 라는 의미이다!

2. SUBSTRING : ~어디까지만 가져오자

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

substring(field,i,j) : field안의 문자열의 i번째부터 j개 까지

---

✨ CASE문

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

subquery를 이용하면 case문으로 통계를 낼 수 있다.

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

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