Subquery

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

• 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를 이용해서 동일한 결과를 볼 수 있다.

#1 (...)에 쿼리문을 넣을 수 있다.
SELECT * FROM users
WHERE user_id in (.....)

#2 아래 쿼리문을 위(....)에 넣는다.
SELECT user_id FROM orders
WHERE payment_method = 'kakaopay'

#3 완성 (평소 서브쿼리문에 Tab 필수 !) 
SELECT user_id,name,email FROM users
WHERE user_id in (
	SELECT user_id FROM orders
	WHERE payment_method = 'kakaopay'
)

Where 에 들어가는 Subquery

where 필드명 in (subquery)

• 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력

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

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

SELECT * FROM point_users pu
WHERE point > (
	SELECT AVG(point) from point_users 
)

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

SELECT * FROM point_users pu 
WHERE point > (
	SELECT round(AVG(pu.point)) FROM point_users pu
	inner join users u
	on pu.user_id = u.user_id
	WHERE u.name = '이**'
);

Select 에 들어가는 Subquery

select 필드명, 필드명, (subquery) from ..

• '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지

#1 우선 표에서 4b8a10e6 라는 사람의 평균(평소에 받은 좋아요 수)을 구해본다.
SELECT AVG(likes)  FROM checkins
WHERE user_id = '4b8a10e6'

#2 checkin_id, user_id, likes를 찾아본다.
SELECT checkin_id, user_id, likes
FROM checkins

#3 2번에 1번을 넣어서 표 옆에 평균 컬럼을 넣는다.
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

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

select c.checkin_id,
       c.course_id, 
       c.user_id,likes,  
       (
       SELECT AVG(likes) FROM checkins
       WHERE course_id = c.course_id
       ) as course_avg
from checkins c

• 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 

From 에 들어가는 Subquery

가장 많이 사용하는 유형

• 유저별 point와 평균적으로 본인이 받은 좋아요의 상관관계 파악

#1 아래(평균적으로 받은 좋아요) 쿼리문과 likes를 조인 하면 바로 해결 될텐데...
SELECT user_id, round(AVG(likes),1) as avg_likes
FROM checkins c 
group by user_id 

#2 아래 쿼리문이 마치 있었던 것 처럼 생각한다.
SELECT user_id, point FROM point_users pu 

#3 삽입 !
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

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

SELECT course_id, COUNT(DISTINCT(user_id)) as cnt_checkins\
FROM checkins c
group by c.course_id 

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

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

• [결과] course_id별 like 개수에 전체 인원을 붙이기

SELECT a.course_id,
       a.cnt_checkins,
       b.cnt_total 
  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

• 퍼센트를 나타내기

SELECT a.course_id, 
       a.cnt_checkins, 
       (a.cnt_total / 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

• 강의 제목도 나타나면 좋겠네요!

SELECT c2.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 c2
on a.course_id = c2.course_id