Day001 함수(1)

dual 테이블이란?
 연산이나 날짜에 대한 정보를 보기 위해9 사용하는 테이블 . 데이터가 한 개의 행으로 구성되어 있어 별도의 테이블 생성없이 보는 것이 가능

단일 행 함수 -> 단일행 함수랑 각 행마다 적용되는 함수를 의미한다.

1. 숫자 함수

MOD 함수 -> 나누기

SELECT MOD(10,3) "MODULUS" FROM dual;

ROUND 함수-> 반올림 소수점 n+1자리 (<=> TRUNC())

SELECT MOD(10,3) "MODULUS" FROM dual;

 WIDTH_BUCKET 함수 -> 지정된 값이 있고 최소값 최대값을 설정하고 그 사이를 정해진 구간으로 정한 뒤 지정된 값의 구간을 찾는 함수 (지정된 값, 최소값, 최대값, 몇구간으로 나눌지 정한 값)

SELECT WIDTH_BUCKET(92,0,100,10) "Score" FROM dual; 결과값은 : 10

2. 문자 함수

LOWER 함수 -> 문자값 소문자로 변환

SELECT LOWER('Korea') "Lower" FROM dual;

SUBSTR 함수 -> 문자열에서 일부 문자값을 선택적으로 반환 자바는 0부터 오라클은 1부터, 자바는 몇번째 자리인지 오라클은 몇글자인지

SELECT SUBSTR("Korea fighting" ,1,5) "Substr" FROM dual;

3. 날짜 함수

SYSDATE -> 시스템에 설정된 시간값 가져오기

SELECT SYSDATE FROM dual;

ADD_MONTHS(date, Integer) -> 시스템 설정된 시간값에 설정된 Integer 값이 더해진 날짜를 리턴

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 30),'yyyy-mm-dd') FROM dual;

LAST_DAY(date) -> 해당 월의 마지막 날을 반환

SELECT LAST_DAY(SYSDATE) - SYSDATE "Remain Days" FROM dual;

MONTHS_BETWEEN -> 현재 달의 마지막 날짜로부터 오늘 날짜를 뺀 결과를 월로 나타냄 !!  첫번재 인수가 두번째 인수보다 커야 함

SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE) "Remain months" FROM dual;

4. 변환 함수

TO_CHAR ->date타입을 format에 맞춰 변환

SELECT TO_CHAR(sysdate,'yyyy-mm-dd day') "sysdate" FROM dual; 결과: sysdate 2009-07-14 화요일

1998년 입사한 사람들 중 월별로 그룹화 하여 월별 입사한 사람 수 계산 SELECT TO_CHAR(hire_date, 'yyyy-mm'), COUNT(LAST_NAME) FROM employees WHERE TO_CHAR(hire_date, 'yyyy') = '1998' GROUP BY TO_CHAR(hire_date, 'yyyy-mm') ORDER BY TO_CHAR(hire_date, 'yyyy-mm'); 연도별 입사한 사람 수 계산 SELECT TO_CHAR(hire_date, 'yyyy')"연도", COUNT(LAST_NAME) FROM employees GROUP BY TO_CHAR(hire_date, 'yyyy') ORDER BY TO_CHAR(hire_date, 'yyyy'); 1996년 이상 연도별 입사한 사람 수 계산 SELECT TO_CHAR(hire_date, 'yyyy')"연도", COUNT(LAST_NAME) FROM employees WHERE TO_CHAR(hire_date,'yyyy') >= '1996' GROUP BY TO_CHAR(hire_date, 'yyyy') ORDER BY TO_CHAR(hire_date, 'yyyy');

 5. 기타 함수

DECODE 함수 ->DECODE (expr,search,result, default) exprt와 seach가 같으면 result 값 반환 다르면 default 값 출력

SELECT job_id, DECODE(job_id,'SA_MAN','Sales Dept','SA_REP','Sale Dept','Another')  "decode" FROM jobs WHERE job_id LIKE 'S%';

사용 예) SELECT job_id, DECODE(job_id,'SA_MAN','Sales Dept','SA_REP','Sale Dept','Another')  "decode" FROM jobs WHERE job_id LIKE 'S%'; SELECT job_id, DECODE(SUBSTR(job_id,1,2),'SA','Sales Dept','IT','Development Dept','Another') "decode" FROM jobs; select job_id, decode(job_id, 'SA_MAN', 'SALES DEPT', 'SA_REP', 'SALES DEPT', 'IT_PROG','DEPARTMENT DEPT', 'ANOTHER') "DECODE" from jobs; case문을 사용하여 변경 SELECT job_id, CASE job_id WHEN 'SA_MAN' THEN 'Sales dept' WHEN 'SA_REP' THEN 'Sales dept' WHEN 'IT_PROG' THEN 'Development dept' ELSE 'Another' END CASE FROM jobs; SELECT job_id, CASE SUBSTR(job_id,1,2) WHEN 'SA' THEN 'Sales dept' WHEN 'IT_PROG' THEN 'Development dept' ELSE 'Another' END CASE FROM jobs;

NVL 함수 -> NVL(expr1,expr2) expr1의 값이 NULL이면  expr2의 값을 반환

SELECT employee_id,salary,NVL(commission_pct,0)" commission_pct" FROM employees WHERE job_id = 'IT_PROG';

집합 함수

AVG 함수 -> 하나 이상의 값들의 평균값 구함

SELECT AVG(salary) FROM employees; 결과값: 6461.68224299065420560747663551401869159 SELECT ROUND(AVG(salary)) FROM employees; 결과값: 6462

분석 함수로서 AVG 함수 -> AVG(expr) OVER (analytical_clause)

부서별 평균값을 반환  SELECT employee_id,department_id,salary, ROUND(AVG(salary OVER (PARTITION BY department_id)),0)" AvgByDeptid" FROM employees WHERE department_id IN(10,20,30); 결과값: 나온다 ㅋㅋ

RANK 함수 -> RANK(expr) WITHIN GROUP(ORDER BY expr)

급여가 $3000인 사람의 상위 급여 순위 SELECT RANK(3000) WITHIN GROUP(ORDER BY salary DESC) "rank" FROM employees; 결과값: 82

급여 낮은 순위 SELECT employee_id, salary, RANK() OVER (ORDER BY salary) "rank" FROM employees; 결과값: 급여가 같으면 순위가 같음

SUM,MIN,MAX,COUNT함수 ->

연도별 입사자의 수와 최대, 최소 급여 구하기  SELECT TO_CHAR(hire_date,'yyyy'), COUNT(last_name) ,MAX(salary), MIN(salary) FROM employees GROUP BY TO_CHAR(hire_date,'yyyy') ORDER BY TO_CHAR(hire_date,'yyyy'); 결과값:  잘나옴