[SQL] (오라클 기준) GROUP BY 확장함수(ROLLUP, CUBE)

--부서별 평균급여와 부서 및 직무별 평균 급여를 조회하세요.
SELECT department_id, NULL AS job_id, avg(salary)
FROM employees
GROUP BY department_id
UNION ALL  -- 첫 번째 컬럼을 따라감  ( SELECT 두 번 수행하게 됨 -- cost 두배)
SELECT department_id, job_id, avg(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY 1, 2;
-------------------------ROLLUP(GROUP BY의 확장기능)
SELECT department_id, job_id, avg(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id)  --(GROUP BY 3번 일어남(그룹 안된거 포함))
      -- department_id, job_id
      -- department_id
      -- ()
ORDER BY 1, 2;

SELECT department_id, job_id, avg(salary)
FROM employees
GROUP BY CUBE (department_id, job_id) -- 가능한 모든 조합 (2^n개의 조합)
ORDER BY 1, 2;

SELECT department_id, job_id, avg(salary)
FROM employees
GROUP BY GROUPING SETS(department_id, job_id) --원하는 그룹만 보고 싶을 때
ORDER BY 1, 2;
----ROLL 쓰면 아래와 같음
SELECT department_id, job_id, avg(salary)
FROM employees
GROUP BY GROUPING SETS(department_id, job_id, ())
ORDER BY 1, 2;

SELECT dapartment_id, job_id, avg(salary)
FROM employees
GROUP BY ROLLUP(department_id), CUBE(job_id, manager_id), last_name;
      -- department_id          job_id, manager_id        last_name
      -- ()                     job_id
      --                        manager_id
      --                        ()
      
SELECT CASE WHEN GROUPING(department_id) + GROUPING(job_id) = 2 THEN '전체평균'
            WHEN GROUPING(department_id) = 1 THEN '직무별평균'
            WHEN GROUPING(job_id) = 1 THEN '부서별평균'
                                        ELSE '부서및직무별평균'
       END 분류,
       department_id, job_id, ROUND(avg(salary)),
       GROUPING(department_id) AS GRP_DEPT,  -- 의미없을 때 1(비트값을 십진수로 반환)
       GROUPING(job_id) AS GRO_JOB
FROM employees
GROUP BY ROLLUP(department_id, job_id)
ORDER BY 1, 2;

SELECT department_id, job_id, SUM(salary),
       GROUPING(department_id) AS GRP_DEPT,
       GROUPING(job_id) AS GRP_JOB,
       GROUPING_ID(department_id, job_id) AS GRP_ID  -- 3 = 10 + 01
FROM employees
GROUP BY ROLLUP(department_id, job_id);

SELECT CASE GROUPING_ID(department_id, job_id)
            WHEN 1      THEN '부서별평균급여'
            WHEN 2      THEN '직무별평균급여'
            WHEN 3      THEN '전체평균'
                        ELSE '부서및직무별평균'
       END 분류,
       department_id, job_id, ROUND(avg(salary)),
       GROUPING(department_id) AS GRP_DEPT,
       GROUPING(job_id) AS GRP_JOB
FROM employees
GROUP BY ROLLUP(department_id, job_id);