SQL을 다루면서 기본적인 문법을 사용하지만, 함수를 많이 사용합니다. 이번 글에서는 SQL의 함수에 대해 알아보겠습니다.
혹시 함수에 대해서만 찾아볼 분들을 위해 아래 해당 부분으로 넘어갈 수 있도록 링크를 걸어두도록 하겠습니다.
문자열 관련 함수
수치 관련 함수
날짜 관련 함수
데이터 타입 변환 함수
NULL 관련 함수
CASE 구문, DECODE 함수
그룹함수-집계함수
그룹함수-소계함수
윈도우 함수
함수
함수는 특정 기능을 처리하기 위해 사용합니다. 하나의 함수만 사용할 수 있고, 두 개 이상의 함수를 중첩해서 사용이 가능합니다.
또한, DBMS마다 의존적인 함수가 존재하기 때문에 여러 DBMS에서 사용이 가능한 함수가 있는 반면 그렇지 못한 함수도 있습니다.
SQL에서 함수는 크게 단일행 함수, 그룹함수, 윈도우 함수가 존재합니다.
1. 단일행 함수
- 단일행 함수는 행(레코드) 단위로 적용되는 함수로, 대부분의 함수가 속해있습니다.
- 문자열 관련 함수, 수치 관련 함수, 날짜 관련 함수와 데이터 타입을 변경할 때 사용하는 변환 함수, NULL 관련 함수가 있습니다.
- 아래 함수 인자 중 대괄호([]) 안에 들어간 인자는 선택적인 인자로, 넣지 않아도 정상적으로 작동합니다.
문자열 관련 함수
| 함수(Oracle) |
기능 |
| INITCAP('문자열') |
첫 글자만 대문자로 변경, 나머지 글자는 모두 소문자 처리 |
| LOWER('문자열') |
모두 소문자로 변경 |
| UPPER('문자열') |
모두 대문자로 변경 |
| CONCAT('문자열', '문자열') |
두 문자열을 연결(병합) |
| LPAD('문자열1', 수[, '문자열2']) |
문자열1의 길이가 수만큼 되도록 왼쪽에 문자열2(지정하지 않은 경우 공백)을 추가 |
| RPAD('문자열1', 수[, '문자열2']) |
문자열1의 길이가 수만큼 되도록 왼쪽에 문자열2(지정하지 않은 경우 공백)을 추가 |
| SUBSTR('문자열', 시작지점[, 추출개수]) |
시작지점부터 추출개수(없으면 문자열 끝까지)만큼 반환 |
| LENGTH('문자열') |
문자열의 길이를 반환 |
| REPLACE('문자열', '대상 문자'[, '새로 넣을 문자']) |
문자열에서 대상 문자를 새로 넣을 문자로 변경(지정 안할 시 제거) |
| INSTR('문자열', '특정문자'[, 시작위치, n]) |
문자열의 시작위치부터 특정문자를 찾아 위치를 반환(시작위치 지정 안할 시 처음부터 찾음) n이 지정될 경우 n번째로 나온 위치를 반환 |
| LTRIM('문자열'[, '특정문자']) |
문자열 왼쪽에서 특정문자를 제거(지정안할 시 공백 제거) |
| RTRIM('문자열'[, '특정문자']) |
문자열 오른쪽에서 특정문자를 제거(지정안할 시 공백 제거) |
| TRIM(메서드 '특정문자' FROM '문자열') |
메서드에 따라 문자열에서 특정문자를 제거(공백 지정 가능) 메서드: LEADING(왼쪽), TRAILING(오른쪽), BOTH(양쪽) |
수치 관련 함수
| 함수(Oracle) |
기능 |
| ABS(수) |
수의 절댓값을 반환 |
| CEIL(수) |
주어진 수보다 크거나 같은 최소 정수 반환 |
| FLOOR(수) |
주어진 수보다 작거나 같은 최대 정수 반환 |
| MOD(수1, 수2) |
수1을 수2로 나눈 나머지 |
| ROUND(수[, 자릿수]) |
자릿수의 위치에서 반올림. 자릿수를 생략하면 0으로 처리. 양수는 소수점 부분, 음수는 정수 부분의 자릿수를 나타냄 |
| TRUNC(수[, 자릿수]) |
자릿수의 위치에서 절삭. 자릿수를 생략하면 0으로 처리. 양수는 소수점 부분, 음수는 정수 부분의 자릿수를 나타냄 |
| SIGN(수) |
수의 부호를 나타냄. 양수는 1, 음수는 -1, 0은 0 반환 |
날짜 관련 함수
- 참고로, Oracle의 경우 날짜 연산(+, -)이 가능합니다.
| 함수(Oracle) |
기능 |
| SYSDATE, SYSTIMESTAMP |
현재 날짜(날짜 및 시간)을 반환 |
| MONTHS_BETWEEN('날짜1', '날짜2') |
두 날짜 사이의 월 수를 계산 |
| ADD_MONTHS('날짜', 수) |
날짜에 수를 더하여 반환(음수도 가능) |
| NEXT_DAY('날짜', '요일') |
요일에 대해 가장 가까운 미래의 날짜를 반환 |
| LAST_DAY('날짜') |
해당 월의 마지막 날짜 반환 |
| ROUND('날짜', '단위') |
명시한 단위에서 반올림하여 반환 |
| TRUNC('날짜', '단위') |
명시한 단위에서 절삭하여 반환 |
| EXTRACT('단위' FROM '날짜') |
날짜에서 단위에 해당하는 정보를 추출 |
변환 함수
- 데이터 타입을 변환하는 방법은 두 가지가 있습니다.
1.묵시적 변환: 입력받은 데이터의 형태를 보고 자동으로 수치, 문자, 날짜로 형태를 지정하는 방법입니다.
우리가 흔히 엑셀에서 어떤 데이터를 입력했을 때 그에 맞춰 형식이 맞춰지는 것과 같은 것입니다.
2.명시적 변환: 데이터 타입을 사용자가 강제로 수치, 문자, 날짜로 정하는 것입니다. 아래의 함수들을 이용해서 변환할 수 있습니다.
단, 변환하려고 하는 형식과 맞아야 변환이 가능하며, 그렇지 않은 경우 에러가 발생합니다.
| 함수(Oracle) |
기능 |
| TO_NUMBER('문자'[, '형식']) |
지정된 형식에 맞는 문자를 같은 형식의 숫자로 변환 |
| TO_CHAR(수[, '형식']) |
지정된 형식의 수를 문자로 변환 |
| TO_DATE('문자'[, '형식']) |
형식에 따르는 문자를 날짜로 변환 |
| TO_CHAR('날짜'[, '형식]) |
날짜를 형식에 맞춘 문자로 변환 |
- 변환함수를 사용할 때 대표적으로 형식에 사용하는 것들은 아래 표와 같습니다.
NULL 관련 함수
| 함수(Oracle) |
기능 |
| NVL(인수1, 인수2) |
인수1의 값이 NULL인 경우 인수2를 반환하고, 그렇지 않은 경우 인수1을 그대로 반환 |
| NULLIF(인수1, 인수2) |
인수1과 인수2가 같으면 NULL을 반환, 그렇지 않으면 인수1을 반환 |
| COALESCE(인수1, 인수2, 인수3, ...) |
NULL이 아닌 최초의 인수를 반환 |
- 숫자 형식
| 형식 표현 |
기능 |
| ,(콤마) .(온점) |
구분점(예: 9,000 $99.99) |
| $ |
달러 표시 |
| L |
Locale 정보(NLS)에 따른 해당 지역의 화폐 표기 |
| 0 |
빈 자리수에는 0을 표시(예: 0003) |
| 9 |
숫자 하나를 의미. 0으로 채워지면 표시되지 않음(예: 0001을 입력 -> 1로 반환) |
| S |
부호(+, -)를 반환 |
- 날짜 형식
| 형식 표현 |
기능 |
| YYYY |
네 자리 연도 |
| YY |
년도 중 끝 두자리 반환 |
| MM |
월 반환(예: 5) |
| MON |
월 반환(예: 5월, JUL) -> NLS가 영어인 경우 영어 반환 |
| MONTH |
월 반환(예: 5월, JULY) |
| DD |
일 |
| HH |
시(24시 기준) |
| HH12 |
시(12시 기준) |
| MI |
분 |
| SS |
초 |
| DAY |
요일 반환(예: 화요일) |
| DY |
요일 반환(예: 화) |
| (AM), (PM) |
오전, 오후 반환 |
CASE 구문
- Oracle에서만 사용할 수 있는 DECODE 함수와 같은 역할을 하는 구문으로, 동등 비교, 부등 비교를 해서 값을 반환합니다.
- 기본 형식은 다음과 같습니다.
CASE 컬럼 WHEN 값 THEN 연산(또는 값) END (simple case) CASE WHEN 컬럼 [>= 10] THEN 연산(또는 값) END (searched case)- 추가로 DECODE 함수의 경우 다음과 같은 방식으로 사용할 수 있습니다.
DECODE(컬럼, '값1', '값1일 경우 표시할 문자', '값2', '값2일 경우 표시할 문자', ..., 'default')
2. 그룹 함수
- 반드시 레코드가 그룹으로 묶여야 사용이 가능한 함수로, 단일행 함수와 다르게 여러 행을 인자로 받아 하나의 반환값을 결과로 제시합니다.
- 그룹으로 묶는 방법은 다음 두 가지 방법이 있습니다.
- 자동(묵시적): 테이블의 전체 레코드가 하나의 그룹으로 묶이는 방법
- 명시적: GROUP BY 절을 이용해서 그룹을 묶는 방법
- 그룹으로 묶지 않은 일반 컬럼은 그룹 함수와 같이 사용할 수 없습니다.
- 그룹함수는 집계함수와 소계(총계)함수로 구분할 수 있습니다.
집계함수
| 함수(Oracle) |
기능 |
| COUNT(* OR 컬럼) |
*: 전체 레코드의 개수를 반환 컬럼: 컬럼값이 NULL인 레코드를 제외한 개수를 반환 컬럼 앞에 DISTINCT를 적용하면 중복까지 제외해서 반환한다. |
| SUM(컬럼) |
컬럼 값들의 합계를 반환 |
| AVG(컬럼) |
컬럼 값들의 평균을 반환 |
| MIN(컬럼) |
컬럼 값들 중 최솟값을 반환 |
| MAX(컬럼) |
컬럼 값들 중 최댓값을 반환 |
소계함수
- 그룹으로 묶을 때, 소그룹간의 소계 및 총계를 계산하는 함수들입니다.
- ROLLUP: 소그룹 간 소계 및 총계를 계산하는 함수입니다.
- ROLLUP(A): A 그룹 소계, 총합을 반환합니다.
- ROLLUP(A, B): A, B 그룹 소계, A 그룹 소계, 총합을 반환합니다.
- ROLLUP(A, B, C): A, B, C 그룹 소계, A, B 그룹 소계, A 그룹 소계, 총합을 반환합니다.
- CUBE: 소그룹 간의 소계 및 총계를 다차원적으로 계산할 수 있는 함수입니다.
- CUBE(A): A 그룹 소계, 총합을 반환합니다.
- CUBE(A, B): A, B 그룹 소계, A 그룹 소계, B 그룹 소계, 총합을 반환합니다.
- CUBE(A, B, C): A, B, C 그룹 소계, A, B 그룹 소계, A, C 그룹 소계, B, C 그룹 소계, A 그룹 소계, B 그룹 소계, C 그룹 소계, 총합을 반환합니다.
- GROUPING SETS: 특정 항목에 대한 소계를 계산합니다. 인자 값으로 ROLLUP이나 CUBE를 중복해서 사용할 수 있습니다.
- GROUPING SETS(A, B): A 소계, B 소계를 반환합니다.
- GROUPING SETS(A, B, ()): A 소계, B 소계, 총합을 반환합니다.
- GROUPING SETS(A, ROLLUP(B, C)): A 소계, ROLLUP(B, C)의 결과를 반환합니다. 즉, A 소계, B, C 소계, B 소계, 총합을 반환합니다.
- GROUPING: ROLLUP, CUBE, GROOPING SETS 등과 함께 쓰이며, 소계를 나타내는 레코드를 구분할 수 있게 합니다.
위의 함수와 달리 원하는 위치에 원하는 텍스트를 출력할 수 있다는 장점이 있습니다.
3. 윈도우 함수
순위함수
- OVER 키워드와 함께 사용되며 역할에 따라 나누어진다.
| 함수(Oracle) |
기능 |
| RANK |
순위를 매기면서 같은 순서가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다. 예: 1, 2, 3, 4, 4, 6, 7 |
| DENSE_RANK |
순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다. 예: 1, 2, 3, 4, 5, 5, 6 |
| ROW_NUMBER |
순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여 예: 1, 2, 3, 4, 5, 6, 7 |
행 순서 함수
| 함수(Oracle) |
기능 |
| FIRST_VALUE |
파티션 별 가장 선두에 위치한 데이터를 구하는 함수 |
| LAST_VALUE |
파티션 별 가장 끝에 위치한 데이터를 구하는 함수. 의도한 결과를 나오게 하려면 WINDOW 절을 명시하는 것이 필요하다. |
| LAG |
파티션별로 특정 수만큼 앞선 데이터를 구하는 함수 |
| LEAD |
파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수 |
비율 함수
| 함수(Oracle) |
기능 |
| RATIO_TO_REPORT |
파티션 별 합계에서 차지하는 비율을 구하는 함수 |
| PERCENT_RANK |
해당 파티션의 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수 |
| CUME_DIST |
해당 파티션에서 누적 백분율을 구하는 함수 |
| NTILE |
주어진 수만큼 행들을 N등분한 후 현재 행에 해당하는 등급을 구하는 함수 |