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'

--  서브쿼리 : 쿼리 안의 쿼리. 미리 결과값을 확인한 하위쿼리문 그 자체를 하나의 테이블처럼 사용. 정렬유의

-- 서브쿼리 활용 예시(Where) >> IN 사용법 중요

SELECT user_id, name, email FROM users u
WHERE user_id IN (
         	  select user_id from orders o
         	  WHERE payment_method = 'kakaopay'	
          	  )

-- 서브쿼리 활용 예시(Select) >> 기존 테이블에 새로운 필드항목을 추가할때 유용

SELECT c.checkin_id, 
	   c.user_id, 
	   c.likes,
	   (
	   SELECT ROUND(AVG(likes),1)  FROM checkins 
	   WHERE user_id = c.user_id
	   ) as avg_like
  FROM checkins c 
  
-- 서브쿼리 활용 예시(From) >> ★가장 많이 사용! 하위 쿼리문을 하나의 테이블로 하여 Join하는 형태.
  
 select p.user_id, p.point, a.avg_likes FROM point_users p
 inner join (
            select user_id, ROUND(AVG(likes),1) as avg_likes FROM checkins c
            group by user_id 
            ) a on p.user_id = a.user_id

-- Where 연습 1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기 

SELECT * FROM point_users p
WHERE point > (
	SELECT AVG(point) from point_users
)


-- Where 연습 2. 이씨 성을 가진 유저들의 평균 포인트보다 큰 유저들의 데이터 추출하기 

SELECT * FROM point_users p
WHERE point > (
	select AVG(point) from users u 
inner join point_users p on u.user_id = p.user_id 
	 where name = '이**'
)

SELECT * FROM point_users p
WHERE point > (
	SELECT AVG(point) FROM point_users pu 
     WHERE user_id in (
	SELECT user_id FROM users WHERE name = '이**'
	)
)

SELECT * FROM users WHERE name = '이**'

-- >> 서브쿼리 안에 서브쿼리 중첩 가능 

 
-- Select 연습 1. checkins 테이블에 course_id별 평균 like수 필드 우측에 붙여보기

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 round(avg(likes),1) from checkins c2
 where course_id = '5f0ae408765dae0006002817'
  
-- >> 직접 필드하나를 찍어서 실제로 원하는 형태가 나오는지 확인 >> 상위테이블의 항목으로 받기
  
 
-- Select 연습 2. checkins 테이블에 과목명별 평균 like수 필드 우측에 붙여보기
 
 select checkin_id, c2.title, user_id, 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
 
 select round(avg(likes)) from checkins
 where course_id = '5f0ae408765dae0006002817'
 
--  From 절 연습
 
--  코스별 체크인 한 유저 수, 전체 유저 수, 비율

-- 1) course_id별 유저의 체크인 수 구하기
  
select course_id, count(distinct(user_id)) as cnt_checkins  from checkins
group by course_id

-- 2) course_id별 수강인원 구하기

select course_id, count(*) as cnt_total from orders
group by course_id

-- 3) course_id별 체크인 수에 전체인원 붙이기

select a.course_id, cnt_checkins, cnt_total 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

-- >> 1), 2)가 각각 하나의 테이블이 되어서 join. "select * from () inner () on" 형태를 미리 만들고 시작

-- 4) 비율 나타내기

select a.course_id, cnt_checkins, cnt_total, cnt_checkins/cnt_total as Ratio from (
		select course_id, count(distinct(user_id)) as cnt_checkins  from checkins
      group by course_id
) as a
   inner join (
   		select course_id, count(*) as cnt_total from orders
	  group by course_id
) as b on a.course_id = b.course_id

-- 5) 과목명으로 표시

select c.title , cnt_checkins, cnt_total, cnt_checkins/cnt_total as Ratio from (
		select course_id, count(distinct(user_id)) as cnt_checkins  from checkins
      group by course_id
) as a
   inner join (
   		 	select course_id, course_title, count(*) as cnt_total from orders
	  group by course_id
) as b on a.course_id = b.course_id
   inner join courses c on a.course_id = c.course_id 

select * from courses

-- >> 과목명이 있는 courses 테이블을 다시 조인하여 course_id 대신 수정

-- with : 테이블 단위에 붙이는 일종의 alias. 쿼리반복으로 인한 혼선방지 및 가독성을 위해 사용.
--        "with 이름 as (쿼리문), 이름2 as (쿼리문), ..." 형태로 사용

with table1 as (
				select course_id, count(distinct(user_id)) as cnt_checkins from checkins
              group by course_id
                ),
     table2 as (
     			select course_id, course_title, count(*) as cnt_total from orders
	          group by course_id
	            )
select c.title, 
	   cnt_checkins, 
	   cnt_total, 	
	   cnt_checkins/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 
 
 
-- 실전문법 (문자열,case) 주어진 데이터에서 원하는 문자형태로 다듬을 때
 
-- 1. 문자열 쪼개기 : substring_index(문자열,기준점,반환위치) 사용

select user_id, email, substring_index(email, '@', 1) from users u

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

-- 2. 문자열 일부만 출력 : substring(문자열,시작위치,출력문자수) 사용

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

select substring(created_at, 1, 10) as date, count(*) as orders_date from orders
group by date

-- 3. 경우에 따라 원하는 값을 새 필드에 출력 : case 사용 (조건문과 유사)

-- 특정 항목에 대한 case문 사용

select user_id, point,
	  (case when point > 10000 then '잘 하고 있어요!'
	   		else '조금 더 달려주세요!' end) as 메세지
  from point_users
  
-- 서브쿼리,with 등 활용
  
select point_user_id, point,
	  (case when point > 10000 then '1만 이상'
	        when point > 5000 then '5천 이상'
	   		else '5천 미만' end) as level
  from point_users
  
  
with table1 as (select point_user_id, point,
		  			(case when point > 10000 then '1만 이상'
		        		  when point > 5000 then '5천 이상'
		   				  else '5천 미만' end) as level
      			  from point_users)
select level, count(*) as cnt from table1
group by level


select point_user_id, point,
		  			(case when point > (select round(avg(point)) from point_users) then '잘 하고 있어요!'
		   				  else '열심히 합시다!' end) as msg
      			  from point_users

select round(avg(point)) from point_users

select substring_index(email, '@', -1) as domain, count(*) as cnt_domain from users
group by domain

select * from checkins
where comment like '%화이팅%'

with table1 as (
				select enrolled_id, count(*) as done_cnt from enrolleds_detail e2
			     where done = 1
		      group by enrolled_id
		        )
select e.enrolled_id, a.done_cnt, count(*) as total_cnt, round(a.done_cnt/count(*),2) as ratio from enrolleds_detail e
inner join table1 a on a.enrolled_id = e.enrolled_id
group by e.enrolled_id