윈도우 함수란?
menu_book 윈도우 함수란?
- 일반적인 집계 함수(SUM, AVG, COUNT, 등)는 여러 행을 하나로 집계합니다.
- 윈도우 함수는 집계를 하되, 각 행마다 결과를 계산해서 반환합니다.
- OVER() 절을 사용하며, PARTITION BY, ORDER BY, ROWS BETWEEN 등을 조합하여 정교한 분석이 가능합니다.
⸻
구문 기본 형태
<함수명>(컬럼) OVER ( PARTITION BY ... -- 그룹핑 기준 ORDER BY ... -- 정렬 기준 ROWS BETWEEN ... -- 분석 범위 )
⸻
대표적인 윈도우 함수 목록
| 함수명 | 설명 | 예제 SQL | 설명 결과 |
|---|---|---|---|
| ROW_NUMBER() | 파티션 내 행 번호를 순서대로 부여 | SELECT EMP_NAME
, ROW_NUMBER() OVER (PARTITION BY DEPT_ID
ORDER BY SALARY DESC) AS RN
FROM EMPLOYEE;
|
각 부서별 급여 내림차순 순위 (중복 순위 없음) |
| RANK() | 동점자에게 같은 순위를 부여하고 다음 순위는 건너뜀 | SELECT EMP_NAME
, RANK() OVER (PARTITION BY DEPT_ID
ORDER BY SALARY DESC) AS RANK
FROM EMPLOYEE;
|
부서 내 급여 순위 (동점자 동일 순위) |
| DENSE_RANK() | RANK와 비슷하지만 순위를 건너뛰지 않음 |
SELECT EMP_NAME
, DENSE_RANK() OVER (PARTITION BY DEPT_ID
ORDER BY SALARY DESC) AS DRANK
FROM EMPLOYEE;
|
순위에 공백 없이 채워짐 |
| LAG() | 현재 행 기준 이전 행의 값을 가져옴 |
SELECT EMP_NAME, SALARY
, LAG(SALARY) OVER (ORDER BY EMP_ID) AS PREV_SAL
FROM EMPLOYEE;
|
이전 직원의 급여 정보 확인 |
| LEAD() | 현재 행 기준 다음 행의 값을 가져옴 |
SELECT EMP_NAME, SALARY
, LEAD(SALARY) OVER (ORDER BY EMP_ID) AS NEXT_SAL
FROM EMPLOYEE;
|
다음 직원의 급여 정보 확인 |
| SUM(), AVG(), COUNT(), MAX(), MIN() | 집계 함수도 윈도우와 함께 사용 가능 | SELECT EMP_NAME, SALARY
, AVG(SALARY) OVER (PARTITION BY DEPT_ID) AS AVG_SAL
FROM EMPLOYEE;||부서별 평균 급여 | |
| FIRST_VALUE(), LAST_VALUE() | 파티션 내 정렬 기준 첫/마지막 값 반환 | SELECT EMP_NAME, SALARY
, FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID
ORDER BY SALARY DESC) AS TOP_SAL
FROM EMPLOYEE;
|
부서별 최고 급여 |
⸻
예제: 누적합(Cumulative Sum)
SELECT EMP_NAME, SALARY,
SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY) AS CUM_SAL
FROM EMPLOYEE;
부서별 급여 누적합을 계산합니다.
⸻
윈도우 프레임 옵션
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| 의미 | 설명 |
|---|---|
| UNBOUNDED PRECEDING | 현재 파티션의 맨 처음부터 시작하는 윈도우 범위 지정 (예: 파티션 시작 ~ 현재 행까지 누적 계산 시 사용) |
| CURRENT ROW | 현재 행까지만 포함하는 윈도우 범위 지정 (예: 이동 평균 계산 시 행 단위 제한) |
| 1 PRECEDING, 1 FOLLOWING | 현재 행을 기준으로 앞뒤 1행씩 포함한 범위 지정 (예: 3행 이동 평균 계산 시 사용) |
예:
SUM(SALARY) OVER ( PARTITION BY DEPT_ID ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
부서별로 급여 순으로 정렬하고, 현재 행까지 누적된 급여 합계를 구합니다.
실무 예제 1: 부서별 상위 3위 급여 직원 조회
SELECT * FROM ( SELECT EMP_ID, EMP_NAME, DEPT_ID, SALARY, RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS SAL_RANK FROM EMPLOYEE ) WHERE SAL_RANK <= 3;
각 부서별로 급여 순위를 매긴 뒤, 상위 3명의 직원만 필터링합니다.
실무 활용: 팀별 Top Performer 조회 등
실무 예제 2: 직전과의 급여 변화량 분석
SELECT EMP_ID, EMP_NAME, SALARY, LAG(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE) AS PREV_SAL, SALARY - LAG(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE) AS DIFF_SAL FROM EMPLOYEE;
부서 내 입사일 기준으로 정렬하여 직전 직원과 급여 차이를 분석합니다.
실무 활용: 급여 갭 파악, 트렌드 분석 등
실무 예제 3: 부서별 누적 인건비 계산
SELECT EMP_ID, EMP_NAME, DEPT_ID, SALARY, SUM(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_SALARY FROM EMPLOYEE;
입사일 기준으로 누적 급여를 계산합니다.
실무 활용: 연속적인 비용 추적, 월별 누적 등
실무 예제 4: 최초 입사자 정보 함께 보여주기 (FIRST_VALUE)
SELECT EMP_ID, EMP_NAME, DEPT_ID, HIRE_DATE, FIRST_VALUE(EMP_NAME) OVER (PARTITION BY DEPT_ID ORDER BY HIRE_DATE) AS FIRST_JOINED FROM EMPLOYEE;
각 부서에서 가장 먼저 입사한 직원의 이름을 모든 직원에게 보여줍니다.
실무 활용: 리더, 시니어 직원 분석 등
실무 예제 5: 부서 내 직원 수 대비 자신의 비율 (비율 분석)
SELECT EMP_ID, EMP_NAME, DEPT_ID, COUNT(*) OVER (PARTITION BY DEPT_ID) AS DEPT_TOTAL, 1.0 / COUNT(*) OVER (PARTITION BY DEPT_ID) AS RATIO FROM EMPLOYEE;
각 직원이 부서 인원 중 차지하는 비율을 계산합니다.
실무 활용: KPI 가중치, 구성비 분석 등
이 외에도, 윈도우 함수는 보고서, 통계 분석, SLA 계산, 재고 흐름 추적 등 다양한 실무 시나리오에 활용됩니다.
요점 정리
• 윈도우 함수는 분석용 SQL에서 매우 중요합니다. • OVER() 절로 집계 범위를 제한하며, 집계 결과를 각 행에 붙여서 반환합니다. • RANK, ROW_NUMBER, LAG/LEAD, FIRST_VALUE 등 다양하게 활용 가능합니다.