1. WINDOW
FUNTION
행과 행간의 관계를 쉽게 정의하기 위해 만든 함수
분석함수(ANALYTIC
FUNCTION)나 순위 함수(RANK FUNCTION)으로도 불림
중첩해서는 사용이 불가능하나 서브쿼리에서 사용 가능
가. WINDOE FUNTION의 종류
- 순위 관련 함수 : RANK, DENSE_RANK,
ROW_NUMBER 대부분의 DBMS에서 지원
- 집계 관련 함수 : SUM, MAX, MIN, AVG,
COUNT 대부분의 DBMS에서 사용하나 SQL SERVER의 경우 OVER절 내의 ORDER BY 구문 지원하지 않음
- 그룹 내 행 순서 관련 함수 : FIRST_VALUE,
LAST_VALUE, LAG, LEAD ORACLE에서만 지원되는 함수
- 그룹 내 비율 관련 함수 : CUME_DIST,
PERCENT_RANK, NTILE, RATIO_TO_REPORT, 1,2번은 전부, 3번은 표준에는 없지만 지원, 4번은 ORACLE에서만 지원
- 선형분석 포함하는 통계 함수
나. SYNTAX
SELECT WINDOW_FUNCTION (ARGUMENTS)
OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] )
FROM 테이블 명;
-
WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.
-
ARGUMENTS (인수) : 함수에 따라 0 ~ N개의 인수가 지정될 수 있다.
-
PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
-
ORDER BY 절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
-
WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
ROWS는
물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.
다만,
WINDOWING 절은 SQL Server에서는 지원하지 않는다.
- BETWEEN
사용 타입
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR
PRECEDING/FOLLOWING
AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR
PRECEDING/FOLLOWING
- BETWEEN
미사용 타입
ROWS | RANGE
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR
PRECEDING
2. 그룹 내 순위 함수
가. RANK 함수
특정 항목에 대한 순위를 구하는 함수, 특정 범위내에서 순위를 구할수도 있음, 동일값에 대해서는 동일한 순서 부여
예제) RANK( ) OVER (ORDER BY
SAL DESC) ALL_RANK : 전체 중에 연봉 순위
RANK( )
OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK : 그룹별로 나눈 후 연봉 순위
나. DENSE_RANK 함수
동일한 건수를 하나의 건수로 취급
동점이 있을 시 같은 등수 예를 들어 순위는 1, 2, 2, 3, 4, 5, 5, 6
다. ROW_NUMBER 함수
동일한 값이라도 고유의 순서를 부여함 예를 들어 순위는 1, 2, 3, 4, 5, 6, 7, 8 로 같은 값이여도 유니크한 순위를 정함
ORACLE 경우는 ROWID가 적은 행부터 나옴
3. 일반 집계 함수
가. SUM 함수
누적값 구할 수 있음
예제) SELECT MGR, ENAME, SAL,
SUM(SAL)
OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM
FROM EMP;
RANGE
UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
나. MAX 함수
INLINE VIEW로 파티션별 최대값을 가진 행 추출 가능
예제) SELECT MGR, ENAME, SAL
FROM
(SELECT MGR, ENAME, SAL,
MAX(SAL)
OVER (PARTITION BY MGR) as IV_MAX_SAL
FROM
EMP)
WHERE
SAL = IV_MAX_SAL ;
각 매니져 별로 연봉이 가장 높은 직원을 추출
다. MIN 함수
예제) SELECT MGR, ENAME,
HIREDATE, SAL,
MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN
FROM
EMP;
라. AVG 함수
파티션별 ROWS 윈도우를 통해 평균값 구할 수 있음
예제) SELECT MGR, ENAME,
HIREDATE, SAL,
ROUND
(AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND
1 FOLLOWING)) as MGR_AVG
FROM
EMP;
ROWS
BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을
범위로 지정한다.
(ROWS는
현재 행의 앞뒤 건수를 말하는 것임)
마. COUNT 함수
예제) SELECT ENAME, SAL,
COUNT(*)
OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT
FROM
EMP;
RANGE
BETWEEN 50 PRECEDING AND 150 FOLLOWING : 현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에
포함된 모든 행이 대상이 된다.
(RANGE는
현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)
4. 그룹 내 행 순서 함수
가. FIRST_VALUE 함수
파티션별 윈도우에 가장 먼저 나온 값, SQL SERVER 미지원(MIN 활용 가능)
공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW나 ORDER BY 절에 칼럼 추가
예제) SELECT DEPTNO, ENAME,
SAL,
FIRST_VALUE(ENAME)
OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED
PRECEDING) as RICH_EMP
FROM
EMP;
나. LAST_VALUE 함수
파티션별 윈도우에 가장 나중에 나온 값, SQL SERVER 미지원(MAX 활용 가능)
공동 함수 인정하지 않고 처음 나온 행만 처리, 의도적으로 나누고 싶다면 INLINE VIEW나 ORDER BY 절에 칼럼 추가
예제) SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME)
OVER
(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING) as DEPT_POOR
FROM
EMP;
ROWS
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를
지정한다.
다. LAG 함수
파티션 별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있음, SQL SERVER 미지원
NVL, ISNULL 기능과 같음
예제) SELECT ENAME,
HIREDATE, SAL,
LAG(SAL,
2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE
JOB = 'SALESMAN'
LAG(SAL,
2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리한다.
[실행 결과] ENAME HIREDATE SAL PREV_SAL
--------- ------------- -------- ----------
ALLEN 1981-02-20 1600 0
WARD 1981-02-22 1250 0
TURNER 1981-09-08 1500 1600
MARTIN 1981-09-28 1250 1250
라. LEAD 함수
파티션 별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있음, SQL SERVER 미지원
NVL, ISNULL 기능과 같음
예제) SELECT ENAME,
HIREDATE,
LEAD(HIREDATE,
1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"
FROM
EMP;
[실행
결과] ENAME HIREDATE NEXTHIRED
----------- ------------ ---------------
ALLEN 1981-02-20 1981-02-22
WARD 1981-02-22 1981-04-02
TURNER
1981-09-08 1981-09-28
MARTIN
1981-09-28
5. 그룹 내 비율 함수
가. RATIO_TO_REPORT 함수
파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함
결과 값은 > 0 & <= 1이고,
합계는 1, SQL SERVER 미지원
예제 ) ELECT ENAME, SAL,
ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE
JOB = 'SALESMAN';
전체 합 중에 로우 별 연봉이 차지하는 비율이 나오게 됨, 개별 RATIO의 전체 합은 1
나. PERCENT_RANK 함수
윈도우에 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행 순서별 백분율을 구함
결과 값은 > 0 & <= 1이고, SQL SERVER 미지원
예제) SELECT DEPTNO, ENAME,
SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R
FROM
EMP;
3개의 로우가 된다면 0, 0.5, 1 순서로 되고, 5개의 로우라면 0, 0.25, 0.5, 0.75, 1로
추출됨 ORDER
BY역할
다. CUME_DIST 함수
파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율
결과 값은 > 0 & <= 1이고, SQL SERVER 미지원
예제) SELECT DEPTNO, ENAME,
SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST
FROM
EMP;
5개의 로우가 선택되면 0.2, 0.4, 0.6, 0.8, 1의
값이 추출 ORDER BY역할
동일 순서면 뒤의 행의 함수 결과 값을 따름 예를 들면, 0.4, 0.4, 0.6, 0.8, 1
라. NTILE 함수
파티션별 전체 건수를 ARGUMENT 값으로 N 등분 한 결과, 조를 나누는 것과 같음
예제) SELECT ENAME, SAL,
NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE
FROM EMP;
4개의 파티션으로 나뉘면, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 4 등으로 로우가 추출되어서 파티션별 조를 정할 수 있음
