단일행 함수 IV (숫자 함수, 변환형 함수, 분석 함수)

숫자함수

ROUND : 입력받은 숫자의 지정된 자리수에서 반올림 한다. 단, 양수가 소숫점 자리이고, 음수는 정수 자리이다.
TRUNC : ROUND와 같은 양식이나, 반올림이 아닌 버림을 한다.

SELECT 34.615,
            ROUND(34.615,0),TRUNC(34.615,0),
            ROUND(34.615),TRUNC(34.615),
            ROUND(37.999,-1),TRUNC(37.999,-1)
FROM dual;

MS-SQL과는 다르게 ORACLE은 별도의 정수형이 없이 NUMBER형 하나로 통일되어 처리된다. 그러므로 5/2의 결과는 2가 아닌 2.5이다. 그러므로 몫만을 가져오기 위해선 FLOOR 함수를 사용해야 한다.
FLOOR :  입력 받은 수보다 작은 수 중 최대의 수를 반환한다.

SELECT 2.5 , 5/2 , FLOOR(5/2),TRUNC(5/2)
FROM dual;

MOD : 두 수를 입력 받아 앞의 수를 뒤의 수로 나누어 나머지를 가져온다. C에서 % 연산 역할을 한다.

SELECT MOD(5,2)
FROM dual;

POWER : 두 수를 입력 받아 앞의 수를 뒤의 수만큼 스스로 곱한다.

SELECT POWER(4,3)
FROM dual;

SQRT : 입력받은 수의 Root를 반환한다.

SELECT SQRT(2),SQRT(3),SQRT(4)
FROM dual;

SIGN, COS, TAN : 입력 받은 값, 또는 식의 Sign, Cosign, Tangent 값을 반환한다.

SELECT SIGN(5-2),SIGN(5-5),SIGN(2-5)
FROM dual;

SELECT SIN(150),COS(150),TAN(150)
FROM dual;

ASIN, ACOS, ATAN : 입력 받은 값, 또는 식의 Asign, Acosign, Atangent 값을 반환한다.

SELECT ASIN(-0.8),ACOS(0.9),ATAN(0.001)
FROM dual;

LOG : 두수를 입력받고, 앞의 수를 밑으로 하는 Log 값을 반환한다.

SELECT LOG (10,100)
FROM dual;

변환형 함수
CHR : 입력 받은 수에 해당하는 문자를 반환한다. (ASCII 기준)

SELECT CHR(65),CHR(97),
            CHR(66),CHR(98)
FROM dual;

ASCII : 입력 받은 문자의 해당하는 ASCII Code 값을 반환한다.

SELECT ASCII('A'),ASCII('a')
FROM dual;

TO_CHAR : NUMBER형 값을 VARCHAR형으로 바꿔 준다. 실제로 가장 많이 쓰이는 함수 중 하나이다. 일반적으로 DATE타입에서 원하는 값을 가져올 때 쓰거나, 기존의 숫자값을 일정 형태로 바꾸어 문자열로 반환하는데 많이 쓰인다.

SELECT ename,sal, LTRIM(TO_CHAR(sal,'$9,999')), LTRIM(TO_CHAR(sal,'L9,999'))    
FROM EMP;

TO_NUMBER : VARCHAR형을 NUMBER형으로 바꾸는 데 쓰인다. 단, 입력받은 값이 숫자이어야 한다. 문자형으로 쓰인 숫자일지라도 연산의 대상이 되면 자동변환 되기도 한다.

SELECT '123' + 20,  TO_NUMBER('123') + 20
FROM dual;

TO_DATE : 문자형을 DATE형으로 변경한다. 단, 변경시 필요한 양식을 만족시켜주어야 한다.

SELECT TO_DATE('2009-05-22 13:01:03','yyyy-mm-dd hh24:mi:ss') +1
FROM dual;

SELECT SYSDATE ,TO_CHAR(SYSDATE,'q')
FROM dual;

분석함수
RANK : 별도의 입력값 없이 OVER와 같이쓰이며 ORDER BY와 함께 쓰여 순위를 나타낸다. 만일 PARTITION BY 와 함께 쓰인다면 RANK의 영역을 나눠서 할당할 수도 있다.

SELECT deptno "부서번호", ename "사원명", sal "급여", comm "커미션",
 RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별 순위",
    RANK() OVER (ORDER BY sal desc) "순위"
FROM EMP
ORDER BY DEPTNO; 

위의 쿼리문을 보면 급여를 나타내는 sal만을 기준으로 RANK() 했을 때에는 전체 순위가 나왔지만, 이를 DEPTNO를 기준으로 나눠 부서별 급여 순위도 나타냈음을 알 수 있다. RANK의 경우에는 중복되는 순위가 있을 시에는 중복되는 수만큼의 다음 순위를 할당한다.

각 부서번호 별 SAL 1등인 사원의 부서번호, 사원명, 급여를 출력해보자

SELECT deptno "부서번호", ename "사원명", sal "급여", comm "커미션",
 RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별 순위",
    RANK() over (ORDER BY sal desc) "순위"
FROM EMP
ORDER BY DEPTNO 
WHERE RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별 순위" = 1;

얼핏 보면 위의 쿼리문은 아무런 이상이 없어 보인다. 하지만 이를 실행 시 다음과 같은 에러가 발생한다. 이는 분석함수를 WHERE 구문에서 사용할 수 없기 때문이다.

그러므로 다음과 같은 쿼리로 처리하게 된다. 기존의 순위를 정렬한 테이블을 서브쿼리로 삼아 그로부터 WHERE 조건을 달아 처리하면 된다.

SELECT T."부서번호",T."사원명",T."급여",T."순위",T."부서별 순위"
FROM (SELECT deptno "부서번호", ename "사원명", sal "급여", comm "커미션",
 RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별 순위",
    RANK() over (ORDER BY sal desc) "순위"
FROM EMP
ORDER BY DEPTNO) T
WHERE "부서별 순위" = 1 ; 

또는 다음과 같이 상관 서브쿼리를  이용하여도 된다. 상관 서브쿼리란, 본 쿼리문과 이에 포함된 서브쿼리문의 WHERE절이 서로 참조하는 것을 말한다. 이 때, 같은 테이블로부터 가져오는 값을 비교하기 위해서 테이블에 별칭을 넣는 것을 조심해야 한다.

SELECT E.DEPTNO, E.ENAME , E.SAL ,
      (SELECT COUNT(*) +1 
      FROM EMP M
      WHERE M.SAL >  E.SAL) "전체순위",
      (SELECT COUNT(*) +1
      FROM EMP P
      WHERE P.SAL>E.SAL AND P.DEPTNO = E.DEPTNO
      ) "부서별 순위"
FROM EMP E
ORDER BY 1,3 DESC;

전체순위에 M.SAL > E.SAL이 의미하는 것은 화면에 보여질 기본 쿼리문의 테이블 E 값을 기준으로 그보다 큰 값을 서브쿼리에 포함된 테이블 M 에서 찾아서 숫자를 센다. 부서별 순위 역시 같은 방식이나 다만 DETPNO를 추가로 기준삼아 DEPTNO별 순위를 처리하는 것이다.

SELECT deptno "부서번호", ename "사원명", sal "급여", comm "커미션",
  DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) "부서별 순위",
    DENSE_RANK() over (ORDER BY sal desc) "순위"
FROM EMP
ORDER BY 6; 

RANK 함수의 경우 동등한 순위를 가질 경우, 그 다음 순위는 중복된 수만큼 건너뛰고 처리하지만 DENSE_RANK는 그와 상관없이 처리된다. 즉, RANK 는 1,2,2,4 식이고 DENSE_RANK는 1,2,2,3 식으로 늘어난다. 
                              
HR에서 변환 함수를 이용, 사원당 급여와 세금, 실 수령액을 계산해 보자. 계정 예제 1

SELECT  T.*,LTRIM(TO_CHAR((T.PAY-T.TAX),'$99,999')) "수령액"
FROM (SELECT first_name||' '|| last_name "사원명",
salary "PAY" ,
case
 WHEN salary >=14000 THEN (salary * 0.45)
 WHEN salary >=12000 THEN (salary * 0.44)
    WHEN salary >=10000 THEN (salary * 0.42)
    WHEN salary >=8000 THEN (salary * 0.40)
    WHEN salary >=6000 THEN (salary * 0.30)
    WHEN salary >=4000 THEN (salary * 0.20)
    WHEN salary >=2000 THEN (salary * 0.09)
    ELSE 0 END   "TAX"
FROM EMPLOYEES
ORDER BY salary DESC) T;

 
CASE 구문은 임시로 부여된 세율을 적용하기 위해 삽입한 부분이며, 실 수령액은 변환함수를 이용하여 $로 표기되었다.