[SQL] 숫자함수 ABS, SIGN, SQRT, POWER, GREATEST, LEAST, ROUND, TRUNC, FLOOR, CEIL, MOD, REMAINDER

#1 숫자 함수

1. 수학적 함수
- ABS, SIGN, SQRT, EXP, LOG, POWER,...
 1) ABS(n), SIGN(n), SQRT(n), POWER(n, e)
    - ABS : n의 절댓값 -- 부호를 제거시킨 값
    - SIGN : n의 부호 값(n이 양수이면 1, 음수이면 -1, 0이면 0 반환)
    - SQRT : n의 평방근 -- 루트 값
    - POWER : n의 e승(n^e) 값을 반환 -- 거듭제곱 값
   사용예)
    SELECT ABS(-1000), ABS(0), ABS(0.0001),
                  SIGN(-0.0009), SIGN(0), SIGN(100000),
                  SQRT(1024), -- (2^5)^2 ≫ 2^5 = 32
                  POWER(2,10)
      FROM DUAL;

 2) GREATEST(n1, n2 [,...]), LEAST(n1, n2 [,...])
    - GREATEST : 주어진 n1 ~ n에서 가장 큰 수를 반환
                             MAX와 차이점은 MAX는 하나의 컬럼 값 중 가장 큰 값을 반환
                             GREATEST는 여러 컬럼 중 가장 큰 값을 반환
    - LEAST : 주어진 n1 ~ n에서 가장 작은 수를 반환
                     MIN와 차이점은 MIN는 하나의 컬럼 값 중 가장 작은 값을 반환
                     LEAST는 여러 컬럼 중 가장 작은 값을 반환
   사용예)
     SELECT GREATEST(PROD_COST, PROD_PRICE, PROD_SALE)
        FROM PROD;

     SELECT MAX(PROD_COST)
        FROM PROD;  

   SELECT LEAST('홍길동', 125, 'ABC'), -- 아스키코드값(ASCII)으로 변환하여 가장 작은 값
                  ASCII('홍길동'),
                  ASCII(125),
                  ASCII('ABC'),
                  GREATEST('홍길동', 125, 'ABC') -- 아스키코드값(ASCII)으로 변환하여 가장 큰 값
      FROM DUAL;

   문제) 회원 테이블에서 보유 마일리지가 1000보다 작은 회원들의 마일리지를 1000으로 변경시켜 출력하시오.
             Alias 회원번호, 회원명, 원본 마일리지,
     SELECT MEM_ID AS 회원번호,
                    MEM_NAME AS 회원명,
                    MEM_MILEAGE AS 원본 마일리지,
                    GREATEST(MEM_MILEAGE,1000) AS 변경 마일리지
        FROM MEMBER;

 

3) ROUND(n, 1oc), TRUNC(n, 1oc)
    - ROUND는 반올림을 수행하고, TRUNC는 자리 버림을 수행함
    - 숫자 n에서 소수점 이하 1oc +1번째 자리에서 반올림(자리 버림)하여 1oc 자리까지 결과 반환
    - 1oc가 음수이면 정수 부분 1oc자리에서 반올림(자리 버림) 후 값 반환
   사용예)
     SELECT ROUND(123.45678,2), -- 소수점 3번째 자리에서 반올림
                    ROUND(123.45678), -- 0 생략, 소수점 자리에서 반올림
                    ROUND(123.45678,-2), -- 정수 2번째 자리에서 반올림
                    TRUNC(123.45678,2), -- 소수점 2번째 자리에서 자리 버림
                    TRUNC(123.45678), -- 0 생략, 소수점 자리에서 자리 버림
                    TRUNC(123.45678,-2) -- 정수 2번째 자리에서 자리 버림
         FROM DUAL;

   사용예) 상품테이블의 상품별 마일리지컬럼(RPOD_MILEAGE)에 판매단가의 0.6%(0.006)에 해당하는 값을 입력하시오.
                단, 모든 값은 정수로 입력하시오.
     UPDATE PROD
            SET PROD_MILEAGE=ROUND(PROD_PRICE * 0.006);

    COMMIT;

 

   사용예) 상품 테이블에서 각 상품들의 원가율(매입 가격/판매 가격)을 %로 출력하시오.
                단, 원가율은 소수점 1자리까지 구하며, 원가율이 큰 상품부터 출력하시오.
                 Alias는 상품코드, 상품명, 매입 가격, 원가율
     SELECT PROD_ID AS 상품코드,
                    PROD_NAME AS 상품명,
                    PROD_COST AS 매입 가격,
                    PROD_PRICE AS 판매 가격,
                    ROUND(PROD_COST/PROD_PRICE,3) * 100 AS 원가율 -- ex) 0.0045 ≫ 0.4%
        FROM PROD
      ORDER BY 5 DESC;

4) FLOOR(n), CEIL (n) -- 사용빈도(★★☆☆☆)
    - FLOOR : n과 같거나 작은 수 중 제일 큰 정수(n을 초과하지 않는 최대 정수)
                     세금과 같이 징수 계산에 주로 사용
    - CEIL : n과 같거나 큰 수 중 제일 작은 정수(n보다 작지 않은 최소 정수)
                 급여와 같이 지급액 계산에 주로 사용
    - 주로 급여, 세금 등과 같은 금액 관련 계산에 주로 사용
   사용예) 
     SELECT FLOOR(20), FLOOR(20.999), FLOOR(-20.67),
                    CEIL(20), CEIL(20.999), CEIL(-20.67)
        FROM DUAL;

5) MOD(n1, n2), REMAINDER(n1, n2) -- 사용빈도(★★★☆☆)
    - 나머지를 반환
    - MOD는 일반적인 나머지를 반환하며, REMAINDER는 나머지가 제수의 절반보다 큰 값이면
      현재수에서 다음 몫의 수를 차감한 결과를 반환
    - 내부적으로 MOD(FLLOR 사용)와 REMAINDER(ROUND 사용)는 사용하는 함수가 다름
    - MOD = n1 - n2 * FLOOR(n1/n2)
      REMAINDER = n1 - n2 * ROUND(n1/n2)
      ex) MOD(27,5) = 27 - 5 * FLOOR(27/5)
                               = 27 - 5 * FLOOR(5.4)
                               = 27 - 5 * 5
                               = 2
            REMAINDER(27,5) = 27 - 5 * ROUND(27/5)
                                            = 27 - 5 * ROUND(5.4)
                                            = 27 - 5 * 5
                                            = 2
             MOD(29,5) = 29 - 5 * FLOOR(29/5)
                                = 29 - 5 * FLOOR(5.8)
                                = 29 - 5 * 5
                                = 4
             REMAINDER(29,5) = 29 - 5 * ROUND(29/5)
                                             = 29 - 5 * ROUND(5.8)
                                             = 29 - 5 * 6
                                             = -1
              SELECT MOD(25,5), MOD(29,5),
                             REMAINDER(25,5), REMAINDER(29,5)
                 FROM DUAL;

 

 

 

 

2022.11.17 Oracle SQL 수업