4일차에 배운 내용을 정리하였다.
목차는 다음과 같다. :
01. 조건 치환
-01 decode
-02 case
02. 숫자 함수
-01 반올림/버림
-02 올림/내림
-03 절댓값
-04 나머지
-05 양음판별
03. 날짜 함수
-01 기본 포맷 및 주의사항
-02 문자상수의 날짜타입 변환
-03 날짜 파싱
-04 날짜 출력 형식 변환
-05 날짜 언어 변경
-06 기타 날짜 함수
01. 조건 치환
-01 decode(대상, 조건1, 치환1, 조건2, 치환2,....,그 외 리턴)
오라클 전용 함수이며 값 자체가 특정 조건을 만족할 때 아예 다른값으로 치환한다.
또한 여러개의 조건을 전달 가능하며 조건은 상수의 형태여야만 한다.
'그 외 리턴'은 그 어떤 조건에도 만족하지 않는 값이 발생했을 떄 치환할 값인데, 이를 지정해주지 않으면 NULL이 리턴된다.
if문과 비슷하나, sql은 if문이 존재하지 않기 때문에 다른 함수를 사용해야 한다.
select deptno,
decode(deptno, 10, '인사부'),
decode(deptno, 10, '인사부', '총무부')
from emp;
decode(deptno, 10, '인사부'), : deptno가 10인 경우는 인사부, 그 외 리턴값을 지정해주지 않아 NULL이 출력되었다.
decode(deptno, 10, '인사부', '총무부') : deptno가 10인 경우는 인사부, 그 외의 값은 총무부로 출력되었다.
또한 decode를 사용한 이중분기는 다음과 같다. 이중분기는 다음 그림과 같이 조건 내에 조건이 존재하는 형태이며, decode(대상, decode(대상, 조건1, 치환1...... 식으로 decode 내 decode가 위치한다.
emp table을 이용하여 부서번호(depno)가 10이면서 직업(job)이 'CLERK'면 'A', 직업이 'CLERK'가 아니면 'B', 그 외는 'C'인 경우 쿼리는 다음과 같다.
select deptno, job,
decode(deptno, 10, decode(job, 'CLERK', 'A', 'B'), 'C') as 분류
from emp
order by 1,2;
그러나 업체에 따라 성능 문제로 decode 내 decoode를 사용하는 쿼리 작성을 지양하기도 하며, 이 경우 case문 이용을 권장한다고 한다.
-02 case
case when 조건1 then 리턴1
when 조건2 then 리턴2
...
else 그 외 리턴
end [as 별칭]
case는 보다 넓은 범위의 조건 치환이 가능하다. decode는 값이 일치할 때만 치환 가능하지만 case는 모든 연산자에 대한 조건 치환이 가능하다.
또한 다중분기에 대한 가독성이 우수하며, 축약형 문법이 존재한다.
else 값은 생략 가능하나 선언하지 않으면 NULL을 리턴한다.
emp 테이블을 이용하여 부서번호(deptno)가 10이면 인사부, 20이면 총무부, 30이면 재무부라는 값을 리턴할 시, decode문과 case문의 쿼리는 다음과 같다.
select ename, deptno,
decode(deptno, 10, '인사부', 20, '총무부', 30, '재무부') as dname1,
case when deptno = 10 then '인사부'
when deptno = 20 then '총무부'
else '재무부'
end as dname2
from emp
order by deptno;
decode와 case 모두 같은 결과를 출력하였음을 확인할 수 있다.
또한 case문은 축약형을 지원한다.
case 조건대상 when 값1 then 리턴1
when 값2 then 리턴2
각 조건의 대상이 모두 같으면서 일치조건일 경우에 한해 축약형 문법이 사용 가능하며, 조건대상과 비교값의 데이터 타입이 일치해야 한다. (일치하지 않을 시 에러 발생)
- decode : 데이터 타입이 일치하지 않아도 자동으로 변환을 해줌
- case : 일반적인 문법 사용 시 데이터 타입이 일치하지 않아도 사용 가능
- case(축약형 문법) : 데이터 타입이 반드시 일치해야 사용 가능
student 테이블을 이용하여 주민등록번호(JUMIN)의 7번째 자리를 기준으로 성별을 구하고, 학년(GRADE)이 3,4학년인 학생의 정보만 열람하는 쿼리는 다음과 같다.
select studno, name, grade,
case substr(jumin, 7, 1) when '1' then '남자'
else '여자'
end as 성별
from student
where grade in (3,4);
축약형 case문을 사용하였으며 원하는 결과가 출력되었음을 확인할 수 있다.
02. 숫자 함수
-01 반올림/버림
자릿수의 경우 양수와 음수 모두 표현 가능하다.
- 양수 : 소숫점 자리(반올림한 결과가 해당 자릿수를 만족해야 함)
- 음수 : 정수 자리(자릿수에서 직접 반올림.)
round(대상[, 자릿수])
자릿수는 생략 가능하며 생략 시 소숫점 첫 번째 자리에서 반올림하여 정수 자리로 리턴한다.
select round(10.23),
round(10.5678, 2),
round(1567.89, -2)
from dual;
trunc(대상[,자릿수])
자릿수는 생략 가능하며 생략 시 소숫점 천 번째에서 버림하여 정수 자리로 리턴한다.
select trunc(10.23),
trunc(10.5678, 2),
trunc(1567.89, -2)
from dual;
-02 올림/내림
ceil(대상)
올림이며 크거나 작은 값중에 가장 작은 정수를 리턴한다.
floor(대상)
내림이며 작거나 같은 값중에 가장 큰 정수를 리턴한다.
select ceil(-3.3),
floor(-3.3)
from dual;
-03 절댓값
abs(대상)
절댓값을 구한다.
select abs(-5), abs(5), abs(0)
from dual;
-04 나머지
mod(대상, 나눌값)
대상을 나눌값으로 나눈 후 남은 나머지를 반환한다.
0으로 나누려고 하면 대상이 그대로 리턴된다.
select mod(10, 3),
mod(10, 0)
from dual;
-05 양음판별
sign(대상) : 대상값이 양수이면 1, 음수면 -1, 0과 같으면 0 리턴
비교연산자 사용이 어려운 상황일 때 많이 사용한다.
값이 특정 분기점 기준으로 값이 더 커다란지, 작은지, 같은지를 판단한다.
예)
sal > 3000 여부 확인 -> (sal - 3000) 값이 항상 양수
sal < 3000 여부 확인 -> (sal - 3000) 값이 항상 음수
sal = 3000 여부 확인 -> (sal - 3000) 값이 항상 0
select sign(3000), sign(-3000), sign(0)
from dual;
문자 그대로 값이 양수인지, 음수인지, 0인지를 판단한다.
emp 테이블에서 각 직원의 이름과 급여, 급여등급을 출력하되 급여등급은 3000 미만일 때 B, 이상일 때 A라는 조건으로 쿼리를 작성하였다.
select *
from emp;
select ename, sal, sal-3000, sign(sal-3000),
case when sal < 3000 then 'B'
else 'A'
end as grade1,
decode(sign(sal-3000), -1, 'B', 0, 'A','A') as grade2
from emp
order by sal desc;
select ename, sal,
case문과 deccode문 모두 동일한 결과가 출력되었다.
03. 날짜 함수
-01 기본 포맷 및 주의사항
날짜는 DBMS마다 기본 출력 포맷을 가지고 있다. ('연/월/일', '일/월/연' 등...)
또한 IDE마다 날짜 출력 포맷이 다르다.
따라서 IDE를 사용할 때 보이는 날짜 포맷 = DBMS의 날짜 포맷이 아닐 수도 있다.
DBMS 날짜 포맷과 IDE 날짜 포맷이 다르면 다음과 같은 결과를 확인할 수 있다.
sysdate : IDE(orange) 포맷으로 출력
substr : DBMS 포맷으로 출력
select sysdate,
substr(sysdate, 1, 4) as dbms
from dual;
2026이 아닌 26/0이 출력되었다.
DBMS가 가지고 있는 포맷을 확인하기 위해서는 cmd창에서 sqpplus scott/oracle 접속 후 sysdate로 확인 가능하다.
26/03/10의 형태로 앞서 substr(sysdate, 1, 4)의 결과물이 26/0인 이유를 알 수 있다.
-02 문자상수의 날짜타입 변환
to_date(대상)
날짜처럼 생겼어도 문자형인 경우가 있어, 문자상수의 경우 to_date를 통한 날짜타입 변환이 필요하다.
select '2026/03/10' + 100
from dual;
보이는 것은 '2026/03/10'이지만 문자형이기에 날짜 계산이 되지 않는다.
select to_date('2026/03/10') + 100
from dual;
to_date를 통한 날짜변환이 진행되었음을 확인할 수 있다.
-03 날짜 파싱
to_date(대상[, 포맷])
파싱이 필요한 (문법적 해석이 필요한) 날짜처럼 생긴 문자열이나 해당 값을 가지고 있는 컬럼을 대상으로 하며, 포맷은 다음과 같다. :
- 연도 yyyy/yy/rrrr/rr
- 월 : mm/month/mon
- 일 : dd
- 시 : hh,hh12/hh24
- 분 : mi
- 초 : ss,
- 요일 : day
- 분기 : Q
포맷은 대소구분이 없다. to_date에 특정한 포맷을 지정하지 않고 대상을 넣으면 DBMS가 기본적으로 가지고 있는 형식에 맞추어 해석하지만, DBMS의 포맷과 데이터가 다를 수 있으니 필요에 따라 포맷을 지정해준다.
select to_date('12/03/20'), -- 순서대로 연/월/일로 해석(dbms 기본 순서)
to_date('12/03/20', 'mm/dd/yy') -- 순서대로 월/일/연으로 해석(포맷 순서대로)
from dual;
또한 to_date는 해석해주는 역할을 하는 것이지 출력에는 관여하지 않는다. 날짜로 인지할 수 있는 곳까지만 관여한다.
select substr(to_date('12/03/20', 'mm/dd/yy'), 2)
from dual;
-04 날짜 출력 형식 변환
날짜를 출력하는 형식을 변경하기 위해서는 2가지 방법이 존재한다. :
1) DBMS 자체 날짜 포맷 변경(권고 X, 매우 위험)
2) IDE 날짜 포맷 변경
3) 쿼리를 통한 세션에서의 날짜 포맷 변경 출력(권고)
alter session set nls_date_format = 'mm/dd/yyyy';
to_char(날짜, 포맷)
데이터 타입을 문자로 바꾸지만 날짜출력방식을 변경할 때에도 사용한다.
포맷을 입력하여 원하는 값을 출력할 수 있다.
select to_char(sysdate, 'yyyy') 연도,
to_char(sysdate, 'mm') 월,
to_char(sysdate, 'dd') 일,
to_char(sysdate, 'hh') 시,
to_char(sysdate, 'mi') 분,
to_char(sysdate, 'ss') 초,
to_char(sysdate, 'day') 요일1,
to_char(sysdate, 'DY') 요일2, --축약형
to_char(sysdate, 'd') 요일3 -- 숫자 출력
from dual;
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
다만 요일을 숫자로 출력할 시 일요일부터 토요일의 순서로 1번부터 7번의 값을 가진다. (SQL은 미국기준으로 요일을 셈)
월요일부터 일요일의 순으로 출력을 원한다면 별도로 조건을 지정해야 한다.
-05 날짜 언어 변경
날짜를 표현하는 언어를 변경하기 위해서는 2가지 방법이 존재한다. :
1) DBMS 자체 날짜 변경(권고 X)
2) 세션마다 날짜 언어 변경
alter session set nls_date_language = 'american'; -> 영어로 변경
alter session set nls_date_language = 'korean'; -> 한글로 변경
일반적으로 포맷에 대소문자를 구분하지 않지만, 날짜 언어가 영어로 설정되어 있으며 문자형태로 출력되는 날짜 포맷의 경우는 대소문자를 구분한다.
select to_char(sysdate, 'MONTH') as 월1, -- 월의 문자 출력 형식
to_char(sysdate, 'MON') as 월2, -- 월의 문자 출력 형식(축약형)
to_char(sysdate, 'DDSPTH') as "일(서수)" -- 일자의 서수식 출력
from dual;
포맷을 대문자로 설정하니 결과도 모두 대문자로 출력되었음을 확인할 수 있다.
-06 기타 날짜 함수
1) round / trunc (대상[, 자릿수])
숫자에만 사용하는 것이 아니라 날짜에도 사용 가능하다.
자리수는 생략 가능하며, 생략할 시 시간 단위에서 반올림 혹은 버림을 진행하여 일자 단위에서 맞춘다.
select sysdate,
round(sysdate),
round(sysdate, 'MONTH'),
round(sysdate, 'YEAR'),
trunc(sysdate)
from dual;
2) months_between
months_between(날짜1, 날짜2)
두 날짜 사이의 월(month) 수를 리턴한다.
emp table을 사용하여 각 직원의 이름 및 근무개월수를 출력하는 쿼리는 다음과 같다.
select ename, sysdate, hiredate, trunc(months_between(sysdate, hiredate)) as 근무개월수
from emp;
또한 전체 근무 일수를 다음과 같은 쿼리를 통해 출력할 수 있다.
select ename, sysdate, hiredate,
trunc((sysdate-hiredate)/365),
trunc(months_between(sysdate, hiredate)/12)
from emp;
다만 윤달을 포함하여 보다 정확한 근무 일수 계산을 원한다면 단순히 365로 나누는 것이 부정확할 수도 있다.
근속달을 구한 뒤 12로 계산하는 것이 좀 더 정확하다.
3) last_day
last_day(날짜)
해당 날짜가 포함된 월의 마지막 날짜를 리턴한다.
select sysdate, last_day(sysdate)
from dual;
4) next_day
next_day(날짜, 요일)
날짜 뒤에 오는 특정 요일에 해당되는 일자를 리턴한다.
select sysdate,
next_day(sysdate, '수'),
next_day(sysdate, 4),
next_day(next_day(sysdate, '수'), '수')
from dual;
5) add_months
add_months(날짜, 숫자)
해당 날짜로부터 n개월 되는 날짜를 리턴한다.
음수를 입력하는 경우 기준 날짜로부터 이전 날짜를 리턴한다.
select sysdate,
sysdate+100 as "100일 뒤",
sysdate+365*3 as "3년 뒤",
add_months(sysdate, 3)
from dual;
select sysdate,
sysdate + 3,
add_months(sysdate, 3),
add_months(sysdate, 36)
from dual;
6) extract
etract(단위 from 날짜)
날짜의 일부를 추출한다. to_char로도 가능하다.
단위는 year, month, day, hour 등을 이용 가능하며, 시간의 경우 데이터는 가지고 있으나 문법적 선언이 불가능하다.
select extract(year from sysdate)
from dual;
> 날짜에서의 과반 기준이 이상하다?
물론 날짜를 반올림할 일이 몇 번이냐 있겠냐만서도, 2월은 28일까지 있고 다른 월은 30일, 31일까지 존재한다.
그러면 30일같은 경우는 15일을 기준으로 과반을 결정할 것인데, 이 역시 반으로 딱 자르려하니 기준이 애매한 느낌이라 시간을 기준으로 자르는 것인지, 15일이면 무조건 다음달로 계산하는지도 헷갈리는 것이다.
시간을 전부 절삭하고 생각해보려해도 긴가민가하다.
그렇다면... ->
날짜에서의 반올림은 (한 달의 일 수 / 2) 를 기준으로 계산하는가?
select round(to_date('2026/02/14', 'yyyy/mm/dd'), 'MONTH') "2월 14일",
round(to_date('2026/02/15', 'yyyy/mm/dd'), 'MONTH') "2월 15일",
round(to_date('2026/02/16', 'yyyy/mm/dd'), 'MONTH') "2월 16일",
round(to_date('2026/03/15', 'yyyy/mm/dd'), 'MONTH') "3월 15일",
round(to_date('2026/03/16', 'yyyy/mm/dd'), 'MONTH') "3월 16일",
round(to_date('2026/04/14', 'yyyy/mm/dd'), 'MONTH') "4월 14일",
round(to_date('2026/04/15', 'yyyy/mm/dd'), 'MONTH') "4월 15일",
round(to_date('2026/04/16', 'yyyy/mm/dd'), 'MONTH') "4월 16일"
from dual;
이상하게도 모두 16일 이상일 때 올림처리가 되었다.
31일 기준인가? 혹은 30일 기준인데 15일에 대한 처리가 애매해서 16일을 기준으로 세운 것일까?
그러면 2월은 어떻게 되는가? 28일의 절반은 14일인데, 그렇다면 앞의 기준을 따르려면 15일에 반올림되어야 하는 것 아닐까?
강사님께 질문을 드렸고, 15일 내지 16일 기준으로 반올림이 진행되는 기작인 것 같다고 말씀주셨다.
개발자가 편의상 의도적으로 반올림이 계산되는 일을 지정한 것 같다는 말씀에 찾아보니 정말로 월에 관계없이 16일을 기준으로 반올림하는 함수임을 알 수 있었다.
(참고 : ROUND and TRUNC Date Functions)
학생들의 월별 생일 분포라거나 특정 물건이 잘 판매되는 월의 경우 굳이 반올림을 사용할 필요도 없고,
월에 해당하는 부분만 다른 함수들을 이용해 추출할 수도 있을 것이다.
다만 날짜를 반올림해야하는 상황이 생긴다면 통상적으로 월의 절반 정도로 여겨지는 15일 + 1일 정도가 기준점이라는 정도만 알아두면 될 것 같다.
> 그러나 또다시 신경이 쓰이기 시작했다...
그러면 월초와 중순과 월말은 대체 어떻게 구분할까?
타 분야에서는 어떻게 작성할지는 모르겠지만 굉장히 모호한 표현이긴 하다.
어쩌면 애매하게 말할바에는 일 혹은 주 단위로 묶어서 이야기하는 것이 더 나을지도 모르겠다.
하지만 가끔 생물에 대한 데이터가 모이게 되었을 때 어떤 기준으로 분류해야할지, 혹은 결과를 어떻게 해석해야할지 고민하며 도감을 열어보면 이런식으로 기술 할 때가 있다. :
[해당 종은 3월 말부터 6월 초까지 번식한다.]
만약 1년간 조사를 진행하였는데 연초에 한해 출현이 확인된 종이 있고, 그 종의 주요 출현시기를 도감처럼 기술해야 한다면 어떻게 분류해야할까?
아직 create 함수에 대해 진도가 나가지 않았고 날짜에 대한 지정이 계속 오류가 나는 상황이어서 테이블 생성은 chatGPT의 도움을 받았다.
create table test_month (testdate date);
insert into test_month values (date '2026-02-01');
insert into test_month values (date '2026-02-03');
insert into test_month values (date '2026-02-02');
insert into test_month values (date '2026-02-09');
insert into test_month values (date '2026-02-11');
insert into test_month values (date '2026-02-04');
insert into test_month values (date '2026-02-16');
insert into test_month values (date '2026-02-15');
insert into test_month values (date '2026-02-20');
insert into test_month values (date '2026-02-27');
insert into test_month values (date '2026-02-28');
insert into test_month values (date '2026-02-04');
insert into test_month values (date '2026-01-24');
insert into test_month values (date '2026-01-26');
insert into test_month values (date '2026-01-17');
insert into test_month values (date '2026-03-02');
insert into test_month values (date '2026-03-01');
insert into test_month values (date '2026-02-24');
commit;
select * from test_month;
select to_char(testdate,'yyyy-mm') as 조사월,
case
when to_char(testdate,'dd') between '01' and '10' then '초반'
when to_char(testdate,'dd') between '11' and '20' then '중반'
else '후반'
end as 시기,
count(*) as 수
from test_month
group by to_char(testdate,'yyyy-mm'),
case
when to_char(testdate,'dd') between '01' and '10' then '초반'
when to_char(testdate,'dd') between '11' and '20' then '중반'
else '후반'
end
order by 1,2;
1월 말부터 2월 초반에 가장 출현빈도수가 높은 것을 확인할 수 있다.
select to_char(testdate,'yyyy-mm') as 조사월,
case
when to_char(testdate,'dd') between '01' and '07' then '1번째 주'
when to_char(testdate,'dd') between '08' and '14' then '2번째 주'
when to_char(testdate,'dd') between '15' and '21' then '3번째 주'
else '4번째 주'
end as 시기,
count(*) as 수
from test_month
group by to_char(testdate,'yyyy-mm'),
case
when to_char(testdate,'dd') between '01' and '07' then '1번째 주'
when to_char(testdate,'dd') between '08' and '14' then '2번째 주'
when to_char(testdate,'dd') between '15' and '21' then '3번째 주'
else '4번째 주'
end
order by 1,2;
월요일이 1일로 시작한다는 전제 하에 주 단위로 쪼개보면 2월 첫째주에 이 종이 가장 활발하게 출현하였다~ 정도로 이야기 할 수 있다.
끝!
'아이티윌_데이터 분석 55기 > 강의내용 필기_SQL' 카테고리의 다른 글
| #6 6일차_ JOIN : equi join, non equi join, 세 개 이상 테이블의 조인 (0) | 2026.03.12 |
|---|---|
| #5 5일차_날짜 파싱 시 유의사항, 변환 함수, NULL 치환 함수 (0) | 2026.03.11 |
| #3 3일차_ORDER BY, 문자 함수 (0) | 2026.03.09 |
| #2) 2일차_SELECT문 : WHERE, GROUP BY, HAVING (0) | 2026.03.08 |
| #1) 1일차_오라클과 오렌지 설치하기, SELECT문 (0) | 2026.03.08 |