SQL 4주차

[4주차 개발일지 시작 ^_^] - subquery, 실전SQL

1)subquery 연습

1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출

select * from point_users pu 
where point > (
	select avg(point) from point_users
)

2. 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출
   방법1

select * from point_users pu 
where point > (
		select avg(point) from point_users pu 
		inner join users u on pu.user_id = u.user_id 
		where u.name = '이**'
)

방법2

select * from point_users pu 
where point > (
	select avg(point) from point_users pu 
	where user_id in (
			select user_id from users where name = '이**'
	)
)

• 한눈에 보기좋게 짜는게 중요. tab들여쓰기로 줄 잘 맞춰주기

3. checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

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

4. checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

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

*course 테이블 조인한 결과 다 보는 법

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.*   (c2의 결과 모든 것을 보여줘라)
    from checkins c
inner join courses c2 on c.course_id = c2.course_id

5. 코스제목별 체크인수, 전체, 비율
   course_id별 유저의 체크인 개수 구하기,
   course_id별 인원 구해보기,
   course_id별 checkins 개수에 전체 인원 붙이기,
   퍼센트 나타내기
   강의 제목(title) 도 나타내기

select c.title,
       a.course_id, 
       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 o 
group by course_id 
) b on a.course_id = b.course_id
inner join courses c  on a.course_id = c.course_id

5-1
(계속 서브쿼리가 붙으면서 헷갈릴 때 그때 사용하는 것이 with 절! )

with table1 as ( ),
table2 as ( )
select from table1 a
inner join table2 b on a.ㅇ = b.ㅇ
inner join (courses c on a.ㅇ=c.ㅇ

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 o 
	group by course_id
)

select c.title,
       a.course_id, 
       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절 사용시 간결해져 맨하단 3줄 어떻게 조인했는지 명확해짐)

실전에서 유용한 SQL문법(문자열, Case)

문자열 쪼개기

substring_index(필드, '문자', 1(앞) or -1(뒤) )

ex) 이메일에서 아이디만 가져오기

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

ex2) 이메일에서 도멘인만 가져오기

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

문자열 일부 출력

substring(필드, 시작포인트, 시작포인트부터 몇자)

ex) orders 테이블에서 날짜 출력하기

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

ex2) orders 테이블에서 일별 주문수 출력하기

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

CASE 활용

case when (조건1) then (결과1)
when (조건2) then (결과2)
else (결과3) end

ex) 포인트 1만점 넘으면 잘하고 있어요. 그렇지 않으면 조금만 더 파이팅 메세지 출력하기

select pu.user_id, pu.point,
       (case when pu.point > 10000 then '잘 하고 있어요!'
       else '조금만 더 파이팅!' end) as msg       
  from point_users pu

ex2) case when (서브쿼리로 통계내기)
1만이상은 '1만 이상', 5천 이상은 '5천 이상', 그 외는 '5천 미만' 으로 그룹을 나눈 후 alias는 lv(level)로 정하고 그룹별 개수 세기.
with 절 이용해 하나의 테이블로 이용하기 위해 table1 이름으로 바꿔주기

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(*) as cnt from table1 a
group by a.lv  

subquery 연습문제
1. 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요'/ 낮으면 '열심히 합시다!' 표시하기

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

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

나>

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

쌤>

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

3. '화이팅'이 포함된 오늘의 다짐만 출력해보기
   (어렵게 substring_index를 생각하지 말고 like % 이용하는 것도 생각하기)

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

(중급)
1. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
방법1

select a.enrolled_id, a.done_cnt, b.total_cnt  from (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail ed 
	where ed.done = 1
	group by enrolled_id 
) a
inner join (
	select enrolled_id, count(*)as total_cnt from enrolleds_detail ed 
	group by enrolled_id 
) b on a.enrolled_id = b.enrolled_id

방법2

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

2. 수강등록정보(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

3. 더 간단하게 만들 수 있지 않을까? (sum함수 활용, done = 1 이라서 가능.
   sum 3이면 3명 이런식으로 바로 인원수로 나와서 가능)

select enrolled_id, 
       sum(done) as done_cnt,
       count(*) as total_cnt ,
       round(sum(done)/count(*),2) as ratio
   from enrolleds_detail ed 
group by enrolled_id