SQL Subquery With : 스파르타 코딩클럽 엑셀보다 쉬운 SQL 4주차

쿼리, 신난다. 하하.

 

1. Subquery

하나의 SQL 쿼리 안에 또 다른 SQL 쿼리가 있는 것

Subquery가 많은 경우 한 눈에 볼 수 있게 잘 정리하는 게 성장 목표!

(  Tab  을 이용한다.)

 

kakaopay로 결제한 유저들 정보 보기

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'

subquery 활용해서 위에 쿼리 바꿔보기

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

 

- select 에 들어가는 subquery

SELECT 	c.checkin_id , 
		c.user_id , 
		c.likes,
		(
		SELECT avg(likes) from checkins 
		WHERE user_id = c.user_id
		) as avg_like_user
from checkins  c

- from 절에 들어가는 Subquery (가장 많이 유용하게 씀)

 

유저별 좋아요 평균

SELECT u.user_id, round(avg(c.likes),1) as avg_likes FROM users u 
inner join checkins c on u.user_id  = c.user_id 
group by u.user_id ;

 

포인트가 높은 사람이 좋아요를 많이 받을까?

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

 

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

SELECT * FROM point_users pu 
WHERE pu.point > (
	SELECT avg(point) as avg_point from point_users 
) ;

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

SELECT * FROM point_users pu 
WHERE pu.point > (
	SELECT avg(point) as avg_point from point_users p
	inner join users u on p.user_id  = u.user_id 
	WHERE name = '이**'
) ;

 

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

SELECT * from checkins c 
inner join (
			select course_id , avg(likes) from checkins 
			group by course_id 
			) a on c.course_id = a.course_id ;

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

SELECT * from checkins c 
inner join (
			select c2.title , c2.course_id , avg(likes) from checkins c1
			inner join courses c2 on c1.course_id = c2.course_id 
			group by c2.title 
			) a on c.course_id = a.course_id ;

 

주의!!

제가 스스로 작성한 쿼리문이기 때문에 강의의 답안과 다를 수 있습니다. ^ㅂ^

정확한 답안은 강의를 참고해 주시거나 다른 좋은 자료를 찾아 보시길 바랍니다~

 

[준비 1] course_id 별 유저의 체크인 개수를 구해보기!

SELECT c.course_id ,  count(distinct(user_id)) as cnt_checkins from checkins c 
group by c.course_id

[준비 2] course_id 별 인원을 구해보기!

SELECT o.course_id, count(*) as cnt_users from orders o 
group by o.course_id

[진짜 하고 싶은 것] course_id 별 like 개수에 전체 인원을 붙이기

select a.course_id, cnt_checkins, a.likes, b.cnt_total from 
(
	SELECT c.course_id , count(distinct(c.user_id)) as cnt_checkins, count(c.likes) as likes from checkins c 
	group by c.course_id
)a
inner join
(
	SELECT o.course_id, count(*) as cnt_total from orders o 
	group by o.course_id
)b  on a.course_id = b.course_id
group by a.course_id ;

[한 걸음 더] 퍼센트를 나타내기

select 
a.course_id, 
cnt_checkins, 
a.likes, b.cnt_total, 
round(cnt_checkins / cnt_total * 100,1) as ratio 
from 
(
	SELECT c.course_id , count(distinct(c.user_id)) as cnt_checkins, count(c.likes) as likes from checkins c 
	group by c.course_id
)a
inner join
(
	SELECT o.course_id, count(*) as cnt_total from orders o 
	group by o.course_id
)b  on a.course_id = b.course_id
group by a.course_id ;

 

[반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!

select 
c.title,
a.course_id, 
a.cnt_checkins, 
a.likes, b.cnt_total, 
round(cnt_checkins / cnt_total * 100,1) as ratio 
from 
(
	SELECT c.course_id , count(distinct(c.user_id)) as cnt_checkins, count(c.likes) as likes from checkins c 
	group by c.course_id
)a
inner join
(
	SELECT o.course_id, count(*) as cnt_total from orders o 
	group by o.course_id
)b  on a.course_id = b.course_id
inner join
(
	select * from courses c2 
)c on a.course_id = c.course_id
group by a.course_id

 

2. With 절

table 로 묶어준다!

with table1 as
(
	SELECT course_id , count(distinct(user_id)) as cnt_checkins, count(likes) as likes from checkins 
	group by course_id
), table2 as
(
	SELECT course_id, count(*) as cnt_total from orders 
	group by course_id
), table3 as
(
	select * from courses
)
select 
c.title,
a.course_id, 
a.cnt_checkins, 
a.likes, 
b.cnt_total, 
round(a.cnt_checkins /b.cnt_total * 100,1) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id 
inner join table3 c on a.course_id = c.course_id

 

3. 소감

복잡해지면 조금 시간이 걸리는 것 같다. 파이팅.