[210923] ROLLUP, CUBE, GROUPING, GROUPING SETS, 집합 연산자

ROLLUP, CUBE

그룹 별 산출한 결과 값의 집계를 계산하는 함수

 

예시 SELECT JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY ROLLUP(JOB_CODE) ORDER BY 1; --JOB_CODE 기준으로 오름차순 SELECT JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY CUBE(JOB_CODE) ORDER BY 1;

평균에 대한 추가 집계는 평균으로 집계 한다. (엑셀로 확인 하면 편함)

 

부서마다 다 다른 인원수 그것의 값까지 다 포함하여 평균을 낸 것 (월급의 전체 인원 수에 대한 평균인 것 = 6xxx )

부서별 월급 평균에 대한 평균이 아님 (엑셀에서 내가 부서별 평균 냈을때  = 8xxx )

ROLLUP

: 인자로 전달받은 그룹 중 가장 먼저 지정한 그룹별로 추가적 집계 결과 반환

 

부서별로 집계 (급여평균, 인원수) : 널이 있냐 없냐, 부서아이디 30에 직무가 없다면 부서별 집계한 것이므로.

부서, 직무와 결합해서도 (급여평균, 인원수) 를 구한 것 :  부서도 있고 직무도 있다.

SELECT DEPARTMENT_ID      , JOB_ID      , AVG(SALARY) AS 급여평균      , COUNT(*) AS 인원수   FROM EMPLOYEES  WHERE DEPARTMENT_ID IS NOT NULL  GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID);  ROLLUP 결과 : (DEPARTMENT_ID, JOB_ID) -- 부서, 직무별                     (DEPARTMENT_ID)  -- 부서별                     ( ) -- 전체

 

CUBE

: 인자로 지정된 그룹들로 가능한 모든 조합 별로 집계한 결과 반환

 

부서는 널이고 직무에 대한 결과도 구함 다른 부분은 롤업과 동일 

SELECT DEPARTMENT_ID      , JOB_ID      , AVG(SALARY) AS 급여평균      , COUNT(*) AS 인원수   FROM EMPLOYEES  WHERE DEPARTMENT_ID IS NOT NULL  GROUP BY CUBE(DEPARTMENT_ID, JOB_ID); -- 가능한 모든 조합별 집계 CUBE 결과 : (DEPARTMENT_ID, JOB_ID) -- 부서, 직무별                  (DEPARTMENT_ID)  -- 부서별                  (JOB_ID)  -- 직무별                  ( ) -- 전체

 

 

--GROUPING 

ROLLUP이나 CUBE에 의한 집계 산출물이 인자로 전달받은 컬럼 집합의 산출물이면 0 반환 아니면 1 반환

 

GROUPING 함수를 통해 나오는 결과는 0 또는 1

0 : DEPT_CODE 로 집계했다는 뜻 - 0이면 어떤 컬럼 기준 집계인 지 알 수 있는 것

전부다 1 : 기준 모두 해당 안 됨 = 전체 집계한 것

GROUPING 예시 SELECT DEPT_CODE, JOB_CODE, SUM(SALARY),            CASE WHEN GROUPING(DEPT_CODE) = 0 AND  -- 조건 2개인 것, 그리고(AND)~면 '부서별합계'                             GROUPING(JOB_CODE) = 1             THEN '부서별 합계‘            WHEN GROUPING(DEPT_CODE) = 1 AND                      GROUPING(JOB_CODE) = 0             THEN '직급별 합계'            WHEN GROUPING(DEPT_CODE) = 1 AND                      GROUPING(JOB_CODE) = 1             THEN '총 합계'            ELSE '그룹별 합계'       END AS 구분                                          -- 구분이라는 별칭의 컬럼 생성해서 글씨 출력하는 것 FROM EMPLOYEE GROUP BY CUBE(DEPT_CODE, JOB_CODE) ORDER BY 1;

ROLLUP 결과 31 rows   SELECT DEPARTMENT_ID      , JOB_ID      , AVG(SALARY) AS 급여평균      , COUNT(*) AS 인원수      , CASE WHEN GROUPING(DEPARTMENT_ID) = 0 AND GROUPING(JOB_ID) = 1 THEN '부서별 합계'             WHEN GROUPING(DEPARTMENT_ID) = 0 AND GROUPING(JOB_ID) = 0 THEN '부서의 직무별 합계'             ELSE '총합계'  -- ROLLUP(exp1, exp2) 결과는 총 3개         END AS 집계구분   FROM EMPLOYEES  WHERE DEPARTMENT_ID IS NOT NULL  GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID);

CUBE 결과 50 rows    SELECT DEPARTMENT_ID      , JOB_ID      , AVG(SALARY) AS 급여평균      , COUNT(*) AS 인원수      , CASE WHEN GROUPING(DEPARTMENT_ID) = 0 AND GROUPING(JOB_ID) = 1 THEN '부서별'             WHEN GROUPING(DEPARTMENT_ID) = 1 AND GROUPING(JOB_ID) = 0 THEN '직무별'             WHEN GROUPING(DEPARTMENT_ID) = 0 AND GROUPING(JOB_ID) = 0 THEN '부서의 직무별'             ELSE '총합계'  -- CUBE(exp1, exp2) 결과는 총 4개 (2의 2승)         END AS 집계구분   FROM EMPLOYEES  WHERE DEPARTMENT_ID IS NOT NULL  GROUP BY CUBE(DEPARTMENT_ID, JOB_ID);

 

 

Q.    년도별 커미션 유무에 따른 급여 평균 구하기.         년도         커미션유무     평균         1980년도     있음           xxx,xxx         1980년도     없음           xxx,xxx         1980년도                    xxx,xxx         1990년도     있음           xxx,xxx         1990년도     없음           xxx,xxx         1990년도                    xxx,xxx         2000년도     있음           xxx,xxx         2000년도     없음           xxx,xxx         2000년도                    xxx,xxx                                     xxx,xxx */ SELECT SUBSTR(TO_CHAR(HIRE_DATE, 'yyyy'), 1, 3) || '0' AS 년대      , NVL2(COMMISSION_PCT, '있음', '없음') AS 커미션유무      , TO_CHAR(AVG(SALARY), '999,999.99999') AS 평균   FROM EMPLOYEES  GROUP BY ROLLUP(SUBSTR(TO_CHAR(HIRE_DATE, 'yyyy'), 1, 3) || '0'          , NVL2(COMMISSION_PCT, '있음', '없음')); --괄호 잘 닫기, 롤업 안에 두개

---

집합 연산자

여러 개의 SELECT 결과물을 하나의 쿼리로 만드는 연산자

 

 

UNION : 중복 허용하지 않는 합집합

UNION ALL : 중복 허용하는 합집합 

INTERSECT : 교집합

MINUS : 차집합 (작성 순서에 따라 좌항 값이 정해짐)

 

SELECT 부분은 데이터 타입과 작성한 컬럼의 수가 동일해야 한다. (다른 테이블, 별칭 지정은 상관 없음 별칭 - 윗 컬럼을 따른다.)

예시 SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 10 UNION SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; --컬럼 타입이 다른 경우 에러 발생 SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 10 UNION SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; --컬럼의 수가 다른 경우 에러 발생 SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES WHERE DEPARTMENT_ID = 10 UNION SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = 20; --다른 테이블을 조회한 경우 데이터타입과 갯수만 맞다면 가능 SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 10 UNION SELECT LOCATION_ID, DEPARTMENT_NAME, DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_ID = 20;

---

GROUPING SETS

그룹 별로 처리된 여러 개의 SELECT문을 하나로 합친 결과를 원할 때 사용 (집합 연산자 사용과 동일)

 

1. GROUPING SETS(C1, C2)

   결과 : (C1) 으로, (C2) 로 GROUP BY 한 것

2. GROUPING SETS(C3, C4)

   결과 : (C3) 으로, (C4) 로 GROUP BY 한 것

3. GROUPING SETS(C1, C2), GROUPING SETS(C3, C4)

   결과 : (C1, C3) 로 (C1, C4) 로 (C2, C3) 로 (C2 C4) 로 GROUP BY 한 것

SELECT DEPARTMENT_ID      , JOB_ID      , NVL2(COMMISSION_PCT, 'YES', 'NO') AS COMMISSION_YN      , AVG(SALARY) AS AVG_SALARY   FROM EMPLOYEES  WHERE DEPARTMENT_ID IS NOT NULL  GROUP BY GROUPING SETS((DEPARTMENT_ID, JOB_ID)   -- SET 1                       , (DEPARTMENT_ID, NVL2(COMMISSION_PCT, 'YES', 'NO'))   -- SET 2                       , NULL)   -- SET 3 NULL 로 하면 어떤 그룹도 묶지 않은 전체 집계를 구할 수 있음 ORDER BY 1, 2, 3;