[SQL]개발일지(스파르타코딩국비교육4주차)_subquery,with..

SUBQUERY

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

 

예시) kakaopay로 결제한 유저들의 정보 보기(ID, 이름, email주소)

배운대로 쿼리쓰면 요렇게 됨.

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'

요걸 요렇게 해보기.

 

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

select user_id FROM orders o

where payment_method = 'kakaopay' 의 조건을 만족하는 user_id에 대해서만 정보를 가져오기!

가 처리가 되면서 결과적으로 위의 쿼리와 아래의 쿼리가 같은 결과를 출력하게 된다!

 

바로 이 큰 쿼리문 안에 들어가는 쿼리문을 subquery(서브쿼리)라고 부른다.

where, from, select 절 어디나 들어갈 수 있음.

 

실행순서는 써브쿼리를 먼저 실행해서 결과를 만들어내고, 그 다음 밖의 쿼리를 처리함.

써브쿼리를 잘 활용하면 엑셀로 처리가 어렵던 복잡한 처리를 할 수 있다! (+.+) 그게 뭔지 얼른 알려주소~

다

 

Where 에 들어가는 Subquery

형식 : where 필드명 in (subquery)

써브쿼리의 결과를 조건문으로 활용할 때 쓰임

 

 

Select 에 들어가는 Subquery

형식 : select 필드명, 필드명, (subquery) from .. 

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

 

 

From 에 들어가는 Subquery  (★가장 많이 사용되는 유형!★)

형식 : select 필드명, 필드명, (subquery) from .. 

내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용(내가 만든 select 문을 테이블처럼~~)

 

 

써브쿼리를 쉽게 만들려면 쪼개서 작업하는 것을 많이 연습해라. 이거랑 저거랑 따로 만들고 합치는 연습을 하면 됌

 

 

---

Subquery  연습해보기!

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

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

포인트가 평균보다 많은 사람들의 데이터를 추출해보자!

*참고: 평균 포인트는 5380점

 

작성방법 :  평균을 구하는 쿼리는 먼저 만들고 이 쿼리를 서브쿼리로 넣어줌!

 

SELECT * FROM point_users pu
where point > (select avg(point) FROM point_users

)

 

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

 

이씨성을 가진 유저의 포인트의 평균 : 1번처리

1번보다 큰 유저들의 데이터추출 : 2번처리

 

1번처리 :

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

2번처리 : 

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

)

 

 

이렇게도 풀수있다!! Subquery 안에 Subquery!

 

1번처리:

select avg(pu.point) from point_users pu  
WHERE user_id in (
	SELECT user_id from	users u WHERE name = '이**'
)

2번처리 :

SELECT * from point_users pu 
WHERE point > (
	select avg(pu.point) from point_users pu  
	WHERE user_id in (
	SELECT user_id from	users u WHERE name = '이**'
	)


)

 

 

 

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

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

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

안에 들어가는 서브쿼리를 1가지 값에 대해 구해놓고, 그걸 반복되는 값이 되게 이름지정해준뒤에 큰 쿼리안에 넣는다.

쪼매 어려움.

 

 

2.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 avg_likes
from checkins c
inner join courses c2 on c.course_id = c2.course_id

매우 헷갈려지기 때문에 탭(tab)을 활용해서 구분하기 쉽게 정리해주는 것이 매울 중요하다.

 

 

 

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

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

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

가 된다. 훨씬 쿼리 구조를 파악하기 좋아짐.