SQL 윈도우 함수(Window Function)

by

SQL 윈도우 함수(Window Function) 는 데이터베이스에서 행(row) 단위로 연산을 수행하면서도, 기존 행(row)들을 그대로 유지하는 함수입니다.
GROUP BY와 달리 개별 행을 제거하지 않고 그룹 내에서 연산을 수행할 수 있습니다.

🏗 1. 윈도우 함수의 특징

✅ 기존 행을 유지하면서 그룹별 연산 수행

  • GROUP BY를 사용하면 데이터가 그룹별로 묶이면서 개별 행이 사라지지만, 윈도우 함수는 개별 행을 유지한 채 연산을 수행합니다.

✅ OVER() 절을 사용하여 그룹을 정의

  • OVER(PARTITION BY column ORDER BY column) 형식으로 윈도우(창, 그룹) 를 지정합니다.
  • PARTITION BY는 그룹화 기준을 정의하고, ORDER BY는 정렬 순서를 지정합니다.

✅ 대표적인 활용 예시

  • 순위 매기기 (RANK, DENSE_RANK, ROW_NUMBER)
  • 누적 합계, 누적 비율 (SUM, AVG, CUME_DIST)
  • 이전/다음 행 참조 (LAG, LEAD)

📊 2. 윈도우 함수 vs 그룹 함수 차이점

기능윈도우 함수그룹 함수 (GROUP BY)
데이터 유지개별 행 유지개별 행이 제거됨
연산 적용 방식각 행에 대해 적용그룹 전체에 대해 하나의 값 반환
대표적인 함수ROW_NUMBER, RANK, CUME_DIST, NTILE, SUM() OVER()SUM(), AVG(), COUNT()
예제SUM(salary) OVER (PARTITION BY department_id)SUM(salary) GROUP BY department_id

🛠 3. 윈도우 함수의 기본 구조

윈도우 함수() OVER (
    PARTITION BY 그룹화 기준
    ORDER BY 정렬 기준
)

✅ PARTITION BY (선택 사항)

  • 그룹을 나누는 기준 (부서별, 지역별 등)
  • 생략하면 전체 데이터를 하나의 그룹으로 간주

✅ ORDER BY (선택 사항)

  • 계산 순서를 지정 (급여 순, 날짜 순 등)

🔍 4. 대표적인 윈도우 함수 종류

📌 (1) 순위 관련 함수

함수설명반환 값
ROW_NUMBER()각 행에 고유한 순위를 부여1, 2, 3, …
RANK()동일 값에는 같은 순위, 이후 순위 건너뜀1, 2, 2, 4, …
DENSE_RANK()동일 값에는 같은 순위, 이후 순위 건너뛰지 않음1, 2, 2, 3, …
NTILE(n)데이터를 n개 그룹으로 나눔1 ~ n

📌 예제

SELECT 
    employee_id, 
    department_id, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
    NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

📌 (2) 집계 함수

함수설명
SUM()그룹 내 누적 합계
AVG()그룹 내 평균 값
COUNT()그룹 내 행 개수
MIN()그룹 내 최소값
MAX()그룹 내 최대값

📌 예제

SELECT 
    employee_id, 
    department_id, 
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS total_salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

📌 (3) 누적 비율 함수

함수설명
CUME_DIST()누적 분포 계산
PERCENT_RANK()백분율 순위 계산
RATIO_TO_REPORT()그룹 내 전체 합 대비 개별 비율

📌 예제

SELECT 
    employee_id, 
    department_id, 
    salary,
    CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary DESC) AS cum_dist,
    RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS salary_ratio
FROM employees;

📌 (4) 이전/다음 행 참조 함수

함수설명
LAG()이전 행의 값 가져오기
LEAD()다음 행의 값 가져오기
FIRST_VALUE()그룹 내 첫 번째 값 반환
LAST_VALUE()그룹 내 마지막 값 반환
NTH_VALUE(n)그룹 내 n번째 값 반환

📌 예제

SELECT 
    employee_id, 
    department_id, 
    salary,
    LAG(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS prev_salary,
    LEAD(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS next_salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS first_salary
FROM employees;

📝 5. 정리

함수 유형주요 함수설명
순위 함수ROW_NUMBER, RANK, DENSE_RANK, NTILE행별 순위 계산
집계 함수SUM, AVG, COUNT, MIN, MAX그룹 내 합계, 평균 계산
비율 함수CUME_DIST, PERCENT_RANK, RATIO_TO_REPORT누적 비율, 백분율 순위
이전/다음 행 함수LAG, LEAD, FIRST_VALUE, LAST_VALUE이전/다음 행 참조

🚀 6. 윈도우 함수 활용 예시

✅ 1) 부서별 급여 합산하면서 개별 급여도 유지

SELECT 
    employee_id, 
    department_id, 
    salary, 
    SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM employees;

✔️ 개별 급여를 유지한 채 부서별 총 급여를 표시

✅ 2) 상위 20% 급여자 조회 (CUME_DIST)

SELECT *
FROM (
    SELECT 
        employee_id, 
        salary, 
        CUME_DIST() OVER (ORDER BY salary DESC) AS cum_dist
    FROM employees
) subquery
WHERE cum_dist <= 0.2;

✔️ 급여 기준 상위 20% 직원 조회

✅ 3) 직원 성과 평가 그룹 나누기 (NTILE)

SELECT 
    employee_id, 
    performance_score,
    NTILE(3) OVER (ORDER BY performance_score DESC) AS perf_group
FROM employees;

✔️ 직원들을 3개의 성과 그룹으로 분류

🎯 7. 결론

  • 윈도우 함수는 개별 행을 유지하면서 연산을 수행하는 강력한 SQL 기능
  • PARTITION BY를 사용하여 그룹화, ORDER BY로 정렬 기준 지정
  • 순위 계산, 누적 합계, 이전/다음 행 참조 등 다양한 활용 가능
  • 데이터 분석, 보고서 작성, 성과 평가 등에서 필수적인 기능

윈도우 함수 13개 상세 정리

함수명설명반환 값PARTITION BY 사용 가능ORDER BY 필수 여부
ROW_NUMBER()정렬된 행에 대해 순차적인 번호를 부여1, 2, 3, …OO
RANK()동일한 값에 동일한 순위, 이후 순위 건너뜀1, 2, 2, 4, …OO
DENSE_RANK()동일한 값에 동일한 순위, 이후 순위 건너뛰지 않음1, 2, 2, 3, …OO
PERCENT_RANK()(RANK – 1) / (전체 행 수 – 1)로 백분율 순위0.0 ~ 1.0OO
LAG()이전 행의 값 가져오기이전 행 값 또는 NULLOX
LEAD()다음 행의 값 가져오기다음 행 값 또는 NULLOX
FIRST_VALUE()파티션 내 첫 번째 값 반환첫 번째 값OO
LAST_VALUE()파티션 내 마지막 값 반환마지막 값OO
NTH_VALUE()특정 번째(n) 값 반환n번째 값OO
MEDIAN()중간 값 반환 (일부 DBMS에서 지원)중간값OO
함수명설명반환 값PARTITION BY 사용 여부ORDER BY 필수 여부주요 활용
CUME_DIST()특정 값이 그룹 내에서 차지하는 누적 비율0 < 값 ≤ 1.0OO누적 분포 계산
RATIO_TO_REPORT()특정 값이 전체 합계에서 차지하는 비율0 ≤ 값 ≤ 1.0OX전체 대비 특정 행 비율 계산
NTILE(n)데이터를 n개의 그룹으로 나누어 그룹 번호를 반환1 ~ n 값OO데이터 그룹화

1. ROW_NUMBER()

설명

  • 각 행에 고유한 번호를 부여 (ORDER BY 기준)
  • 중복값이 있어도 순차적인 번호를 부여함

SQL 예제

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

결과

employee_iddepartment_idsalaryrow_num
1011090001
1021070002
1031060003

2. RANK()

설명

  • 동일한 값이면 같은 순위를 부여, 이후 순위 건너뜀

SQL 예제

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

결과

employee_iddepartment_idsalaryrank
1011090001
1021070002
1031070002
1041060004

3. DENSE_RANK()

설명

  • 동일한 값이면 같은 순위를 부여, 이후 순위를 건너뛰지 않음

SQL 예제

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

결과

employee_iddepartment_idsalarydense_rank
1011090001
1021070002
1031070002
1041060003

4. PERCENT_RANK()

설명

  • (RANK – 1) / (전체 행 수 – 1) 공식으로 백분율 순위 계산

SQL 예제

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

결과

employee_iddepartment_idsalarypercent_rank
1011090000.0
1021070000.5
1031070000.5
1041060001.0

5. LAG()

설명

  • 이전 행의 값 가져오기 (NULL 허용)

SQL 예제

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

결과

employee_iddepartment_idsalaryprev_salary
101109000NULL
1021070009000
1031070007000

6. LEAD()

설명

  • 다음 행의 값 가져오기 (NULL 허용)

SQL 예제

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

결과

employee_iddepartment_idsalarynext_salary
1011090007000
1021070007000
1031070006000

7. FIRST_VALUE()

설명

  • 첫 번째 값 반환

SQL 예제

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

8. LAST_VALUE()

설명

  • 마지막 값 반환

SQL 예제

SELECT 
    employee_id, 
    department_id, 
    salary, 
    LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_salary
FROM employees;

9. NTH_VALUE()

설명

  • 특정 번째(n) 값 반환

SQL 예제

SELECT 
    employee_id, 
    department_id, 
    salary, 
    NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY salary DESC) AS second_salary
FROM employees;

10. MEDIAN()

설명

  • 중간 값 반환 (일부 DBMS에서 지원)
SELECT 
    department_id, 
    MEDIAN(salary) OVER (PARTITION BY department_id) AS median_salary
FROM employees;

11. CUME_DIST() (누적 분포 함수)

🔹 설명

  • 데이터 그룹 내에서 특정 값이 차지하는 누적 비율을 계산하는 함수
  • 순위 기반 함수로, 해당 값이 속한 그룹에서 몇 번째 누적 비율인지 계산
  • 같은 값이면 같은 비율을 반환하며, 항상 0보다 크고 1 이하
  • 전체 행 수를 기준으로 누적된 값의 개수를 나눈 결과

🔹 공식

🔹 SQL 예제

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

🔹 결과값

employee_iddepartment_idsalarycum_dist
1011090000.25
1021070000.50
1031070000.50
1041060001.00

🔹 설명

  • 부서(department_id)별(PARTITION BY) 급여(salary DESC) 순으로 정렬하여 누적 분포를 계산
  • 7000을 받는 두 명(102, 103)의 CUME_DIST 값이 동일
  • 6000을 받는 104번 직원이 마지막 값이므로 1.00

12. RATIO_TO_REPORT() (비율 계산 함수)

🔹 설명

  • 각 행의 특정 값이 그룹 내 전체 합에서 차지하는 비율을 반환
  • SUM(column)의 전체 합을 기준으로 개별 값의 비율을 계산
  • GROUP BY 없이 개별 행을 유지하면서 비율을 볼 때 유용

🔹 공식

🔹 SQL 예제

SELECT 
    department_id, 
    employee_id, 
    salary, 
    RATIO_TO_REPORT(salary) OVER (PARTITION BY department_id) AS salary_ratio
FROM employees;

🔹 결과값

employee_iddepartment_idsalarysalary_ratio
1011090000.36
1021070000.28
1031070000.28
1041060000.24

🔹 설명

  • PARTITION BY department_id → 부서별 급여 합계를 기준으로 비율 계산
  • 부서 10번(department_id=10)의 전체 급여 합계:
    9000 + 7000 + 7000 + 6000 = 29000
  • 개별 급여를 29000으로 나눈 비율을 반환
    • 9000 / 29000 = 0.36
    • 7000 / 29000 = 0.28
    • 6000 / 29000 = 0.24

13. NTILE(n) (그룹 나누기 함수)

🔹 설명

  • 데이터를 n개의 동일한 크기의 그룹(타일)으로 나누어 그룹 번호를 부여
  • 사분위수, 백분위수와 같은 데이터 분할 분석에 사용
  • NTILE(4) → 데이터를 4등분(사분위) 해서 각 그룹에 순서를 부여

🔹 SQL 예제

SELECT 
    employee_id, 
    department_id, 
    salary, 
    NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

🔹 결과값

employee_iddepartment_idsalaryquartile
1011090001
1021070002
1031070003
1041060004

🔹 설명

  • PARTITION BY department_id → 부서별 그룹화
  • ORDER BY salary DESC → 급여 높은 순서로 정렬 후 그룹 배정
  • NTILE(4) → 데이터를 4개 그룹으로 나누어 순위 부여
    • 9000 (1등급)
    • 7000 (2등급)
    • 7000 (3등급)
    • 6000 (4등급)

Leave a Comment