-
복잡한 SQL문을 작성해야 하던 것을 부분적이나마 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 바로 윈도우함수이다.
-
윈도우 함수는 기존에 사용하던 집계함수도 있고, 새로이 윈도우 함수 전용으로 만들어진 기능도 있다. 그리고 윈도우 함수는 다른 함수와는 달리 중첩해서 사용하지는 못하지만 서브쿼리에서는 사용할 수 있다.
- 윈도우 함수 문법
: 윈도우 함수에는 OVER문구가 키워드로 필수 포함된다.SELECT WINDOW_FUNCTION (ARGUMENTS) OVER([PARTITION BY 컬럼][ORDER BY절][WINDOWING절]) FROM 테이블명;
- WINDOW_FUNCTION : 기존에 사용하던 함수와 새롭게 추가된 함수도 있다.
- ARGUMENTS (인수) : 함수에 따라 0~N개의 인수가 지정될 수 있다.
- PARTITION BY절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
- ORDER BY절 : 어떤 항목에 대해 순위를 지정할지 기술한다.
- WINDOWING절 : 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
그룹 내 순위 함수
1. RANK 함수
: ORDER BY를 포함한 쿼리문에서 특정 컬럼에 대한 순위를 구하는 함수이다.
사원데이터에서 직업별로 급여가 높은 순서를 출력
입력✏️
SELECT JOB
, ENAME
, SAL
, RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) AS SALRANK
FROM EMP;출력🖥️
2. DENSE_RANK 함수
: RANK함수와 유사하나 동일한 순위를 하나의 건수로 취급한다.
사원데이터에서 급여가 높은순서대로 출력
입력✏️
SELECT JOB
, ENAME
, SAL
, RANK() OVER(ORDER BY SAL DESC) AS SALRANK
, DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSERANK
FROM EMP;출력🖥️
3. ROW_NUMBER 함수
: 동일한 값이라도 고유한 순위를 부여한다.
입력✏️
SELECT JOB
, ENAME
, SAL
, RANK() OVER(ORDER BY SAL DESC) AS SALRANK
, ROW_NUMBER () OVER(ORDER BY SAL DESC) AS RWNUM
FROM EMP;출력🖥️
일반 집계함수
: SUM, MAX, MIN, AVG, COUNT 함수가 있다.
입력✏️
SELECT MGR
, ENAME
, SAL
, SUM(SAL) OVER(PARTITION BY MGR) AS SUMSAL
, MAX(SAL) OVER(PARTITION BY MGR) AS MAXSAL
, MIN(SAL) OVER(PARTITION BY MGR) AS MINSAL
FROM EMP;출력🖥️
그룹 내 행 순서 함수
1. FRIST_VALUE 함수
: 파티션별 윈도우에서 가장 먼저 나온 값을 구한다. MIN함수를 활용해 같은 결과를 얻을 수도 있다.
부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션내에서 가장 먼저 나온 값을 출력.
입력✏️
SELECT DEPTNO
, ENAME
, SAL
,FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING) AS ENAME_FV
FROM EMP;
출력💻
2. LAST_VALUE 함수
: 파티션별 윈도우에서 가장 나중에 나온 값을 구한다. Max함수를 활용해 같은 결과를 얻을 수도 있다.
부서별 직원들을 연봉이 높은 순서부터 정렬하고 파티션내에서 가장 마지막에 나온 값을 출력
입력✏️
SELECT DEPTNO
, ENAME
, SAL
,LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS ENAME_LV
FROM EMP;
출력💻
3. LAG 함수
: 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
직원들을 입사일자가 빠른 기준으로 정렬하고 본인보다 입사일자가 한명 앞선 사원의 급여를 본인의 급여와 함께 출력
입력✏️
SELECT ENAME
, HIREDATE
, SAL
, LAG(SAL) OVER(ORDER BY HIREDATE) AS LAG_SAL
FROM EMP
WHERE JOB = 'SALESMAN';출력💻
4.LEAD 함수
: 파티션별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있다.
3개의 인수까지 사용할 수 있다. 두번째인자는 몇번째 후의 행을 가져올지 결정하고 세번째 인자는 파티션의 마지막 행은 가져올 데이터가 없어 NULL값이 들어오는데 이 경우 다른 값으로 바꿀 수 있다. 즉 NVL이나 ISNULL기능과 같다.
입력✏️
SELECT ENAME
, HIREDATE
, SAL
, LEAD(HIREDATE, 1) OVER(ORDER BY HIREDATE) AS LEAD_HIREDATE
FROM EMP
WHERE JOB = 'SALESMAN';출력💻
그룹 내 비율 함수
1. RATIO_TO_REPORT 함수
: 파티션 내 전체 SUM값에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있다. 결과 값은 > 0 & <= 1의 범위를 가진다. 개별 RATIO의 합을 구하면 1이 된다.
직업이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력
입력✏️
SELECT ENAME
, HIREDATE
, SAL
, ROUND ( RATIO_TO_REPORT (SAL) OVER (), 2) AS SAL_PR
FROM EMP
WHERE JOB = 'SALESMAN';출력💻
2. PERCENT_RANK 함수
: 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로해 값이 아닌 행의 순서별 백분율을 구한다. 결과값은 >= 0 & <= 1 의 범위를 가진다.
같은 부서 소속사원들의 집합에서 본인의 급여가 순서상 몇번째 위치에 있는지 0과 1사이의 값으로 출력
입력✏️
SELECT DEPTNO
,ENAME
, SAL
, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS PR
FROM EMP;출력💻
3. CUME_DIST 함수
: 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.
같은 부서소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇번째 위치쯤에 있는지 0과1사이의 값으로 출력.
입력✏️
SELECT DEPTNO
,ENAME
, SAL
, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CD
FROM EMP;출력💻
4. NTILE 함수
: 파티션별 전체 건수를 인수 값으로 N등분한 값을 구할 수 있다.
전체사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.
입력✏️
SELECT ENAME
, SAL
, NTILE(4) OVER (ORDER BY SAL DESC) AS NT
FROM EMP;출력💻
참고자료 : SQL전문가가이드
