Oracle(2022.02.17)-SUM ,RANK() OVER,DENSE_RANK() OVER,ROW_NUMBER() OVER

1. SUM 
  -각 그룹에서 특정 컬럼의 합계를 구하여 반환.
  (사용형식)
  SUM(expr)

사용예) 사원테이블에서 각 부서별 급여 합계를 구하시오.
    SELECT DEPARTMENT_ID AS 부서,
           SUM(SALARY) AS 급여합계
        FROM HR.EMPLOYEES
        GROUP BY DEPARTMENT_ID
        ORDER BY 1;

사용예) 매입테이블에서 2005년 2월 상품별 매입수량 합계와 매입금액 합계를 구하시오.
    SELECT BUY_PROD AS 상품,
           SUM(BUY_QTY) AS "매입수량 합계",
           SUM(BUY_QTY*BUY_COST) AS "매입금액 합계" 
        FROM BUYPROD
        WHERE BUY_DATE BETWEEN TO_DATE('20050201') AND LAST_DAY(TO_DATE('20050201'))
        GROUP BY BUY_PROD
        ORDER BY 1;

사용예) 2005년도 매입자료에서 월별 상품별 매입수량합계와 매입금액합계를 조회하시오.
    SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월 ,
    BUY_PROD AS 상품코드,
    SUM(BUY_QTY) AS 매입수량합계 ,
    SUM(BUY_QTY *BUY_COST) AS 매입금액합계
    FROM BUYPROD
    WHERE EXTRACT(YEAR FROM BUY_DATE)=2005
    GROUP BY EXTRACT(MONTH FROM BUY_DATE),BUY_PROD
    ORDER BY 1;

데이터 일부

사용예) 회원테이블에서 성별 마일리지 합계를 구하시오.
    SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1'OR 
                     SUBSTR(MEM_REGNO2,1,1)='3' THEN '남성회원'
            ELSE '여성회원'
            END AS 구분,
           SUM(MEM_MILEAGE)AS "마일리지 합계"
        FROM MEMBER
        GROUP BY CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1'OR      -->GROUP BY는 집계함수 외에 문장 그대로 써주기
                     SUBSTR(MEM_REGNO2,1,1)='3' THEN '남성회원'
            ELSE '여성회원'
            END;

사용예) 매출테이블에서 2005년 4월 회원별 구매수량합계를 구하시오.
    SELECT CART_MEMBER AS 회원,
           SUM(CART_QTY) AS 구매수량합계
        FROM CART
        WHERE CART_NO LIKE '200504%'
        GROUP BY CART_MEMBER
        ORDER BY 1;

사용예) 매출테이블에서 2005년 4월 회원별 구매수량합계를 구하고 구매수량 기준 
       상위 5명의 정보를 출력하시오
       SELECT CART_MEMBER AS 회원번호,
              SUM(CART_QTY) AS 구매수량합계
        FROM CART
        WHERE CART_NO LIKE '200504%'
            AND ROWNUM<=5
        GROUP BY CART_MEMBER
        ORDER BY 2 DESC;

 -->오류 5명이 안나옴 제일먼저 카트테이블에서 2005년4월달에 판매된 정보 찾고 ->5줄 선택한다.(a가3개사고 t가 2개사고) ->그룹바이->셀렉트

오류가 나온이유

--수정후

SELECT A.CART_MEMBER AS 회원번호,
          A.SAMT AS 구매수량합계
     FROM(SELECT CART_MEMBER ,
                 SUM(CART_QTY) AS SAMT
            FROM CART
           WHERE CART_NO LIKE '200504%'
           GROUP BY CART_MEMBER
           ORDER BY 2 DESC) A
    WHERE ROWNUM<=5;

--등수부여

SELECT CART_MEMBER AS 회원번호,
           SUM(CART_QTY) AS 구매수량합계,
           RANK() OVER(ORDER BY SUM(CART_QTY) DESC) AS 순위
            FROM CART
           WHERE CART_NO LIKE '200504%'
           GROUP BY CART_MEMBER;

          

사용예) 2005년 1~3월 상품별 매입금액합계 중 매입금액이 500만원 이상이 상품만 
       조회하시오.
        SELECT BUY_PROD AS 상품코드,
               SUM(BUY_QTY*BUY_COST) AS 매입금액합계
          FROM BUYPROD
        WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND LAST_DAY(TO_DATE('20050301'))
        GROUP BY BUY_PROD 
        HAVING SUM(BUY_QTY*BUY_COST)>=5000000 -- WHERE 절에 기술할 수 없다.
        ORDER BY 1;

 

---

**등수부여(WINDOW 함수)-SELECT 절에서 사용
-RANK() OVER, DENSE_RANK() OVER, ROW_NUMBER() OVER 제공
-그룹별 등수부여는 RANK() OVER 함수에서 PARTITION BY절을 사용하여 구현
-그룹별 최소 최대값은 KEEP() 함수와 DENSE_RANK() 함수를 사용하여 구현

  1)RANK() OVER
   . 일반적인 등수 출력(동일 값에 동일 등수를 부여하고 다음 등수는 같은값의 갯수만큼
     건너 뛴 값 부여(ex: 1,2,2,2,5,6...)
   (사용형식)
   RANK() OVER(ORDER BY 컬럼명 [ASC|DESC][,컬럼명 [ASC|DESC],...])
  

사용예)사원테이블에서 급여순으로 등수를 부여하시오
       Alias는 사원번호, 사원명, 부서코드,급여,순위
       SELECT EMPLOYEE_ID AS 사원번호,
              EMP_NAME AS 사원명,
              DEPARTMENT_ID AS 부서코드,
              SALARY AS 급여,
              RANK() OVER(ORDER BY SALARY DESC) AS 순위
         FROM HR.EMPLOYEES;

데이터일부..

사용예)사원테이블에서 급여순으로 순위를 부여하되 같은 급여이면 입사일이 빠른사원
      부터 순위부여
       Alias는 사원번호, 사원명, 부서코드,급여,순위
       SELECT EMPLOYEE_ID AS 사원번호,
              EMP_NAME AS 사원명,
              DEPARTMENT_ID AS 부서코드,
              HIRE_DATE AS 입사일,
              SALARY AS 급여,
              RANK() OVER(ORDER BY SALARY DESC) AS 순위1,
              RANK() OVER(ORDER BY SALARY DESC,HIRE_DATE ASC) AS 순위2
         FROM HR.EMPLOYEES;

데이터 일부...

---

-그룹별 최소값, 최대값 구하기
    .KEEP() 함수와 FIRST, LAST 식별자 사용
    .DENSE_RANK() 함수만 사용 가능
    (사용형식)
    MIN(컬럼명)|MAX(컬럼명) KEEP(DENSE_RANK FIRST|LAST ORDER BY 컬럼명 [ASC|DESC])
                          OVER(PARTITION BY 컬럼명)

사용예) 사원테이블에서 사원들의 급여를 조회하되 사원들이 속한 부서의
       최대급여와 최소급여를 조회하시오
       Alias는 사원번호, 사원명, 부서코드, 급여, 최소급여, 최대급여
    SELECT EMPLOYEE_ID AS 사원번호, 
           EMP_NAME AS 사원명,
           DEPARTMENT_ID AS 부서코드,
           SALARY AS 급여, 
           MIN(SALARY) KEEP(DENSE_RANK FIRST ORDER BY SALARY) OVER(PARTITION BY DEPARTMENT_ID)
              AS 부서최소급여, 
              -->셀러리를 기준으로 정렬할거야 (작은값에서 큰값으로)부서별로(첫번째사원이가지고있는 사원번호기준으로)
           MAX(SALARY) KEEP(DENSE_RANK LAST ORDER BY SALARY) OVER(PARTITION BY DEPARTMENT_ID)
              AS 부서최대급여 
              -->제일마지막에 있는 제일 큰 급여
       FROM HR.EMPLOYEES
       ORDER BY 3,4;

데이터 일부

---

2)DENSE_RANK() OVER
    .순위를 부여하는 방법이 RANK() OVER함수와 다르며 그 이외의 특징은 동일함
    .같은 값에 같은 순위를 부여하지만 차 순위는 순차적인 순위 부여 
     (ex : 1,2,2,2,3,4,5...)
    

---

3)ROW_NUMBER() OVER
    .순위를 부여하는 방법이 RANK() OVER함수나 DENSE_RANK()와 다르며 그 이외의 특징은 동일함
    .같은 값에도 차례대로 증가하는 순위부여 
     (ex :값:10,20,20,20,30,40,50...이면
        순위: 1, 2, 3, 4, 5, 6, 7...임)