[DB] 4. 함수 - 숫자 함수 / 형 변환 함수 / 집합 연산자 / 분석 함수

숫자 함수

 

1️⃣ round (반올림)

• 1/0/-1은 자리수를 의미한다. -1은 정수에 반올림 시켜준다.

select round(45.923,1), round(45.923, 0), round(45.923, -1) from dual;

2️⃣ trunc(절삭)

select trunc(45.923, 1), trunc(45.923, 0), trunc(45.923, -1) from dual;

3️⃣ abs(절대값), ceil(올림), floor(내림)

select abs(-24), ceil(3.14), floor(3.14) from dual;

4️⃣ mod(나머지) java의 %와 같은..

select mod(10, 3) as 나머지, 10/3 as 몫 from dual;

5️⃣ 날짜함수

select sysdate from dual; -- 년월일
select systimestamp from dual; -- 년월일시분초

6️⃣ 날짜연산(결과는 일수)

select first_name, sysdate - hire_date from employees;
select first_name, (sysdate-hire_date) / 7 as week from employees;
select first_name, (sysdate-hire_date) / 365 as year from employees;

차례대로 입사한지 일 / 주 / 년 으로 계산한 결과이다.

7️⃣ trunc, round

select round(sysdate) from dual;
select round(sysdate, 'year') from dual; -- 년기준 반올림
select round(sysdate, 'month') from dual; -- 달기준 반올림
select round(sysdate, 'day') from dual; -- 해당 주의 일요일

select trunc(sysdate) from dual;
select trunc(sysdate, 'year') from dual; -- 년기준 반올림
select trunc(sysdate, 'month') from dual; -- 달기준 반올림
select trunc(sysdate, 'day') from dual; -- 해당 주의 일

• salary는 숫자 컬럼인데 5000은 문자로 비교해도 정상적으로 비교가능하다 (자동형변환 - 문자 숫자간 자동형변환)

select * from employees where salary >= '5000';

 

 

형 변환 함수

 

1️⃣ 형변환함수

select to_char(sysdate, 'YYYY-MM-DD') from dual;
select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;
select to_char(sysdate, 'YYYY-MM-DD Month HH:MI:SS') from dual;

select first_name, to_char(hire_date, 'YYYY-MM-DD HH:MI:SS') from employees;
select first_name, to_char(hire_date, 'YYYY"일"MM"월"DD"일"') from employees;

2️⃣ 숫자를 문자로

select to_char(20000, '99999') || '원' from dual; --9는 자리수를 의미한다.
select to_char(20000, '999') || '원' from dual; -- 자리수가 부족할때는 #으로 출력

select to_char(20000.14, '99999.99') || '원' from dual; -- 소수점 표시
select to_char(20000.14, '99999') || '원' from dual; 

select to_char(20000.14, '999,999,999') as  from dual;

select to_char(salary, '$999,999') as salary from employees; --앞에 $를 붙여서 출력
select to_char(salary, 'L999,999') as salary from employees; --앞에 원화 기호를 붙여서 출력

🔸 salary를 현재 환율에 맞춰서 한화로 표현 (환율: 1364원)

--앞에 원화 기호를 붙여서 출력
select to_char(salary, '$999,999') as USD, to_char(salary*1364, 'L999,999,999') as 한화 from employees;

3️⃣ to_number (문자를 숫자로)

-- 자동형변환
select '2000' + 2000 from dual; 
-- 명시적으로 형변환
select to_number('2000') + 2000 from dual; 

-- 형변환이 불가능 한 것들 (누가봐도 문자인것 $이런것은 숫자로 바꿀 수 없다)
select '$3,000' + 3000 from dual; 
-- to_number로 형식을 지정해주면 숫자로 변환이 가능하다.
select to_number('$3,000', '$9,999') + 3000 from dual;

4️⃣ to_date(문자를 날짜로)

select sysdate - '2022-08-01' from dual; -- 에러
select sysdate - to_date('2022-08-01') from dual;
select sysdate - to_date('2022-08-01', 'YYYY-MM-DD') from dual;

select to_date('3030-03-31 12:23:03', 'YYYY-MM-DD HH:MI:SS') from dual;

🔸 xxxx년 xx월 xx일 문자열 형식으로 변환하세요

select to_char(to_date('20050102'), 'YYYY"년"MM"월"DD"일"') from dual;

 

5️⃣ NVL (NULL에 대한 처리)

select NVL(null, 0), NVL(3.14, 0)from dual;

select NVL(commission_pct, 0) from employees;

6️⃣ NVL2(컬럼, NULL 아닌 경우 값, NULL일 경우 값)

select NVL2(null, '널아님', '널') from dual;
select NVL2(commission_pct, salary+(salary*commission_pct), salary) from employees;

select first_name,
    salary,
    commission_pct,
    NVL2(commission_pct, salary+(salary*commission_pct), salary)
from employees;

7️⃣ decode(if~~)

select decode('a', 'a', 'a입니다', 'a가 아닙니다') from dual;

 

🔸 제일 처음의 값이, 조건, 일치하면 결과, 제일마지막은 else와 같은것

select decode( 'C', 'A', 'A입니다',
                    'B', 'B입니다',
                    'C', 'C입니다',
                    '전부아닙니다')
from dual;

select job_id,
        salary,
        decode(job_id, 'IT_PROG', salary*0.1, 'FI_MGR', salary * 0.2, 'AD_VP', salary*0.3, salary)
from employees;

8️⃣ case when then else end

select job_id,
        salary,
        case job_id when 'IT_PROG' then salary * 0.1
                    when 'FI_MGR' then salary * 0.2
                    when 'AD_VP' then salary * 0.3
        end
from employees;

select job_id,
        salary,
        case when job_id = 'IT_PROG' then salary * 1.1
             when job_id = 'FI_MGR' then salary * 1.2
             when job_id = 'AD_VP' then salary * 1.3
             else salary
        end as result
from employees;

 

연습문제

 

--문제 1.
--현재일자를 기준으로 EMPLOYEE테이블의 입사일자(hire_date)를 참조해서 근속년수가 10년 이상인
--사원을 다음과 같은 형태의 결과를 출력하도록 쿼리를 작성해 보세요. 

select employee_id as 사원번호,
        first_name ||' '|| last_name as 사원명,
        hire_date as 입사일자,
        trunc(to_number(sysdate - hire_date) / 365) as 근속년수
        from employees where trunc(to_number(sysdate - hire_date) / 365) >= 10;


--조건 1) 근속년수가 높은 사원 순서대로 결과가 나오도록 합니다
select employee_id as 사원번호,
        first_name ||' '|| last_name as 사원명,
        hire_date as 입사일자,
        trunc(to_number(sysdate - hire_date) / 365) as 근속년수
        from employees where trunc(to_number(sysdate - hire_date) / 365) >= 10 order by hire_date asc;

--문제 2.
--EMPLOYEE 테이블의 manager_id컬럼을 확인하여 first_name, manager_id, 직급을 출력합니다.
--100이라면 ‘사원’, 
--120이라면 ‘주임’
--121이라면 ‘대리’
--122라면 ‘과장’
--나머지는 ‘임원’ 으로 출력합니다.
--조건 1) department_id가 50인 사람들을 대상으로만 조회합니다

select first_name, manager_id, department_id, 
    case manager_id when 100 then '사원'
                    when 120 then '주임'
                    when 121 then '대리'
                    when 122 then '과장'
                    else '임원'
    end as 직급
from employees where department_id >= 50;

 

집합 연산자

 

• 집합 연산자 (위 아래 column 개수가 정확히 일치해야 한다)
• union (합집합 중복 x), union all(합집합 중복 O), intersect(교집합), minus(차집합)

 

1️⃣ union

select employee_id, first_name from employees where hire_date like '04%'
union
select employee_id, first_name from employees where department_id = 20;

2️⃣ union all

select employee_id, first_name from employees where hire_date like '04%'
union all
select employee_id, first_name from employees where department_id = 20;

3️⃣ intersect

select employee_id, first_name from employees where hire_date like '04%'
intersect
select employee_id, first_name from employees where department_id = 20;

4️⃣ minus

select employee_id, first_name from employees where hire_date like '04%'
minus
select employee_id, first_name from employees where department_id = 20;

🔸 컬럼의 개수만 맞다면 여러개의 select구문을 union, union  all 시킬 수 있다.

select employee_id, first_name, salary from employees where department_id = 20
union all
select employee_id, first_name, salary from employees where salary > 10000
union all
select 300, 'hong', 20000 from dual;

 

 

분석함수

 

• 분석함수() over(조건)

select employee_id,
       department_id,
       salary,
       rank() over(order by salary desc) as 중복순위O,
       dense_rank() over(order by salary desc) as 중복순위x,
       row_number() over(order by salary desc) as 일렬번호
from employees
order by salary desc;