(새 문서: 좋습니다! Oracle SQL의 윈도우 함수(Window Function) 는 매우 강력한 기능으로, 행(row)들을 그룹으로 묶어 집계 또는 분석을 수행하면서도 행 단위로 결과를 반환합니다. 아래에 윈도우 함수의 개념, 주요 함수 종류, 설명, 사용 예제를 함께 정리하고, 미디어위키 표 형식으로도 제공합니다. ⸻ ✅ 윈도우 함수란? • 일반적인 집계 함수(SUM, AVG, COUNT, 등)는 여러 행을 하나...) |
편집 요약 없음 |
||
| 29번째 줄: | 29번째 줄: | ||
! 함수명 !! 설명 !! 예제 SQL !! 설명 결과 | ! 함수명 !! 설명 !! 예제 SQL !! 설명 결과 | ||
|- | |- | ||
| ROW_NUMBER() || 파티션 내 행 번호를 순서대로 부여 || | | ROW_NUMBER() || 파티션 내 행 번호를 순서대로 부여 || | ||
SELECT EMP_NAME, ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RN | SELECT EMP_NAME, ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RN | ||
FROM EMPLOYEE; || 각 부서별 급여 내림차순 순위 (중복 순위 없음) | FROM EMPLOYEE; || 각 부서별 급여 내림차순 순위 (중복 순위 없음) | ||
|- | |- | ||
| RANK() || 동점자에게 같은 순위를 부여하고 다음 순위는 건너뜀 || | | RANK() || 동점자에게 같은 순위를 부여하고 다음 순위는 건너뜀 || | ||
SELECT EMP_NAME, RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RANK | SELECT EMP_NAME, RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS RANK | ||
FROM EMPLOYEE; || 부서 내 급여 순위 (동점자 동일 순위) | FROM EMPLOYEE; || 부서 내 급여 순위 (동점자 동일 순위) | ||
|- | |- | ||
| DENSE_RANK() || RANK와 비슷하지만 순위를 건너뛰지 않음 || | | DENSE_RANK() || RANK와 비슷하지만 순위를 건너뛰지 않음 || | ||
SELECT EMP_NAME, DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DRANK | SELECT EMP_NAME, DENSE_RANK() OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS DRANK | ||
FROM EMPLOYEE; || 순위에 공백 없이 채워짐 | FROM EMPLOYEE; || 순위에 공백 없이 채워짐 | ||
|- | |- | ||
| LAG() || 현재 행 기준 이전 행의 값을 가져옴 || | | LAG() || 현재 행 기준 이전 행의 값을 가져옴 || | ||
SELECT EMP_NAME, SALARY, LAG(SALARY) OVER (ORDER BY EMP_ID) AS PREV_SAL | SELECT EMP_NAME, SALARY, LAG(SALARY) OVER (ORDER BY EMP_ID) AS PREV_SAL | ||
FROM EMPLOYEE; || 이전 직원의 급여 정보 확인 | FROM EMPLOYEE; || 이전 직원의 급여 정보 확인 | ||
|- | |- | ||
| LEAD() || 현재 행 기준 다음 행의 값을 가져옴 || | | LEAD() || 현재 행 기준 다음 행의 값을 가져옴 || | ||
SELECT EMP_NAME, SALARY, LEAD(SALARY) OVER (ORDER BY EMP_ID) AS NEXT_SAL | SELECT EMP_NAME, SALARY, LEAD(SALARY) OVER (ORDER BY EMP_ID) AS NEXT_SAL | ||
FROM EMPLOYEE; || 다음 직원의 급여 정보 확인 | FROM EMPLOYEE; || 다음 직원의 급여 정보 확인 | ||
|- | |- | ||
| SUM(), AVG(), COUNT(), MAX(), MIN() || 집계 함수도 윈도우와 함께 사용 가능 || | | SUM(), AVG(), COUNT(), MAX(), MIN() || 집계 함수도 윈도우와 함께 사용 가능 || | ||
SELECT EMP_NAME, SALARY, AVG(SALARY) OVER (PARTITION BY DEPT_ID) AS AVG_SAL | SELECT EMP_NAME, SALARY, AVG(SALARY) OVER (PARTITION BY DEPT_ID) AS AVG_SAL | ||
FROM EMPLOYEE; || 부서별 평균 급여 | FROM EMPLOYEE; || 부서별 평균 급여 | ||
|- | |- | ||
| FIRST_VALUE(), LAST_VALUE() || 파티션 내 정렬 기준 첫/마지막 값 반환 || | | FIRST_VALUE(), LAST_VALUE() || 파티션 내 정렬 기준 첫/마지막 값 반환 || | ||
SELECT EMP_NAME, SALARY, FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS TOP_SAL | SELECT EMP_NAME, SALARY, FIRST_VALUE(SALARY) OVER (PARTITION BY DEPT_ID ORDER BY SALARY DESC) AS TOP_SAL | ||
FROM EMPLOYEE; || 부서별 최고 급여 | FROM EMPLOYEE; || 부서별 최고 급여 | ||
|} | |} | ||
| 102번째 줄: | 109번째 줄: | ||
더 복잡한 예제로 윈도우 함수 + 서브쿼리 + 조건 분기 조합도 가능합니다. | 더 복잡한 예제로 윈도우 함수 + 서브쿼리 + 조건 분기 조합도 가능합니다. | ||
필요하시면 고급 실무 예제도 만들어드릴게요! 원하시는 시나리오가 있으신가요? 💡 | 필요하시면 고급 실무 예제도 만들어드릴게요! 원하시는 시나리오가 있으신가요? 💡 | ||
좋습니다! 실무에서 자주 쓰이는 고급 윈도우 함수 예제를 몇 가지 제공해 드리겠습니다. 이 예제들은 다양한 업무 시나리오에 적용될 수 있도록 구성하였습니다. | |||
✅ 실무 예제 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을 맞춤 설계해 드릴게요! | |||
예: 매출 분석, 재고 추적, 고객 행동 분석 등 있으신가요? 🤔 | |||
2025년 5월 12일 (월) 13:05 판
좋습니다! Oracle SQL의 윈도우 함수(Window Function) 는 매우 강력한 기능으로, 행(row)들을 그룹으로 묶어 집계 또는 분석을 수행하면서도 행 단위로 결과를 반환합니다. 아래에 윈도우 함수의 개념, 주요 함수 종류, 설명, 사용 예제를 함께 정리하고, 미디어위키 표 형식으로도 제공합니다.
⸻
✅ 윈도우 함수란? • 일반적인 집계 함수(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행까지 포함한 범위
예:
SUM(SALARY) OVER (
PARTITION BY DEPT_ID ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
부서별로 급여 순으로 정렬하고, 현재 행까지 누적된 급여 합계를 구합니다.
⸻
✅ 정리 요약 • 윈도우 함수는 분석용 SQL에서 매우 중요합니다. • OVER() 절로 집계 범위를 제한하며, 집계 결과를 각 행에 붙여서 반환합니다. • RANK, ROW_NUMBER, LAG/LEAD, FIRST_VALUE 등 다양하게 활용 가능합니다.
⸻
더 복잡한 예제로 윈도우 함수 + 서브쿼리 + 조건 분기 조합도 가능합니다. 필요하시면 고급 실무 예제도 만들어드릴게요! 원하시는 시나리오가 있으신가요? 💡
좋습니다! 실무에서 자주 쓰이는 고급 윈도우 함수 예제를 몇 가지 제공해 드리겠습니다. 이 예제들은 다양한 업무 시나리오에 적용될 수 있도록 구성하였습니다.
✅ 실무 예제 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을 맞춤 설계해 드릴게요!
예: 매출 분석, 재고 추적, 고객 행동 분석 등 있으신가요? 🤔