엑셀보다 쉬운, SQL - 4일차(2)

실전에서 유용한 SQL 문법

 

4:56 PM - 5:46 PM

SUBSTRING_INDEX , SUBSTRING , Case문 

 

SUBSTRING_INDEX

SUBSTRING_INDEX(email, '@' , 1) email을 @로 쪼개는데 1번째꺼만 보여줘라. 혹은 -1일 경우 마지막것만 보여줘라

SUBSTRING_INDEX(email, '@',-1)

// 예시

select SUBSTRING_INDEX('limchae098@gmail.com', '@',1)

-> 결과는 limchae098

select SUBSTRING_INDEX('limchae098@gmail.com', '@',-1)

-> 결과는 gmail.com

 

SUBSTING

//문자열에서 일부만 출력

//날짜별로 주문 건수 조회하기

select SUBSTRING(created_at,1,10) as date, count(*) from orders o

group by date

 

Case

//CASE문

 

select user_id, point,

(case when point >10000 then '잘하고 있어요'

else '조금만 더 파이팅' end ) as msg

from point_users pu

 

 

//서브쿼리를 이용한 Case문 통계

 

select a.lv, count(*) as cnt from (

select user_id, point,

(case when point >10000 then '1만 이상'

when point >5000 then '5천 이상'

else '5천 미만' end ) as lv

from point_users pu

) a

group by a.lv

 

// with절 사용

with table1 as (

select user_id, point,

(case when point >10000 then '1만 이상'

when point >5000 then '5천 이상'

else '5천 미만' end ) as lv

from point_users pu

)

 

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

group by a.lv

 

퀴즈 

1. 포인트가 평균 이상일 경우 잘하고 있어요 , 평균 미만일 경우 열심히 합시다 출력

/1. 포인트가 평균 이상일 경우 잘하고 있어요 , 평균 미만일 경우 열심히 합시다 출력

select point_user_id, point,

(Case when point >= (select avg(point) from point_users) then '잘하고 있어요'

else '열심히 합시다.' end) as msg

from point_users pu

 

 2. 이메일 도메인별 유저 수 세기

//2. 이메일 도메인별 유저 수 세기

select SUBSTRING_INDEX(email,'@',-1) as domain, count(*) as cnt from users u

group by domain

3. 화이팅이 포함된 오늘의 다짐만 출력해보기

//3. 화이팅이 포함된 오늘의 다짐만 출력해보기

select comment from checkins c

where comment like '%화이팅%'

 

4. 들은 강의, 전체 강의 갯수 조회하기

//4. 들은 강의, 전체 강의 갯수 조회하기

 

select a.enrolled_id, done_cnt, total_cnt, round(done_cnt/total_cnt,2) as ratio from

(

select enrolled_id, count(*) as total_cnt

from enrolleds_detail ed group by enrolled_id

) a

inner JOIN

(

select enrolled_id, count(*) as done_cnt

from enrolleds_detail ed where done = 1

group by enrolled_id

) b on a.enrolled_id = b.enrolled_id

 

 

select enrolled_id, count(*) as cnt_total , sum(done) as cnt_done

from enrolleds_detail ed

group by enrolled_id