[Oracle SQL] 집계함수(2)-MAX, MIN, ROLLUP, CUBE

4)MAX(col), MIN(col)

• 컬럼 col의 값 중 최대값(MAX), 최소값(MIN)을 반환
• 중첩불가능.

사용예) 각 부서별 최대급여와 최소급여를 조회하시오

  SELECT DEPARTMENT_ID AS 부서코드,
            MAX(SALARY) AS 최대급여,
            MIN(SALARY) AS 최소급여
   FROM HR.EMP
  GROUP BY DEPARTMENT_ID
  ORDER BY 1;

사용예) 각 부서별 최대급여와 최소급여를 받는 사원을 조회하시오 --두 개의 쿼리문

(부서별 최소급여)

    SELECT DEPARTMENT_ID AS DID,
        MIN(SALARY)AS NSAL
     FROM HR.EMP
    GROUP BY DEPARTMENT_ID ;
    (부서별 최대급여)
   SELECT DEPARTMENT_ID AS DID,
          MAX(SALARY)AS MSAL
     FROM HR.EMP
    GROUP BY DEPARTMENT_ID ;
    
    (최대급여 사원정보)
    
    SELECT B.EMPLOYEE_ID AS 사원번호,
           B.EMP_NAME AS 사원명,
           A.DID AS 부서코드,
           A.MSAL AS 최대급여
      FROM (SELECT DEPARTMENT_ID AS DID, 
      --인라인서브쿼리. 단독 실행했을때 실행되어야함.
                    MAX(SALARY)AS MSAL
             FROM HR.EMP
            GROUP BY DEPARTMENT_ID)A,-- 테이블조인
           HR.EMP B
      WHERE A.DID=B.DEPARTMENT_ID 
      --조인조건1)같은 부서코드 컬럼 추출
      AND A.MSAL=B.SALARY --조인조건2)
      ORDER BY 3;
      
      (최소급여 사원정보)
    SELECT B.EMPLOYEE_ID AS 사원번호,
           B.EMP_NAME AS 사원명,
           A.DID AS 부서코드,
           A.MSAL AS 최대급여
      FROM (SELECT DEPARTMENT_ID AS DID,
            MIN(SALARY)AS MSAL
            FROM HR.EMP
              GROUP BY DEPARTMENT_ID )A,
           HR.EMP B
      WHERE A.DID=B.DEPARTMENT_ID
      AND A.MSAL=B.SALARY 
      ORDER BY 3;

    

사용예) 급여가 많은 5명의 사원정보 조회

 SELECT A.EMP_NAME  A.SALARY
  FROM (SELECT EMP_NAME, --서브쿼리
                SALARY
         FROM HR.EMP
        ORDER BY 2 DESC)A
 WHERE ROWNUM <=5;

5) ROLLUP(col[,col,...])

• 다양한 종류의 집계를 반환
• GROUP BY 절에서만 사용됨
• 기술된 col의 순서(왼쪽->오른쪽)가 레벨을 결정하고 그 레벨별 집계를 반환
• 사용된 col의 갯수가 n개 일때 n+1 가지의 집계반환
  ex) GROUP BY ROLLUP(C1,C2,C3) =>
  C1,C2,C3를 기준으로 한 집계 =1
  C1,C2를 기준으로 한 집계 =2
  C1를 기준으로 한 집계 =3
  전체집계 =4 --레벨사용안된것

사용예) 장바구니테이블에서 2005년 월별, 회원별, 상품별 판매집계(수량*단가)를 조회하시오.

(GROUP BY 절)
       SELECT SUBSTR(A.CART_NO,5,2) AS 월, 
              A.CART_MEMBER AS 회원번호, 
              A.CART_PROD AS 상품코드, 
              SUM(A.CART_QTY) AS 판매수량합계,
              SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계    
         FROM CART A, PROD B
        WHERE A.CART_PROD=B.PROD_ID
          AND A.CART_NO LIKE '2005%'
        GROUP BY SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD
        ORDER BY 1 ,2;
        
  (GROUP BY ROLL UP 절)
        SELECT SUBSTR(A.CART_NO,5,2) AS 월, 
              A.CART_MEMBER AS 회원번호, 
              A.CART_PROD AS 상품코드, 
              SUM(A.CART_QTY) AS 판매수량합계,
              SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계    
         FROM CART A, PROD B
        WHERE A.CART_PROD=B.PROD_ID
          AND A.CART_NO LIKE '2005%'
        GROUP BY ROLLUP(SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD) 
        ORDER BY 1 ,2;
        
  (부분 ROLLUP: 일부 분류기준 컬럼이 ROLLUP 밖에 기술된 경우)
        SELECT SUBSTR(A.CART_NO,5,2) AS 월, 
              A.CART_MEMBER AS 회원번호, 
              A.CART_PROD AS 상품코드, 
              SUM(A.CART_QTY) AS 판매수량합계,
              SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계    
         FROM CART A, PROD B
        WHERE A.CART_PROD=B.PROD_ID
          AND A.CART_NO LIKE '2005%'
        GROUP BY SUBSTR(A.CART_NO,5,2), ROLLUP(A.CART_MEMBER,A.CART_PROD) 
        -- SUBSTR절과 ROLLUP절은 동등. -> 전체합 안나옴
        -- 롤업: 처음엔 다 포함된 집계, 오른쪽에서 하나를 제거한집계, 다제거한 집계
        ORDER BY 1 ,2;

6) CUBE(col[,col,...])

• 주어진 컬럼들을 조합해 나올 수 있는 모든 경우의 가지수만큼 다양한 집계반환
• 사용된 컬럼의 수가 n개일때 집계의 종류는 2^n 가지임

 SELECT SUBSTR(A.CART_NO,5,2) AS 월, 
              A.CART_MEMBER AS 회원번호, 
              A.CART_PROD AS 상품코드, 
              SUM(A.CART_QTY) AS 판매수량합계,
              SUM(A.CART_QTY*B.PROD_PRICE) AS 금액합계    
   FROM CART A, PROD B
  WHERE A.CART_PROD=B.PROD_ID
          AND A.CART_NO LIKE '2005%'
 GROUP BY CUBE(SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD) 
 ORDER BY 1 ,2;