[SQL] 연습 문제 모음3

🔒 문제

다음과 같은 테이블 구조를 고려합니다.

Employees 테이블

employee_id	name	department_id	salary	hire_date
101	John	1	5000	2020-01-10
102	Alice	2	7000	2019-03-25
103	Bob	1	6000	2018-07-11
104	Charlie	3	4500	2021-08-19
105	David	2	8000	2017-12-05
106	Eva	1	7500	2021-06-15
107	Frank	3	4700	2019-09-10
Departments 테이블

department_id	department_name
1	HR
2	Engineering
3	Sales

질문:
1. 각 부서별로 가장 높은 급여를 받는 직원과 그 급여의 비율을 출력하세요. (전체 급여의 합에 대해 비율을 계산)

2. 모든 직원들 중에서 연봉이 상위 10%에 해당하는 직원들의 이름, 급여, 입사일을 출력하세요.

3. 각 부서에서 입사일이 가장 늦은 직원의 이름과 급여를 출력하세요. (서브쿼리 사용)

4.각 부서별로 급여가 평균 급여보다 높은 직원들의 이름과 급여를 출력하되, 평균 급여보다 높은 직원 중에서 급여가 높은 순서대로 출력하세요. (윈도우 함수 사용)

 

🔓 내가 푼 답

1.
WITH high_salary AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM Employees
    GROUP BY department_id
),
total_salary AS (
    SELECT SUM(salary) AS total_salary
    FROM Employees
)
SELECT e.name, e.salary, 
       (e.salary / t.total_salary) * 100 AS salary_percentage
FROM Employees e
JOIN high_salary h ON e.department_id = h.department_id
JOIN total_salary t
WHERE e.salary = h.max_salary;

2.
-- 이 방법도 계산이 되긴하는데 행마다 전체 급여합을 계산해서 비효율적
SELECT name, salary, hire_date
FROM Employees
WHERE (salary / (SELECT SUM(salary) FROM Employees)) * 100 <= 10;

-- 윈도우 함수를 사용해서 전체 급여합을 한번만 계산
SELECT name, salary, hire_date
FROM (
    SELECT name, salary, hire_date, 
           (salary / SUM(salary) OVER ()) * 100 AS salary_percentage
    FROM Employees
) AS subquery
WHERE salary_percentage <= 10;

3. 
-- 서브쿼리 사용
SELECT e.name, e.salary, e.department_id
FROM Employees e
WHERE e.hire_date = (
    SELECT MAX(hire_date)
    FROM Employees
    WHERE department_id = e.department_id
);

-- 윈도우 함수 사용
SELECT name, salary, department_id
FROM (
    SELECT name, salary, department_id, hire_date,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date DESC) AS rank
    FROM Employees
) AS ranked_employees
WHERE rank = 1;

4.
SELECT sub.name, sub.salary, sub.department_id
FROM (
    SELECT name, salary, department_id, 
           AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
    FROM Employees
) AS sub
WHERE sub.salary > sub.avg_salary
ORDER BY sub.salary DESC;

✅ 사용 문법 정리

윈도우 함수

윈도우 함수(Window Function)는 SQL에서 데이터의 특정 범위 내에서 계산을 수행하는 함수로, 행을 그룹화하지 않고 전체 결과에 대해 계산을 적용합니다. 결과적으로 각 행에 대해 계산된 값을 표시할 수 있습니다. 주로 순위 계산, 이전/다음 값 참조, 누적 합계 등에서 유용하게 사용됩니다.

윈도우_함수() OVER (PARTITION BY 열명 ORDER BY 열명)

 

주요 윈도우 함수 종류

1. ROW_NUMBER():
   • 각 행에 대해 순차적인 번호를 매깁니다.
   • ORDER BY와 함께 사용하여 순서대로 번호를 부여합니다.
    

   SELECT column1, ROW_NUMBER() OVER (ORDER BY column1) AS row_num FROM table;

    
2. RANK():
   • 동일한 값이 있을 경우 동일한 순위를 부여하지만, 순위 번호가 건너뛰어집니다.
   • 예: 1, 1, 3 (두 번째 값이 중복되면 3번째 순위는 건너뜁니다)
    

   SELECT column1, RANK() OVER (ORDER BY column1) AS rank FROM table;
3. DENSE_RANK():
   • RANK()와 비슷하지만, 중복된 값이 있으면 순위 번호를 건너뛰지 않고 연속적으로 부여합니다.
   • 예: 1, 1, 2 (두 번째 값이 중복되더라도 순위 번호는 2로 계속됩니다)
    

   SELECT column1, DENSE_RANK() OVER (ORDER BY column1) AS dense_rank FROM table;
4. NTILE(n):
   • 결과를 n개의 균등한 그룹으로 나누고 각 그룹에 번호를 매깁니다.
   • 예: NTILE(4)는 데이터를 4개의 그룹으로 나누고, 각 그룹에 1, 2, 3, 4라는 번호를 부여합니다.
    

   SELECT column1, NTILE(4) OVER (ORDER BY column1) AS quartile FROM table;
5. LEAD():
   • 현재 행에서 지정된 행 수 만큼 뒤의 값을 가져옵니다.
   • 주로 다음 행의 값을 참조하는 데 사용됩니다.
    

   SELECT column1, LEAD(column1, 1) OVER (ORDER BY column1) AS next_value FROM table;
6. LAG():
   • LEAD()의 반대로, 현재 행에서 지정된 행 수 만큼 앞의 값을 가져옵니다.
   • 주로 이전 행의 값을 참조하는 데 사용됩니다.
    

   SELECT column1, LAG(column1, 1) OVER (ORDER BY column1) AS previous_value FROM table;
7. FIRST_VALUE():
   • 파티션 내에서 첫 번째 값을 반환합니다.
    

   SELECT column1, FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column1) AS first_value FROM table;
8. LAST_VALUE():
   • 파티션 내에서 마지막 값을 반환합니다.
    

   SELECT column1, LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value FROM table;
9. SUM():
   • 윈도우 함수로 사용할 수 있는 집계 함수로, 지정된 범위 내에서 합을 구합니다.
    

   SELECT column1, SUM(column2) OVER (PARTITION BY column1) AS total_sum FROM table;
10. AVG():
    • 윈도우 함수로 사용할 수 있는 집계 함수로, 지정된 범위 내에서 평균을 구합니다.
     

    SELECT column1, AVG(column2) OVER (PARTITION BY column1) AS average_value FROM table;
11. MIN(), MAX():
    • MIN()과 MAX()는 윈도우 함수로 사용할 수 있으며, 지정된 범위 내에서 최소값과 최대값을 구합니다.
     

    SELECT column1, MIN(column2) OVER (PARTITION BY column1) AS min_value FROM table;
12. COUNT():
    • 윈도우 함수로 사용할 수 있는 집계 함수로, 특정 범위 내에서 데이터의 개수를 구합니다.
     

    SELECT column1, COUNT(*) OVER (PARTITION BY column1) AS total_count FROM table;