[40~49] COUNT, RAN, DENSE_RANK, NTILE, CUME_DIST, LISTAGG, LAG, LEAD, SUM+DECODE, PIVOT, UNPIVOT

[40] 건수 출력하기(COUNT)

select count(empno)
from emp;
-- 전체 사원수 출력
--count함수는 건수를 세는 함수

select count(comm)
from emp;
--null값은 count하지 않는다.

 

[41] 데이터 분석 함수로 순위 출력하기(1)_(RAN)

 

--직업이 ANALYST, MANAGER 인 사원들의 이름, 직업, 월급, 월급의 순위를 출력
select job, sal, ename, RANK() over (order by sal desc) 순위
from emp
where job in ('ANALYST', 'MANAGER');
--RANK()는 순위를 출력하는 데이터 분석 함수이다.
--RANK() 뒤에 OVER 다음에 나오는 괄호 안에 출력하고 싶은 데이터를 정렬하는 SQL 문장을 넣으면 그 컬럼 값에 대한 데이터의 순위가 출력된다.
--위의 예제에서는 월급이 높은 사원부터 출력되게 ORDER BY절을 사용하였으므로 월급이 높은 순서부터 순위가 부여되어 출력된다.
--1등 다음 바로 2등을 출력하고자 할 때는 :DENSE_RANK 함수를 이용하면된다.:
select job, sal, ename, DENSE_RANK() over (order by sal desc) 순위
from emp
where job in ('ANALYST', 'MANAGER');

--직업별로 묶어서 순위를 부여하기 위해 ORDER BY 앞에 PARTITION BY job을 사용한다.
select ename, sal, job, rank() over (partition by job order by sal desc) as 순위
from emp;

[42] 데이터 분석 함수로 순위 출력하기(2)_(DENSE_RANK)

 

--직업이 ANALYST, MANAGER 인 사원들의 이름, 직업, 월급, 월급의 순위를 출력, 순위1위인 사원 두명이 있을 경우 다음 순위가 2위로 출력
select ename, job, sal, rank() over (order by sal desc) as 월급순위,
dense_rank() over (order by sal desc) as 월급순위2
from emp
where job in ('ANALYST', 'MANAGER');

--81년도에 입사한 사원들의 직업, 이름. 월급, 순위를 출력, 직업별로 월급이 높은 순서대로 순위를 부여한 쿼리
select ename, job, sal, hiredate, dense_rank() over (partition by job order by sal desc) as 월급순
from emp
where hiredate < to_date('82/01/01', 'RR/MM/DD')
and hiredate > to_date('80/12/31', 'RR/MM/DD');

select ename, job, sal, hiredate, dense_rank() over (partition by job order by sal desc) as 월급순
from emp
where hiredate between to_date('81/01/01', 'RR/MM/DD')
and to_date('81/12/31', 'RR/MM/DD');

--DENSE_PRANK 바로 다음에 나오는 괄호에도 다음과 같이 데이터를 넣고 사용할 수 있다.
SELECT DENSE_RANK(2975) within group (ORDER BY sal DESC) 순위
FROM emp;
--WITHIN 은 ~이내 라는 뜻이다.
--어느 그룹이 바로 group 바로 다음에 다오는 괄호 안의 문법이다.
--월급이 높은 순서대로 정렬해 놓은 데이터의 그룹 안에서 2975의 순위를 출력하는 것이다.

--입사일 81년 11월 17일인 사원 테이블 전체 사원들 중 몇번째로 입사한 것인지 출력
select dense_rank('81/11/17') within group (order by hiredate asc) 순위
from emp;

select *
from emp
order by hiredate asc;

 

[43] 데이터 분석 함수로 등급 출력하기(NTILE)

 

--이름과 월급, 직업, 월급의 등급을 출력. 월급의 등급은 4드읍으로 나눠 1등급(0~25%), 2등급(25~50%), 3등급(50~75%), 4등급(75~100%)으로 출력
select ename, job,sal,
    ntile(4) over (order by sal desc nulls last) 등급
from emp
where job in ('ANALYST', 'MANAGER', 'CLERK');
--order by sal desc 에서 nulls last는 월급을 높은 것부터 출력되도록 정렬하는데,
--null을 맨 아래에 출력하겠다는 의미이다.
select ename, job,sal,
    ntile(4) over (order by sal desc) 등급
from emp
where job in ('ANALYST', 'MANAGER', 'CLERK');

select ename, comm
from emp
where deptno = 30
order by comm desc;

select ename, comm
from emp
where deptno = 30
order by comm desc nulls last;
--null last를 사용했을 때는 null 값이 마지막으로 정렬된다.

 

[44] 데이터 분석 함수로 순위의 비율 출력하기(CUME_DIST)

 

-- 이름과 월급, 월급의 순위, 월급의 순위 비율을 출력
select ename, sal, 
    rank() over (order by sal desc) as 순위1,
    dense_rank() over (order by sal desc) as 순위2,
    cume_dist() over (order by sal desc) as 순위3
from emp;

select ename, sal, 
    rank() over (order by sal desc) as 순위1,
    dense_rank() over (order by sal desc) as 순위2,
    round(cume_dist() over (order by sal desc), 3) as 순위3
from emp;

--partition by job 을 사용해 직업뵬로 cume_dist를 출력
select job, ename, sal, 
rank() over (partition by job order by sal desc) as 순위,
cume_dist() over (partition by job order by sal desc) as 직업순위
from emp;

 

[45] 데이터 분석 함수로 데이터를 가로로 출력하기(LISTAGG)

 

--부서 번호 출력, 부서 번호 옆에 해당 부서에 속하는 사원들의 이름을 가로로 출력
select deptno, listagg(ename, ',') within group (order by ename) as employee
from emp
group by deptno;
--LISTAGG 함수는 데이터를 가로로 출력하는 함수이다.
--LISTAGG 에 구분자로 콤마를 이용하여 이름이 콤마로 구분되게 출력
--within group 은 ~이내의 라는 뜻으로 group 다음에 나오는 괄호에 속한 그룹의 데이터를 출력
--order by ename 으로 이를 abcd순으로 정렬되어 출력

--직업과 그 직업에 속한 사원들의 이름을 가로로 출력
select job, listagg(ename, ',') within group (order by ename asc) as employee
from emp 
group by job;

--각 직업별로 월급의 분포가 어떻게 되는지 한눈에 출력
select job, 
    listagg(ename||'('||sal||')',',') within group (order by ename asc) as empjob
from emp
group by job;

 

[46] 데이터 분석 함수로 바로 전 행과 다음 행 출력하기(LAG, LEAD)

 

--사원번호, 이름, 월급 출력, 그옆에 바로 전 행의 월급 출력, 또 옆에 바로 다음 행의 월급 출력
select empno, ename, sal, 
    lag(sal, 1) over (order by sal asc) "전 행",
    lead(sal, 1) over (order by sal asc) "다음 행"
from emp
where job in ('ANALYST', 'MANAGER');
--lag 함수는 숫자2 를 사용하면 바로 전 숫자인 1을 출력한다.(숫자=행)
--lead 함수는 숫자 2를 사용하면 바로 다음 숫자인 3을 출력한다.(숫자=행)

--'ANALYST', 'MANAGER' 직업인 사원 바로 다음에 입사한 사원의 입사일을 출력
select empno, ename, hiredate,
    lag(hiredate, 1) over (order by hiredate asc) "이전에 사원",
    lead(hiredate, 1) over (order by hiredate asc) "다음 입사한 사원"
from emp
where job in ('ANALYST', 'MANAGER');

--입사한 사원의 입사일을 출력, 부서 번호별로 구분해서 출력
select deptno, empno, ename, hiredate,
    lag(hiredate, 1) over (partition by deptno order by hiredate asc) "이전에 사원",
    lead(hiredate, 1) over (partition by deptno order by hiredate asc) "다음 입사한 사원"
from emp;

 

[47] COLUMN 을 ROW 로 출력하기(1)_(SUM+DECODE)

 

--부서 번호, 부서 번호별 토탈 월급을 출력, 가로로 출력
select sum(decode(deptno, 10, sal)) as "10",
    sum(decode(deptno, 20, sal)) as "20",
    sum(decode(deptno, 30, sal)) as "30"
from emp;

--부서 번호가 10번이면 월급이 출력 아니면 null이 출력
select deptno, decode(deptno, 10, sal) as "10"
from emp;

--deptno 컬럽을 제외하고 decode(deptno, 10, sal)만 출력한 다음 출력된 결과 값을 다 더해서 출력
select
sum(decode(deptno, 10, sal)) as "depno제외하고 더한 값"
from emp;

--직업별 토탈 월급을 가로로 출력
select
sum(decode(job, 'ANALYST', sal)) as "ANALYST",
sum(decode(job, 'CLERK', sal)) as "CLERK",
sum(decode(job, 'MANAGER', sal)) as "MANAGER",
sum(decode(job, 'SALESMAN', sal)) as "SALESMAN"
from emp;
--직업을 모른다고 가정하고 출력되게 하려면 PL/SQL을 사용해야된다.

--deptno 를 그룹 함수와 같이 나욜하였으므로 group by deptno 를 추가하여 실행
select
sum(decode(job, 'ANALYST', sal)) as "ANALYST",
sum(decode(job, 'CLERK', sal)) as "CLERK",
sum(decode(job, 'MANAGER', sal)) as "MANAGER",
sum(decode(job, 'SALESMAN', sal)) as "SALESMAN"
from emp
group by deptno;

[48] COLUMN을 ROW로 출력하기(2)_(PIVOT)

--부서 번호, 부서 번호별 토탈 월급을 Pivot문을 사용하여 가로로 출력
select *
from (select deptno, sal from emp)
pivot (sum(sal) for deptno in (10, 20, 30));
--sum과 decode를 이용해 출력한 결과를 pivot문을 이용하면 좀더 간편한 쿼리문으로 출력할 수 있다.
--from 절에서 추출한 부서 번호와 월급을 가지고 부서 번호멸 토탈 월급을 출력한다.
--부서 번호는 10, 20, 30번에 대한 것을 출력한다.

--문장형 데이터, pivot문을 이용해서 직업과 직업별 토탈 월급을 가로로 출력
select * 
from (select job, sal from emp)
pivot (sum(sal) for job in ('ANALYST', 'CLERK', 'MANAGER', 'SALESMAN'));

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

 

[49] ROW를 COLUMN으로 출력하기(UNPIVOT)

 

--UNPIVOT문을 사용하여 컬럼을 로우로 출력
SELECT *
 FROM order2
 UNPIVOT (건수 for 아이템 in (BICYCLE, CAMERA, NOTEBOOK));

select * from order2;
--unpivot문은 pivot문과느 반대로 열을 행으로 출력한다.
--테이블 order2의 열인 BICYCLE, CAMERA, NOTEBOOK이 출력 결과에서 행으로 출력되고 있다.

--'건수'는 가로롤 저장되어 있는 데이터를 세로로 unpivot 시킬 출력 열 이름이다.
--이 열 이름은 임의로 지정하면 된다.
--for 다음에 '아이템'은 가로로 되어 있는 order2 테이블의 컬럼명을 unpivot 시켜 세로로 출력할 열 이름이다.
--이 열 이름도 임의로 지정하면 된다.
select * 
from order2
unpivot (건수 for 아이템 in (BICYCLE as 'B', CAMERA as 'C', NOTEBOOK as 'N'));

--order2 테이블의 데이터에 null이 포함되어있으면 unpivot된 결과에서 출력이 되지 않는다.
--update문을 실행하여 smith의 notebook을 null로 변경하고 unpivot문을 다시 실행한다.
update order2 set notebook=null where ename='SMITH';
commit;

select * from order2;
--smith의 notebook 정보가 출력되지 않는다. 이럴 때 null값인 행도 결과에 포함 시키려면 다음과 같이 include nulls를 사용해야된다.
select *
from order2
unpivot include nulls (건수 for 아이템 in (bicycle, camera, notebook));

 

 

====== 49 예제 스크립트 . txt 내용 ======

drop  table order2;

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

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

commit;


SELECT *
 FROM order2
 UNPIVOT (건수 for 아이템 in (BICYCLE, CAMERA, NOTEBOOK));

============