[정리노트] SQLD 2024년 개정판 - 함수

 

[정리노트] SQLD 2024년 개정판 - 함수

---

함수

정의

• input value와 output vale의 관계를 정의하는 객체 (input 값을 출력해줌)
• FROM 절을 제외한 모든 절에서 사용 가능

기능

• 기본적인 쿼리문을 강력하게 해줌
• 데이터의 계산을 수행
• 개별 데이터의 항목을 수정
• 표시할 날짜 및 숫자 형식을 지정
• 열 데이터의 유형을 변환

종류

• 단일행 함수: input과 output의 관계가 1:1
• 복수행 함수: 여러 건의 데이터를 동시에 입력 받아서 하나의 요약값을 리턴 (그룹함수 또는 집계 함수 라고도 함)

 

문자 함수의 종류와 특징

• 대부분 단일행 함수를 취함

함수명	함수 기능	사용예시	출력	기타 설명
LOWER(대상)	문자열을 소문자로	LOWER(’ABC’)	abc
UPPER(대상)	문자열을 대문자로	UPPER(’abc’)	ABC
SUBSTR(대상,m,n)	문자열 중 m위치에서 n개의 문자열 추출	SUBSTR(’ABCDE’,2,3)	BCD	n 생략시 끝까지 추출 추출 위치가 -면 뒤에서 부터 n번째 앞의 오른쪽으로 스캔
INSTR(대상,찾을 문자열,찾을시작위치,반복횟수))	대상에서 찾을 문자열 위치 반환	INSTR(’A#B#C#’,’#’,1,1)	2
		INSTR(’A#B#C#’,’#’,3,2)	6	3번째부터 두번째 발견된 # 위치
LTRIM(대상,삭제문자열)	문자열 중 특정 문자열을 왼쪽에서 삭제	LTRIM(’AABABAA’,’A’)	BABAA	삭제문자열 생략 시 공백 삭제
RTRIM(대상,삭제문자열)	문자열 중 특정 문자열을 오른쪽에서 삭제	RTRIM(’AABABAA’,’A’)	AABAB	삭제문자열 생략 시 공백 삭제
TRIM(대상)	문자열 중 특정 문자열을 양쪽에서 삭제	TRIM(’ ABCDE ’)	ABCDE	ORACLE TRIM은 공백만 삭제 가능
LPAD(대상,n,문자열)	대상 왼쪽에 문자열을 추가하여 총 n의 길이 리턴	LPAD(’ABC’,5,’*’)	**ABC
RPAD(대상,n,문자열)	대상 오른쪽에 문자열을 추가하여 총 n의 길이 리턴	RPAD(’ABC,5,’*’)	ABC**
CONCAT(대상1,대상2)	문자열 결합	CONCAT(’A’,’B’)	AB	두 개의 인수만 전달 가능
LENGTH(대상)	문자열 길이	LENGTH(’ABCDE’)	5
REPLACE(대상,찾을문자열,바꿀문자열)	문자열 치환 및 삭제	REPLACE(’ABBA’,’AB’,’ab’)	abBA	세번째 인수를 생략하거나, 빈 문자열 전달 시 찾을문자열 삭제가능
TRANSLATE(대상,찾을문자열,바꿀문자열)	글자를 1대1로 치환	TRANSLATE(’ABBA’,’AB’,’ab’)	abba	매칭되는 글자끼리 치환(A는a로,B는b로 각각) 바꿀문자열 생략 불가, 빈문자열 전달 시 null 리턴

• CHR (숫자): 아스키 코드에 대한 문자 출력
• TRIM (문자열, 점검문자): 문자열의 양쪽으로부터 점검문자를 제거, 점검누자를 지정하지 않으면 공백 문자를 대상으로 함

SELECT LTRIM('       CHRIS') FROM DUAL;
SELECT TRM ('~~~Hi~~~','~") FROM DUAL;
SELECT SUBSTR('MyHome',3) FROM DUAL;
SELECT SUBSTR('YourHome', 1, 3) FROM DUAL;
SELECT REPLACE('MyHome','My','Your') FROM DUAL;
SELECT LPAD('Hi', 5, '~') FROM DUAL;

 

숫자 함수의 종류와 특징

• 단일행 함수 형태의 숫자함수

함수명	함수 기능	사용 예시	출력	기타 설명
ABS(숫자)	절대값 반환	ABS(-1.5)	1.5
ROUND(숫자,자리수)	소수점 특정 자리에서 반올림	ROUND(123.456,2)	123.46	자리수가 음수이면 정수자리에서 반올림
	소수점 특정 자리에서 반올림	ROUND(123.456,-2)	100	자리수를 지정하지 않을 경우 0을 기본값으로 사용
TRUNC(숫자,자리수)	소수점 특정 자리에서 버림	TRUNC(123.456,2)	123.45	자리수를 지정하지 않을 경우 0을 기본값으로 사용
SIGN(숫자)	숫자가 양수면 1 음수면 -1 0이면 0 반환	SIGN(-2)	-1
FLOOR(숫자)	작거나 같은 최대 정수 리턴	FLOOR(3.5)	3
CEIL(숫자)	크거나 같은 최소 정수 리턴	CEIL(3.5)	4
MOD(숫자1,숫자2)	숫자1을 숫자2로 나누어 나머지 반환	MOD(7,2)	1	숫자2가 0일 경우 숫자 1을 반환
POWER(m,n)	m의 n거듭제곱	POWER(2,4)	16
SQRT(숫자)	루트값 리턴	SQRT(16)	4

SELECT ROUND(123.76, 1) FROM DUAL;  = 123.8
SELECT ROUND(173.76, -2) FROM DUAL; = 200
SELECT TRUNC(123.76, 1) FROM DUAL; = 123.7
SELECT TRUNC(123.76, -1) FROM DUAL; = 120
SELECT CEIL (12.77) FROM DUAL; = 13
SELECT FLOOR (25.4) FROM DUAL; = 25
SELECT FLOOR (25.4) FROM DUAL; = 26
SELECT MOD (15, -2) FROM DUAL; = 1
SELECT MOD (-15, -2) FROM DUAL; = -1

 

날짜형 함수의 종류와 특징

• 날짜 연산과 관련된 함수
• ORACLE과 SQL-Sever 함수 거의 다름

함수명	함수 기능	사용예시	출력	기타 설명
SYSDATE	현재 날짜와 시간 리턴	SYSDATE	2024/02/14 18:44:34	날짜 출력형식에 따라 다르게 출력됨 (날짜만 출력될 수 있음)
CURRENT_DATE	현재 날짜 리턴	CURRENT_DATE	2024/02/14	날짜 출력형식에 따라 다르게 출력됨 (날짜만 출력될 수 있음)
CURRENT_TIMESTAMP	현재 타임스탬프 리턴	CURRENT_TIMESTAMP	2024/02/14 18:45:29 +09:00
ADD_MONTHS(날짜,n)	날짜에서 n개월 후 날짜 리턴	ADD_MONTHS(SYSDATE,3)	2024/05/14 18:44:34	n이 음수인 경우 n개월 이전 날짜 리턴
LAST_DAY(날짜)	주어진 월의 마지막 날짜 리턴	LAST_DAY(SYSDATE)	2024/02/29 18:44:34
NEXT_DAY(날짜,n)	주어진 날짜 이후 지정된 요일의 첫 번째 날짜 리턴	NEXT_DAY(SYSDATE,1)	2024/02/18 18:51:35	1:일요일, 2:월요일, … 7:토요일
ROUND(날짜,자리수)	날짜 반올림	ROUND(SYSDATE,’MONTH’)	2024/02/01 0:00	월 이전자리에서 반올림
TRUNC(날짜,자리수)	날짜 버림	TRUNC(SYSDATE,’MONTH’)	2024/02/01 0:00	월 이전자리에서 버fla
MONTH_BETWEEN(날짜1,날짜2)	날짜1과 날짜2 사이의 개월 수 리턴	MONTH_BETWEEN(SYSDATE,HIREDATE)	3.7234	날짜1 < 날짜2로 전달 시 음수 리턴

• SQL-Server
  • SYSDATE → GETDATE
  • ADD_MONTHS → DATEADD (월 뿐만 아니라 모든 단위 날짜 연산 가능)
  • MONTHS_BETWWEN → DATEDIFF (두날짜 사이의 년,월,일 추출)

 

변환 함수의 종류와 특징

• 데이터 타임을 변환
• 문자를 숫자로, 숫자를 문자로, 날짜를 문자로 변경

함수명	함수 기능	사용예시	출력	기타 설명
TO_NUMBER(대상)	숫자 타입으로 변경하여 리턴	TO_NUMBER(’100’)	100	문자100을 숫자 100으로 리턴
TO_CHAR(대상,포맷)	1) 날짜의 포맷 변경	TO_CHAR(SYSDATE,’MM/DD-YYYY’)	02/14-2024	날짜 형식 변경(리턴은 문자타입)
	2) 숫자의 포맷 변경	TO_CHAR(9000,’9,999’)	9,000	천단위 구분기호 생성 (리턴은 문자타입)
TO_DATE(문자,포맷)	주어진 문자를 포맷형식에 맞게 읽어 날짜로 리턴	TO_DATE(’2024/01/01’,’YYYY/MM/DD’)	2024/01/01 00:00:00	날짜로 리턴됨 * 포맷과 문자가 동일 해야함
FORMAT(날짜, 포맷)	날짜의 포맷 변경	FORMAT(GETDATE(), 'YYYY')	2024	SQL SERVER 함수
CAST(대상 AS 데이터타입)	대을 주어진 데이터타입으로 변환	CAST(’100’ AS int)	100	문자100을 숫자100으로 리턴

• SQL-Sever
  • TO_NUMBER, TO_DATE, TO_CHAR → CONVERT (포맷 전달 시)
  • 단순 변환일 경우 주로 CAST 사용

 

그룹 함수의 종류와 특징

• 다중행 함수
• 여러 값이 input값으로 들어가 하나의 요약된 값으로 리턴
• group by와 함께 자주 사용됨
• Oracle과 SQL-Server 거의 동일
• COUNT의 경우 컬럼이 모두 NULL 값이면 0를 리턴, 나머지 함수는 다 NULL값은 계산에 포함하지 않고 무시

함수명	함수기능	사용예시	출력	기타 설명
COUNT(대상)	행의 수 리턴	SELECT COUNT(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
SUM(대상)	총 합 리턴	SELECT SUM(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
AVG(대상)	평균 리턴	SELECT AVG(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
MIN(대상)	최솟값 리턴	SELECT MIN(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
MAX(대상)	최댓값 리턴	SELECT MAX(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
VARIANCE(대상)	분산 리턴	SELECT VARIANCE(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산
STDDEV(대상)	표준편차 리턴	SELECT STDDEV(SAL) FROM EMP;	각 연산 결과	NULL 무시하고 연산

• SQL-Sever
  • VARIANCE → VAR
  • STDDEV → STDEV

 

일반 함수의 종류와 특징

• 기타 함수 (NULL 치환 함수 등)

함수명	함수기능	사용예시	출력	기타 설명
DECODE(대상,값1,리턴1,값2,리턴2,…,그외 리턴)	대상이 값이1이면 리턴1, 값이 2이면 리턴2, 그외에는 그외 리턴값 리턴	DECODE(DEPTNO,10,A,B)	A 또는 B	대소비교에 따른 치환 불가 그외 리턴 생략시 NULL 리턴 * Oracle 전용 함수
NVL(대상,치환값)	대상이 NULL이면 치환값으로 치환하여 리턴	NVL(COMM,0)	COMM값 또는 0 리턴
NVL2(대상,치환값1,치환값2)	대상이 NULL이면 치환값2로 치환, NULL이 아니면 치환값1로 리턴	NVL2(COMM,COMM*1.1,0)	COMM*1.1값 또는 0	COMM값 NULL이면 0, 아니면 COMM*1.1 리턴
COALSESCE(대상1,대상2,….,그외리턴)	대상1이 NULL이면 대상2,대상2가 NULL이면 대상3,…,모두가 NULL이면 그외 리턴값이 리턴됨	COALESCE(NULL, 100)	100	그외리턴값 생략 시 널 리턴
ISNULL(대상, 치환값)	대상이 NULL이면 치환값이 리턴	ISNULL(NULL,100)	100	SQL서버 함수
NULLIF(대상1, 대상2)	두값이 같으면 NULL 리턴, 다르면 대상1 리턴	NULLIF(10,20)	10
CASE문	조건별 치환 및 연산 수행	밑에 참고	밑에 참고

 

ex) DECODE 사용 예시 (부서번호가 10번이면 인사부, 20번이면 재무부, 나머지는 NULL 리턴

SELECT DEPTNO,DECODE(DEPTNO,10,'인사부',20,'재무부') AS DNAME
	FROM EMP;

ex) DECODE 사용 예시2 (DEPTNO가 10이면서 JOB이 CLERK면 A, DEPTNO가 10 이면서 CLERK가 아니면 B, TEPTNO가 10이 아니면 C

SELECT DEPTNO, JOB, DECODE(DEPTNO,10,DEODE(JOB,'CLERK','A','B'),'C') AS RESULT
 FROM EMP;

ex) NVL, NVL2 사용 예제 (NVL2의 경우 NVL이랑 다르게 COMM의 값이 널이 아닐때도 치환값 정의 가능, NVL2의 경우 COMM이 널이 아니면 10% 인상값, 널이면 500 리턴)

SELECT COMM,NVL(COMM,0),NVL2(COMM,COMM*1.1,500)
 FROM EMP;

ex) COALESCE 사용 예제 (DEPTNO1과 DEPTNO2중 널 아닌값 출력, 둘다 널이 아니면 맨 앞 순서대로 출력, 모두 널인 경우 마지막 인수값(0) 출력

SELECT DEPTNO1,DEPTNO2,COALESCE(DEPTNO1,DEPTNO2,0) AS RESULT
 FROM STUDENT;

ex) CASE문 사용 예제 1 (CASE문을 사용하여 여러 조건(대소비교 가능)에 대한 치환 및 연산 가능

SELECT SAL,CASE WHEN SAL < 2000 THEN 'C'
				WHEN SAL < 3000 THEN 'B'
                				ELSE 'A'
		   END AS SAL_GRADE
  FROM EMP;

ex) CASE문 사용 예제2 (동등비교 시 위처럼 비교대상(DEPTNO)를 CASE와 WHEN 사이에 배치하면서 WHEN절 마다 반복하지 않아도 됨, 이때 DEPTNO. 데이터 타입과 WHEN절의 명시된 비교대상의 데이터 타입 반드시 일치해야 함)

SELECT DEPTNO,
			 CASE DEPTNO WHEN 10 THEN '인사부'
						 WHEN 20 THEN '총무부'
						 WHEN 30 THEN '재무부'
								 ELSE '기타'
			 END AS DNAME1,
			 CASE WHEN DEPTNO = 10 THEN '인사부'
			 	  WHEN DEPTNO = 20 THEN '총무부'
				  WHEN DEPTNO = 30 THEN '재무부'
								 ELSE '기타'
			 END AS DNAME2
	FROM EMP;