※예제에 사용된 인적사항은 존재하지 않는 가상의 정보입니다.
단일행함수 - 5. 변환함수
기타함수지만 case, decode 등 정말정말정말 자주 쓰이는 함수들이 포함되어있는 이번 함수편!
5-1. ★ case when then else end ★
(사용 공식)
select case 조건적용절
when 조건1 then 결과1
when 조건 2 then 결과2
else 결과 3
end as 컬럼명
from 테이블명;
ex 1 )
select case 5-2
when 4 then '5-2=4 입니다.'
when 1 then '5-2=1 입니다.'
when 3 then '5-2=3 입니다.'
else '모르겠음'
end AS 결과
from dual;
--(결과) 5-2=3 입니다.
ex 2 )
select case
when 4 > 5 then '4는 5보다 큽니다.'
when 5 > 7 then '5는 7보다 큽니다.'
when 3 > 2 then '3은 2보다 큽니다.'
else '모르겠음'
end AS 결과
from dual;
--(결과) 3은 2보다 큽니다.
5-2. ★ decode ★
(사용 공식)
decode(조건적용절, 조건1, 결과1,
조건2, 결과2,
결과3) as 컬럼명
from 테이블명;
ex 1 )
select case 5-2
when 4 then '5-2=4 입니다.'
when 1 then '5-2=1 입니다.'
when 3 then '5-2=3 입니다.'
else '모르겠음'
end AS 결과1,
decode(5-2, 4, '5-2=4 입니다.'
, 1, '5-2=1 입니다.'
, 3, '5-2=3 입니다.'
, '모르겠음') AS 결과2
from dual;
(결과)
5-3. greatest , least
: 가장 큰 값(greatest)과 가장 작은 값(least)을 알려주는 함수
ex 1)
select greatest(10, 90, 100, 80)
, least(10, 90, 100, 80)
from dual;
--(결과) 100 10
ex 2)
select greatest('가','나나','다다다','라라라라')
, least('가','나나','다다다','라라라라')
from dual;
--(결과) 라라라라 가
5-4. rank 등수(석차)구하기 , dense_rank 서열구하기
: rank, dense_rank는 주어진 컬럼에서 값의 순위를 계산한다.
하지만 rank는 동일한 순위를 다른 건수로 인식하고, dense_rank는 동일한 순위를 동일한 건수로 인식한다.
-특정 데이터 순위 확인
rank(조건) within group (order by 조건 컬럼명 asc/desc)
-전체 순위 확인
rank(조건) over (order by 조건 컬럼명 asc/desc)
ex 1 )
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, nvl(salary + (salary * commission_pct), salary) AS 월급
, rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급등수
, dense_rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급서열
from employees;
(결과)
ex 2 )
select department_id AS 부서번호
, employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, nvl(salary + (salary * commission_pct), salary) AS 월급
, rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급전체등수
, dense_rank() over(order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급전체서열
, rank() over(partition by department_id
order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급부서내등수
, dense_rank() over(partition by department_id
order by nvl(salary + (salary * commission_pct), salary) desc) AS 월급부서내서열
from employees
order by 1;
(결과)
[문제]
employees 테이블에서 모든 사원들에 대해 사원번호, 사원명, 주민번호, 성별, 현재나이, 월급, 입사일자, 정년퇴직일 을 나타내세요.
여기서 정년퇴직일이라 함은
해당 사원의 생월이 3월에서 8월에 태어난 사람은 해당사원의 나이(한국나이)가 63세가 되는 년도의 8월 31일로 하고,
해당 사원의 생월이 9월에서 2월에 태어난 사람은 해당사원의 나이(한국나이)가 63세가 되는 년도의 2월말일(2월28일 또는 2월29일)로 한다.
[풀이]
1) 성별 구하기
case substr(jubun,7,1)
when '1' then '남'
when '3' then '남'
else '여'
end AS 성별1
, decode(substr(jubun,7,1), '1', '남'
, '3', '남'
, '여') AS 성별2
, case
when substr(jubun,7,1) in('2','4') then '여'
else '남'
end AS 성별3
2) 현재나이 구하기
-- 현재년도 - (태어난년도) + 1 AS 현재나이
-- 태어난년도는 주민번호에서 성별을 알수 있는 값이 '1' 또는 '2' 이면 1900년대생이고, 주민번호에서 성별을 알수 있는 값이 '3' 또는 '4' 이면 2000년대생이다.
-- 1900 또는 2000 + (주민번호 앞에서 2자리) 하면 태어난년도가 나온다.
=> extract(year from sysdate) - ( case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + substr(jubun,1,2) ) + 1 AS 현재나이
3) 정년퇴직일 구하기
-- 사원의 현재나이가 60세 이라면 3년(3*12개월)뒤 정년퇴직.
add_months(현재날짜, 3*12) ==> add_months(현재날짜, (63-60)*12) ==> add_months(현재날짜, (63-현재나이)*12)
-- 사원의 현재나이가 55세 이라면 8년(8*12개월)뒤 정년퇴직.
add_months(현재날짜, 8*12) ==> add_months(현재날짜, (63-55)*12) ==> add_months(현재날짜, (63-현재나이)*12)
-- , add_months(sysdate, (63-(extract(year from sysdate) - ( case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + substr(jubun,1,2) ) + 1))*12)
-- , to_char( add_months(sysdate, (63-(extract(year from sysdate) - ( case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + substr(jubun,1,2) ) + 1))*12)
-- , 'yyyy') || '-08-01' -- || '-02-01'
-- , to_char( add_months(sysdate, (63-(extract(year from sysdate) - ( case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + substr(jubun,1,2) ) + 1))*12)
-- , 'yyyy') || case when substr(jubun,3,2) between '03' and '08' then '-08-01' else '-02-01' end
-- , last_day( to_char( add_months(sysdate, (63-(extract(year from sysdate) - ( case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + substr(jubun,1,2) ) + 1))*12)
-- , 'yyyy') || case when substr(jubun,3,2) between '03' and '08' then '-08-01' else '-02-01' end
-- )
[정답]
select employee_id AS 사원번호
, first_name || ' ' || last_name AS 사원명
, jubun AS 주민번호
, case substr(jubun,7,1)
when '1' then '남'
when '3' then '남'
else '여'
end AS 성별1
, decode(substr(jubun,7,1), '1', '남'
, '3', '남'
, '여') AS 성별2
, case
when substr(jubun,7,1) in('2','4') then '여'
else '남'
end AS 성별3
, extract(year from sysdate) - ( case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + substr(jubun,1,2) ) + 1 AS 현재나이
, nvl(salary + (salary * commission_pct), salary) AS 월급
, to_char(hire_date , 'yyyy-mm-dd') AS 입사일자
, to_char( last_day( to_char( add_months(sysdate, (63-(extract(year from sysdate) - ( case when substr(jubun,7,1) in('1','2') then 1900 else 2000 end + substr(jubun,1,2) ) + 1))*12), 'yyyy') ||
case when substr(jubun,3,2) between '03' and '08' then '-08-01' else '-02-01' end), 'yyyy-mm-dd') AS 정년퇴직일
from employees
order by 1;
(결과)
