[SQL] 데이터 분석 함수

RANK, DENSE_RANK

SELECT ename, title, sal,
    RANK() OVER (ORDER BY sal DESC) RANK,
    DENSE_RANK() OVER (ORDER BY sal DESC) DENSE_RANK
  FROM emp
  WHERE title IN ('RESEARCHER', 'COOPERATOR', 'ANALYST');

OVER 뒤에 출력하고 싶은 데이터를 정렬하는 문장을 넣으면, 그 column의 값에 대한 데이터의 순위가 출력된다.

 

 

 

SELECT ename, title, sal, RANK() OVER (PARTITION BY title ORDER BY sal DESC) 순위
  FROM emp;

title별로 묶이고, 순위도 title 내에서 계산된다.

title은 항상 오름차순으로 정렬되는 건지 알아봐야 할 것이다.

title 내에서는 sal DESC 순서로 정렬된다.

 

 

 

SELECT
    RANK(3000) WITHIN GROUP (ORDER BY sal DESC) RANK,
    DENSE_RANK(3000) WITHIN GROUP (ORDER BY sal DESC) DENSE_RANK
  FROM emp;

sal이 3000인 사원은, sal DESC 순서로 정렬한 emp 테이블에서 순위가 몇 위인지 출력한다. 즉 RANK( ), DENSE_RANK( )의 파라미터가 데이터 전체에서 갖는 순위를 출력한다.

 

 

 

NTILE

SELECT ename, title, comm, NTILE(4) OVER (ORDER BY comm DESC NULLS LAST) 등급
  FROM emp
  WHERE title IN ('RESEARCHER', 'LEADER', 'COOPERATOR');

NTILE(n)을 통해 월급의 등급을 n등급(0~25% / 25~50% / 50%~75% / 75%~100%)으로 나누어 출력한다.

NULLS LAST를 사용할 수 있다.

 

 

 

CUME_DIST

SELECT ename, sal,
    RANK() OVER (ORDER BY sal DESC) RANK,
    DENSE_RANK() OVER (ORDER BY sal DESC) DENSE_RANK,
    CUME_DIST() OVER (ORDER BY sal DESC) CUME_DIST
  FROM emp;

 

 

 

SELECT title, ename, sal,
    RANK() OVER (PARTITION BY title ORDER BY sal DESC) RANK,
    CUME_DIST() OVER (PARTITION BY title ORDER BY sal DESC) CUM_DIST
  FROM emp;

 

 

 

LISTAGG

SELECT deptno, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) EMPLOYEE
  FROM emp
  GROUP BY deptno;

데이터를 가로로 출력하는 함수로서 두 번째 파라미터로 구분자를 받는다.

 

 

 

SELECT title, LISTAGG(ename||'('||sal||')', ', ') WITHIN GROUP (ORDER BY ename) EMPLOYEE
  FROM emp
  GROUP BY title;

 

 

 

LAG, LEAD

SELECT empno, ename, sal,
    LAG(sal, 1) OVER (ORDER BY sal DESC) "전 행",
    LEAD(sal, 1) OVER (ORDER BY sal DESC) "다음 행"
  FROM emp;

LAG(column명, n)을 통해 n개 전 행의 데이터를,

LEAD(column명, n)을 통해 n개 다음 행의 데이터를 출력할 수 있다.

 

 

 

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;

 

 

 

SUM OVER

SELECT empno, ename, sal,
    SUM(sal) OVER (ORDER BY empno
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적치
  FROM emp WHERE title in ('ANALYST', 'COOPERATOR');

SUM OVER는 누적 데이터 출력을 위해 사용한다. OVER 뒤에 윈도우를 지정할 수 있다.

 

 

 

SELECT empno, ename, sal,
    SUM(sal) OVER (ORDER BY empno ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) "잔여 합"
  FROM emp
  WHERE title in ('RESEARCHER', 'COOPERATOR');

 

 

 

윈도우 기준	윈도우 방식	설명
ROWS	UNBOUNDED PRECDING	맨 첫 번째 행을 가리킴
	UNBOUNDED FOLLOWING	맨 마지막 행을 가리킴
	CURRENT ROW	현재 행을 가리킴

 

 

 

RATIO_TO_REPORT

SELECT empno, ename, sal,
    RATIO_TO_REPORT(sal) OVER ( ) 비율,
    sal/SUM(sal) OVER ( ) "비교 비율"
  FROM emp
  WHERE deptno = 20;

RATIO_TO_REPORT는 비율 출력을 위해 사용한다.

위 예시에서 0.24685... = 3930 / (3930 + 5000 + 790 + 5000 + 1200)이다.

 

 

 

ROLLUP

SELECT title, SUM(sal)
  FROM emp
  GROUP BY ROLLUP(title);

ROLLUP은 집계 결과 출력을 위해 사용한다.

title 별 sal 합계를 출력하고, 맨 아래쪽에 전체 sal 합계 행을 추가로 출력한다.

더불어, title 컬럼의 데이터는 오름차순으로 정렬되어 출력된다.

 

 

 

SELECT deptno, title, SUM(sal)
  FROM emp
  GROUP BY ROLLUP(deptno, title);

집계 결과의 row 수는 컬럼의 개수에서 1개를 더한 것과 같다.

 

 

 

CUBE

SELECT deptno, title, SUM(sal)
  FROM emp
  GROUP BY CUBE(deptno, title);

CUBE는 집계 결과 출력을 위해 사용한다.

 

 

 

GROUPING SETS

SELECT deptno, title, SUM(sal)
  FROM emp
  GROUP BY GROUPING SETS((deptno), (title), ( ));

집계하고 싶은 컬럼들을 기술하면 그대로 출력된다.

 

 

 

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) ROW_NUM
  FROM emp
  WHERE deptno = 20;

ROW_NUMBER는 출력 결과 넘버링을 위해 사용한다.

 

 

 

SELECT deptno, ename, sal,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) ROW_NUMBER
  FROM emp
  WHERE deptno in (10, 20);