SQL) WINDOW 함수(2) -그룹별 행 순서함수, 그룹별 비율함수
# 그룹별 행 순서함수
1. FIRST_VALUE, LAST_VALUE 함수 (SQL Server 지원 안함)
- 파티션별 윈도우에서 가장 먼저 나온 값 리턴
- 같은값에 대해선 윈도우에 먼저나온 데이터가 우선순위로 취급
# FIRST VALUE & LAST VALUE
SQL> SELECT DEPTNO, ENAME, SAL
,FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING
) AS ENAME_FV
,LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS ENAME_LV
FROM EMP;
#실행결과
DEPTNO ENAME SAL ENAME_FV ENAME_LV
--------------------------------------------------------------------------------------------------
10 가자미 5000 가자미 오르막
10 수산화 4000 가자미 오르막
10 오르막 1800 가자미 오르막
20 내리막 4500 내리막(#) --- 윈도우에 먼저 등장한 내리막이 우선순위를 가져감
20 운동화 4500 내리막(#) --- 만약 우선순위를 바꾸고싶으면 별도의 뷰나 ORDER BY절 사용
20 자장면 2500 내리막 볶음밥
20 볶음밥 2000 내리막 볶음밥
30 눈싸움 4000 눈싸움 기싸움
30 기싸움 3000 눈싸움 기싸움
2. LAG 함수 (SQL Server 지원 안함)
- 파티션별 윈도우에서 현재행 기준 이전 몇 번째 행의 값 리턴
- LAG(exp1, arg1, arg2)
# arg1 : 현재행 기준 몇번째 앞의행을 가져올지
# arg2 : 가져온 행이 NULL 값일 경우 arg2 값으로 대체
3. LEAD 함수 (SQL Server 지원 안함)
- 파티션별 윈도우에서 현재행 기준 이후 몇 번째 행의 값 리턴
- LEAD(exp1, arg1, arg2)
# arg1 : 현재행 기준 몇번째 뒤의행을 가져올지
# arg2 : 가져온 행이 NULL 값일 경우 arg2 값으로 대체
# 그룹별 비율함수
1. RATIO_TO_REPORT 함수 (SQL Server 지원 안함)
- 파티션별 칼럼값(속성값)의 비율 (한 파티션의 개별 ratio의 합은 당연히 1이다.)
2. PERCENT_RANK 함수 (SQL Server 지원 안함)
- 파티션별 값이 아닌 행의 순서별 백분율 (동일값은 동일 백분율로 표시, 이후 값은 동일백분율로 표시된값의 갯수와 상관있음)
ex) 0.2 - 0.4 - 0.4 - 0.8 - 1.0 (5개의 데이터중 2,3행이 중복)
3. CUME_DIST 함수 (SQL Server 지원 안함)
- 파티션별 값이 아닌 행의 순서별 백분율 (동일값은 동일 백분율로 표시, 이후 값은 동일백분율로 표시된값의 갯수와 상관있음)
+ (동일값은 동일 백분율로 표시하나, 다른 WINDOW 함수와는 다르게 맨뒤의 행의 결과값을 따른다)
ex) 0.2 - 0.6 - 0.6 - 0.8 - 1.0 (5개의 데이터중 2,3행이 중복)
4. NTILE 함수 (SQL Server 지원 안함)
- NTILE(N) : 파티션별 전체 건수를 N 등분하고, 등분 순서를 리턴
ex) 총 데이터 건수가 20건일때 NTILE(4) 사용시 5등분되며, 5등분의 첫번째 부분은 값이 1부터 시작하여 마지막부분은 5까지 값을 리턴.
# RATIO_TO_REPORT
SQL> SELECT ENO, SAL
,ROUND (RATIO_TO_REPORT (SAL) OVER (), 2) AS SAL_RR
FROM EMP;
# 실행결과
ENO SAL SAL_RR
----------------------------------------
001 1600 0.29
002 1250 0.22
003 1250 0.22
004 1500 0.27
# PERCENT_RANK
SQL> SELECT DEPTNO, SAL
,PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS PR
FROM EMP;
# 실행결과 (0.00에서 1.00 까지)
DEPTNO SAL PR
------------------------------------------
10 1600 0.00(#)
10 1600 0.00(#)
10 1250 1.00
20 1500 0.50
20 1200 1.00
# CUME_DIST
SQL> SELECT DEPTNO, SAL
,CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CD
FROM EMP;
# 실행결과 (0.00에서 1.00 까지) - 동일 값은 마지막행의 리턴값으로 동일처리
DEPTNO SAL CD
------------------------------------------
10 1600 0.67(#)
10 1600 0.67(#)
10 1250 1.00
20 1500 0.50
20 1200 1.00
# NTILE
SQL> SELECT DEPTNO, SAL
,NTILE(3) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS NT
FROM EMP;
# 실행결과 (0.00에서 1.00 까지) - 동일 값은 마지막행의 리턴값으로 동일처리
DEPTNO SAL NT
-------------------------------------------
10 1600 1
10 1630 1
10 1250 2
10 1500 3
===========================================
20 1220 1
20 1250 2
20 1700 3
===========================================
30 1400 1
30 1800 2