서브쿼리(상관/FROM), IN, EXISTS 함수, CTE(WITH)

 

- Subquery

• Scalar Subquery 스칼라 서브쿼리
  • 서브쿼리 안의 결과는 값 1개여야함

# 미니 실습 4 
select enrollment_id, final_price 
from basic.enrollments 
where final_price > (select avg(final_price) from basic.enrollments) 
order by final_price desc;

• 리스트 서브쿼리: IN (...)
  • IN (...) 안쪽은 한 컬럼짜리 리스트가 나오면 성공

# 미니 실습 5
select 
	enrollment_id, course_id 
from basic.enrollments 
where course_id in 
(	select course_id 
	from basic.courses 
	where level = 'beginner'
)
order by enrollment_id;

• EXISTS / NOT EXISTS 
  • NOT EXISTS 대신 LEFT JOIN+NULL 사용할 수 있지만
  • 직관적으로 이해하기 쉽다는 점에서 NOT EXISTS 선호함

# 수강신청을 한번이라도 한 학생
select s.student_id, s.student_name 
from basic.students s
where exists (
select 1 
from basic.enrollments e 
where e.student_id = s.student_id
)
order by s.student_id; 

# 수강신청을 한번도 안한 학생
SELECT
  s.student_id,
  s.student_name
FROM basic.students s
WHERE not EXISTS (
SELECT 1
FROM basic.enrollments e
WHERE e.student_id = s.student_id
)
ORDER BY s.student_id;

# LEFT JOIN + IS NULL 사용
# 수강신청을 한번도 안한 학생 
select s.student_id, s.student_name 
from basic.students s 
left join basic.enrollments e 
on s.student_id = e.student_id 
where e.enrollment_id is null

• 상관 서브쿼리 (Correlated Subquery)

select 
	s.student_id, 
	s.student_name, 
	( 	select count(*) 
		from basic.enrollments e 
		where e.student_id = s.student_id
	) as enroll_cnt
from basic.students s 
order by s.student_id;

 

 

- FROM 서브쿼리 (파생 테이블)

• 그대로 JOIN할 때 행의 뻥튀기가 일어나지 않도록 
  • 파생테이블 자체를 미리 바깥 테이블과 행 단위 (grain)을 맞춰놓음
• 파생 테이블에 별칭 무조건 붙이기

# 미니 실습 7
select
	s.student_id, s.student_name,
	ifnull(enr.enroll_cnt,0) as enroll_cnt
from basic.students s 
left join (
	select
		e.student_id, count(*) as enroll_cnt
	from basic.enrollments e 
	group by e.student_id 
) as enr 
on s.student_id = enr.student_id
order by student_id

 

 

- CTE (WITH) 

• Common Table Expression 이름이 있는 임시 결과
• 단일 statement 범위에서만 존재 (한 줄 띄면 사용 못함)
• 그 statement 안에서는 여러 번 사용 가능
• 임시적으로 사용할 수 있는 테이블이라고 생각하면 됨

with student_enroll_cnt as (
select student_id, count(*) as cnt
from basic.enrollments
group by student_id
)
select
	s.student_id, s.student_name,
	ifnull(sec.cnt,0) as cnt,
	case when ifnull(sec.cnt,0) = 0 then 'no_enroll'
		when ifnull(sec.cnt,0) >= 1 then 'has_enroll'
		else null end as enroll_label
from basic.students s
left join student_enroll_cnt sec 
on s.student_id = sec.student_id

# CTE 두개 사용 문법
with
enroll_by_student as (
), 
coupon_by_student as (
)
select * 
from basic.students s 
left join enroll_by_student e 
on s.student_id = e.student_id 
left join coupon_by_student c 
on s.student_id = c.student_id

 

 

- SQL 사전 퀘스트 달리기반

 

- CTE 두개 중첩

• Lv4. 단골 고객님찾기 2번 문제 저번에 풀다가 포기함
• https://myun0506.tistory.com/17

• BUT!!!! 오늘은 성공했지롱 
  • 정답 쿼리에선 서브쿼리 두번 사용했지만 나는 CTE 두번 사용해서 성공함!!! 

낄낄낄~~~~~~~

# 내가 직접 작성한 쿼리
with ranked as (
select  
	c.country, 
	c.customername, 
	sum(o.totalamount) as top_spent
from customers2 c 
left join orders2 o 
on c.customerid = o.customerid 
group by c.country, c.customername
),
final_ranked as (
select country, customername, top_spent, 
 	   row_number() over (
 	   		partition by country order by top_spent desc)
 	   		as rnk 
from ranked
order by country, customername 
)
select country, customername, top_spent 
from final_ranked 
where rnk = 1

• 내 쿼리에서 final_ranked CTE 안에 ORDER BY가 들어가 있는데,
  • CTE 내부의 ORDER BY는 최종 결과에 영향을 주지 않거나 엔진에 따라 에러를 낼 수 있음.
  • ORDER BY는 가장 마지막 SELECT 문 다음에 쓰는 것이 표준!!!

# 정답 쿼리 -- 오류가 너무 많이 나고 복잡 BAD!!!
SELECT 
    c.Country,
    c.CustomerName AS Top_Customer,
    SUM(o.TotalAmount) AS Top_Spent
FROM 
    Customers c
JOIN 
    Orders o ON c.CustomerID = o.CustomerID
GROUP BY 
    c.Country, c.CustomerName
HAVING 
    SUM(o.TotalAmount) = (
        SELECT 
            MAX(SumSpent)
        FROM 
            (SELECT 
                 SUM(o2.TotalAmount) AS SumSpent
             FROM 
                 Customers c2
             JOIN 
                 Orders o2 ON c2.CustomerID = o2.CustomerID
             WHERE 
                 c2.Country = c.Country
             GROUP BY 
                 c2.CustomerID) AS Subquery
    );

 

ㄴ 20260102 TIL

 

https://myun0506.tistory.com/22

20260102 TIL