[ SQL ] 고급 함수

오늘은 SQL의 고급 함수에 대해서 포스팅해보려 합니다.

고급 함수로는 null값을 처리하는 함수, 비교 처리 함수(decode), 순위를 구하는 함수가 있는데, 순차적으로 알아봅시다.

 

■ NULL 처리 함수

 

null 처리 함수는 오라클 함수를 먼저 알고 넘어가는 게 좋습니다. 오라클 함수란, 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어를 의미합니다. 오라클 함수는 함수를 제작한 주체를 기준으로 다음과 같습니다.

 

• 내장 함수(built-in function) : 오라클에서 기본으로 제공하는 함수 입니다.

     (내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉩니다.)

• 단일행 함수(single-row function) : 데이터가 한 행씩 입력되고, 입력된 각 행별로 결과가 하나씩 나오는 함수입니다.
• 다중행 함수(multiple-row function) : 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수입니다.
• 사용자 정의 함수(user-defined function) : 사용자가 필요에 의해 직접 정의한 함수입니다.

그럼  null 처리 함수에 대해서 알아봅시다.

 

1 ) NVL함수

 

NVL이란 null로 되어 있는 칼럼의 값을 인자로 지정한 숫자 혹은 문자로 변경하여 반환합니다.

 

예제를 통해서 알아봅시다. 

 

<예제 1>

-- 연봉 계산 시 특정 컬럼에 null이 있으면 계산이 안되기 때문에 nvl(A,B)함수를 써서 처리해야 한다.
select empno,ename,sal,comm, sal*12+nvl(comm,0) from emp;
-- nvl(A,B) --> null이 아니면 A값으로 처리 null인 경우 B값으로 처리

NVL 함수 결과 예제

COMM 데이터가 null일 때 null 대신 0을 넣어 값을 반환해 줍니다. 

 

2 ) NVL2 함수

 

NVL2 함수는 칼럼명이 null이 아닌 경우 대체하고자 하는 값을 적고 null인 경우 대체 하고자 하는 값을 적습니다.

즉, null값인 경우와 아닌 경우 모두 특정 값으로 변환하는 함수입니다.

 

예제를 통해서 알아봅시다.

 

<예제 2>

--nvl2 형식 : nvl2(A,B,C) A가 null이 아니면 B, null이면 C

--emp 테이블에서 사원이름, 부서번호, 급여, 커미션 ,특별보너스를 출력.(특별보너스에서 커미션이 원래 있는 직원은 급여의 5%를 적용
--없던 직원은 3%를 적용)
select ename, deptno, sal, comm, sal*nvl2(comm,0.05,0.03) as 특별보너스
from emp;

NVL2 함수 결과 예제

출력 결과를 확인해 보면 COMM이 있는 직원은 5%가 적용되고, 없는 직원은 3%가 적용되는 걸 확인할 수 있습니다.

 

3 ) NULLIF 함수

 

NULLIF 함수는 두 값을 비교하여 같으면 null을 반환하고, 값이 다르면 첫 번째(비교값 1)를 반환해 주는 함수입니다.

 

 

그럼 예제를 통해서 알아봅시다. 

emp테이블에서 급여가 같은 사람들을 출력하기 위해 이름과 급여를 출력합니다. 

 

<예제  3>

--nullif : 두 값을 비교해서 같으면 null, 다르면 비교값1을 반환
--형식 : nullif(비교값1, 비교값2)
--emp테이블에서 급여가 같은 사람들에 대한 결과값을 null처리
select ename, sal from emp order by sal desc; -- 박종수,나대호 325

출력 결과를 확인해 보면 박종수, 나대호 직원의 연봉이 동일하기 때문에 nullif 함수를 활용해 보겠습니다.

 

<예제 4>

select ename, sal, nullif(sal,325)
from emp
order by sal desc;

NULLLIF함수 결과 예제

 

4 ) Coalesce 함수

 

Coalesce 함수는 지정한 표현식들 중에 null이 아닌 첫 번째 값을 반환합니다. (모든 DBMS에서 사용가능합니다.)

 

표현식은 여러 항목 지정이 가능하고, 처음으로 만나는 null이 아닌 값을 출력합니다. 또한 표현식이 모두 null일 경우엔 결과도 null을 반환합니다.

 

그럼 예제를 통해서 알아봅시다.

 

<예제 5>

-- 형식 : coalesce(값,값2,값3...) null이 아닌 첫번째 값
--emp 테이블에서 커미션이 있으면 커미션을, 만약 커미션이 없으면 해당급여를, 커미션과 급여가 다 없는 경우(null)는 임의로
--20을 치환해서 사원번호, 이름, 커미션, 급여,해당 치환값으로 검색
select empno, ename, comm, sal, coalesce(comm,sal,20) 치환값
from emp;

Coalesce 함수 결과 예제

 

5 ) Rollup 함수

 

Rollup 함수는 그룹별로 중간 집계를 처리하는 함수로 주로 group by절과 같이 사용됩니다.

 

group by절에 의해서 그룹 지어진 집합 결과에 대해서 좀 더 상세한 정보(전체 총계)를 반환하는 기능을 수행하고, select절에 rollup을 사용함으로써 보통의 select 된 데이터와 그 테이터의 총계를 구할 수 있습니다.

 

그럼 예제를 통해서 알아봅시다.

 

<예제 6>

--rollup을 사용하면 직책별 급여합계뿐만 아니라 해당급여의 총합계까지 구할 수 있다.
select job, sum(sal)
from emp
group by rollup(job);

rollup 함수 결과 예제

 

rollup 함수를 활용하여 각 직책별 급여를 출력을 할 수 있고, 해당 급여들의 총합계까지 구할 수 있습니다.

 

6 ) CUBE 함수

 

CUBE 함수는 그룹별 산출한 결과를 집계하는 함수로 Rollup 함수가 처리하는 중간 집계뿐만 아니라 그룹별 최종집계까지 처리할 수 있는 함수입니다. 

 

예제를 통해서 알아봅시다.

 

<예제 7>

--cube함수 : 그룹별 최종집계까지 처리
-- 부서별 급여합계와 직책별 급여합계를 모두 구하고자 할때는 cube함수를 쓰면 편하다.

select d.dname 부서명,e.job 직책, sum(e.sal) 급여합계
from emp e,dept d
where e.deptno = d.deptno
group by cube(d.dname, e.job);

 

■ Decode

Decode 함수란 값을 비교하여 해당하는 값을 돌려주는 함수입니다. 단, 비교 시에는 정확히 같은 값(=)만 비교가 가능합니다.(JAVA의 switch~case문과 같다.)

 

그럼 예제를 통해서 알아봅시다.

 

<예제 8>

-- decode
--emp 테이블에서 각 사원의 이름과 급여, 급여등급(급여가 400만원 이상이면 A등급,300이상 B,200이상 C, 100이상 D,100미만 E)출력
select ename, sal, trunc(sal/100), decode(trunc(nvl(sal,0)/100),0,'E',1,'D',2,'C',3,'B','A') 급여등급
from emp;

decode 함수 결과 예제

 

■ 순위를 구하는 함수

SQL에서의 순위함수 Rank, Dense_Rank, Row_number 세 가지 함수가 있는데, 각각의 함수에 대해 간략한 설명과 함께 쿼리를 통해 결과를 확인해 봅시다.

 

순위 함수는 동일한 emp 테이블로 진행하고, 테이블에서 전체 사원에 대해서 부서번호, 이름, 급여, 급여순위를 조회합니다.

 

1 ) Rank 함수

 

Rank 함수는 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력하도록 하는 함수입니다.( order by를 포함한 query문에서 특정 칼럼에 대한 순위를 구하는 함수 )

 

Rank 함수 예제를 통해서 알아봅시다.

 

<예제 9>

--emp 테이블에서 전체 사원에 대해서 부서번호, 이름, 급여, 급여순위 조회
select deptno, ename, sal, 
rank() over(order by nvl(sal,0) desc) "rank() 함수",

Rank 함수 결과 예제

 

2 ) Dnese_Rank 함수

 

Dnese_Rank 함수는 중복값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복값 개수와 상관없이 순차적인 순위 값을 출력하도록 하는 함수입니다.

 

Dnese_Rank 함수 예제를 통해서 알아봅시다.

 

<예제 10>

select deptno, ename, sal, 
dense_rank() over(order by nvl(sal,0) desc) "dense_rank() 함수"
from emp;

dnese_rank 함수 결과 예제

 

3 ) Row_number 함수

 

Row_number 함수는 중복 값들에 대해서도 순차적인 순위를 표시하도록 출력하는 함수입니다. 즉, 중복과 관계없이 무조건 순서대로 반환해 줍니다.

 

Row_number 함수 예제를 통해서 알아봅시다.

 

<예제 11>

select deptno, ename, sal, 
row_number() over(order by nvl(sal,0) desc) "row_number() 함수"
from emp;

row_number함수 결과 예제

 

그럼 세 가지 순위 함수에 대해서 차이점을 확인해 봅시다.

 

<차이점>

--emp 테이블에서 전체 사원에 대해서 부서번호, 이름, 급여, 급여순위 조회
select deptno, ename, sal,
rank() over(order by nvl(sal,0) desc) "rank() 함수",
dense_rank() over(order by nvl(sal,0) desc) "dense_rank() 함수",
row_number() over(order by nvl(sal,0) desc) "row_number() 함수"
from emp;

차이점

---

마치며

 

오늘은 고급 함수에 대해서 알아봤습니다.

다음 포스팅으로는 HTML을 시작하려고 합니다..!! 

점점 프로젝트를 준비하는 단계에 가까워지며 긴장 반 설렘 반이네요..:)

그럼 다음 포스팅에서 뵙겠습니다~:)