*35일차 (선택 / 그룹함수)

d. 형변환 함수

        to_char            to_date

    ----------------------->  --------------------->

number            char            date

    <----------------------   <-------------------

        to_number         to_char

   

-- to_char(date, format_char) : char
select
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 날짜,
    to_char(sysdate, 'yyyy-mm-dd (day) hh:mi:ss') 날짜,
    to_char(sysdate, 'yyyy-mm-dd (day) (dy) (d)') 날짜, -- 1:일요일, 2:월요일, 3:화요일, 4:수요일, 5...
    to_char(sysdate, 'yyyy"년" mm"월" dd"일"') 날짜,
    to_char(sysdate, 'fmyyyy"년" mm"월" dd"일"') 날짜 -- 포맷문자덕에 생긴 0/공백 제거
from
    dual;

-- to_char(number, format_char) : char
-- 세자리 콤마적용. 소수점이하 처리
-- 실제값보다 형식문자 자리수가 적으면 표시할 수 없다.
select
    to_char(1234567890, '9,999'), -- ######
    to_char(1234567890, '999,999,999,999'), --    1,234,567,890
    to_char(1234567890, 'fm999,999,999,999'),
    123 숫자숫자숫자,
    to_char(1234567890, 'FML999,999,999,999'), -- L 지역통화기호
    to_char(123.456, 'FM99999.99999'), -- 해당자리수가 없을때 소수점이상 공백처리, 소수점이하는 0채움처리
    to_char(123.456, 'FM00000.00000')  -- 해당자리수가 없을때 소수점이상/이하는 0채움처리
from 
    dual;

-- 사원테이블에서 사원명, 급여, 연봉(급여 * 12), 입사일 조회
-- 금액형식지정, 년월일 형식
select
    emp_name,
    to_char(salary, 'FML9,999,999') 급여,
    to_char(salary * 12, 'FML9,999,999,999') 연봉,
    to_char(hire_date, 'FMyyyy"년" mm"월" dd"일"') 입사일
from 
    employee;

-- to_number(char, format_char) : number 리턴
-- 그룹핑처리된 숫자를 순수 숫자로 변환해서 연산처리
select
    to_number('￦8,000,000','L999,999,999') + 1000,
    '1000' + '100', -- +는 숫자사이에서만 가능
    '1000' || '100'  -- || 문자열 연결연산에 사용
from 
    dual;


-- to_date(char, format_char) : date리턴
select
    to_date('1999년 3월 16일', 'yyyy"년" mm"월" dd"일"') 날짜,
    extract(year from to_date('1955/01/01', 'yyyy/mm/dd')) 날짜,
    extract(year from to_date('1955/01/01', 'rrrr/mm/dd')) 날짜,
    extract(year from to_date('55/01/01', 'yy/mm/dd')) 날짜, -- 현재년도 기준으로 100년(2000 ~ 2099)
    extract(year from to_date('55/01/01', 'rr/mm/dd')) 날짜  -- 현재년도(2022) 기준으로 100년(1950 ~ 2049), 
    --    현재년도가 2055년이라면 100년
from 
    dual;
  
-- 나이 구하기 
-- 450505 -> 1945, 550505 -> 1955, 070707 -> 2007
-- yy? rr? 둘다 아니다. 
-- 주민번호의 뒷 첫번째자리를 근거로 1900 + 생년2자리, 2000 + 생년2자리
select
    extract(year from to_date('550505', 'yymmdd')),
    extract(year from to_date('070707', 'yymmdd')),
    
    extract(year from to_date('450505', 'rrmmdd')),
    extract(year from to_date('550505', 'rrmmdd')),
    extract(year from to_date('070707', 'rrmmdd'))
from
    dual;

-- 현재시각으로 부터 1일 2시간 3분 4초뒤를 시각조회
-- 년월일시분초 형태로 출력
-- 날짜 + 숫자(1:하루)
select
    to_char(sysdate + 1 + (2 / 24) + (3 / 24 / 60) + (4 / 24 / 60 / 60), 'yyyy/mm/dd hh24:mi:ss') result
from
    dual;

-- 2022/08/29 남은 일수 구하기
select
    '수료일로부터 D-' || ceil(to_date('2022/08/29', 'yyyy/mm/dd') - sysdate) || '입니다.'
from
    dual;

-- 기간 interval타입
-- 1. interval year to month
-- 2. interval day to second
select
    numtodsinterval(to_date('2022/08/29', 'yyyy/mm/dd') - sysdate, 'day') 기간,
    extract(day from numtodsinterval(to_date('2022/08/29', 'yyyy/mm/dd') - sysdate, 'day')) 일,
    extract(hour from numtodsinterval(to_date('2022/08/29', 'yyyy/mm/dd') - sysdate, 'day')) 시간,
    extract(minute from numtodsinterval(to_date('2022/08/29', 'yyyy/mm/dd') - sysdate, 'day')) 분,
    extract(second from numtodsinterval(to_date('2022/08/29', 'yyyy/mm/dd') - sysdate, 'day')) 초
from 
    dual;

 

 

 

e.기타함수

• nvl(nullable값, null인 경우 사용값) : 값
• nvl2(nullable값, notnull인 경우 사용값, null인경우 사용값) : 값

select
    emp_name, 
    bonus,
    nvl2(bonus, '보너스 있음', '보너스 없음') 보너스여부
from
    employee;

 

*선택함수 decode

• decode(표현식 값1 결과값1 값2 결과값2 값3 결과값3 ...[ 기본값]) : 결과값
• job_code (J1 대표  J2부사장 J3부장 J4차장 J5과장 J6대리 J7사원)

select
    emp_name,
    job_code,
    decode(job_code, 'J1', '대표', 'J2', '부사장', 'J3', '부장', 'J4', '차장', 'J5', '과장', 'J6', '대리', '사원') 직급명
from
    employee; 

-- 사원테이블에서 이름, 주민번호, 성별(남/여) 조회
select
    emp_name,
    emp_no,
    substr(emp_no, 8, 1),
    decode(substr(emp_no, 8, 1), '1', '남', '2', '여', '3', '남', '4', '여') 성별,
    decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') 성별
from
    employee;

 

*선택함수 case

/*

타입1 (조건절로 처리)

    case

        when 조건절1 then 결과값1

        when 조건절2 then 결과값2

        ...

        [else 기본값]

    end

   

타입2 (decode와 유사)

    case 표현식

        when 값1 then 결과값1

        when 값2 then 결과값2

        ...

        [else 기본값]

    end

*/

 

--타입1
select
    emp_name,
    case
        when substr(emp_no, 8, 1) = 1 then '남'
        when substr(emp_no, 8, 1) = 2 then '여'
        when substr(emp_no, 8, 1) = 3 then '남'
        when substr(emp_no, 8, 1) = 4 then '여'
    end gender,
    case
        when substr(emp_no, 8, 1) in ('1','3') then '남'
        when substr(emp_no, 8, 1) in ('2','4') then '여'
    end gender,
    case
        when substr(emp_no, 8, 1) in ('2','4') then '여'
        else '남'
    end gender
from 
    employee;
    
-- 타입2
select
    emp_name,
    case substr(emp_no, 8, 1)
        when '1' then '남'
        when '2' then '여'
        when '3' then '남'
        when '4' then '여'
    end gender,
    case substr(emp_no, 8, 1)
        when '1' then '남'        
        when '3' then '남'
        else '여'
    end gender
from
    employee;

-- 사원테이블에서 생일조회
select
    emp_name,
    emp_no,
    to_char(to_date(substr(emp_no, 1, 6), 'yymmdd'), 'yyyy-mm-dd') 생일,
    to_char(to_date(substr(emp_no, 1, 6), 'rrmmdd'), 'yyyy-mm-dd') 생일,
    decode(substr(emp_no, 8, 1), '1', 1900, '2', 1900, 2000) + substr(emp_no, 1, 2) 출생년도, 
--    decode(substr(emp_no, 8, 1), '1', 1900, '2', 1900, 2000) + substr(emp_no, 1, 2) || substr(emp_no, 3, 4),
    to_char(to_date(decode(substr(emp_no, 8, 1), '1', 1900, '2', 1900, 2000) + substr(emp_no, 1, 2) || substr(emp_no, 3, 4), 'yyyymmdd'), 'yyyy-mm-dd') 생일
from
    employee;

 

 

 

*그룹함수

• 그룹단위로 처리되는 함수
• group by 지정이 없다면 전체행을 하나의 그룹으로 처리
• 일반컬럼과 혼용해 쓸수없다.

-- sum(컬럼) : 총합을 리턴
select
--    emp_name, -- ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
    sum(salary),
    trunc(avg(salary))
from 
    employee;

-- 컬럼값이 null인 경우, 그룹함수에서 제외된다.
select 
    sum(bonus)
from 
    employee;
    
-- 실급여 합계구하기 (가상컬럼)
select
    sum(salary + (salary * nvl(bonus, 0)))
from 
    employee;


-- count(컬럼) : 해당 컬럼이 null이 아닌 행의 수를 리턴
select
    count(bonus),
    count(dept_code),
    count(*) -- *는 한행을 의미, 행이 존재하면 카운팅.
from
    employee;

select
    count(*)
from
    employee
where 
    bonus is not null;
    
-- sum을 이용해 bonus 받는 사원 조회
select
    sum(
        case
            when bonus is null then 0
            when bonus is not null then 1
        end
        ),
    count(bonus)
from
    employee;

-- max / min
-- 숫자, 날짜, 문자열(사전등재순)
select
    max(salary),
    min(salary),
    max(hire_date),
    min(hire_date),
    max(emp_name),
    min(emp_name)
from 
    employee;
    
-- 1. 남자사원의 급여총합을 조회
select
    to_char(sum(salary), 'FML999,999,999') 급여총합
from
    employee
where
    substr(emp_no, 8, 1) in ('1', '3');

-- 2. 부서코드가 D5인 사원들의 보너스 총합 조회 (보너스금액)
select
    sum(salary * nvl(bonus, 0)),
    sum(salary * bonus)
from
    employee
where
    dept_code = 'D5';

-- 3. 남/여 사원의 급여 총합/평균 조회
select
    to_char(sum(salary), 'fml999,999,999')  급여총합,
    to_char(trunc(avg(salary)), 'fml999,999,999') 급여평균
from
    employee
where
--    substr(emp_no, 8, 1) in ('1', '3');
    substr(emp_no, 8, 1) in ('2', '4');
    
-- 남자사원급여 가상컬럼, 여자사원급여 가상컬럼
select
    decode(substr(emp_no, 8, 1), '1', '남', '3', '남', '여') 성별,
    decode(substr(emp_no, 8, 1), '1', salary, '3', salary) 남자사원급여만,
    decode(substr(emp_no, 8, 1), '2', salary, '4', salary) 여자사원급여만
from 
    employee;
    
select
    sum(decode(substr(emp_no, 8, 1), '1', salary, '3', salary)) 남자사원급여총합,
    avg(decode(substr(emp_no, 8, 1), '1', salary, '3', salary)) 남자사원급여평균,
    sum(decode(substr(emp_no, 8, 1), '2', salary, '4', salary)) 여자사원급총합,
    avg(decode(substr(emp_no, 8, 1), '2', salary, '4', salary)) 여자사원급평균
from 
    employee;


-- 4. 전사원의 보너스율 평균을 소수점 둘째자리까지 반올림처리해서 출력
select
--    avg(bonus), -- 9명 평균
    round(avg(nvl(bonus, 0)), 2) -- 24명 평균
from
    employee;