[22.02.14] SQL - Subquery (4주차)

Subquery란?

- 쿼리 안의 쿼리라는 뜻.

상위 쿼리 안에 들어가는 하위 쿼리를 서브쿼리라고 함.

하위 쿼리의 결과를 상위 쿼리에서 사용하면 더욱 간단해짐.

 

 

 

where 절에 들어가는 subquery

SELECT user_id, name, email from users u
where user_id in (
	SELECT user_id from orders o
	where payment_method = 'kakaopay'
)

select 절에 들어가는 subquery

SELECT c.checkin_id,
		c.user_id,
		c.likes,
		(
	 	SELECT avg(likes) from checkins c
	 	WHERE user_id = c.user_id 
		) AS avg_likes_user
	from checkins c;

from 절에 들어가는 subquery

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 c
	group by user_id
	) a on pu.user_id = a.user_id;

 

 

 

---

 

[문제] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

내가 쓴 쿼리 (작동 안 됨)

SELECT point_user_id, pu.point ROUND(avg(pu.point),1) FROM point_users pu
WHERE point > (
	SELECT * FROM point_users pu
	WHERE point
)

정답 쿼리

SELECT * FROM point_users pu
WHERE point > (
	SELECT avg(point) FROM point_users
)

 

 

---

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

정답 쿼리

SELECT * from point_users pu
where point > (
	SELECT round(avg(point),1) FROM point_users pu 
	inner join users u on pu.user_id = u.user_id
	WHERE u.name = '이**'
)

 

[문제] checkins 테이블에 course_id별 평균 likes 수 필드 우측에 붙여보기

내가 쓴 쿼리 = 정답 쿼리

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

 

[문제] checkins 테이블에 과목명별 평균 likes 수 필드 우측에 붙여보기

정답 쿼리

SELECT c.checkin_id, 
		c1.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 c1 on c.course_id = c1.course_id;

 

 

 

---

 

[단계1] course_id별 유저의 체크인 개수를 구해보기

SELECT course_id, count(DISTINCT(user_id)) as cnt_checkins FROM checkins c 
group by course_id

 

[단계2] course_id별 인원을 구해보기

SELECT course_id, count(*) as cnt_total FROM orders o
group by course_id

 

[최종 문제] course_id별 like 개수에 전체 인원을 붙이기

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