[Oracle] 데이터 분석 함수 2 - column/row

데이터 분석 함수

• listagg : 데이터를 가로로 출력
• lag / lead : 이전 / 다음 행 출력
• sum+decode : column을 row로 출력
• pivot : row를 column으로 출력
• unpivot : column을 row로 출력

 

 

 

 

예제 1. 부서 번호, 부서 번호 별로 속한 사원들의 이름을 가로로 출력

select deptno, listagg(ename, ',') within group (order by ename asc) 이름
    from emp
    group by deptno;

 

 

 

예제 2. 사원 번호, 이름, 월급을 출력하는데, 월급의 그 전 행과 다음 행을 함께 출력

select empno, ename, sal, lag(sal, 1) over (order by sal asc) 이전행
                        , lead(sal, 1) over (order by sal asc) 다음행
    from emp;

 

 

 

예제 3. 직업, 직업 별 토탈 월급을 가로로 출력

select sum(decode(job, 'ANALYST', sal, null)) as "ANALYST"
       , sum(decode(job, 'CLERK', sal, null)) as "CLERK"
       , sum(decode(job, 'MANAGER', sal, null)) as "MANAGER"
       , sum(decode(job, 'SALESMAN', sal, null)) as "SALESMAN"
       , sum(decode(job, 'PRESIDENT', sal, null)) as "PRESIDENT"
from emp;

 

 

 

예제 4. 입사한 년도(4자리)별, 부서 번호 별 토탈 월급을 출력

select to_char(hiredate, 'RRRR') hire_yea
       , sum(decode(deptno, 10, sal, 0)) as "10"
       , sum(decode(deptno, 20, sal, 0)) as "20"
       , sum(decode(deptno, 30, sal, 0)) as "30"
    from emp
    group by to_char(hiredate, 'RRRR');

 

 

 

예제 5. 부서 번호, 부서 번호 별 토탈 월급을 가로로 출력

select *
    from (select deptno, sal from emp)
    pivot (sum(sal) for deptno in (10, 20, 30));

from 절을 실행하면서 결과를 보기 위해 필요한 데이터인 부서 번호와 월급 데이터만 가져온다.

그 다음에 pivot 문을 수행하는데, pivot 의 뜻이 '회전하다' 이다.

즉, 세로 출력이 아니라 가로로 출력 되는 결과를 보고 싶다는 뜻이다.

 

 

 

예제 6. 직업, 직업 별 토탈 월급을 가로로 출력

select *
    from (select job, sal from emp)
    pivot (sum(sal) for job in ('ANALYST' as "ANALYST", 
                                'CLERK' as "CLERK", 
                                'MANAGER' as "MANAGER", 
                                'PRESIDENT' as "PRESIDENT", 
                                'SALESMAN' as "SALESMAN"));

출력 되는 결과에 싱글 쿼테이션 마크가 안 나오게 하려면, as 다음에 더블 쿼테이션 마크를 감싼 직업명을 쓰면 된다.

 

 

select *
    from (select job, sal from emp)
    pivot xml (sum(sal) for job in (select distinct job from emp));

pivot xml 이라고 하고 in 다음에 괄호 안에다가 직업을 중복 제거해서 출력하는 쿼리문을 작성한다.

xml 코드가 결과로 출력 되는데, xml 코드를 메모장에 .html 로 저장해서 열어보면 데이터가 보인다.

 

 

 

 

select *
    from 테이블명
    unpivot (컬럼명1 for 컬럼명2 in (데이터로 변경할 컬럼명 기술));

 

 

예제 7. order 테이블의 컬럼을 unpivot 문을 이용해서 행으로 구성

create  table  order
( ename     varchar2(10),
  bicycle   number(10),
  camera    number(10),
  notebook  number(10) );

insert  into  order  values('SMITH', 2, 3, 1);
insert  into  order  values('ALLEN', 1, 2, 3);
insert  into  order  values('KING', 3, 2, 2 );

commit;

select *
    from order
    unpivot (건수 for 아이템 in (bicycle, camera, notebook));

 

 

 

예제 8. 위에서 출력 되는 결과를 테이블 items 로 생성

create table items
as
select *
    from order
    unpivot (건수 for 아이템 in (bicycle, camera, notebook));

commit;

create table 테이블명 as 하고서 다음에 작성한 쿼리의 결과대로 테이블이 생성된다.