2022-11-22 데이터 베이스 오라클 : 등위함수(WINDOW 분석함수)

 등위함수(WINDOW 분석함수)
  - 특정 컬럼을 기준으로 순위를 부여할때 사용
  - RANK, DENSE_RANK, ROW_NUMBER 함수 제공
  - SELECT 절에 사용

 

(사용형식)
    RANK()|DENSE_RANK()|ROW_NUMBER() 

             OVER(ORDER BY 컬럼명1[ASC|DESC][, 컬럼명2 [ASC|DESC],...])
    . RANK() : '컬럼명1'의 값이 같으면 같은 순위 부여하고 동점순위가 n개일 때

                      차 순위는 '현재순위' + 'n'임
        ex) 18,16,16,16,14,13,10
                  1  2  2  2  5  6  7
    . DENSE_RANK() : '컬럼명1'의 값이 같으면 같은 순위 부여하고 동점순위가 n개일 때

                                     차 순위 차례대로 다음값 부여
           ex) 18,16,16,16,14,13,10
                  1  2  2  2  3  4  5
    . ROW_NUMBER :  '컬럼명1'의 값이 같아도 차례대로 순위부여 
      ex) 18,16,16,16,14,13,10
                  1  2  3  4  5  6  7 -- 마지막 번호는 전체 데이터의 갯수

사용예) 회원테이블에서 회원들의 마일리지 순으로 등수를 부여하시오. 같은 값이면 동일 등수를 부여하고
        차순위는 동점자 수만큼 건너뛴 등수부여
        Alias는 회원번호,회원명,나이,마일리지,등수

    SELECT MEM_ID AS 회원번호,
           MEM_NAME AS 회원명,
           EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) AS 나이,
           MEM_MILEAGE AS 마일리지,
           RANK() OVER(ORDER BY MEM_MILEAGE DESC) AS "등수1(RANK)", 
           DENSE_RANK()  OVER(ORDER BY MEM_MILEAGE DESC) AS "등수2(DENSE_RANK)", 
           ROW_NUMBER() OVER(ORDER BY MEM_MILEAGE DESC)AS "등수3(ROW_NUMBER)"
      FROM MEMBER

 

 

 

사용예) 회원테이블에서 회원들의 마일리지 순으로 등수를 부여하시오. 
       마일리지가 동일한 값이면 나이가 적은 회원에게 앞선 등수를 부여하고
       나이도 같은 값이면 동일 등수를 부여하고 차순위는 동점자 수만큼 건너뛴 등수를 부여하세요
       Alias는 회원번호,회원명,나이,마일리지,등수
        
   

SELECT MEM_ID AS 회원번호,
       MEM_NAME AS 회원명,
       EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) AS 나이,
       MEM_MILEAGE AS 마일리지,
       RANK() OVER (ORDER BY MEM_MILEAGE DESC,
                             EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) ASC) 
                             AS "등수1(RANK)", 
       DENSE_RANK() OVER(ORDER BY MEM_MILEAGE DESC,
                                  EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) ASC)
                                 AS "등수2(DENSE_RANK)", 
       ROW_NUMBER() OVER(ORDER BY MEM_MILEAGE DESC,
                                  EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR) ASC) 
                                 AS "등수3(ROW_NUMBER)"        
  FROM MEMBER

 

** 그룹내 순위부여
  - 그룹별로 순위 부여

(사용형식)
    RANK()|DENSE_RANK()|ROW_NUMBER() OVER(PARTITION BY 컬럼명1[컬럼명2,...]  
                                          ORDER BY 컬럼명1[ASC|DESC][,컬럼명2 [ASC|DESC], ...]

-- PARTITION BY 그룹을 묶는 것 GROUP BY과 같음

 

사용예) 사원테이블에서 각 부서별로 급여순으로 순위를 부여하시오(RANK()함수 사용)
        Alias는 사원번호,사원명,부서코드,급여,순위

 SELECT EMPLOYEE_ID AS 사원번호,
        EMP_NAME AS 사원명,
        DEPARTMENT_ID AS 부서코드,
        SALARY AS 급여,
        RANK() OVER(PARTITION BY DEPARTMENT_ID 
                    ORDER BY SALARY DESC) AS 순위
   FROM HR.EMPLOYEES
  ORDER BY 3;

 

 

 

 

 

 

사용예)2020년 분류별 판매액집계를 구하고 분류별 순위를 조회하시오
     Alias는 분류코드,분류명,판매액,순위

(서브쿼리) 
    SELECT B.LPROD_GU AS 분류코드,
           B.LPROD_NM AS 분류명,
           SUM(A.CART_QTY*C.PROD_PRICE) AS 판매액    
      FROM CART A, LPROD B, PROD C
     WHERE A.CART_PROD=C.PROD_ID
       AND C.PROD_LGU=B.LPROD_GU
       AND A.CART_NO LIKE '2020%'
     GROUP BY B.LPROD_GU ,B.LPROD_NM
     ORDER BY 1;

 --------------------------------------------------------------  

 SELECT D.BLGU AS 분류코드,
        D.BLNM AS 분류명,
        TO_CHAR(D.CSUM,'999,999,999') AS 판매액,
        RANK() OVER( ORDER BY D.CSUM DESC) AS 순위
   FROM (SELECT B.LPROD_GU AS BLGU,
                B.LPROD_NM AS BLNM,
                SUM(A.CART_QTY*C.PROD_PRICE) AS CSUM     
           FROM CART A, LPROD B, PROD C
          WHERE A.CART_PROD=C.PROD_ID
            AND C.PROD_LGU=B.LPROD_GU
            AND A.CART_NO LIKE '2020%'
          GROUP BY B.LPROD_GU ,B.LPROD_NM)D

-----------------------------------------------------------------------------                      

(제품별집계 후 분류별 순위)

SELECT B.LPROD_GU AS 분류코드,
       B.LPROD_NM AS 분류명,
       P.PROD_ID AS 상품코드,
       P.PROD_NAME AS 상품명,
       TO_CHAR(D.CSUM,'999,999,999') AS 판매액,
       RANK() OVER(PARTITION BY B.LPROD_GU ORDER BY D.CSUM DESC) AS 순위 
       --그룹별로 순위
  FROM LPROD B, PROD P,
       (SELECT A.CART_PROD AS ACID,                   
               SUM(A.CART_QTY*C.PROD_PRICE) AS CSUM     
          FROM CART A, PROD C
         WHERE A.CART_PROD=C.PROD_ID
         GROUP BY A.CART_PROD)D        
 WHERE B.LPROD_GU=P.PROD_LGU
   AND P.PROD_ID=D.ACID
 ORDER BY 1;