SQL) WINDOW 함수(1) -그룹내 순위함수,일반 집계함수

# WINDOW 함수 ( = RANK, ANALYTIC 함수)

 

# WINDOW FUCTION SYNTAX

 - OVER 키워드가 필수로 포함되어야 한다.

 - SQL Server에서는 OVER 절 내에 ORDER BY절, WINDOWING절 사용 불가

# []는 옵션(선택사항)

SELECT WINDOW_FUCNTION(arguments) OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블명;

# WINDOWING 절

# BETWEEN 사용 타입
ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
                 AND UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

# BETWEEN 미사용 타입
ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

---

# 그룹 내 순위 함수

 

 1. RANK 함수

  - ORDER BY를 포함한 쿼리문에서 특정 칼럼에 대한 순위를 구하는 함수 (동일한 값은 동일한 순위를 부여)

  - PARTITION BY 에 의해 구분된 JOB_RK는 같은 직업 내에서 순위를 구한다.

  - PARTITION BY와 ORDER BY를 같이 쓸 경우 PARTITION별로 ORDER BY 됨.

# SQL 전문가 가이드 예시
# 이 예시는 RANK가 두번쓰이고 각 RANK 함수의 정렬조건이 달라, 
#                             첫번째 RANK함수의 정렬조건으로 정렬됨
SQL> SELECT JOB, ENAME, SAL
           , RANK() OVER (ORDER BY SAL DESC) AS ALL_RK
           , RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_RK
     FROM EMP;
     
# 실행결과
 JOB                  ENAME                 SAL                 ALL_RK            JOB_RK
 -----------------------------------------------------------------------------------------------
 PRESIDENT           KINGG                 5000                  1                 1
 ANALYST               FORDD                 3000                 2                 1
 ANALYST               SCOTT                 3000                  2                 1
 MANAGER               JOHNS                 2975                4                 1               
 MANAGER               BLAKE                  2850               5                 2
 MANAGER               CLARK                 2450                6                 3
 SALESMAN             ALLEN                 1600                 7                 1
 SALESMAN             JOKKE                 1500                 8                 2
 CLERK                   JOHNN                 1300                9                  1
 SALESMAN             JONNY                 1250                10                3
 SALESMAN             HONNY                 1250               10                3
 CLERK                   COMMY                 1100               12                2
 CLERK                   DALLY                  950                 13                3
 CLERK                   LONNY                  800                14                4

 

 2. DENSE_RANK 함수

  - 동일한 순위를 하나의 등수로 간주.

   ex) RANK 함수              - [1, 1, 3, 4, 5, 6, 6, 8, 9, 10] 

        DENSE_RANK 함수    - [1, 1, 2, 3, 4, 5, 5, 6, 7, 8]

 

 3. ROW_NUMBER 함수

  - 동일한 순위라도 다른 등수(unique)로 표현.

  - 동일 값에 대한 순서를 관리하고 싶다면, 추가로 기준 정의 필요.

    ex) ROW_NUMBER() OVER (ORDER BY SAL DESC, ENAME)

 

    ex) RANK 함수              - [1, 1, 3, 4, 5, 6, 6, 8, 9, 10] 

         DENSE_RANK 함수    - [1, 1, 2, 3, 4, 5, 5, 6, 7, 8]

         ROW_NUMBER 함수  - [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

 

---

 # 일반 집계함수

 

 1. SUM 함수

  - 파티션별 윈도우의 합 리턴

 

# 예시1
SQL> SELECT MGR, ENAME, SAL
           ,SUM (SAL) OVER (PARTITION BY MGR) AS SAL_SUM
     FROM EMP;
     
# 출력결과 (가독성을 위해 (#) 표시)
MGR            ENAME         SAL           SAL_SUM
---------------------------------------------------
111            핑구          3000          6000
111            토끼          3000          6000(#)          
115            사자          2000          4500
115            힝구          2500          4500(#)
120            밍구          4500          8500
120            딩구          4000          8500(#)

# 예시2
SQL> SELECT MGR, ENAME, SAL
           ,SUM (SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS SAL_SUM
     FROM EMP;



# 출력결과 (가독성을 위해 (#) 표시) 
--- (SAL 단위로 정렬했으므로, SAL이 같을경우 같은 순서로 취급하여 누적합이 같게됨)
MGR            ENAME         SAL           SAL_SUM
---------------------------------------------------
111            동구          2500          2500
111            핑구          3000          8500
111            토끼          3000          8500(#)          
115            사자          2000          2000
115            힝구          2500          4500(#)
120            밍구          4500          4500
120            딩구          4000          8500(#)

 

 2. MAX 함수 , MIN 함수

  - 파티션별 윈도우의 최대, 최소값 리턴

 

# MAX 함수
SQL> SELECT MGR, ENAME, SAL
           ,MAX (SAL) OVER (PARTITION BY MGR) AS MAX_SAL
     FROM EMP;
     
# 출력결과
MGR            ENAME         SAL           MAX_SAL
---------------------------------------------------
111            동구          2500          3000
111            핑구          3000          3000
111            토끼          3000          3000(#)        
115            사자          2000          2500
115            힝구          2500          2500(#)
120            밍구          4500          4500
120            딩구          4000          4500(#)

# MIN 함수 (정렬없이 전체파티션에서의 최솟값)
SQL> SELECT MGR, ENAME, SAL
           ,MIN (SAL) OVER (PARTITION BY MGR) AS MIN_SAL
     FROM EMP;
     
# 출력결과
MGR            ENAME         SAL           MIN_SAL
---------------------------------------------------
111            동구          2500          2500
111            핑구          3000          2500
111            토끼          3000          2500(#)         
115            사자          2000          2000
115            힝구          2500          2000(#)
120            밍구          4500          4000
120            딩구          4000          4000(#)


# MIN 함수 + ORDER BY 절
SQL> SELECT MGR, ENAME, SAL
           ,MIN (SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) AS MIN_SAL
     FROM EMP;
     
# 출력결과 (데이터가 HIRE DATE로 정렬됨)
           (정렬된 순서(출력=윈도우)에서 MIN 값을 비교하며 구함)
MGR            ENAME         SAL           MIN_SAL
---------------------------------------------------
111            핑구          3000          3000(#)   --- 여기선 최솟값이 3000
111            동구          2500          2500(#)   --- 3000과 2500 비교, 최솟값 변경
111            토끼          3000          2500        
115            사자          2000          2000
115            힝구          2500          2000
120            밍구          4500          4500
120            딩구          4000          4000

3. AVG 함수 (+ WINDOWING 절 ROWS)

  - 파티션별 평균값

 

# AVG함수 
SQL> SELECT MGR, ENAME, SAL
           ,AVG (SAL) OVER (PARTITION BY MGR) AS AVG_SAL
     FROM EMP;
     
# 출력결과
MGR            ENAME         SAL           AVG_SAL
---------------------------------------------------
111            동구          6000          4000
111            핑구          3000          4000
111            토끼          3000          4000(#)        
115            사자          2000          2250
115            힝구          2500          2250(#)
120            밍구          4500          4250
120            딩구          4000          4250(#)


# AVG함수 + ROWS 윈도우
# WINDOWING 절로 인해 1행앞 ~ 1행뒤 까지를 범위지정됨
# 기준은 파티션별, 1행앞뒤는 HIREDATE 에 의해 정렬된 윈도우 기준
SQL> SELECT MGR, ENAME, SAL
           ,AVG (SAL) OVER (PARTITION BY MGR
                            ORDER BY HIREDATE
                            ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
           ) AS AVG_SAL
     FROM EMP;
     
# 출력결과
MGR            ENAME         SAL           AVG_SAL
---------------------------------------------------
111            동구          6000          4500      --- (앞행 0 + 현재행 6000 + 다음행 3000) /2
111            핑구          3000          4000      --- (앞행 6000 + 현재행 3000 + 다음행 3000) /3
111            토끼          3000          3000(#)   --- (앞행 3000 + 현재행 3000 + 다음행 0) /2     
115            사자          2000          2250      --- (앞행 0 + 현재행 2000 + 다음행 2500) /2
115            힝구          2500          2250(#)   --- (앞행 2000 + 현재행 2500 + 다음행 0) /2
120            밍구          4500          4250
120            딩구          4000          4250(#)

 4. COUNT 함수 (+ WINDOWING절 RANGE)

  - 파티션별 데이터 수

 

# COUNT함수 + WINDOWING 절 RANGE
# ROW는 행단위로 비교 했으나, RANGE는 값단위로 비교
# 파티션지정되면 지정파티션내에서 RANGE 비교, 파티션 지정없을시 모든값 대상으로 비교
# 자기자신도 데이터에 포함되어 카운트 됨
SQL> SELECT ENAME, SAL
           ,COUNT (*) OVER (ORDER BY SAL
                            RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING    
                            # 지정 데이터별 봉급에서 -1000 ~ +1000 사이 데이터 카운팅(모든 데이터 기준)
           ) AS EMP_CNT
     FROM EMP;
     
# 출력결과
ENAME         SAL           EMP_CNT
---------------------------------------------------
동구          6000             2    # 동구+핑구
핑구          5000             2    # 동구+핑구           
토끼          3500             3    # 토끼+사자+힝구              
사자          3000             2    # 토끼+힝구      
힝구          2500             3    # 사자+힝구+밍구      
밍구          1700             2    # 힝구+밍구          
딩구          500              1    # 딩구 (1500 ~ -500)