Subquery, WITH

자주 쓰이는 서브 쿼리 유형

1. WHERE절에 들어가는 서브 쿼리
   • WHERE 속성명 IN (subquery)
   • 서브쿼리의 결과를 조건으로 활용하고 싶을 때
2. SELECT절에 들어가는 서브쿼리
   • SELECT 속성명1, 속성명2, (subquery) FROM ...
   • 기본 테이블에 함께 보고 싶은 통계 데이터를 붙이고 싶을 때
3. FROM절에 들어가는 서브쿼리
   • FROM 테이블 a INNER JOIN (subquery) b ON a.속성명 = b.속성명;
   • 내가 만든 SELECT문을 한 테이블처럼 다른 테이블과 JOIN하고 싶을 때

---

• 1번 예시

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

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

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

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

 

• 2번 예시

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

- checkins 테이블에 과목명별 평균 likes 수 필드 우측에 붙이기

select checkin_id, c2.title, user_id, likes, (
	SELECT round(avg(likes),1) from checkins
	where course_id = c.course_id 
	) course_avg 
from checkins c 
inner join courses c2 on c.course_id = c2.course_id

 

• 3번 예시

- course_id 별 checkins 개수에 전체 인원을 붙이기

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

 

---

WITH: 맨 위에 서브쿼리 별칭을 붙여 사용 가능

WITH 별칭1 AS (subquery1) [, 별칭2 AS (subquery2)]

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