[50~59]SUM OVER, RATIO_TO_REPORT, CUBE, ROW_NUMBER, ROWNUM, Simple TOP-n Queries, EQUI JOIN, NON EQUI JOIN

[50] 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)

 

SELECT empno, ename, sal, 
    SUM(SAL) OVER (ORDER BY empno ROWS BETWEEN UNBOUNDED PRECEDING 
    AND CURRENT ROW) 누적치
FROM emp
WHERE job in ('ANALYST', 'MANAGER');
--월급의 누적치를 출력

--OVER다음의 괄호 안에는 값을 누적할 윈도우를 지정할 수 있다.
--ORDER BY empno를 통해 사원 번호를 오름차순으로 정렬을 하고 정렬된 것을 기준으로 월급의 누적치를 출력
--ROWS : 윈도우 기준
--윈도우 방식 : UNBOUNDED PRECEDING(맨 첫 번째 행을 가리킴), UNBOUNDED FOLLOWING(맨 마지막 행을 가리킴), CURRENT ROW(현재 행을 가리킴)

select empno, ename, sal, job,
    sum(sal) over (order by empno rows between unbounded preceding and current row) 누적
from emp;
where job in ('ANALYST', 'MANAGER')
;

[51] 데이터 분석 함수로 비율 출력하기(RATIO_TO_REPORT)

 

--20번 부서 번호 내에서 자신의 월급 비율이 어떻게 되는지 출력
SELECT deptno, empno, ename, sal, 
    RATIO_TO_REPORT(sal) OVER () as 비율
FROM emp
WHERE deptno = 20;

--20부서 번호인 사원들의 월급을 20번 부서 번호인 사원들의 전체 월급으로 나누어 출력한다. 
--RAITIO_TO_REPORT(sal)의 결과와 동일하게 출력
select deptno, empno, ename, sal, 
    RATIO_TO_REPORT(sal) OVER () as 비율,
    sal/sum(sal) over () as 전체비율
from emp
where deptno = 20;

 

[52] 데이터 분석 함수로 집계 결과 출력하기(1)_(ROLLUP)

 

--직업과 직업별 토탈 월급을 출력하는데, 맨 마지막 행에 토탈 월급을 출력
select job, sum(sal)
    from emp
group by rollup(job);
--직업과 직업별 토탈 월급을 출력하는 쿼리에 ROLLUP만 붙여주면 전체 토탈 월급을 추가적으로 볼 수 있다.

select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);
--ROLLUP(deptno, job)으로 Rollup에 컬럼을 2개 사용하면 집계 결과는 컬럼의 개수(2개)+1로 3개가 출력됩니다.

 

[53] 데이터 분석 함수로 집계 결과 출력하기(2)_ (CUBE)

 

--직업, 직업별 토탈 월급을 출력하는데, 첫 번째 행에 토탈 월급을 출력
select job, sum(sal)
from emp
group by cube(job);

--위에 직업별 토탈 월급 출력 (토탈월급, 직업별토탈월급, 부서별 토탈월급)
select deptno,job, sum(sal)
from emp
group by cube(deptno, job);

 

[54] 데이터 분석 함수로 집계 결과 출력하기(3)_ GROUPING SETS

 

--부서 번호와 직업, 부서 번호별 토탈 월급과 직업별 토탈 월급, 전체 토탈 월급을 출력
select deptno, job, sum(sal)
from emp
group by grouping sets((deptno), (job), ());
--grouping sets괄호 안에 다음고 ㅏ같이 집계하고 싶은 컬럼명을 기술하면, 기술한대로 결과를 출력
select deptno, sum(sal)
from emp
group by grouping sets((deptno), ());

select deptno, sum(sal)
from emp
group by rollup(deptno);

 

[55] 데이터 분석 함수로 출력 결과 넘버링 하기(ROW_NUMBER)

 

select empno, ename, sal, 
    rank() over (order by sal desc) rank, 
    dense_rank() over (order by sal desc) dense_rank,
    row_number() over (order by sal desc) 번호
from emp
where deptno = 20;
--row_number()는 출력되는 각 행에 고유한 숫자 값을 부여하는 데이터 분석 함수이다.
--PSEUDOCOLUMN인 ROWNUM과 유사하며 RANK와 DENSE_RANK분석 함수와는 다르다.
--위 결과를 보면 첫 번쨰 행인 FORD와 두 번째 행인 SCOTT의 월급이 서로 같아 PANK와 DENSE_RANK는 순위를 둘 다 1로 출력하고 있으나
--ROW_NUMBER는 1, 2로 출력하고 있다. ()안에 반드시 ORDER BY  를 지정해야된다. 
 select empno, ename, sal, row_number() over (order by sal) 번호
 from emp;
 
 --patition by 를 사용하여 부서 번호별로 파티션해서 순위를 부여한다.
 select empno, ename, sal, row_number() over (partition by deptno order by sal desc) 번호
 from emp
 where deptno = 20;
 
select deptno,empno, ename, sal, row_number() over (partition by deptno order by sal desc) 번호
from emp
where deptno in (20, 30);

[56] 출력되는 행 제한하기(1)_(ROWNUM)

 

--사원 테이블에서 사원 번호, 이름, 직업, 월급을 상담 5개의 행만 출력
select rownum, empno, ename, job, sal
from emp
where rownum <= 5;
--ROWNUM은 PSEUDO COLUNM으로 '가짜의'라는 뜻 그대로 *로 검색해서는
--출력되지 않는 감춰진 컬럼이다.
--대용량 테이블의 데이터 상단 행만 잠깐 살펴볼 때 유용하게 사용할 수 있다.

[57] 출력되는 행 제한하기(2)_(Simple TOP-n Queries)

 

--월급이 높은 사원순으로 사원 번호, 이름, 직업, 월급을 4개의 행으로 제한해서 출력
select empno, ename, job, sal
from emp
order by sal desc fetch first 4 rows only;
--위의 sql을 TOP-N Query라고 한다. 
--정렬된 결과로부터 위쪽 또는 아래쪽의 N개의 행을 반환하는 쿼리이다.
--56번 예제의 ROWNUM을 이용해 같은 출력 결과를 보려면 뒤에서 배울 fROM절의 서브 쿼리를 사용해야 하므로 SQL이 다소 복잡해진다.
--그런데 FETCH FIRST N ROWS ONLY 는 단순하게 위의 결과를 출력할 수 있다.

--월급이 높은 사원들 중 20&에 해당하는 사원들만 출력하는 쿼리
select empno, ename, job, sal
from emp
order by sal desc
fetch first 20 percent rows only;

--with ties 옵션을 이용하면 여러 행이 N번째 행의 값과 동일하다면 같이 출력한다.
select empno, ename, job, sal
from emp
order by sal desc
fetch first 2 rows with ties;
--2개의 행이 출력되지 않고 실제로는 3개의 행이 출력이되고있다.
--세번째 행의 월급 3000이 세번째 행의 3000과 동일하기 때문에 출력이된다.

--offset 을 이용하면 출력이 시작되는 행의 위치를 지정할 수 있다.
select empno, ename, job, sal
from emp
order by sal desc offset 9 rows;
--위의 결과에서 시작 행인 사원 번호 7521는 월급이 1250으로 사원 테이블 전체 사원 중에
--월급이 10번째 로 높은 사원이다. 10번째 행부터 끝까지 결과를 출력한다
--offset과 fetch를 다음과 같이 서로 조합해서 사용할 수 있다.
select empno, ename, job, sal
from emp
order by sal desc offset 9 rows
fetch first 2 rows only;
--offset 9로 출력된 5개의 행 중에서 2개의 행만 출력

select empno, ename, job, sal
from emp
order by sal desc offset 5 rows
fetch first 2 rows only;

 

[58] 여러 테이블의 데이터를 조인해서 출력하기(1)_(EQUI JOIN)

 

select ename, loc
from emp, dept
where emp.deptno = dept.deptno;
--서로 다른 테이블에 있는 컬럼들을 하나의 결과로 출력하려면 조인(JOIN)을 사용해야된다.
--ename, emp 테이블에 있고 loc는 dept 테이블에 존재하므로 
--ename과 loc를 하나의 결과로 출력하기 위해서는 from 절에 emp와 dept 둘 다 기술한다
--emp와 dept를 조인하기 위해서는 조인 조건이 있어야 한다.
--조인 조건은 두 개의 테이블을 연결하기 위한 연결고리이다. emp 테이블에도 deptno가 존재하고
--dept 테이블에도 depno가 존재하므로 emp테이블의 부서 번호는 dept테이브르이 부서 번호와 같다 라는 조건
--emp.deptno=dept.deptno를 주어 조인을 수행한다.

 

[59] 여러 테이블의 데이터를 조인해서 출력하기(2)_(NON EQUI JOIN)

 

--사원emp 테이블과 급여 등급salgrade 테이블을 조인하여 이름, 월급, 급여 등급을 출력
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

--sagrade 테이블은 급여 등급 테이블이다.
--grade는 등급이고 losal은 등급을 나누는 월급 범위의 하단,
--hisal은 월급 범위의 상단을 나타낸다. 5등급이 제일 높은 등급이다.
select * from salgrade;

--emp테이블의 sal 컬럼은 salgrade 테이블의 losal과 hisal 컬럼 사이에 있다.
select e.ename, e.sal, s.grade
from emp e, salgrade s 
where e.sal BETWEEN s.losal and s.hisal