DataBase

2022-11-17 데이터 베이스 오라클 : 숫자 함수

헤니s 2022. 11. 17. 13:36

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), -- 루트 값 =32 * 32
           POWER(2,10) -- 2의 10승
      FROM DUAL;


 

 2) GREATEST(n1,n2[,....], LEAST(n1,n2[,.....]) 
  - GREATEST : 주어진 수 n1 ~ n 에서 가장 큰 수를 반환
               MAX와 차이점은 MAX는 하나의 컬럼 값 중 가장 큰 것을 반환 -- MAX는 한 열에서 제일 큰 값
               GREATEST는 여러 컬럼중 가장 큰 값을 반환 --GREATEST는 한 행에서 제일 큰 값
  - LEAST : 주어진 수 n1 ~ n 에서 가장 작은 수를 반환
            MIN와 차이점은 MIN는 하나의 컬럼 값 중 가장 작은 것을 반환
            LEAST는 여러 컬럼중 가장 작은 값을 반환

SELECT GREATEST(PROD_COST,PROD_PRICE,PROD_SALE)   

-- 3개의 컬럼 중에 각 상품별 제일 큰 값       
        FROM PROD;

 SELECT MAX(PROD_COST) 
-- 매입가격중에 제일 큰 매입가격 : 1개만
   FROM PROD;  

MAX 값 : 3150000

 

 

 

 

문제] 회원테이블에서 회원들의 보유 마일리지가 1000보다 작은 회원들의 마일리지를 1000으로 변경시켜 출력하시오
        Alias는 회원번호,회원명,원본 마일리지

   SELECT MEM_ID AS 회원번호,
          MEM_NAME AS 회원명,
          MEM_MILEAGE AS 원본마일리지,
          GREATEST(MEM_MILEAGE,1000) AS 변경마일리지
-- GREATEST : 원본 마일리지와 1000을 비교해서
-- 더 큰값을 반환한다.
     FROM MEMBER;

 


--      숫자, 위치값 / loc는 자리 위치
 3) ROUND(n,[,loc]), TRUNC(n,[,loc])
  - ROUND는 반올림을 수행하고 TRUNC는 자리버림을 수행함
  - 숫자 n에서 loc +1번째 자리에서 반올림(자리버림) 하여 loc 자리까지 결과 반환
  - loc가 음수이면 정수부분 loc자리에서 반올림(자리버림) 후 값 반환
  - loc가 생략되면 0으로 간주됨

 

    SELECT ROUND(123.45678,2),  -- 123.46 : 소숫점 아래 3번째에서 반올림
           ROUND(123.45678),    -- 123 : loc 생략 = 0
           ROUND(123.45678,-2), -- 100 : 정수 2번째 자리에서 반올림 : 100
           TRUNC(123.45678,2),  -- 123.45 : 소숫점 아래 3번째에서 숫자를 자름
           TRUNC(123.45678),    -- 123 :  loc 생략 = 0
           TRUNC(123.45678,-2)  -- 100 : 정수 2번째 자리에서 버림
      FROM DUAL;

 

 

사용예) 상품테이블의 상품별 마일리지컬럼(PROD_MILEAGE)에 판매단가의 0.6% 해당하는 값을 입력하시오.
       단 모든 값은 정수로 입력하시오. 업데이트 /모든 상품으로 WHERE 절 없음

    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 원가율 
   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),
            -- 20       /   20     /    -21
           CEIL(20), CEIL(20.999),CEIL(-20.67) 
  -- 20 /   21 : 20.999보다 큰 수중 가장 작은수 / -20 :  음수 -20.67 보다 큰 수는 -20이다
     FROM DUAL;

 

 5) MOD(n1, n2), REMAINDER(n1, n2) -***
  - 나머지를 반환
  - MOD는 일반적인 나머지를 반환하며, REMAINDER는 나머지가 젲수의 절반보다 큰 값이면 
    현재수에서 다음 몫의 수를 차감한 결과를 반환
  - 내부적으로 MOD와 REMAINDER는 사용하는 함수가 다름
 
  - MOD = n1 - n2 * FLOOR(n1/n2)
  - REMAINDER = n1 - n2 * ROUND(n1/n2)
  

 

MOD(29,5) = 29 - 5 * FLOOR(29/5) -- 첫번째 매개변수를 두번째 매개변수로 나눔 (분자/분모) 나머지를 반환한다.
          = 29 - 5 * FLOOR(5.8) -- 반환되는 값은 FLOOR 사용
          = 29 - 5 * 5
          = 4          -- 29 / 5 의 몫은 5 / 5.8 => 라운드로 반올림하면 6 // 29 -30 = -1
REMAINDER(29,5) =  = 29 - 5 * ROUND(29/5) -- 몫과 나머지를 반환하는데 실행되는 함수가 다르다.
                   = 29 - 5 * ROUND(5.8) -- 반올림 사용
                   = 29 - 5 * 6
                   = -1
    SELECT MOD(27,5), MOD(29,5),
           REMAINDER(27,5),REMAINDER(29,5)
      FROM DUAL;

 


 6) WIDTH_BUCKET(n, min, max, b) - ** -- 하한값 : min / 상한값  : max
  - 구간의 하한값 min 에서 상한값 max까지 b개의 블록으로 구분하고 주어진 값 val이
    속한 구간의 index값을 반환
  - val 값은 각 구간의 상한값과 같은 경우 다음 구간 순번을 반환. 

    즉, 구간 하한<= val < 구간 상한 -- min<= 값 < max
  - b는 전체구간을 분할할 갯수를 의미하며 사용되는 구간의 갯수는 b + 2 개이다.

   (총구간의 개수는 b+2 // min 보다 하한의 값은 0구간 , b구간, max 보다 상한의 값은 b+1구간 )
  
사용예) 회원테이블에서 회원들의 마일리지를 기준으로 1000-8000까지를 3개의 구간으로 구분하고 
        회원들이 어느 구간에 속하는지를 판별하시오
        Alias는 회원번호,회원명,마일리지,구간값

SELECT MEM_ID AS 회원번호, 
       MEM_NAME AS 회원명,
       MEM_MILEAGE AS 마일리지, 
       WIDTH_BUCKET(MEM_MILEAGE,1000,8000,3) AS 구간값 
-- min 보다 하한의 값은 0구간
  FROM MEMBER 
-- max 보다 상한의 값은 b+1구간

                                                                                                             

 

 -- 마일리지 구간을 반대로 8000이상을 1 구간으로 정렬

   SELECT MEM_ID AS 회원번호, 
          MEM_NAME AS 회원명, 
          MEM_MILEAGE AS 마일리지, 
          5-WIDTH_BUCKET(MEM_MILEAGE,1000,8000,3) AS 구간값
     FROM MEMBER    
  
 
   SELECT MEM_ID AS 회원번호, 
          MEM_NAME AS 회원명, 
          MEM_MILEAGE AS 마일리지, 
          1 + WIDTH_BUCKET(MEM_MILEAGE,8000,999,3) AS 구간값 
     FROM MEMBER

 

 *** CASE WHEN THEN 표현식
    - 자바의 SWITCH ~ CASE 와 비슷한 기능 제공
    - SELECT 절에서만 사용가능 (PL/SQL에서는 명령문으로 제공)

(사용형식_1)
    CASE WHEN 조건식1 THEN   -- CASE WHEN = if(조건식)
              명령1
         WHEN 조건식2 THEN
              명령2
              .
              .
        ELSE -- = 자바 스위치 문의 default
              명령n
    END
      . 조건식1을 평가하여 그 값이 참이면 '명령1'을 수행하고 거짓이면 다음 WHEN을 수행
      . 조건식이 모두 만족하지 않은 경우 마지막 ELSE 다음의 '명령n'을 수행
       

 

 CASE WHEN 값1 THEN
              명령1
        WHEN 값2 THEN
              명령2
              .
              .
        ELSE
              명령n
    END
      . 조건식을 평가하여 그 값이 '값1' 이면 명령1을 '값2'면 명령2를 수행하고
        맞는 값이 없으면 '명령n'을 수행

 

사용예) 회원테이블에서 회원들의 마일리지를 기준으로 1000-8000까지를 3개의 구간으로 구분하고 
        회원들이 어느 구간에 속하는지를 판별하고 그 값이
        0-1이면 '열심회원'
        2-3이면 '보통회원'
        4 이상이면 '새싹회원'을 비고에 출력하시오
         Alias는 회원번호,회원명,마일리지,구간값,비고

 SELECT MEM_ID AS 회원번호, 
        MEM_NAME AS 회원명, 
        MEM_MILEAGE AS 마일리지, 
        WIDTH_BUCKET(MEM_MILEAGE,8000,1000,3) AS 구간값,
   CASE WHEN  WIDTH_BUCKET(MEM_MILEAGE,8000,1000,3) IN(0,1) 
        THEN
            '열심회원'
        WHEN WIDTH_BUCKET(MEM_MILEAGE,8000,1000,3) IN(2,3) 
        THEN
            '보통회원'
        ELSE
            '새싹회원'
         END AS 비고        
   FROM MEMBER;