[DBMS/Oracle] Window 분석함수

[DBMS/Oracle] Window 분석함수

Window 분석함수

  - 등위를 구하는 함수
  - RANK, DENSE_RANK, ROW_NUMBER 등이 제공됨 
  - RANK : 동일 값에 같은 등수를 부여하고 그 다음 등수는 "현재 등수 + 동점자 수"의 등수가 부여됨

      ex) 9, 8, 8, 8, 7, 7, 6, 5,
            1  2  2  2  5  5  7  8
  - DENSE_RANK : 동일 값에 같은 등수를 부여하고 그 다음 등수는 동점자 수에 관계 없이 순차적인 값을 부여
      ex) 9, 8, 8, 8, 7, 7, 6, 5,
            1  2  2  2  3  3  4  5
  - ROW_NUMBER : 동점자에 상관없이 순차적인 값을 부여            
      ex) 9, 8, 8, 8, 7, 7, 6, 5,
            1  2  3  4  5  6  7  8

(사용형식)
  RANK|DENSE_RANK()|ROW_NUMBER()(ORDER BY 기준컬럼 [ASC|DESC]) [AS 별칭]

 

사용예) 회원테이블에서 회원들의 보유 마일리지가 많은 회원부터 등수를 부여하시오.
               Alias는 회원번호,회원명,마일리지,순위이다.

  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_MILEAGE AS 마일리지,
         RANK() OVER(ORDER BY MEM_MILEAGE DESC) AS "RANK_순위",
         DENSE_RANK() OVER(ORDER BY MEM_MILEAGE DESC) AS "DENSE_RANK_순위",
         ROW_NUMBER() OVER(ORDER BY MEM_MILEAGE DESC) AS "ROW_NUMBER_순위"
    FROM MEMBER;

 

사용예) 2020년 5월 회원별 구매금액합계를 구하고 상위 10명을 순위를 부여하여 출력하시오.
               Alias는 회원번호,회원명,구매금액합계,순위

  SELECT C.CMID AS 회원번호,
         C.CNAME AS 회원명,
         C.CSUM AS 구매금액합계,
         RANK() OVER(ORDER BY C.CSUM DESC) AS 순위
    FROM (SELECT A.CART_MEMBER AS CMID, --(서브쿼리: 2020년 5월 회원별 구매금액합계)
                 M.MEM_NAME AS CNAME,
                 NVL(SUM(A.CART_QTY*B.PROD_PRICE),0) AS CSUM
            FROM CART A, PROD B, MEMBER M
           WHERE A.CART_PROD=B.PROD_ID
             AND A.CART_MEMBER=M.MEM_ID
             AND A.CART_NO LIKE '202005%'
           GROUP BY A.CART_MEMBER,M.MEM_NAME
           ORDER BY 3 DESC) C
   WHERE ROWNUM<=10;

 

  - 그룹별 순위

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

  . 'PARTITION BY 컬럼명' : 그룹을 묶을 기준 컬럼 (PARTITION => GROUP와 같은 기능)
  . 'ORDER BY 컬럼명' : 순위부여의 기준 컬럼

 

사용예) 사원테이블에서 각 부서별 사원들의 급여에 순위를 부여하여 출력하시오.
             급여가 동일한 경우 입사일 순으로 순위 부여
             Alias는 사원번호,사원명,부서코드,부서명,급여,순위

  SELECT A.EMPLOYEE_ID AS 사원번호,
         A.EMP_NAME AS 사원명,
         A.DEPARTMENT_ID AS 부서코드,
         B.DEPARTMENT_NAME AS 부서명,
         A.HIRE_DATE AS 입사일,
         A.SALARY AS 급여,
         RANK() OVER(PARTITION BY A.DEPARTMENT_ID ORDER BY A.SALARY DESC, A.HIRE_DATE) AS 순위
    FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
   WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
   ORDER BY 3;