아직도 상관 서브 쿼리가 이해가 안 돼서
오늘은 하루 종일 이것을 이해해 보려고 노력 중이다.
Lv4. 단골 고객
SELECT
c.country,
c.customerName AS top_customer,
SUM(o.totalAmount) AS top_spent
FROM
p4_customers_table c
JOIN
p4_orders_table o ON c.customerID = o.customerID
GROUP BY
c.country, c.customerName
# 여기서부터 문제
# 그룹화 되어 있는 것들 중에 조건 부여
# 조건 = 각 나라별 최고 금액을 구해라
HAVING
SUM(o.totalAmount) = (
SELECT
MAX(sum_spent)
FROM
(
SELECT
SUM(o2.totalAmount) AS sum_spent
FROM
p4_customers_table c2
JOIN
p4_orders_table o2 ON c2.customerID = o2.customerID
WHERE
c2.country = c.country
GROUP BY
c2.customerID
) AS subquery
);
# 바깥쿼리 Select절에 있는 SUM(o.totalAmount)는
# 전체를 계산한 것중 가장 큰 금액이여야 한다
# 그래서 다시 SUM(o2.totalAmount) 이걸 계산하고
# 그에 필요한 테이블을 불러와서 join on을 사용
# Where절에 c2.country = c.country
🔵 1단계
바깥 쿼리는 고객의 나라, 이름, 총 주문 금액을 구하는 중이다.
→ GROUP BY를 통해 고객별로 묶고, SUM(amount)로 금액 합산함.
🔵 2단계
이 중에서 조건은 “각 나라별로 가장 많이 주문한 고객만 남겨야” 한다.
→ 즉, 한 나라에 여러 명이 있어도 그 중 최고 고객만 뽑자!
🔵 3단계
이 조건을 HAVING절에서 처리해야 한다.
→ WHERE는 그룹핑 전, HAVING은 그룹핑 후 필터니까!
🔵 4단계
HAVING에서는 나라별 최대 주문 금액을 알아야 하므로
→ 서브쿼리를 써서 다시 나라별 고객의 주문 총액을 계산한다.
🔵 5단계
서브쿼리는 “지금 보고 있는 나라에 속한 고객들만 계산”해야 하므로
→ WHERE u2.country = u.country 조건을 넣는다.
🔵 6단계
이 WHERE 조건이 핵심이다!
→ 바깥 쿼리에서 보고 있는 country와
→ 서브쿼리 안의 country가 같을 때만 계산하도록 필터링함
🔵 7단계
바깥 쿼리가 한 행(예: USA, Alice, 450)을 처리할 때
→ HAVING절이 실행되며
→ 서브쿼리는 USA 사람들의 총 주문 금액만 보고, 그중 최댓값을 계산한다
🔵 8단계
서브쿼리에서 구한 USA 내 최고 금액이
→ 바깥에서 보고 있는 사람의 SUM(amount)와 같다면
→ 그 고객은 **“그 나라의 최고 소비자”**이므로 출력 대상이다 ✅
🔵 9단계
그 결과는 SELECT절로 들어가
→ country, name, SUM(amount)로 정리돼 출력된다
🔵 10단계
바깥 쿼리는 다음 행으로 넘어가서 (예: UK, Bob)
→ 같은 과정을 반복하며
→ 각 나라별 최고 금액 고객만 최종 결과로 출력된다
다시 3단계로 정리
1. 바깥쿼리에서 나라, 고객, 총 주문금액 쿼리 작성
2. HAVING절에서 MAX사용 최대 주문고객 구하는 쿼리 작성
3. HAVING절 서브쿼리에서 계산중인 나라가 바깥쿼리의 나라와 같으면 출력
where c2.country = c.country (바깥쿼리와 서브쿼리를 연결하는 키, join on의 공통 키값과 비슷한 개념)
* 만약 where절 country를 뺀다면 전체에서 가장 높은 금액만 출력이 될 것임
* where절에서 바깥, 서브를 이어주는 country를 하는 이유는 둘을 이어주어 "나라별" 최고금액을 구하기 위함
일반적인 서브쿼리는 필요한 정보들을 테이블화 시켜서 메인쿼리에서 참조함
상관 서브쿼리는 메인쿼리 값을 상관 서브쿼리에 하나 씩 반복적으로 대입해서 결과를 도출함
그래서 데이터양이 많으면 성능저하를 일으킬 수 있기때문에 피할 수 있다면 피해야한다 .
Lv4. 가장 높은 월급을 받는 직원은?
각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.
# 이건 정답쿼리
select e.name, e.Department, e.Salary , e2.Name as top_earner, e2.Salary as Top_Salary
from Employees e
join Employees e2 on e.Department = e2.Department
where e2.Salary = (
select MAX(e3.Salary)
from Employees e3
where e3.Department = e.Department
)
# 이건 left join 사용 쿼리
# 이렇게 해도 되겠는데 ? 라는 생각에서 나옴
select e3.Name, e3.Department,e3.Salary, t.name, t.Salary
from employees e3
left join
(select e.name, e.Department, e.Salary
from Employees e
where e.Salary = (
select max(salary)
from employees e2
where e2.Department = e.Department
)) t on t.Department = e3.Department