Oracle(2022.02.23)-ROLLUP,CUBE

앞부분 이어서 <ANSI 외부조인>

사용예)사원테이블과 부서테이블에서 모든 부서별 사원수를 조회하시오.
      Alias는 부서번호, 부서명, 인원수 (둘다 부족한 상태라 안시조인 사용)
      SELECT B.DEPARTMENT_ID AS 부서번호,
             B.DEPARTMENT_NAME AS 부서명, -->SLELCT절은 되도록 적은 행 선택
             COUNT(A.EMPLOYEE_ID) AS 인원수 -->COUNT에 기본키를 써주는것이 제일 안전
        FROM HR.EMPLOYEES A  --(사원테이블은 NULL1개를 포함한 12개,부서테이블은 27개 부서코드존재  EMPLOYEES는 16개부족, DEPARTMENT 얘는 NULL부서코드가 없으니까 서로 부서코드가 부족한 상황
        FULL OUTER JOIN HR.DEPARTMENTS B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID)
       GROUP BY B.DEPARTMENT_ID,B.DEPARTMENT_NAME
       ORDER BY 1;

---

ROLLUP과 CUBE 함수
 -GROUP BY 절에 사용되는 함수로 다양한 집계를 제공(혼자는 사용 X)
  GROUP BY 절에는 SELECT절에 5가지 집계함수외에 일반 절이 그대로 쓰임

1.ROLLUP
  (사용형식)

 GROUP BY ROLLUP [컬렴명1[컬럼명2,...])

 

.ROLLUP에 기술된 컬럼들을 기준으로 레벨별(오른쪽->왼쪽) 집계를 반환

ex) GROUP BY ROLLUP(c1,c2,c3)인 경우
       -c1과 c2 및 c3가 기본으로 적용된 집계 (레벨3)
       -c1과 c2가 기본으로 적용된 집계(레벨2)
       -c1만 기본으로 적용된 집계(레벨1)
       -전체집계(레벨 0)
.ROLLUP절에 기술된 컬럼이 n개일때 반환되는 집계의 종류는 n+1개임

< ROLLUP 미사용>

사용예)2005년 월별,회원별,상품별 판매집계(SUM)를 조회하시오 ->(먼저월별 나누고 그다음 회원별로 그다음 또 쪼개서 상품별로)
      Alias는 월,회원명,상품명,구매수량
      SELECT SUBSTR(A.CART_NO,5,2) AS 월,  -->WHRER 절에서 이미2005년 처리 했으므로 월만 뽑아도 2005년 알아서..
             B.MEM_NAME AS 회원명,
             C.PROD_NAME AS 상품명,
             SUM(A.CART_QTY) AS 구매수량
        FROM CART A, MEMBER B, PROD C
        WHERE SUBSTR(A.CART_NO,1,4)='2005'
          AND A.CART_MEMBER=B.MEM_ID
          AND A.CART_PROD=C.PROD_ID
        GROUP BY SUBSTR(A.CART_NO,5,2), B.MEM_NAME,C.PROD_NAME
        ORDER BY 1;

<ROLLUP 사용>

(ROLLUP 사용)-->월별의 총 구매수량,회원별로 총 구매수량이 나온다.전체 집계도 나온다 총4가지의 합계
     SELECT SUBSTR(A.CART_NO,5,2) AS 월,  -->WHRER 절에서 이미2005년 처리 했으므로 월만 뽑아도 2005년 알아서..
             B.MEM_NAME AS 회원명,
             C.PROD_NAME AS 상품명,
             SUM(A.CART_QTY) AS 구매수량
        FROM CART A, MEMBER B, PROD C
        WHERE SUBSTR(A.CART_NO,1,4)='2005'
          AND A.CART_MEMBER=B.MEM_ID
          AND A.CART_PROD=C.PROD_ID
        GROUP BY ROLLUP(SUBSTR(A.CART_NO,5,2), B.MEM_NAME, C.PROD_NAME)
        ORDER BY 1;

 

월과 회원명

 -c1과 c2 및 c3가 기본으로 적용된 집계 (레벨3)
  -c1과 c2가 기본으로 적용된 집계(레벨2) 그래서 상품명인 c3가 null

 -c1과 c2 및 c3가 기본으로 적용된 집계 (레벨3)
  -c1과 c2가 기본으로 적용된 집계(레벨2)
  -c1만 기본으로 적용된 집계(레벨1)

그래서 c2인 회원명과 c3인 상품명이 null이 나옴

월 회원명 상품명 다

월별 및 회원별 및 상품별 집계,월별 및 회원별 집계,  월별집계, 전체집계

<부분 ROLLUP 사용>

사용예)2005년 월별,회원별,상품별 판매집계(SUM)를 조회하시오 ->(먼저월별 나누고 그다음 회원별로 그다음 또 쪼개서 상품별로)
      Alias는 월,회원명,상품명,구매수량
SELECT SUBSTR(A.CART_NO,5,2) AS 월,  -->WHRER 절에서 이미2005년 처리 했으므로 월만 뽑아도 2005년 알아서..
             B.MEM_NAME AS 회원명,
             C.PROD_NAME AS 상품명,
             SUM(A.CART_QTY) AS 구매수량
        FROM CART A, MEMBER B, PROD C
        WHERE SUBSTR(A.CART_NO,1,4)='2005'
          AND A.CART_MEMBER=B.MEM_ID
          AND A.CART_PROD=C.PROD_ID
        GROUP BY SUBSTR(A.CART_NO,5,2),ROLLUP(B.MEM_NAME,C.PROD_NAME) 
        ORDER BY 1;

 

        월은 분류기준에서 1순위로
        1.SUBSTR(A.CART_NO,5,2), B.MEM_NAME,C.PROD_NAME
        2.SUBSTR(A.CART_NO,5,2), B.MEM_NAME 즉 월,회원별
        3.SUBSTR(A.CART_NO,5,2) 즉 월로만 구성되어진

---

CUBE

(사용형식)
  GROUP BY ROLLUP [컬렴명1[컬럼명2,...])

 

.CUBE에 기술된 컬럼들로 조합 가능한 모든 집계를 반환

ex) GROUP BY CUBE(c1,c2,c3)인 경우
       -c1과 c2 및 c3가 기본으로 적용된 집계 
       -c1과 c2가 기본으로 적용된 집계
       -c1과 c3가 기본으로 적용된 집계
       -c2과 c3가 기본으로 적용된 집계
       -c1만 기본으로 적용된 집계
       -c2만 기본으로 적용된 집계
       -c3만 기본으로 적용된 집계
       -전체집계
  .CUBE절에 기술된 컬럼이 n개일때 반환되는 집계의 종류는 2의 n승 가지임

  사용예)2005년 월별,회원별,상품별 판매집계(SUM)를 조회하시오 ->(먼저월별 나누고 그다음 회원별로 그다음 또 쪼개서 상품별로)
      Alias는 월,회원명,상품명,구매수량
  SELECT SUBSTR(A.CART_NO,5,2) AS 월,  
             B.MEM_NAME AS 회원명,
             C.PROD_NAME AS 상품명,
             SUM(A.CART_QTY) AS 구매수량
        FROM CART A, MEMBER B, PROD C
        WHERE SUBSTR(A.CART_NO,1,4)='2005'
          AND A.CART_MEMBER=B.MEM_ID
          AND A.CART_PROD=C.PROD_ID
        GROUP BY CUBE(SUBSTR(A.CART_NO,5,2), B.MEM_NAME, C.PROD_NAME)
        ORDER BY 1;