Oracle 날짜 함수, 변환 함수

select to_char(sysdate, 'yyyy-mm-dd-dy hh24:mi:ss') from dual;
// yyyy-mm-dd-dy hh24:mi:ss 를 yyyy-mm-dd 등 여러 형태로 바꾸어 사용가능,

to_number(char) : char를 숫자로 바꿈( 날짜를 숫자로 변환해서 계산이 가능하게 됨 )

SQL> select to_char(200000, '$999,999') "money" from dual; // 200000을 $999,999까지 표현해라
money
---------

$200,000
SQL> select to_char(200000000, '$999,999') "money" from dual; // $999,999를 넘어서서 안됨
money

---------
#########
SQL> select to_char(200000, '$999,999,999') "money" from dual;

money
-------------
$200,000

1998년도 월별 입사자 수를 보이시오.

SQL> select to_char(hire_date, 'yyyy-mm'),count(*) from employees
2 where to_char(hire_date, 'yyyy') = '1998' // having으로는 안됨
3 group by to_char(hire_date, 'yyyy-mm')
4 order by to_char(hire_date, 'yyyy-mm');

TO_CHAR COUNT(*)
------- ----------
1998-01 2
1998-02 3
1998-03 5
1998-04 3
1998-05 1
1998-06 1
1998-07 3
1998-08 1
1998-09 1
1998-11 2
1998-12 1

cast(열_명 as 형_변환) 하여 Explicitly(표면적으로) ( <=>implicitly 암묵적으로 ) 형변환을 할 수 있다.
varchar(10) : 일반적으로 영어계열, 문자열 10자리.
varchar2(10) : 한글, 중국어, 일본어 등 표기법으로 10자리 의미. // 한글은 2byte, 영문은 1byte

SQL> select cast(employee_id as varchar2(3)) as EMP_ID_var,
2 cast(department_id as number(3)) as DEPT_ID_num,
3 cast(null as varchar2(3)) as NULL_var from employees
4 where rownum <= 10;

EMP DEPT_ID_NUM NUL
--- ----------- ---
198 50
199 50

convert(char, dst_char, src_char) : 주어진 문자열을 다른 문자열로 변환한다.
src_char 형태의 char를 dst_char로 바꾸어라
convert(독일어, 영어, 유럽) 유럽 문자열 형태의 독일어를 영어로 바꾸어라

날짜함수

sysdate : (오늘) 날짜입력. // select sysdate from dual;

add_month(int) : 해당 월(int) 숫자를 달로 환원하여 반환

last_day (date) : 해당 월의 마지막 날짜까지 남은 일수.

select LAST_DAY(sysdate) - sysdate "이번 달 말까지 남은 일수" from dual; // 이번 달 마지막 날 - 오늘

이번 달 말까지 남은 일수

 

---------------------------
28
select LAST_DAY(sysdate) from dual; // 이번 달 마지막 날
LAST_DAY
-------------

22/06/30
months_between(date1,date2) : date1 ~ date2 를 뺀 일수를 월수로 환원하여 반환
select round(months_between(last_day(sysdate),sysdate),2) from dual;
ROUND(MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE),2)
--------------------------------------------------

.9
//months_between(last_day(sysdate),sysdate) 남은 일수 28, 6월 총 일수 30 // 28/30 계산하여 0.9

select to_char(add_months(sysdate, 12), 'yyyy-mm-dd') from dual;
TO_CHAR(A
---------
2023-06-02

​

변환 함수 : 날짜나 기타 수식을 변환하는 함수.

to_char(date,'format') : date를 format으로 변환

 

 

​

to_date() : 문자열을 날짜_열로

 

SQL> select to_date('20220602111900','yyyymmddhh24miss') as one,
2 to_date('20220602','yyyymmdd') as two from dual;

ONE TWO // 원래 시간도 나옴
-------- --------
22/06/02 22/06/02

select sysdate - to_date('20220101','yyyymmdd') from dual
select to_date('20220609','yyyymmdd') - to_date('20220101','yyyymmdd') from dual
select to_date(sysdate) - to_date('20220101','yyyymmdd') from dual
select to_date('20220609') - to_date('20220101') from dual
select sysdate - to_date('20220101') from dual

​

기타함수

DECODE(expr, 조건, 참_값, 거짓_값)​ <= 3항 연산자 ( if ( a>b : 5 : 3 ) )

​

select distict job_id, decode (job_id, 'SA_MAN', 'Sales Dept', 'SA_REP', 'Sales Dept', 'Others') from employees;

JOB_ID DECODE(JOB
---------- ----------
AD_VP Others
IT_PROG Others
PR_REP Others
SA_MAN Sales Dept
// job_id 에서 'SA_MAN' 은 'Sales Dept' 를 'SA_REP' 는 'Sales Dept' 를 나머지는 Others로 표시

select first_name, salary, 
decode(job_id, 'SA_REP', salary+180, 'SALESMAN', salary+150,'MANAGER',salary+150,salary+100) "NEW_SAL"
from employees

// decode 함수를 이용해서 department_id(부서) 각 10,20,30,40 의 부서명과 지역명을 보여라
select department_id, decode(department_id,10,'ACCOUNTING',20,'RESEARCH',30,'SALES'  ,40,'OPERATION') 부서_명,
                      decode(department_id,10,'Seoul'     ,20,'Pusan'   ,30,'Incheon',40,'Kangwon') 지역명,
count(*) 사원수, round(avg(salary),2) 평균급여
from employees
group by department_id

​

CASE WHEN THEN

 

select distinct job_id,
CASE WHEN job_id='SA_MAN' THEN 'Sales_Dept'
WHEN job_id='SA_REP' THEN 'Sales_Dept'
ELSE 'Other'
END as depts
from employees;
// where job_id LIKE 'SA%';

select distinct job_id,
case when job_id = 'SA_MAN' then 'Sales_Dept'
when job_id = 'SA_REP' then Sales_Dept
else 'Other'
end from employees;

SA_REP, SA_MAN 에서 급료가 10000 이상이면, high_sal 7000~10000이면 mid_sal, 그 외, low_sal 표시
case - when - then 문으로 만드시오.

select salary, job_id,
case when salary >= 10000 then 'high_sal'
when salary between 7000 and 10000 then 'mid_sal'
else as 'low_sal'
from employees
where job_id in ('SA_REP','SA_MAN');
// where job_id LIKE 'SA%'
// order by sal_grade desc;

​

보너스 계산

 

select job_id, last_name, salary,
case when salary >= 10000 then salary*0.1
when salary >= 7000 then salary*0.07 // when (salary between 7000 and 10000) then salary * 0.7
else salary*0.05
end as "Bonus"
from employees
where job_id in ('SA_REP','SA_MAN');

JOB_ID LAST_NAME SALARY Bonus
SA_MAN Russell 14000 1400
SA_MAN Partners 13500 1350

​

NVL (None value Logic) - nvl(expr1, expr2) : expr1 이 null 일 때, expr2 값을 할당한다.

expr1 값이 있으면 두고, 없으면 expr2 값을 할당하라.

빅데이터 분석에서 없는 값이 있거나, NULL로 되어 있으면 이런 데이터를 대상으로 어느 연산을 수행하면,

무조건 값이 없음으로 나오기에 이런 NULL (0은 아니지만 값을 지정하지 않은 것)이 있으면

전체 데이터는 무의미해진다.

​

NEXT_DAY() - day 요일 date 날짜

​

집합함수 : 함수를 사용하여,

하나의 행을 적용하여 하나의 결과를 반환하는 것이 단일행 함수.

여러 행(하나의 집단)에 적용해서 하나의 결과를 반환해주는 것이 집합함수이다.

​

AVG(열_명)함수는 평균값을 반환하는데, avg() over(partition by ~ ) <=group by와 유사

여기서는 이렇게 over(oder by ~ ) 등을 사용하여 분석함수로 사용할 수 있다.

- 분석함수 (일반 함수로는 처리하기 어려운 정밀한 함수 패턴) partition 분할

select department_id, salary, avg(salary) over(partition by department_id) from employees

where department_id in (10,20,30)

DEPARTMENT_ID SALARY AVG(SALARY)OVER(PARTITIONBYDEPARTMENT_ID)

10 4400 4400

20 13000 9500

20 6000 9500

30 2600 4150

30 2800 4150