SQL 4주차

4주차

Subquery

• 쿼리 안의 쿼리
• 원하는 데이터를 더 쉽게 얻어올 수 있음

1. where 절에 들어가는 Subquery

• 쿼리 결과를 조건으로 활용

select * from users u
where u.user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
)

2. select 절에 들어가는 Subquery

• 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙일 때 사용

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

3. from 절에 들어가는 Subquery

• 내가 만든 Select를 원래 있던 테이블처럼 다른 테이블과 Join하고 싶을 때 사용

select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
	select user_id, round(avg(likes),1) as avg_likes from checkins
	group by user_id
) a on pu.user_id = a.user_id

실습 (where, select)

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

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

 

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

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

 

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

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

 

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

select checkin_id, c3.title, user_id, likes, 
(
	select round(avg(c2.likes),1) from checkins c2
	where c.course_id = c2.course_id
) as course_avg
from checkins c
inner join courses c3 on c.course_id = c3.course_id

 

실습 (from, inner join)

1. course_id별 유저의 체크인 개수를 구해보기

select course_id, count(DISTINCT(user_id)) as cnt_checkins from checkins
group by course_id

• 중복 제거하고 셀 때 distinct

2. course_id별 인원을 구해보기

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

 

3. course_id별 like 개수에 전체 인원을 붙이기

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

 

4. 전체 중 얼마나 like하는지 붙이고 강의 제목도 나타내기

select c.title, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio from
(
	select course_id, count(*) as cnt_total from orders
	group by course_id
) a
inner join(
	select course_id, count(DISTINCT(user_id)) as cnt_checkins from checkins
	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
	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

• 서브쿼리가 계속 붙으면 inner join 안쪽이 헷갈릴 수 있어 table로 치환하는 느낌

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

• SUBSTRING_INDEX(email, '@', 1) : @ 앞의 내용 가져오기
• SUBSTRING_INDEX(email, '@', -1) : @ 뒤의 내용 가져오기
• SUBSTRING(created_at, 1, 10) : 문자열의 시작부터 10자리 가져오기

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

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

• 10000점을 기준으로 다른 메시지를 새 필드에 출력하기

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 level
		  from point_users pu
)
select a.level, count(*) as cnt from table1 a
group by a.level

• with절로 묶으면 table을 아래에서 계속 사용 할 수 있음

 

퀴즈

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

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

 

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

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

 

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

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

 

4. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

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


#with절 사용
with done as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
), total as (
	select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id
)
select a.enrolled_id, a.done_cnt, b.total_cnt from done a
inner join total b on a.enrolled_id = b.enrolled_id

 

5. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

with done as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
), total as (
	select enrolled_id, count(*) as total_cnt from enrolleds_detail
	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 done a
inner join total b on a.enrolled_id = b.enrolled_id