2-1. SQL 기본_(6)함수(FUNCTION)

제6절 함수(FUNCTION)

 

1. 내장 함수(BUILT-IN FUNCTION) 개요: 벤더(DBMS)에서 제공하여 내장된 함수
  가. 단일행 함수(Single-Row Function)
    - 문자형, 숫자형, 날짜형, 변환형, NULL관련함수
  나. 다중행 함수(Multi-Row Function)

    - 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)

 

 

2. 문자형 함수

문자형 함수	함수 설명
LOWER(문자열)	문자열의 알파벳을 소문자 변경
UPPER(문자열)	문자열의 알파벳을 대문자 변경
CONCAT(문자열1, 문자열2) 문자열1 || 문자열2 문자열1 + 문자열2	문자열1과 문자열2를 연결, ||(파이프라인), + 연결과 동일
SUBSTR(문자열, m, n) SUBSTRING(문자열, m, n)	문자열 중 m위치부터 n개 문자 길이까지만 추출, n생략시 마지막 문자까지 추출
LENGTH(문자열) LEN(문자열)	문자열의 개수를 세서 숫자로 추출
LTRIM(문자열, 지정문자)	문자열 왼쪽부터 지정문자가 나타면 해당 문자 제거, 지정문자가 없으면 공백 제거
RTRIM(문자열, 지정문자)	문자열 오른쪽부터 지정문자가 나타면 해당 문자 제거, 지정문자가 없으면 공백 제거
TRIM(지정문자 FROM 문자열)	문자열에서 양쪽에 있는 지정문자 제거, 지정문자가 없으면 공백 제거
ASCII(문자)	문자나 숫자를 ASCII코드 번호로 변경
CHS/CHAR(ASCII번호)	ASCII 코드 번호를 문자나 숫자로 변경

 

 

3. 숫자형 함수

숫자형 함수	함수 설명
ABS(숫자)	숫자를 절대값으로 변경
SIGN(숫자)	숫자를 양수, 음수, 0 으로 구별
MOD(숫자1, 숫자2) 숫자1%숫자2	숫자1을 숫자2로 나누어 나머지 값 추출
CEIL(숫자) CEILING(숫자)	숫자보다 크거나 같은 최소 정수 추출
FLOOR(숫자)	숫자보다 작거나 같은 최대 정수 추출
ROUND(숫자, m)	숫자를 소수점 m자리에서 반올림, m 생략시 디폴트 값은 0
TRUNC(숫자, m)	숫자를 소수점 m자리에서 자르기, m 생략시 디폴트 값은 0
SIN, COS, TAN ...	숫자의 삼각함수 값을 추출
EXP(), POWER(), SQRT(), LOG(), LN()	숫자의 지수, 거듭제곱, 제곱근, 자연로그

 

 

4. 날짜형 함수(DATE 타입)

날짜형 함수	함수 설명
SYSDATE GETDATE()	현재 날짜와 시간
EXTRACT( 'YEAR' | 'MONTH' | 'DAY' from d ) DATEPART( 'YEAR' | 'MONTH' | 'DAY' , d )	날짜 데이터에서 년/월/일 데이터를 추출
TO_NUMBER( TO_CHAR (d, 'YYYY') ) TO_NUMBER( TO_CHAR (d, 'MM') ) TO_NUMBER( TO_CHAR (d, 'DD') ) YEAR(d) MONTH(d) DAY(d)	년, 월, 일 데이터 추출

연산	결과	설명
날짜 + 숫자	날짜	숫자만큼의 날수를 날짜에 더한다.
날짜 - 숫자	날짜	숫자만큼의 날수를 날짜에서 뺀다.
날짜1 - 날짜2	날짜수	다른 하나의 날짜에서 하나의 날짜를 빼면 일수가 나온다.
날짜 + 숫자/24	날짜	시간을 날짜에 더한다.

 

 

5. 변환형 함수

종류	설명
명시적(Explicit) 데이터 유형 변환	데이터 변환형 함수로 데이터 유형을 변환
암시적(Implicit) 데이터 유형 변환	데이터베이스가 자동으로 데이터 유형을 변환

변환형 함수	함수 설명
TO_NUMBER(문자열) CAST(문자열 AS 숫자형_데이터타입)	문자열을 숫자로 변환
TO_CHAR(숫자 or 날짜, [FORMAT])	숫자 또는 날짜를 주어진 문자열 FORMAT으로 변환
TO_DATE(문자열, [FORMAT])	문자열을 주어진 날짜 FORMAT으로 변환

 

 

6. CASE 표현

  - IF 조건문과 유사함

표현식 기본 (조건 1개)	표현식 확장 (조건 2개 이상)	설명
CASE 컬럼1     WHEN 조건문자1  THEN 반환문자1     ELSE 반환문자2(나머지) END	CASE 컬럼1     WHEN 조건문자1  THEN 반환문자1     WHEN 조건문자2  THEN 반환문자2     WHEN 조건문자3  THEN 반환문자3     ....     ELSE 반환문자4(나머지) END	컬럼 값이 특정 문자랑 같은지 조건으로 사용하는 경우
CASE WHEN 조건1 THEN 반환문자1     ELSE 반환문자2(나머지) END	CASE WHEN 조건1 THEN 반환문자1     WHEN 조건2 THEN 반환문자2     WHEN 조건3 THEN 반환문자3     ....     ELSE 반환문자4(나머지) END	다양한 조건을 사용하는 경우

# 표현식 기본 (조건 1개)
# BillingCountry의 값을 미국만 한국어로 변경하기

SELECT InvoiceId, InvoiceDate, BillingCity, BillingCountry, Total
	, CASE BillingCountry 
		WHEN 'USA' THEN '미국'
		ELSE 'ETC'
		END Country_Korean
FROM Invoice;

 

# 표현식 확장 (조건 2개 이상)
# BillingCountry의 값을 전부 한국어로 변경하기

SELECT InvoiceId, InvoiceDate, BillingCity, BillingCountry, Total
	, CASE BillingCountry 
		WHEN 'USA' THEN '미국'
		WHEN 'Germany' THEN '독일'
		WHEN 'Canada' THEN '캐나다'
		WHEN 'Norway' THEN '노르웨이'
		WHEN 'Belgium' THEN '벨기에'
		ELSE 'ETC'
		END Country_Korean
FROM Invoice;

 

# 다양한 조건으로 표현식 확장 (조건 2개 이상)
# Total 값을 구간화(Binning) 한 컬럼 생성


SELECT InvoiceId, InvoiceDate, BillingCity, BillingCountry, Total
	, CASE WHEN Total <= 1 THEN '1이하'
		WHEN Total <= 3 THEN '1초과 3이하'
		WHEN Total <= 5 THEN '3초과 5이하'
		WHEN Total <= 10 THEN '5초과 10이하'
		ELSE '10초과'
		END Total_Binning
FROM Invoice;

 

 

7. NULL 관련 함수

  - 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다. 0은 숫자이고, 공백은 하나의 문자이다.
  - 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널 값을 포함할 수 있다.
  - 널 값을 포함하는 연산의 경우 결과 값도 널 값이다. 
  - 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.

 

# NULLIF

SELECT BillingCountry, BillingCity
	, NULLIF(BillingCountry,'USA')  # BillingCountry가 USA면 NULL로 변경
FROM Invoice

 

# COALESCE

SELECT BillingCountry, BillingCity, NULL_IF
	, COALESCE(NULL_IF, '미국') #NULL_IF을 먼저 추출하되 NULL이면 그 다음 값('미국')으로 추출
FROM Invoice