[정리노트] 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;