[오라클] 그룹화 관련된 여러 함수 group / cube / grouping sets/ grouping : rollup,cube/ grouping_id: rollup,cube 예제

 

'Rollup'  소계, 합계를 자동으로 뽑을 때 사용 GROUP BY 후 기준으로 사용할 컬럼을 ROLLUP(컬럼) 으로 묶어주는 식

예) select deptno, job, count(*),max(sal), sum(sal),avg(sal)
    from emp
    group by rollup(deptno,job);

'CUBE'  함수는 계산 가능한 모든 소계와 합계 를 반환한다.

예)  select deptno, job, count(*),max(sal), sum(sal),avg(sal)
     from emp
     group by cube(deptno, job) order by deptno,job;

'GROUPING SETS' 지정한 각 열 별 그룹화!  소괄호 안에 그룹화할 칼럼을 먼저 넣어서 하나의 그룹을 만들어주고 해당 그룹의 각각 칼럼을 이용해서 필요한 데이터만 선택해서 출력 가능함. "(  )" 괄호 문자를 이용하면 전체 함산 데이터를 받을 수 있음

예) select deptno, job, count(*)
     from emp
     group by grouping sets(deptno,job) order by deptno, job;

'GROUPING_ID' (속성1, 속성2) : 속성1과 속성2의 GROUPTING 여부를 0,1로 2진수로 표현해서 10진수 화

예) select deptno, job, count(*), sum(sal), grouping(deptno), grouping(job), grouping_id(deptno,job)
from emp
group by cube(deptno,job) order by deptno,job;

LISTAGG( [합칠 컬럼명], [구분자]) WITHIN GROUP(ORDER BY [정렬 컬럼명])

직책별 급여 역순으로 검색하시오

예) select job, listagg(ename,',')
     within group(order by sal desc) as enames
     from emp
     group by job; 

 

 

 

예제

--9. 부서별, 직책별 평균 급여를 구하되 급여 평균이 1000불 이상안 경우 출력
select deptno, job, avg(sal) from emp group by deptno,job having avg(sal)>= 1000; 
//모았을 때의 그룹을 쓸 때는 having 절에서 사용

--10.부서별, 직책별 평균 급여를 구하되 급여가 2500불 이하이고, 급여평균이 2000불 이상인 경우 출력
select deptno, job, avg(sal) from emp where sal <=2500 group by deptno, job having avg(sal)>=2000 order by deptno,job;

--11. 부서별,직책별 소계 및 총계 
select deptno, job, count(*),max(sal), sum(sal),avg(sal) from emp group by rollup(deptno,job);

--12 cube는 각 부서별, 직책별 소계와 총계까지 나온다. 
select deptno, job, count(*),max(sal), sum(sal),avg(sal) from emp group by cube(deptno, job) order by deptno,job;

--13. 부서별 소계  안나옴 나는 찾아보기
select deptno,job, count(*) from emp group by deptno,rollup(job);

--14.직책 부서별 인원 소계 
select deptno, job, count(*) from emp group by job,rollup(deptno);

--15. 부서별, 직책별 소계만
select deptno, job, count(*) from emp group by grouping sets(deptno,job) order by deptno, job;

--16. --0: 그룹화 o 1:그룹화 x
select deptno,job,count(*),max(sal),sum(sal), grouping(deptno), grouping(job) from emp group by cube(deptno,job) order by deptno,job;

--17.decode(속성,조건1,값1,조건2, 값2, 조건3,값3,,,그외 값)
select decode(grouping(deptno),1,'all_deptno',deptno) as deptno, decode(grouping(job),1,'all _job',job) as job, count(*), max(sal),sum(sal),avg(sal)
from emp group by cube(deptno,job) order by deptno, job;

--18. grouping_id(속성1, 속성2) : 속성1과 속성2의 grouping 여부를 0,1로 2진수로 표현해서 10진수화
select deptno, job, count(*), sum(sal), grouping(deptno), grouping(job), grouping_id(deptno,job)
from emp group by cube(deptno,job) order by deptno,job;

--19.부서번호별,이름별,츨력
select deptno,ename from emp group by deptno,ename order by deptno,ename;

--20.부서별 이름을 급여 역순으로 가로 방향으로 검색
select deptno,listagg(ename,',') within group(order by sal desc) as enames
from emp group by deptno;

--21 각 부서별 입사순으로 방향 검색
select deptno,listagg(ename,',') within group(order by hiredate) as enames
from emp group by deptno;

--22.직책별 급여 역순으로 검색
select job,listagg(ename,',') within group(order by sal desc) as enames from emp 
group by job;

--23.부서별 직책별 최고 급여
select deptno,job,max(sal) from emp group by deptno, job order by deptno,job;

--24.피벗테이블(2차원 테이블)
select * from (select deptno,job,sal from emp) pivot(max(sal) 
for deptno in(10,20,30)) order by job;

--25. 
select * from (select job, deptno, sal from emp) pivot(max(sal) 
for job in('CLERK' as clerk,'SALESMAN' as salesman,'PRESIDENT' as president,'MANAGER' as manger,'ANALIST' as analist))
order by deptno;

--26. 
SELECT DEPTNO,MAX(DECODE(JOB,'CLIERK', SAL)) AS CLOERK,
            MAX(DECODE(JOB,'SALESMAN', SAL)) AS SALESMAN,
            MAX(DECODE(JOB,'PRESIDENT', SAL)) AS PRESIDENT,
            MAX(DECODE(JOB,'MANAGER', SAL)) AS MANAGER,
            MAX(DECODE(JOB,'ANALYST', SAL)) AS ANALYST
FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

--27.직책별 입사역순으로 가로 방향으로 사원명을 검색 
SELECT JOB, LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY HIREDATE DESC) AS ENAMES FROM EMP GROUP BY JOB;

--28. 부서별 직책별 최고급여
SELECT DEPTNO,JOB,MAX(SAL)FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO,JOB;

--29 UNPIVOT
SELECT * FROM (SELECT DEPTNO,MAX(DECODE(JOB,'CLOERK',SAL)) AS CLERK,
                             MAX(DECODE(JOB,'SALESMAN',SAL)) AS SALESMAN,
                             MAX(DECODE(JOB,'PRESIDENT',SAL)) AS PRESIDENT,
                             MAX(DECODE(JOB,'MANAGER',SAL)) AS MANAGER,
                             MAX(DECODE(JOB,'ANALYST',SAL)) AS ANALYST
FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO) 
UNPIVOT(SAL FOR JOB IN(CLERK,SALESMAN,PRESIDENT,MANAGER,ANALYST)) ORDER BY DEPTNO,JOB;
--UNPIVOT할때는 '' 제외한다. 

--30.부서별 급여평균,(단,소수이하 버림),최대급여,최소급여, 사원수
select deptno,trunc(avg(sal))as avg_sal,max(sal) as max_sal ,min(sal)as min_sal,count(*)as cnt from emp group by deptno;

--31.각 사원의 이름,입사일, 입사년도 출력
select ename,hiredate,to_char(hiredate,'yyyy')as hire_year from emp;

--32.입사년도별 부서별 인원수
select to_char(hiredate,'yyyy') as hire_year, deptno,count(*) as cnt from emp group by to_char(hiredate,'yyyy'),deptno;
select to_char(hiredate,'yyyy') as hire_year, deptno,count(*) as cnt from emp group by hire_year,deptno;  --오류남 실행되는 순서가 group by가 먼저임★

--select문 실행 순서: from -> where ->group by->having ->select ->order by select에서 사용한 별칭이 group by에서 사용 x

--33.보너스(null이 아닌 경우과 null인 경우)별 사원의 수, 보너스가 null이 아니면 o, null이면 x 출력
--nvl(속성,null인 경우 값) nvl2(속성, null아닌 경우값, null인 경우 값)
select nvl2(comm,'o','x') as exist_comm, count(*) as cnt from emp
group by nvl2(comm,'o','x');