데이터베이스

SQL 윈도우 함수(Window Function)

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등급)

zerg96

Leave a Comment
Share
Published by
zerg96
Tags: CUM_DISTNTILERATIO_TO_REPORTSQLSQL 윈도우 함수SQL 윈도우펑션SQLDSQLPWindow Function
1년 ago

Recent Posts

충격! 코스피 8% 폭락에 SK텔레콤 AI 차단까지 – 한국의 AI 도박이 터졌다

코스피 8% 폭락, 서킷브레이커 발동, SK텔레콤 Claude AI 차단까지. 한국의 AI 레버리지 버블이 단 하루…

2주 ago

당신 얼굴이 이미 쓰이고 있다… AI 딥페이크 범죄, 생각보다 훨씬 심각합니다

SNS 사진 1장으로 30초 만에 딥페이크 영상이 완성됩니다. 당신의 얼굴이 이미 범죄에 악용되고 있을 수…

3주 ago

당신 얼굴이 이미 쓰이고 있다 — AI 딥페이크 범죄, 생각보다 훨씬 심각합니다

SNS 사진 1장으로 30초 만에 딥페이크 영상이 완성됩니다. 당신의 얼굴이 이미 범죄에 악용되고 있을 수…

3주 ago

달러·원 환율 급등, 지금 당신이 꼭 알아야 할 것들

달러/원 환율이 급등하는 이유와 실생활 영향을 정리했습니다. 지금 당장 활용할 수 있는 환전·투자 대응 전략까지…

3주 ago

미래에셋·미래에셋벤처투자·미래에셋생명 동반 급등, 스페이스X와 무슨 관계?

미래에셋·미래에셋벤처투자·미래에셋생명이 동반 급등한 이유는 스페이스X 상장 기대감입니다. 세 회사가 스페이스X와 어떻게 연결되어 있는지 상세히 분석했습니다.

3주 ago

스페이스X 상장 D-데이? 일론 머스크가 절대 말 안 하는 진짜 이유

스페이스X 상장이 계속 미뤄지는 진짜 이유를 파헤쳤습니다. 화성 계획, 스타링크 분리, 국방 계약... 머스크가 절대…

3주 ago