[SQL] Subquery 연습

Where 절에 들어가는 Subquery 연습해보기

예제 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 = "이**");

Select 절에 들어가는 Subquery 연습해보기

예제 1) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select checkin_id, course_id, user_id, likes, 
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) 
from checkins c;

예제 2) 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 절에 들어가는 Subquery 연습해보기

예제 1) 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

예제 2) 퍼센트를 나타내기

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

with절 연습하기

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
	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
	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