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