[스파르타/SQL] Sub query - Inner join, with / Substring, Substring Index / Case when ~ then else ~ end

제일 어려웠던 서브쿼리
코드가 길어지다보니 너무 헷갈린다

문제와 테이블을 파악하고
가장 효율적인 방법을 쓸 것 !

[Substring]

substring_index(어느 열, 어디 기준_숫자 '문자', 1은 앞 -1은 뒤)
substring(어느 열, 어디 기준, 몇개 추출) <-엑셀의 left, right 함수

[Case]

Select ~, ~,
      (Case when point > 100 then '굳'
      		when point > 50 then '낫밷'
		    else '힘' end) as msg
from ~

[Case를 서브쿼리로]

Select ~, ~
from (
      Select ~, ~,
            (Case when point > 100 then '굳'
                  when point > 50 then '낫밷'
                  else '힘' end) as msg
      from ~
)

[With로 정리 - Select ~ from 필요함]

with table1 as (
				Select ~, ~,
    		   (Case when point > 100 then '굳'
      				 when point > 50 then '낫밷'
		   			 else '힘' end) as msg
				from ~
             )
             
Select ~, ~ from table1

[distinct 사용시 중복없이 센다]

[0등의 값은 sum을 사용해보는 것도 좋음]

[4주차 실습]

####4주차
#카카오페이로 결제한 유저들의 정보 보기
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 * from users u 
where user_id in (.....) #유저아이디가 in()해당되는 것만 보고 싶다

select user_id  from orders o 
where payment_method = 'kakaopay'

#>>>
#서브쿼리 사용하기 - 서브쿼리에 연결되는 메인쿼리 데이터 추출하기
select * from users u 
where user_id in (
	select user_id  from orders o 
	where payment_method = 'kakaopay'
)

################
#1
select AVG(likes)  from checkins
where user_id = '4b8a10e6'

#2
select checkin_id, user_id , likes from checkins

#3
select c.checkin_id, 
	   c.user_id , 
	   c.likes,
	   ( 				#user_id 나올 때마다 아래 서브쿼리 실행함. 
	    select AVG(likes)  from checkins
		where user_id = c.user_id
		) as ave_likes_user
   from checkins c

#유저별 좋아요 평균
 select c.user_id , ROUND(avg(c.likes),1) as avg_likes from checkins c 
 group by user_id 

 
#>>>>>>>>좋아요가 높으면 포인트도 많이 받았을까?
 select pu.user_id , pu.point from point_users pu  
  
#>>>>>조인시키기
 select pu.user_id , pu.point from point_users pu
 inner join (
	 select c.user_id , ROUND(avg(c.likes),1) as avg_likes from checkins c 
	 group by user_id 
 ) a 
 on pu.user_id = a.user_id
 
 #계속
 select pu.user_id , pu.point, a.avg_likes from point_users pu
 inner join (
	 select c.user_id , ROUND(avg(c.likes),1) as avg_likes from checkins c 
	 group by user_id 
 ) a 
 on pu.user_id = a.user_id
 
 #전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
 select * from point_users pu 
 where pu.point > ( 
 					select avg(point) from point_users
 )
 
#이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출
#내가 푼 것
select * from point_users pu
where pu.point > ( 	)
   
 select * from point_users
 where point > (
	 select avg(point)  from users u 
	 inner join point_users pu on u.user_id = pu.user_id 
	 where u.name like "이%"
  )
 
 #ver2
select * from point_users pu
where pu.point > (
	  select avg(point) from point_users pu 
	  where user_id in (
	  		select user_id from users
	  		where name = "이**"
	  		)
	)
 
#select절에 들어가는 서브쿼리 연습
#checkins 테이블에 course_id 별 평균 likes 수 필드 우측에 붙이기

select c.checkin_id , c.course_id , c.user_id , c.likes, 
	   () 
from checkins c 

##
select avg(likes) from checkins
where course_id = '5f0ae408765dae0006002817'

##
select c.checkin_id , c.course_id , c.user_id , c.likes, 
	   (
	   select ROUND(avg(likes),1) from checkins
		where course_id = c.course_id 
		) as course_avg
   from checkins c 

##
 select * from courses

##
select c.checkin_id , c.course_id , c.user_id , c.likes, 
	   (
	   select ROUND(avg(likes),1) from checkins
		where course_id = c.course_id 
		) as course_avg,
		c2.*
  from checkins c 
  inner join courses c2 on c.course_id = c2.course_id 
 	  
##최종ㅇ
  select c.checkin_id , c2.title , c.user_id , c.likes, 
	   (
	   select ROUND(avg(likes),1) from checkins
		where course_id = c.course_id 
		) as course_avg
  from checkins c 
  inner join courses c2 on c.course_id = c2.course_id 


###### distinct 를 써야 중복없이 센다. 한 번이라도 한 사람의 수.
 select course_id, count(distinct(user_id)) as cnt_checkins from checkins c 
 group by course_id 
 
 select course_id, count(*) as cnt_total from orders
 group by course_id 
 
 ##두 개 조인하기
 select a.course_id, a.cnt_checkins, b.cnt_total from 
 (
	  select course_id, count(distinct(user_id)) as cnt_checkins from checkins c 
	  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
	
 
 ##ratio
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 c 
	  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로 정리하기 젤 위에서 네이밍해주기
 with table1 as (
 	 select course_id, count(distinct(user_id)) as cnt_checkins from checkins c 
	 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 
 
##substring 
#문자열 정렬하기 SUBSTRING_INDEX(어떤 열, 어느 기준으로, 1이면 앞, -1이면 뒤)
select user_id , email , SUBSTRING_INDEX(email,'@',-1) from users

#substring (어떤 열, 어디부터, 몇자)
select SUBSTRING(created_at, 1, 10) as date, count(*)   from orders
group by date

##case 구간별 표기
select pu.user_id , pu.point, 
		(case when pu.point > 10000 then '잘 함'
		      else '힘내라' end) as msg
	from point_users pu 
 
select pu.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 a.lv, count(*) from (
		select pu.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 a.lv
 

##with로 정리하기
with table1 as (
		select pu.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 a.lv, count(*) from table1 a
group by a.lv


#포인트 평균 이상시 '잘 하고 있어요!'/미만은 '열심히 합시다!'
#풀이
select pu.user_id , pu.point,
		(
		case when pu.point > 5000 then '잘 하고 있어요!'
			 else '열심히 합시다!' end
		) as msg
		from point_users pu 		
		
#내풀이	
select pu.point_user_id, pu.point,
	  (
	  case when pu.point >= (select avg(point) from point_users) then 'a'
	  else 'b' end
	  ) as msg
from point_users pu 


##이메일 도메인별 유저의 수 세어보기
select SUBSTRING_INDEX(email, "@",-1) as domain, count(*) as cnt_domain from users
group by domain

#풀이
select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email, "@",-1) as domain from users
) a
group by domain


##3. '화이팅'이 포함된 오늘의 다짐만 출력해보기
select * from checkins
where comment like "%화이팅%"

##중급퀴즈
#수강등록정보 enrolled_id 별 전체 강의 수와 들은 강의의 수 출력해보깅
select enrolled_id , count(*) as done_cnt from enrolleds_detail ed 
 where ed.done = 1
 group by enrolled_id

select enrolled_id , count(*) as total_cnt from enrolleds_detail ed 
 group by enrolled_id

##합치기
with table1 as (
	select enrolled_id , count(*) as done_cnt from enrolleds_detail ed 
	   where ed.done = 1
	   group by enrolled_id
) , table2 as (
	 select enrolled_id , count(*) as total_cnt from enrolleds_detail ed 
	 group by enrolled_id
)
select a.enrolled_id, a.done_cnt, b.total_cnt from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

#진도율 = 들은 강의 수/전체강의수
with table1 as (
	select enrolled_id , count(*) as done_cnt from enrolleds_detail ed 
	   where ed.done = 1
	   group by enrolled_id
) , table2 as (
	 select enrolled_id , count(*) as total_cnt from enrolleds_detail ed 
	 group by enrolled_id
)
select a.enrolled_id,
	   a.done_cnt,
	   b.total_cnt, 
	   round((a.done_cnt/b.total_cnt),2) as ratio 
	   from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

#다른방법 - 초간단 
select enrolled_id ,
	   sum(done) as done_cnt, #0은 더해도 0이니까
	   count(*) as total_cnt ,
	   round(sum(done)/count(*),2) as ratio
   from enrolleds_detail ed 
group by enrolled_id