[SQL] 7. 데이터 분석함수

7. 데이터 분석함수

앞서 어떤 조건에 해당하는 데이터를 뽑아내는 작업을 해봤는데,
이번에는 분석을 위해 사용되는 좀 더 높은 수준의 함수인 데이터 분석함수에 대해 알아보자.

데이터 분석함수에는 크게 listagg, rank, pivot, ntile, lag, lead가 있다.

listagg

listagg함수는 Group by문과 함께 사용하는 함수로,
그룹별로 구분한 데이터를 한 컬럼(변수)안에 넣어주는 기능을 한다.

사용 방법은
listagg(나열할 데이터, 기준 구분자) within group ( order by 정렬 기준의 변수) 이다.
예제로 먼저 알아보자.

예) 부서번호와 부서번호 별로 속한 사원들의 이름을 가로로 출력하시오!
select deptno,
listagg(ename, ',') within group (order by ename)
from emp
group by deptno;

일반적인 그룹함수로는 해결할 수 없었던 기능을 제공하고 있으며,
'order by 변수' 뒤에 붙이는 것은 은 asc(순서대로), desc(역순서대로)가 있다.
지금은 아무것도 쓰지 않은 상태(=asc)에 ename을 기준으로 하였으므로
문자형 ename의 정렬은 알파벳 순서대로 진행된다.

listagg 함수를 활용하면 좀 더 알아보기 쉬운 데이터를 만들 수 있다.
select deptno,
listagg(ename || '(' || job || ')' , ',') within group (order by job)
from emp
group by deptno;

rank

rank함수는 순위를 출력하는 함수로, order by 문으로 정렬을 하며
이에 따른 순위를 출력하는 컬럼을 생성한다.

기본적으로
rank() over (order by 정렬기준 변수(=순위 적용할 함수))로 사용하며,
같은 등수인 데이터가 두개 이상 있을 때 그 다음 등수를 중복 갯수 감안하여 설정한다.
예) 2등이 2명이라면, 다음 순위는 4위로 설정된다.

위의 결과를 2등 다음에 3등이 오게하고 싶으면 rank()를 dense_rank()로 변경한다.

예제) 이름, 월급, 순위를 출력하는데 월급이 높은 사원부터 순위가 출력되게 하시오!
select ename, sal, rank() over ( order by sal desc) 순위
from emp;

▼ 위와 동일한 코드 dense_rank()로 적용
select ename, sal, dense_rank() over (order by sal desc) 순위 from emp;

• partition by는 rank와 dense_rank에서 결과물을 원하는 파트별로
  보여주고 싶을때 사용한다.

위의 예시에서 deptno(근무부서)와 함께 근무부서별 월급 순위를 출력하려면
select enaem, sal, deptno,
dense_rank() over (partition by deptno order by sal desc) 파트별 순위
from emp;

pivot

pivot은 '회전하다'라는 뜻으로, 기존 데이터셋을 회전시켜주는 기능을 한다.
원 데이터셋의 로우 ↔ 컬럼 변경 작업에 사용된다.

사용법은 from 데이터셋 다음에 Pivot (a for b in c)으로 작성하며,
b(데이터가 될 것)의 c(컬럼이 될 것)에 대한 a(함수)를 해라!라는 뜻이다.
사용하는데 어려울 수 있으나, 적응되면 상당히 유용한 함수이다.
똑같은 기능을 구현하는데 pivot을 쓰지 않으면 여러줄의 decode함수로 대체해야 하기 때문이다.

예제) 부서번호, 부서별 총 월급을 출력하시오! (Pivot 사용하지 않고)
select deptno, sum(sal)
from emp
group by deptno ;

이제 위의 결과를 아래와 같이 변경하고자 한다.

10	20	30
8750	10875	9400

우선 이전에 알아본 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;

지금은 적은 줄로 구현했으나 컬럼이 많아지면 많아질수록
점점 코드가 길어질 것다.
실제로 해보면 상당히 불편하다.....

이 것을 간단하게 구현해주는 것이 pivot 함수이다.
select * from emp
pivot(sum(sal) for deptno in (10,20,30));

10, 20, 30이라는 컬럼이 새로 생겼으나, 원래 목표로 하는 데이터셋은 아니다. 또한, 부서별 총 월급도 확인 할 수 없다. 단순히 deptno에 따라 구별된 컬럼이 생성되었을 뿐이다.

물론 select문에서 바로 10, 20, 30을 출력하는 명령문도 작성해보았으나, 결과는 아래와 같았다.
select 10, 20, 30
from emp
pivot(sum(sal) for deptno in (10, 20, 30));

위 두가지 쿼리의 문제점은 from emp가 기존 emp에 있는 데이터 모두를 가져와서 발생하는 문제이다.
이는 서브쿼리를 사용하면 한 번에 해결되며, 보통 pivot함수는 서브쿼리와 함께 사용된다.

select * (모두 출력하라)
from (select deptno, sal from emp) ← 서브쿼리(emp에서 deptno, sal만 가져옴)
pivot(sum(sal) for deptno in (10,20,30));

서브쿼리란 쿼리(명령문)안의 쿼리로서, 데이터의 일부 값을 기본 데이터셋으로 지정할 수 있다.
서브쿼리에 대해서는 차후 자세히 다뤄보도록 하자.

ntile

ntile은 데이터의 등급을 나눠주는 함수로서
ntile(나누려는 등급의 개수) over (order by 기준컬럼(변수) desc nulls last)의
형식으로 사용한다.

예제) emp테이블의 월급을 4등급으로 나누어 이름, 월급과 함께 출력하시오!
select ename, sal,
ntile(4) over (order by sal desc nulls last) 등급
from emp;

위 결과를 확인해보면 14개의 데이터를 월급에 따라
1등급: 2975 ~ 5000, 4개의 데이터
2등급: 1500 ~ 2850, 4개의 데이터
3등급: 1250 ~ 1300, 3개의 데이터
4등급: 800 ~ 1100, 3개의 데이터로 나누었다.
알아서 4등급으로 나누었지만, 그 기준을 정확히 인지할 수 없다.
→ 위와 같이 월급과 같은 예민한 부분을 다루기보다는 나누고자 하는 등급(n개)으로
데이터의 총 갯수가 나누어지거나, 가볍게 사용하는 용도일 때 사용하는 것이 좋을 것 같다.

lag, lead

lag와 lead는 반대의 개념으로, 어떠한 컬럼(변수)을 기준으로 정렬한 상태에서
n번째 전/후의 값을 출력할 때 사용한다.

lag가 전, lead가 후의 값을 출력하며
lag(출력할 변수, n(n번째 전의 데이터)) over (order by 정렬기준변수)
lead(출력할 변수, n(n번째 후의 데이터)) over (order by 정렬기준변수) 로 사용한다.

예제) 사원번호, 이름, 월급, 월급순 바로 아래 사람, 월급순 바로 위 사람을 출력!
select empno, ename, sal
lag(ename, 1) over (order by sal) 전행,
lead(ename, 1) over (order by sal) 다음행
from emp;

전행의 1행 값과 다음행의 마지막 값은 당연히 Null로 출력된다.

=================이 글은 Markdown을 활용해 작성하였습니다=================