[내일 배움 캠프, SQL 달리기 반] Lv4. 가장 높은 월급을 받는 직원은?

1. 문제

 

 

 

 

[4-1] 풀이

SELECT Name,
       Department,
       Salary,
       if(Top_Salary<>1, 0 ,Name) Top_Earner,
       if(Top_Salary<>1, 0 ,Salary) Top_Salary
FROM
(
SELECT Name,
       Department,
       Salary,
       RANK() OVER(PARTITION BY Department ORDER BY Salary desc) Top_Salary
FROM employees
ORDER BY Name
) a

내 풀이에 대한 결과

• 문제를 푸는 과정에서 Top_Earner와 Top_Salary를 구하려면, Name, Department, Salary 컬럼이랑 같이 구할 수가 없다. 하지만 기대결과에서는 이 모든 컬럼이 다 같이 나와야 한다. (이 부분이 막혀서 더이상 풀지 못했다.)

 

 

 

 

[4-1] 정답

SELECT e.Name,
       e.Department,
       e.Salary,
       te.Name AS Top_Earner,
       te.Salary AS Top_Salary
FROM employees e
LEFT JOIN (
    SELECT Department, Name, Salary
    FROM (
        SELECT Name,
               Department,
               Salary,
               RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rnk
        FROM employees
    ) sub
    WHERE rnk = 1
) te
ON e.Department = te.Department;

• 내가 몰랐던 부분 : SELECT 문으로 조회한 결과를 다른 쿼리와 JOIN하는 것이 가능하다는 것을 몰랐다.
• 위에서 te는 서브쿼리의 결과를 이용한 임시 테이블 역할을 한다.

 

 

 

[4-1] 정리 : CTE (Common Table Expression)

WITH Top_Earners AS (
    SELECT Name, Department, Salary
    FROM (
        SELECT Name,
               Department,
               Salary,
               RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rnk
        FROM employees
    ) sub
    WHERE rnk = 1
)
SELECT e.Name,
       e.Department,
       e.Salary,
       te.Name AS Top_Earner,
       te.Salary AS Top_Salary
FROM employees e
LEFT JOIN Top_Earners te
ON e.Department = te.Department;

- CTE (Common Table Expression) : 공동 테이블 표현식이라고도 한다. 쿼리 내에서 일시적으로 사용할 수 있는 결과 집합을 정의하는 방법이다.

 

CTE의 장점

1. 가동성 증가
   • 긴 서브쿼리를 정리하여 더 읽기 쉬운 코드 작성 가능
   • 복잡한 쿼리를 단계적으로 분리 가능
2. 코드 재사용 가능
   
   • CTE를 사용하면 같은 서브쿼리를 여러 번 쓰지 않고 한 번 정의해서 여러 번 참조 가능

 

 

 

 

---

 

 

 

[4-2] 풀이

SELECT Department,
       Avg_salary
FROM
(
	SELECT Department,
		   AVG(Salary) Avg_salary,
		   RANK() OVER(ORDER BY AVG(Salary) DESC) rk
	FROM employees
	GROUP BY Department
) a
WHERE rk=1

내 풀이에 대한 결과

• 값이 동일한 경우 하나의 값만 도출할 수 있는 방법에 대해 몰랐다.

 

 

 

[4-2] 정답

SELECT Department,
       Avg_salary DESC
FROM
(
	SELECT Department,
		   AVG(Salary) Avg_salary,
	FROM employees
	GROUP BY Department
) a
WHERE rk=1
LIMIT 1

• RANK 함수를 사용하지 않았다. 단순히 연봉 평균이 가장 높은 부서를 구하는 것이기 때문에 내림차순 정렬을 사용하였다.
• LIMIT 절을 사용하여, 반환되는 행 수를 1개로 제한하였다.

 

 

 

 

[4-1] 정리 : LIMIT

- 정의 : SQL에서 결과 집합의 반환되는 행 수를 제한하는 데 사용되는 절이다. 주로 데이터베이스에서 특정 개수의 행만 조회하고 싶을 때 사용한다.

 

- 구조 : 

SELECT * FROM 테이블명
LIMIT 개수 OFFSET 시작위치;

• 개수 : 반환할 최대 행 수를 지정함
• OFFSET 시작위치 : 몇 번째 행부터 가져올지를 지정함
  • OFFSET 사용 안할 시 : 첫번째 행부터 지정한다.
  • OFFSET은 0부터 시작한다. (OFFSET 10 : 11번째 행을 의미)