엑셀보다 쉬운 SQL - 4주차

Subquery란? 쿼리 안의 쿼리라는 의미

* Subquery는 where, select, from 절에서 유용하게 사용가능 *

 

 

• Where 에 들어가는 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 에 들어가는 Subquery (select 필드명, 필드명, (subquery) 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

 

 

• with 절로 더 깔끔하게 쿼리문을 정리
• 계속 서브쿼리가 붙으면, inner join 안쪽이 헷갈릴 수 있음

  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 절! 결과는 같은데 훨씬 보기가 좋음

 

 

• 실전에서 유용한 SQL 문법  : 문자열 쪼개기 (SUBSTRING_INDEX)

예시 ) 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!

 

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

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

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

 

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

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

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

 

 

• 실전에서 유용한 SQL 문법 : CASE ( 경우에 따라 원하는 값을 새 필드에 출력해보기)

특정 조건에 따라, 데이터를 구분해서 정리해주고 싶을 때가 있겠죠?

이런 경우에 CASE 라는 문법이 사용됩니다

 

 

• .10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?
  
•  
• 포인트 보유액에 따라 다르게 표시해주기
• select pu.point_user_id, pu.point,
• case
• when pu.point > 10000 then '잘 하고 있어요!'
• else '조금 더 달려주세요!'
• END as '구분'
• from point_users pu;

 

포인트 보유액에 따라 구분을 나누기	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
서브쿼리를 이용해서 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 lv  from point_users pu ) a group by 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 lv