[MySQL] + rank, 서브쿼리

1. 고객별로 주문 건수와 총 주문 금액을 조회하는 SQL쿼리를 작성해주세요.

select c.customername, 
       count(1) OrderCount, 
       sum(TotalAmount) totalSpent
from customers c left join orders o on o.customerID=c.customerID
group by 1

1. 고객 이름과 국적은 Customer 테이블, totalamount는 Orders테이블 기대결과에 맞게 출력하려면 join 사용 필요

2. 주문한적이 없는 고객도 결과에 포함되어야 하기때문에 left join 사용

3. 고객 이름별 count와 Sum(totalAmount) 진행

2. 나라별로 총 주문 금액이 가장 높은 고객의 이름과 그 고객의 총 주문 금액을 조회하는 SQL 쿼리를 작성해주세요.

select customername,
       Country,
       count(1) OrderCount,
       sum(TotalAmount) totalSpent
from customers c left join orders o on o.customerID=c.customerID
group by 1, 2

1. 문제 1에서 작성한 쿼리에 국가를 넣어주고 고객 이름, 국가별 group by

select Country,
       customername,
       MAX(totalSpent) Top_spent
from
(
select customername,
       Country,
       count(1) OrderCount,
       sum(TotalAmount) totalSpent
from customers c left join orders o on o.customerID=c.customerID
group by 1, 2
) a
group by 1

2. max 사용하여 카테고리별 계산하려고 하였으나 기대결과 customername을 출력하지 못하게 됨.

select Country,
       CustomerName Top_Customer,
       TotalSpent Top_Spent
from
(
select Country,
       customername,
       count(1) OrderCount,
       sum(TotalAmount) totalSpent,
       RANK() OVER (PARTITION BY c.Country ORDER BY sum(TotalAmount) desc) "랭크"
from customers c left join orders o on o.customerID=c.customerID
group by 1, 2
) a
where 랭크=1

3. rank함수를 사용하여 서브쿼리 내 순위를 구하고 where 랭크 1만 선택하여 출력

참고) rank 함수 Alias 지정할때 rank로 하면 이미 사용된 예약어로 출력안되는 현상 발생.해결 방법: rnk 또는 ranking 같은 다른 별칭 사용

문제2

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

1. 우선 기대결과를 보면 Top_Earner, Top_Salary를 볼 수 있다 각 부서별 최고급여를 받는 사람이 누구인지, 급여는 얼마인지를 보여주는 컬럼으로 보인다.

2. 사람별로 부서를 보여주고 급여를 보여준 후 해당부서의 최고급여자, 최고급여를 보여주고 있다. 행렬의 연동이 필요하기에 join을 사용해야한다.

3. JOIN을 사용하기 전, EMPLOYEES 테이블과 엮을 각 부서의 최고급여자, 최고급여를 출력하는 서브쿼리를 작성해야한다.

select name, 
       department, 
       salary,
       rank() over (partition by Department order by salary desc) as rnk
from employees
where rnk=1

3-1 문제점 rnk=1만 지정해서 보여주고 싶었으나

이유는 필터링 순서에 있었다.필터링 순서 : from > where > group by > having > select > orderby

WHERE 절은 SELECT 절 실행 전에 필터링이 이루어지기 때문에 select에서 as지정해준 rnk를 사용할 수 없었던 것이다.

select name, 
       department, 
       salary,
       rank() over (partition by Department order by salary desc) as rnk
from employees

4. 서브쿼리 필터링 후 본쿼리에서 where 서브쿼리.rnk=1을 지정해주겠다.

select e.name,
       e.department,
       e.salary,
       a.name Top_Earner,
       a.salary Top_Salary
from Employees e 
left join(select name, 
       department, 
       salary,
       rank() over (partition by Department order by salary desc) as rnk
from employees) a 
on e.Department=a.Department
where a.rnk=1;

기대결과와 동일하게 출력된 것으로 확인된다.

2. 부서별로 평균 월급이 가장 높은 부서의 이름과 해당 부서의 평균 월급을 조회하는 SQL 쿼리를 작성해주세요.

select Department,
       avg(Salary) "평균월급"
from employees
group by 1
order by 2 desc
limit 1

기대결과와 동일한 값을 위한 limit

cf)

의문점) 기대결과와는 동일하게 나왔으나 IT, Finance의 평균월급은 동일하기에 '가장높은부서' IT, Finance로 두개일 것이다. 두개를 출력하려면 어떻게 해야할까? 제약사항에 있는 HAVING등을 사용하여 풀 수 있어야한다를 참고해보자

select Department,
       avg(Salary) "평균월급"
from employees
group by 1

이 쿼리에서; avg가 가장 높은 부서만을 출력하기 위해선 HAVING이 필요하다 다음은 CHATGPT의 답변이다

HAVING AVG(Salary) = 
(
    SELECT MAX(Avg_Salary)
    FROM (
        SELECT AVG(Salary) AS Avg_Salary
        FROM Employees
        GROUP BY Department
    ) AS subquery
)

AVG(SALARY)가 서브쿼리 내 MAX(AVG_SALARY) 인 결과만 도출 가능

taeung515 님의 블로그