본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.
 |
|
6. 함수 (FUNCTION)
1. 내장 함수(BUILT-IN FUNCTION) 개요
내장 함수 분류
-
단일행 함수 (Single-Row Function): 입력 값이 단일행
-
다중행 함수 (Multi-Row Function): 입력 값이 여러 행
- 집계 함수 (Aggregate Function)
- 그룹 함수 (Group Function)
- 윈도우 함수 (Window Function)
함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1 관계라는 중요한 특징을 가지고 있다. 단일행 함수의 경우 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 표현될 수 있다. 다중행 함수의 경우도 여러 레코드의 값들을 입력 인수로 사용하는 것이다.
단일행 함수의 특징
- SELECT, WHERE, ORDER BY 절에 사용 가능하다.
- 각 행들에 대해 개별적으로 작용하여, 각각의 행에 대한 조작 결과를 반환한다.
- 여러 인자를 입력해도 단 하나의 결과만 반환한다.
- 함수의 인자로 상수, 변수, 표현식이 사용 가능하고, 인수의 개수는 하나부터 여러 개까지 가질 수 있다.
- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능하다.
아래 함수의 예에서 SUBSTR / SUBSTRING 으로 표시한 것은 같은 기능을 하지만 다 르게 표현되는 Oracle 내장 함수와 SQL Server 내장 함수를 순서대로 표현한 것이다.
2. 문자형 함수
LOWER: 문자열을 소문자로 바꾼다.
LOWER('SQL Expert') >> 'sql expert'
UPPER: 문자열을 대문자로 바꾼다.
UPPER('SQL Expert') >> 'SQL EXPERT'
ASCII: 문자나 숫자를 ASCII 코드 번호로 바꾼다.
ASCII('A') >> 65
CHR/CHAR: ASCII 코드를 문자나 숫자로 바꾼다.
CHR(65) >> 'A'
CHAR(65) >> 'A' -- SQL Server 방식
CONCAT: 2개의 문자열을 연결한다. (Oracle, MySQL에서 유효하며 인자로 3개는 불가능)
CONCAT('RDBMS', ' SQL') >> 'RDBMS SQL'
'RDBMS' || ' SQL' >> 'RDBMS SQL'
'RDBMS' + ' SQL' >> 'RDBMS SQL' -- SQL Server 방식
SUBSTR/SUBSTRING: 문자열 내 m 위치에서 n개의 문자를 반환 (n이 생략되면 마지막 문자까지)
SUBSTR('SQL Expert', 5, 3) >> 'Exp'
SUBSTRING('SQL Expert', 5, 3) >> 'Exp' -- SQL Server 방식
LENGTH/LEN: 문자 개수를 숫자값으로 반환 (문자열 길이 반환)
LENGTH('SQL Expert') >> 10
LEN('SQL Expert') >> 10 -- SQL Server 방식
LTRIM, RTRIM, TRIM: 각각 문자열에서 지정 문자가 나타날 때까지 문자 제거
- 지정 문자가 없다면 공백 값이 Default
- SQL Server에서는 지정 문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
- 공백 제거 및 CHAR와 VARCHAR 데이터 타입 비교 시 용이하게 사용된다.
LTRIM('xxxYYZZxYZ', 'x') >> 'YYZZxYZ'
RTRIM('XXYYzzXYzz', 'z') >> 'XXYYzzXY'
TRIM('x' FROM 'xxYYZZxYZxx') >> 'YYZZxYZ'
위 문자형 함수들을 연습하고자 할 때, Oracle을 사용한다면 아래 사항을 참고해야 한다.
Oracle 내에서는 FROM 절이 SELECT 문장에 필수 절이기 때문에 단순하게 함수를 실행할 때에도 더미 테이블을 사용해야 한다.
SELECT LENGTH('SQL Expert') FROM DUAL;
DUAL 테이블의 특징
-
사용자 SYS가 소유하며 모든 사용자가 액세스 가능하다.
-
SELECT ~ FROM ~ 형식을 갖추기 위한 더미 테이블
-
DUMMY라는 문자열 유형의 컬럼에 'X'라는 값이 들어 있는 행을 1건 포함한다.
SELECT * FROM DUAL;
3. 숫자형 함수
ABS: 절대값 반환
ABS(-15) >> 15
SIGN: 양수, 음수, 0인지 구별
SIGN(-20) >> -1
SIGN(0) >> 0
SIGN(20) >> 1
MOD: 숫자1을 숫자2로 나눈 나머지 값 반환 (% 연산자로 대체 가능)
MOD(7, 3) >> 1
7 % 3 >> 1
CEIL/CEILING: 숫자보다 크거나 같은 최소 정수 반환
CEIL(38.123) / CEILING(38.123) >> 39
CEIL(38) / CEILING(38) >> 38
CEIL(-38.123) / CEILING(-38.123) >> -38
FLOOR: 숫자보다 작거나 같은 최대 정수를 반환
FLOOR(38.123) >> 38
FLOOR(-38.123) >> -39
ROUND: 숫자를 소수점 m자리까지 반올림 (m이 생략되면 Default는 0)
ROUND(38.5235, 3) >> 38.524
ROUND(38.5235, 1) >> 38.5
ROUND(38.5235) >> 39
TRUNC: 숫자를 소수점 m자리에서 버림 (m이 생략되면 Default는 0)
TRUNC(38.5235, 3) >> 38.523
TRUNC(38.5235, 1) >> 38.5
TRUNC(38.5235) >> 38
-- SQL Server에서 미지원
SIN, COS, TAN: 삼각함수 값을 반환
SIN(2) >> 0.90...
COS(2) >> -0.41...
TAN(2) >> -2.18...
EXP, POWER, SQRT, LOG, LN: 숫자의 지수, 거듭제곱, 제곱근, 자연로그 값 반환
4. 날짜형 함수
SYSDATE / GETDATE(): 현재 날짜와 시각 출력
SELECT SYSDATE FROM DUAL;
SELECT GETDATE() AS CURRENTTIME;
EXTRACT('YEAR'|'MONTH'|'DAY' FROM d): 년, 월, 일 데이터 출력
-- Oracle
SELECT EXTRACT(YEAR FROM HIREDATE) 입사년도,
EXTRACT(MONTH FROM HIREDATE) 입사월,
EXTRACT(DAY FROM HIREDATE) 입사일
FROM EMP;
DATEPART('YEAR'|'MONTH'|'DAY', d): 년, 월, 일 데이터 출력
-- SQL Server
SELECT DATEPART(YEAR, HIREDATE) 입사년도,
DATEPART(MONTH, HIREDATE) 입사월,
DATEPART(DAY, HIREDATE) 입사일
FROM EMP;
TO_NUMBER(TO_CHAR(d, 'YYYY')) / YEAR(d): 년 데이터 출력
TO_NUMBER(TO_CHAR(d, 'MM')) / MONTH(d): 월 데이터 출력
TO_NUMBER(TO_CHAR(d, 'DD')) / DAY(d): 일 데이터 출력
SELECT ENAME,
HIREDATE,
TO_NUMBER(TO_CHAR(HIREDATE, 'YYYY')) 입사년도,
TO_NUMBER(TO_CHAR(HIREDATE, 'MM')) 입사월,
TO_NUMBER(TO_CHAR(HIREDATE, 'DD')) 입사일
FROM EMP;
------
SELECT ENAME,
HIREDATE,
YEAR(HIREDATE) 입사년도,
MONTH(HIREDATE) 입사월,
DAY(HIREDATE) 입사일
FROM EMP;
DATE 변수가 데이터베이스에 어떻게 저장되는지 살펴보면, 데이터베이스는 날짜를 저장할 때 내부적으로 세기(Century), 년(Year), 월(Month), 일(Day), 시(Hours), 분(Minutes), 초(Seconds)와 같은 숫자 형식으로 변환하여 저장한다.
데이터베이스는 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산이 가능하다. 즉, 날짜에 숫자 상수를 더하거나 뺄 수 있다.
5. 변환형 함수
변환형 함수의 종류
- 명시적 데이터 유형 변환: 데이터 변환형 함수로 데이터 유형을 변환하도록 명시
- 암시적 데이터 유형 변환: 데이터베이스가 자동으로 데이터 유형을 변환하여 계산
암시적 데이터 유형 변환의 경우 성능 저하가 발생할 수 있으며, 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직하다.
Oracle 변환형 함수
TO_NUMBER(문자열): 숫자로 변환
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')) 날짜
FROM DUAL;
TO_CHAR(숫자|날짜 [, FORMAT]): 숫자나 날짜를 주어진 FORAMT 형태의 문자로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜,
TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형
FROM DUAL;
TO_DATE(문자열 [, FORMAT]): 문자를 주어진 FORMAT 형태의 날짜 타입으로 변환
SQL Server 변환형 함수
CAST (expression AS data_type [(length)]): expression을 목표 데이터 유형으로 변환
SELECT TEAM_ID,
CAST(ZIP_CODE1 AS INT) + CAST(ZIP_CODE2 AS INT) 우편번호합
FROM TEAM;
CONVERT (data_type [(length)], expression [, style]): expression을 목표 데이터 유형으로 변환
SELECT CONVERT(VARCHAR(10),GETDATE(),111) AS CURRENTDATE;
6. CASE 표현
CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL 의 비교 연산 기능을 보완하는 역할을 한다. 또한 Oracle 의 Decode 함수와 같은 기능을 한다.
단일행 CASE 표현의 종류
1. SIMPLE_CASE_EXPRESSION
SELECT LOC,
CASE LOC
WHEN 'NEW YORK' THEN 'EAST'
WHEN 'BOSTON' THEN 'EAST'
WHEN 'CHICAGO' THEN 'CENTER'
WHEN 'DALLAS' THEN 'CENTER'
ELSE 'ETC' END as AREA
FROM DEPT;
- 조건이 맞으면 THEN 절을 수행하고, 맞지 않으면 ELSE 절을 수행
- CASE 다음에 바로 조건에 사용되는 컬럼이나 표현식을 표시
- WHEN 절 앞에서 정의한 컬럼이나 표현식과 같은지 아닌지 판단하는 조건만 사용 가능
2. SEARCHED_CASE_EXPRESSION
SELECT ENAME,
CASE
WHEN SAL >= 3000 THEN 'HIGH'
WHEN SAL >= 1000 THEN 'MID'
ELSE 'LOW' END AS SALARY_GRADE
FROM EMP;
- 조건이 맞으면 THEN 절을 수행하고, 맞지 않으면 ELSE 절을 수행
- CASE 다음에 컬럼이나 표현식을 표시하지 않음
- WHEN 절에서 EQUI(=) 조건 포함 여러 비교 조건을 이용한 조건절 사용이 가능
- SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 적용할 수 있다는 장점
3. DECODE (Oracle)
SELECT LOC,
DECODE(LOC,
'NEW YORK', 'EAST',
'BOSTON', 'EAST',
'CHICAGO', 'CENTER',
'DALLAS', 'CENTER',
'ETC'
) AREA_DECODE,
CASE LOC
WHEN 'NEW YORK' THEN 'EAST'
WHEN 'BOSTON' THEN 'EAST'
WHEN 'CHICAGO' THEN 'CENTER'
WHEN 'DALLAS' THEN 'CENTER'
ELSE 'ETC' END as AREA
FROM DEPT;
- 표현식의 값이 기준값1이면 값1을 출력하고, 기준값2이면 값2를 출력하는 방식
- 기준값이 없으면 디폴트 값을 출력한다.
- SIMPLE_CASE_EXPRESSION 조건과 동일하게 사용된다.
7. NULL 관련 함수
가. NVL/ISNULL 함수
- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.
- NULL 값의 대상이 숫자 타입일 경우 주로 0으로, 문자 타입일 경우 주로 'X'로 바꾼다.
- 산술 계산에 NULL 값이 포함될 경우, 이를 0으로 바꾸는 데에 유용하게 사용된다.
-- Oracle NVL 함수
SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION, '없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08';
-- SQL Server 함수
SELECT PLAYER_NAME 선수명, POSITION, ISNULL(POSITION, '없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08;
NVL/ISNULL 함수를 사용한 SQL 문장을 CASE 문장으로 표현할 수 있다.
SELECT PLAYER_NAME 선수명,
POSITION,
CASE
WHEN POSITION IS NULL THEN '없음'
ELSE POSITION END AS 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08';
NVL 함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 발생할 수 있으므로 굳이 NVL 함수를 사용할 필요가 없다. 다중행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL 이 나오고 전체 건수 중에서 일부만 NULL 인 경우는 다중행 함수의 대상에서 제외한다. 예를 들면 100 명 중 10 명의 성적이 NULL 값일 때 평균을 구하는 다중행 함수 AVG 를 사용하면 NULL 값이 아닌 90 명의 성적에 대해서 평균값을 구하게 된다.
나. NULL 과 공집합
SELECT 1 FROM DUAL WHERE 1 = 2; 와 같은 조건이 대표적인 공집합을 발생시키는 쿼리이며, 위와 같이 조건에 맞는 데이터가 한 건도 없는 경우를 공집합이라고 하고, NULL 데이터와는 또 다르게 이해해야 한다.
NVL 함수는 공집합을 다른 값으로 변화시키지 않는다.
SELECT MGR FROM EMP WHERE ENAME = 'JSC';
-- 'JSC'는 없는 데이터이므로 공집합 발생
SELECT NVL(MGR, 9999) FROM EMP WHERE ENAME = 'JSC';
-- 인수의 값이 공집합이므로 공집합이 그대로 출력
SELECT MAX(MGR) MGR FROM EMP WHERE ENAME = 'JSC';
-- 1 개의 행이 선택되었고, 실 데이터는 NULL이다.
-- 집계 함수와 Scalar Subquery의 경우는 결과값이 공집합인 경우에도 NULL 출력
SELECT NVL(MAX(MGR), 9999) MGR FROM EMP WHERE ENAME = 'JSC';
-- 공집합에 집계 함수를 사용하여 인수의 값이 공집합이더라도 원하는 값으로 변경시킬 수 있다.
다. NULLIF
EXPR1이 EXPR2와 같으면 NULL 을, 같지 않으면 EXPR1 을 반환한다.
-- NULLIF(EXPR1, EXPR2)
SELECT ENAME, EMPNO, MGR, NULLIF(MGR, 7698) NUIF
FROM EMP;
라. COALESCE
임의의 개수 EXPR 에서 NULL 이 아닌 최초의 EXPR 을 나타낸다. 만일 모든 EXPR 이 NULL 이라면 NULL 을 반환한다.
-- COALESCE(EXPR1, EXPR2, ...)
SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL
FROM EMP;
마지막 컬럼에 함수를 적용하여 COMM 값이 <null>일 때에는 SAL 값을 반환했다.
↓SQL 전문가 가이드 요약 목록
1장. 데이터 모델링의 이해
Part 1. 데이터 모델링의 이해
Part 2. 데이터 모델과 성능
2장. SQL 기본 및 활용
Part 1. SQL 기본
Part 2. SQL 활용
Part 3. SQL 최적화 기본원리
따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.
'데이터 사이언스 > SQL' 카테고리의 다른 글
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.8. ORDER BY 절 (0) | 2020.11.23 |
|---|---|
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.7. GROUP BY, HAVING 절 (2) | 2020.11.23 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.5. WHERE 절 (0) | 2020.11.22 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.4. TCL (0) | 2020.11.22 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.3. DML (0) | 2020.11.19 |
