[SQL] 5. 윈도우 함수(Window Functions)

1. 윈도우 함수 개념

• 윈도우 함수는 결과 집합 내에서 ‘현재 행을 기준으로 주변 행’을 참조해 계산
• OVER( [PARTITION BY …] [ORDER BY …] ) 절 필수

---

2. 기본 형태

함수명(컬럼_or_식) OVER (
  [PARTITION BY 분할컬럼…]
  [ORDER BY   정렬컬럼…]
)

• PARTITION BY: 그룹화 (GROUP BY와 유사하나, 결과행 수는 유지)
• ORDER BY: 윈도우 내 순서 지정
• 윈도우 프레임 지정 없이 기본값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

---

3. 순위 함수

3-1. ROW_NUMBER(): 순번 부여 (중복 없이 1,2,3…)

SELECT department_id,
       employee_id,
       salary,
       ROW_NUMBER() OVER (
         PARTITION BY department_id 
         ORDER BY salary DESC
       ) AS rn
FROM   employees;

3-2. RANK(): 동순위 건너뛰며 순위

SELECT department_id,
       employee_id,
       salary,
       RANK() OVER (
         PARTITION BY department_id 
         ORDER BY salary DESC
       ) AS rnk
FROM   employees;

3-3. DENSE_RANK(): 동순위라도 순위 건너뛰지 않음

SELECT department_id,
       employee_id,
       salary,
       RANK() OVER (
         PARTITION BY department_id 
         ORDER BY salary DESC
       ) AS rnk
FROM   employees;

---

4. 누적·이동 집계 함수

4-1. 누적 합계 (Cumulative SUM)

SELECT hire_date,
       salary,
       SUM(salary) OVER (
         ORDER BY hire_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cum_sum
FROM   employees
ORDER  BY hire_date;

4-2. 이동 합계 (Moving SUM): 최근 3행 합

SELECT hire_date,
       salary,
       SUM(salary) OVER (
         ORDER BY hire_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_sum_3
FROM   employees;

---

5. LAG / LEAD 함수

• LAG(col, offset, default): 이전 행 값 조회
• LEAD(col, offset, default): 다음 행 값 조회

SELECT employee_id,
       salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date)   AS prev_sal,
       LEAD(salary, 1, 0) OVER (ORDER BY hire_date)  AS next_sal
FROM   employees
ORDER  BY hire_date;

---

6. 연습 문제

1. 부서별로 급여 순위(rnk ≤ 3)인 직원만 조회하세요.
2. 입사일 오름차순으로 누적 직원 수를 계산하는 cnt 컬럼을 추가하세요.
3. 직원별로 전 직원 대비 자신의 연봉 차이(salary - avg_salary)를 계산하세요.
   • 힌트: AVG(salary) OVER () 사용