데이터 분석 함수

sum over : 누적 데이터 출력

sum(컬럼명) over (order by 기준 컬럼)

sum(칼럼명) over () -- 전체 누적값 출력

  select empno, ename, sum(sal) over (order by empno) 누적치
  from emp;

-- 사원번호를 기준으로 정렬한 것에 대해서 월급 누적치를 출력함

order by 기준 컬럼 rows between unbounded preceding and current row

• 원래 전체
• 원래 디폴트로 지정되어 있어서, 쓰지 않아도 되지만 되도록이면 명시

unbounded preceding : 제일 첫번쩨 컬럼
current row : 현재 행

  select empno, ename, sum(sal) over (order by empno rows between unbounded preceding and current row) 누적치
  from emp;

원래는 이와 같이 작성해야 함

ratio_to_report : 비율 출력

ratio_to_report(컬럼명) over()

select empno, ename, sal, round(ratio_to_report(sal) over(), 2) 비율
from emp
where deptno = 20;

rollup : 집계 결과 출력 (맨 아래에 출력)

group by 절에 rollup(기준 컬럼)

맨 아래에 전체 칼럼 값을 더한 값을 출력함

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

cube : 집계 결과 출력 (맨 위에 출력)

group by 절에 cube(기준 컬럼)

  select to_char(hiredate, 'RRRR'), sum(sal)
  from emp
  group by cube(to_char(hiredate, 'RRRR'));

grouping sets : 집계 결과 출력 (여러 칼럼의 집계 결과 종합 출력)

group by 절에 grouping sets((기준 컬럼1), (기준 컬럼2) ....,())

-- () : 전체 집계 결과 출력 시

grouping sets 안에서 괄호()의 역할은 기준 칼럼들이 동시에 묶인 결과를 출력할 지, 단일 결과를 출력할 지를 결정하는 중요한 역할을 함

  select deptno, job, sum(sal)
  from emp
  group by grouping sets((deptno), (job),())

  select deptno, job, sum(sal)
  from emp
  group by grouping sets((deptno, job),()) -- 이렇게 묶어서 집계 결과를 출력할수도 있음 

grouping sets 안에서 괄호()의 역할은 기준 칼럼들이 동시에 묶인 결과를 출력할 지, 단일 결과를 출력할 지를 결정하는 중요한 역할을 함

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

  select job, sum(sal)
  from emp
  group by grouping sets((job),());

위 두개의 쿼리는 동일한 형태의 출력
(grouping sets으로 구현할 시, 해석이 더 용이함)

  select to_char(hiredate,'RRRR'), job, sum(sal)
  from emp
  group by grouping sets((to_char(hire_date,'RRRR')), (job));

row_number : 출력 결과 넘버링

row_number() over (order by 기준 컬럼)

서브쿼리를 이용해서, 순서상 몇 번째의 칼럼만 출력하고 싶을 때 사용

  select empno, ename, sal, rank() over (order by sal desc) as rank,
                          dense_rank() over (order by sal desc) as dense_rank,
                          row_number() over (order by sal desc) as 번호 -- 출력된 결과의 sal 칼럼에 대해서 내림차순으로 넘버링
  from emp
  where deptno = 20;

아래는 전체 사원을 대상으로 사원 번호에 대해서 넘버링하는 쿼리

  select empno, ename, sal, row_number() over (order by empno asc) as 번호
  from emp;

예제) 월급이 1000에서 3000인 사원들에 대해서 넘버링된 칼럼을 출력

select ename, sal, row_number() over (order by empno asc) as 번호
from emp
where sal between 1000 and 3000;

ROWNUM : 출력되는 행 제한

ROWNUM은 쉐도우 컬럼으로, 어느 테이블에든 존재하지만 'SELECT * '로는 보여지지 않음

SELECT 절에서 ROWNUM 칼럼명을 명시해야만 확인 가능

마치 ROW_NUMBER를 호출한 것처럼 출력됨

사원 테이블에서 맨 위의 5개의 행만 아래와 같이 출력

  SELECT ROWNUM, EMPNO, ENAME, JOB, SAL
  FROM EMP
  WHERE ROWNUM <= 5;

  SELECT ROWNUM, EMPNO, ENAME, JOB, SAL
  FROM EMP
  WHERE ROWNUM = 1;

위와 같이 첫번째 행을 출력하는 조건문은 출력이 가능하지만, 그 외의 행에 대해서는 개별 출력이 불가능함 (2,3,4...)

  SELECT ROWNUM, EMPNO, ENAME, JOB, SAL
  FROM EMP
  WHERE ROWNUM <= 2;

2번 행을 출력하려면, 위와 같이 1,2번 행을 같이 출력할 수 밖에 없음

예제) 직업이 SALESMAN인 사원들의 이름과 월급과 직업을 출력하는데 맨위의 행 2개만 출력

SELECT ENAME, SAL, JOB
FROM EMP
WHERE JOB = 'SALESMAN'
AND rownum <= 2; -- SELECT 절에 ROWNUM을 명시하지 않아도 조건절에서 사용가능

Simple TOP-n Queries : 출력되는 행 제한

select 절에 'fetch first 출력할 행의 수 rows only'

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