2022-11-17 데이터 베이스 오라클 : 숫자 함수
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;