[mysql] having절, 서브 쿼리 실습

서브 쿼리 실습

실습용 테이블

문제 1. 각 직원이 속한 부서에서 가장 높은 월급을 받는 직원들만 포함된 결과를 조회하는 SQL 쿼리를 작성해주세요. (출력 결과에는 직원의 이름, 부서, 그리고 월급이 포함되어야 합니다.)

//나의 코드
select name, department, salary
from (
select name, department, salary,
	   rank() over (partition by department order by salary desc) as ranking
from employees5
) sub
where ranking=1


//정답지 코드
SELECT 
    e.Name,
    e.Department,
    e.Salary
FROM 
    Employees e
WHERE 
    e.Salary = (
        SELECT 
            MAX(Salary)
        FROM 
            Employees e2
        WHERE 
            e2.Department = e.Department
    );

 

문제를 보자마자 rank 함수를 사용하면 되겠다고 생각했다.

partition by에 department를 줘서 부서별로 순위를 계산하도록 했고, order by에 salary를 줘서 월급에 대한 순위를 가져오고자 했다.

이 코드도 정확한 결과를 출력하지만 정답지처럼 where 절에 서브 쿼리를 넣을 생각은 하지 못했고, having절 실습을 하다보면 정답지 코드처럼 서브쿼리에서 max값을 반환하고 메인 쿼리에서 비교하여 해당 값만 출력하는 로직을 자주 사용해야 하기 때문에 연습하고자 블로그에 남기게 됐다.

 

---

 

 

having절 실습

문제 2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과, 그 고객의 총 주문 금액을 조회해주세요.

Order 테이블

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2024-01-01	150
102	2	2024-01-03	200
103	1	2024-01-04	300
104	3	2024-01-04	50
105	2	2024-01-05	80
106	4	2024-01-06	400

 

Customers 테이블

CustomerID	CustomerName	Country
1	Alice	USA
2	Bob	UK
3	Charlie	USA
4	David	Canada

 

//1차 시도
select Country, Top_Customer, max(Top_Spent)
from
(
select c.Country as Country, c.CustomerName as Top_Customer, 
sum(o.TotalAmount) as Top_Spent
from orders o inner join customers c on o.CustomerID = c.CustomerID 
group by 1, 2
) a
group by 1


//에러 메세지
SQL Error [1055] [42000]: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.Top_Customer' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

a 서브쿼리는 국가-고객 이름 별로 총 주문 금액을 출력한다.

이때 결과값은 제대로 나오되 한 국가에 여러 사람의 데이터가 출력되기 때문에, 국가별로 한 명의 데이터만을 출력하도록 해야 했다.

그래서 나는 단순히 group by(Country)로 그룹화해서 각각 하나의 데이터씩만 출력하면 되겠다! 라고 생각했다.

group by는 그룹화시키는 함수이기 때문에 Country가 Korean이라고 가정하면, 나머지 Top_Customer라는 고객의 이름 컬럼에는 어느 레코드의 값도 들어갈 수가 없다.

즉, group by를 사용하는 경우에는 select 문의 모든 컬럼은 집계 함수 안에 포함되어 있거나, group by에 포함되어 있어야 하는 것이다.

 

이에 정답지를 확인했을 때 정답지는 having절을 사용했는데, 코드가 꽤나 길고 서브쿼리도 여러 번 사용해서 이해하기 힘들었다.

 

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

 

Subquery라는 이름을 가진 서브 쿼리는 외부 쿼리의 국가와 서브 쿼리 내부의 국가가 같을 때, 고객마다 총 주문 금액을 반환하고, 그 중 가장 큰 값을 having 절 안에 있는 SUM 함수와 비교한다.

즉, 외부 쿼리와 서브 쿼리가 동일한 국가를 두고 비교하게 둔 뒤, 외부 쿼리는 서브 쿼리에서 보내주는 가장 큰 주문 금액을 가진 고객의 데이터를 받아 그대로 출력하는 코드이다. 

그렇게 해서 각 국가마다 가장 큰 주문 금액을 가진 사람을 한 명씩 출력할 수 있는 것이다.

위와 같은 방법 대신 rank() 함수를 사용한다면 어떻게 구현할 수 있을까?

 

select Country, Top_Customer, Top_Spent
from
(
	select c.Country as Country, 
		   c.CustomerName as Top_Customer,
		   sum(o.TotalAmount) as Top_Spent,
		   rank() over (partition by c.Country order by sum(o.TotalAmount) desc) as ranking
	from customers c inner join orders o on c.CustomerID = o.CustomerID
	group by c.Country, c.CustomerName
) TotalAmount_of_people
where ranking<2
order by 2

 

간단히 설명하자면 TotalAmount_of_people 서브 쿼리에서 국가, 손님 이름, 순위가 매겨진 총 주문 금액을 반환하고, 외부 쿼리는 where 절을 통해 순위가 가장 높은 데이터만 출력하는 것이다.

 

 

문제 3. 각 직원의 이름, 부서, 월급, 그리고 그 직원이 속한 부서에서 가장 높은 월급을 받고 있는 직원의 이름과 월급을 조회하는 SQL 쿼리를 작성해주세요.

employees 테이블

EmployeeID	Name	Department	Salary	ManagerID
1	Alice	HR	70000	NULL
2	Bob	IT	90000	1
3	Charlie	IT	80000	2
4	David	IT	85000	2
5	Eve	HR	75000	1
6	Frank	Finance	95000	NULL
7	Grace	Finance	80000	6
8	Heidi	IT	95000	2

select e1.name, e1.department, e1.salary, e2.name as Top_Earner, e2.salary as Top_Salary
from employees e1 join employees e2 on e1.department = e2.department
having e2.salary = (
	select max(salary)
	from (
		select name, salary
		from employees e3
		where e1.department = e3.department
	) a
)
order by 1

 

문제 1, 2번 정답지와 유사한 형식이다.

a 쿼리 안에서 부서별 이름과 월급을 반환하고, having절에서 부서별 가장 큰 월급을 e2.salary와 비교하여 같을 시에만 출력하는 코드이다.

 

이 문제는 employees 테이블끼리 조인해서 기존 테이블에 같은 부서의 다른 사람의 정보 컬럼을 추가해줘야 했기 때문에, 정답지 코드를 보기 전까지는 많이 헤맸던 문제이다.

 

 

문제 4. 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요. (문제 3번과 동일한 테이블 사용)

//1차 시도
select Department, avg(Salary) as avg_Salary
from employees
group by Department
order by 1 desc, 2 desc limit 1


//2차 시도
select department, avg(salary) as max_avg
from employees
group by department
having avg(salary) = (
	select max(avg_salary)
	from (
		select avg(salary) as avg_salary
		from employees
		group by department
	) a
)

 

1차 시도는 having절 없이 평균 월급 순으로 정렬 후 하나의 행만 출력하도록 한 쿼리이다.

여기서 문제는 평균 월급이 가장 높은 부서가 하나가 아닌 경우, 첫 번째 행을 제외한 나머지 부서가 잘려버린다는 것이다.

그래서 2차 시도는 having절을 사용해 제대로된 결과물을 출력했다.

 

이렇게 해서 몇 가지 문제들로 having절을 실습해봤다.

having절과 where절을 비교하면 아래와 같다.

having	집계 함수 사용 O	grouping 후의 필터링
where	집계 함수 사용 X	grouping 전의 필터링