23-02-01 (1) 데이터베이스 (날짜함수, 그룹함수)

DATABASE

23-02-01 (1) 데이터베이스 (날짜함수, 그룹함수)

모건이삼촌 2023. 2. 1. 13:20

※ 복습

null - is null, is not null

함수

문자함수 - 대상이 문자, 대소문자 관련

문자열 길이반환 함수 - length, lengthb

문자조작 함수 - lpad, rpad 지정문자 삽입 / ltrim, rtrim 지정문자 삭제

숫자함수 - round, trunc, mod, ceil, floor

날짜함수

2) 날짜 함수의 종류

종류	의미	결과
SYSDATE	시스템의 현재 날짜	날짜
MONTHS_BETWEEN	날짜와 날짜 사이의 개월을 계산	숫자
ADD_MONTHS	날짜에 개월을 더한 날짜 계산	날짜
NEXT_DAY	날짜후의 첫 요일의 날짜를 계산	날짜
LAST_DAY	월의 마지막 날짜를 계산	날짜
ROUND	날짜를 반올림	날짜
TRUNC	날짜를 절삭	날짜

2-1) SYSDATE 함수

시스템에 저장된 현재 날짜를 반환하는 함수로서 초 단위까지 반환

시스템의 현재 날짜 출력

SELECT SYSDATE
FROM DUAL;

2-2) MONTHS_BETWEEN, ADD_MONTHS

입사한지 360개월 미만인 교수의 교수번호, 입사일, 입사일로부터 현재일까지의 개월수, 입사일에서 6개월후의 날짜를 출력

SELECT PROFNO, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) TENURE, ADD_MONTHS(HIREDATE, 6)
FROM PROFESSOR
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE) < 360;

2-3) LAST_DAY, NEXT_DAY

NEXT_DAY에서 오늘 해당되는 요일을 생각하고 입력하면 다음주가 나옴

오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력

SELECT
    SYSDATE
    , LAST_DAY(SYSDATE)
    , NEXT_DAY(SYSDATE, 1)
FROM DUAL;

2-4) ROUND, TRUNC

날짜를 반올림, 절삭

ROUND함수는 정오를 넘으면 다음날 출력

TRUNC함수는 시간정보와 상관없이 당일날을 출력

월시분초..등 도 반올림, 절삭 할 수 있으며 기준은 절반이다.

예) 월은 6월기준, 시간은 정오를 기준, 분은 30분을 기준

예제1)

SELECT 
    TO_CHAR(SYSDATE +3 /24, 'YYMMDD HH24MISS') 
    , TO_CHAR(ROUND(SYSDATE+3/24), 'YYMMDD HH24MISS')
    , TO_CHAR(TRUNC(SYSDATE+3/24), 'YYMMDD HH24MISS')
FROM DUAL;

예제2)

SELECT ROUND(TO_DATE('231224', 'YYMMDD'),'MM')
FROM DUAL;

예제3)

SELECT TO_CHAR(HIREDATE, 'YYDDMM HH24MISS') 
    , TO_CHAR(ROUND(HIREDATE), 'YYDDMM HH24MISS')
    , TO_CHAR(ROUND(HIREDATE, 'MM'), 'YYDDMM HH24MISS')
    , TO_CHAR(ROUND(HIREDATE, 'YY'), 'YYDDMM HH24MISS')
FROM PROFESSOR
WHERE DEPTNO = 101;

3) 데이터 타입의 변환

- 숫자나 날짜 타입을 문자와 함께 결합하거나 보고서 양식에 맞추기 위해 주로 사용

3-1) 묵시적인 데이터 타입 변환

- 정확한 연산을 위하여 오라클에서 데이터 타입을 내부적으로 자동 형변환하는 경우

[표]WHERE A(칼럼)=B(상수)

A의 데이터 타입	B의 데이터 타입	반환 결과
NUMBER	VARCHAR2 또는 CHAR	B가 NUMBER 타입으로 변환
VARCHAR2 또는 CHAR	NUMBER	A가 NUMBER 타입으로 변환

결과적으로 모두 NUMBER타입으로 변환된다.

문자타입의 숫자타입으로 변환은 문자열이 숫자로 구성된 경우에만 가능하다.

3-2) 명시적인 데이터 타입 변환

- 사용자가 데이터 타입 변환 함수를 이용하여 명시적으로 데이터 타입을 변환

4) TO_CHAR 함수

- 날짜나 숫자를 문자로 변환하기 위해 사용

- 날짜 출력 형식 변경

위의 표는 사용을 잘 안한다.

위의 표에서 자주 사용되는건

Q, MM, WW(연중 주 / 연간통계 등등),

W(달의 몇번째 주인지 / 1월1일이 시작요일이 기준점),

DD,D

DY,D

예제1) 학생테이블에서 전인하 학생의 학번과 생년월일 중에서 년, 월만 출력

SELECT STUDNO, TO_CHAR(BIRTHDATE, 'YYMM')
FROM STUDENT
WHERE NAME = '전인하';

5) 시간표현 형식의 종류

주로 사용되는 형식은 'HH24:MI:SS' 이다.

예제) 교수 테이블에서 101번 학과 교수의 이름과 입사일을 출력

SELECT NAME, TO_CHAR(HIREDATE, 'MONTH DD, YYYY HH24:MI:SS PM')
FROM PROFESSOR
WHERE DEPTNO = 101;

6) 기타 날짜 표현 형식

종류	의미
"TEXT"	결과와 함께 출력할 문자열을 인용 부호로 표시
TH	서수로 표시
SP	숫자(기수)를 영문으로 표시
SPTH 또는 THSP	서수를 영문으로 표시

7) 숫자를 문자 형식으로 변환

[표]TO_CHAR함수를 이용한 숫자 출력 형식 변환

종류	의미	사용 예	결과
9	한자리의 숫자 표시	(1234, '99999')	1234
0	앞부분을 0으로 표시	(1234, '099999')	001234
$	달러 기호를 앞에 표시	(1234, '$99999')	$1234
.	소수점을 표시	(1234, '99999.99')	1234.00
,	특정위치에 ','표시	(1234, '99,999')	1,234
MI	오른쪽으로 '-'기호 표시	(1234, '99999MI')	1234-
PR	음수값을 <>으로 표시	(1234, '99999PR)	<1234>
EEEE	지수 표기법으로 표시	(1234, '9.999EEEE(10의3승)')	1.234E+03
V	10N을 곱한값으로 표시	(1234, '999V99')	123400
B	공백을 0으로 표시	(1234, 'B9999.99')	1234.00

예제)

SELECT NAME, SAL, COMM, TO_CHAR((SAL+COMM)*12, '9,999') 
FROM PROFESSOR
WHERE COMM IS NOT NULL;

위 예제에서 '9,999'는 계산한 값에 , 를 붙이기 위해 사용했다.

사용시 주의점은 아래 예제를 보면서 설명하겠다.

SELECT TO_CHAR(1234567890, '999,999,999') FROM DUAL;

,를 사용하여 단위를 구분하려고 할때 좌측에 있는 입력값보다 범위가 작으면 결과값이 제대로 출력되지않는다.

SELECT TO_CHAR(1234567890, '9,999,999,999') FROM DUAL;

꼭 범위를 맞추거나 범위보다 높게 지정하자!

8) TO_NUMBER 함수

숫자로 구성된 문자열을 숫자 데이터로 변환하기 위한 함수 (보통 FORMAT와 같이 사용)

9) TO_DATE (FORMAT와 같이 사용)

숫자와 문자로 구성된 문자열을 날짜 데이터로 변환하는 함수

10) 중첩함수

F1함수의 결과값은 F2의 인수로, F2의 결과값은 F3의 인수로 사용된다.

예제)

11) 일반함수 NVL함수

- NVL함수는 NULL을 0또는 다른 값으로 변환하기 위한 함수

예제)201번학과 교수의 이름, 직급,급여,보직수당, 급여와 보직수당의 합계를 출력 단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산

SELECT NAME, POSITION, SAL, COMM, SAL+COMM, SAL+NVL(COMM, 0), NVL(SAL+COMM, SAL)
FROM PROFESSOR
WHERE DEPTNO = 201;

12) NVL확장함수 NVL2

첫번째값이 NULL이 아니면 두번째 인수값을 출력, 첤번째 인수값이 NULL이면 세번째 인수값을 출력

예제)

SELECT NAME, POSITION, SAL, COMM
    , SAL+NVL(COMM, 0)
    , NVL(SAL+COMM, SAL)
    , NVL2(COMM, SAL+COMM, SAL)
FROM PROFESSOR
WHERE DEPTNO = 201;

13) NVL확장함수 NULLIF함수

두개의 값을 비교하여 같으면 NULL, 다르면 앞에값이다.

14) NVL확장함수 COALESCE함수

NULL을 만날때까지 찾는다 NULL을 만나면 첫번째 인수를 반환한다.

15) DECODE함수 (자주사용)

- JAVA의 SWITCH문이라고 생각하면 편하다.비교연산자는 '='만 사용가능, 일치하는 값이 없거나 NULL인 경우는 NULL반환

- 조건식이 짧을때만 사용

예제) 교수테이블에서 교수의 소속학과 번호를 학과 이름으로 변환하여 출력,

학과번호가 101번이면 '컴퓨터공학과' 102번'멀티미디어과' 201번'전자공학과', 나머지는'기계공학과'로 변환

SELECT NAME, DEPTNO
    ,DECODE(DEPTNO, 101, '컴퓨터공학과', 102, '멀티미디어학과', 201, '전자공학과', '기계공학과')
FROM PROFESSOR;

예제2)

SELECT 
    TO_CHAR(BIRTHDATE, 'MM') A
    , TRUNC(TO_CHAR(BIRTHDATE, 'MM')/3) B
    , DECODE(TRUNC(TO_CHAR(BIRTHDATE, 'MM')/3), 1,'봄', 2,'여름', 3, '가을', '겨울') C
    , BIRTHDATE D
    , ADD_MONTHS(BIRTHDATE, -2) E -- 풀이2
    , TO_CHAR(ADD_MONTHS(BIRTHDATE, -2),'Q') F
    , DECODE(TO_CHAR(ADD_MONTHS(BIRTHDATE, -2), 'Q'), 1, '봄', 2,'여름', 3, '가을', '겨울') G
FROM STUDENT;

15-1) DECODE사용시 주의사항

- SIGN, LEAST, GREATEST문을 사용하지말고 CASE문으로 대체해서 사용

- ELSE없는 IF를 사용하기 때문에 성능상의 이득이 있을수 있다.

16)CASE함수 (JAVA의 IF문과 비슷함)

- CASE함수는 DECODE함수의 기능을 확장한 함수

- 산술연산, 관계연산, 논리연산과 같은 다양한 비교가 가능

- WHEN절에서 표현식을 다양하게 정의

- 직관적인 문법체계와 다양한 비교 표현식 사용

예제)교수테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 출력
학과 번호별로 보너스가 다르다 학과번호가 101이면 보너스는 급여의 10%, 102는 20%, 201이면 30%, 나머지는 0%

SELECT PROFNO, NAME, SAL, DEPTNO
    , DECODE(DEPTNO, 101, SAL*.1, 102, SAL*.2,201, SAL*.3, 0) A,
    CASE DEPTNO 
        WHEN 101 THEN SAL*.1 
        WHEN 102 THEN SAL*.2
        WHEN 201 THEN SAL*.1
        ELSE 0
    END B,
    CASE
        WHEN DEPTNO = 101 THEN SAL*.1
        WHEN DEPTNO = 102 THEN SAL*.2
        WHEN DEPTNO = 201 THEN SAL*.3
        ELSE 0
    END C
FROM PROFESSOR;

첫번째 계산은 DECODE로 계산하였다.

16.DECODE사용시 주의사항 (p.276)

- DECODE함수 내부에서 DECODE문을 사용하는 경우에는 성능저하 발생

	DECODE	CASE WHEN
기능	조건단위 분기 SWITCH문 대응
좋은점	코드의 함축성
불편한점
사족

2. 그룹함수

1) 그룹함수의 개념

- 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 그룹화 하여 그룹별로 결과를 출력하는 함수

- 그룹함수는 통계적인 결과를 출력하는데 자주 사용

2)사용법

- GROUP BY : 전체행을 그룹화

- HAVING : 그룹바이절에 의해 생성된 그룹별로 조건 부여

3) 그룹함수의 종류