SQL을 사용하기 시작했다면, 바로 보게 되는 것이 합니다. 이를 dbms 내장 함수라고 하는데요. 이 부분은 SQLD 시험에 자주 출제되는 부분이기 때문에 잘 이해하고 넘어가야 합니다. SQL을 효율적으로 사용할 수 있는 주요 기능 중 하나가 다양한 함수입니다.
이글에서는 다양한 SQL 함수를 살펴보고 이를 사용하는 방법을 설명하도록 하겠습니다. 또 함수를 통해 데이터 조작 및 분석에 활용할 수 있는 방법을 같이 알아보시죠.
함수의 활용
- 데이터베이스 데이터들의 계산할 때 수행합니다. → ANY, ALL
- 개별 데이터 항목, 즉, 컬럼을 수정할 때 사용합니다. → 문자 함수
- 행 그룹에 대한 출력 결과를 조작할 때 사용합니다. → 분석 함수
- 표시할 날짜 및 숫자 형식 지정하거나 변환할 때 사용합니다. → 숫자 함수, 날짜 함수
- 열(컬럼)의 데이터 유형 변환할 때 사용합니다. → 단일행 함수
단일행 함수
| 함수 구분 | 설명 |
| 문자 | 문자를 입력 값으로 받으며 문자 또는 숫자 값을 반환 합니다. |
| 숫자 | 숫자를 입력하면 숫자 값을 반환합니다. |
| 날짜 | DATE 데이터 유형의 값에 동작합니다. 모든 날짜 함수는 DATE 데이터 유형 값을 반환하며 MONTHS_BETWEEN 함수만 숫자를 반환합니다. |
| 변환 | 값의 데이터 유형을 변환합니다. |
| 일반 | NVL, NVL2, DECODE, CASE 등 |
문자 함수
문자 함수는 문자 데이터를 조작하는 아래의 함수를 제공합니다. 예를 들어, 두 개 이상의 문자열을 연결하는 CONCAT 함수가 있습니다.
| 함수 | 용도 | 예제 |
| upper | 알파벳 값을 대문자로 변환 | select * from (select 'aBcD' ori_col from dual) t1, (select upper('aBcD') upper_col from dual) t2, (select lower('aBcD') lower_col from dual) t3 |
| lower | 알파벳 값을 소문자로 변환 | |
| concat | 두 문자열을 하나로 연결. ||과 동일해서 자주 사용 안함 |
select concat('aa','bb') from dual; select 'aa'||'bb' from dual; |
| substr | 문자열중 원하는 부분만 선택 (문자 수 기준) (세 번째 인자값이 없을 경우 문자열의 끝까지를 의미) |
select ori_col , substr(ori_col,1,2) , substr(ori_col,3,4) from ( select '123456' ori_col from dual ); select ori_col , substr(ori_col,1,2) , substr(ori_col,3,3) from ( select '가나다라마바사' ori_col from dual ); |
| substrb | substr과 달리 문자수가 아닌 문자의 byte 기준 | select ori_col , substrb(ori_col,1,2) , substrb(ori_col,3,3) from ( select '123456' ori_col from dual ); select ori_col , substrb(ori_col,1,2) , substrb(ori_col,3,3) from ( select '가나다라마바사' ori_col from dual ); |
| replace | 첫 번째 인자 값에서 두 번째 인자 값에 해당하는 문자를 세 번째 인자 값으로 바꿈 | select replace('Oracle 10g','10g', '11g') from dual; |
| length | 문자열 길이를 구함 (문자 수 기준) |
select length('123') from dual; select length('가나다') from dual; |
| lengthb | 문자열 길이를 구함 (Bytes 기준) |
select lengthb('123') from dual; select lengthb('가나다') from dual; |
| lpad | 첫째 인자에 둘째 인자를 셋째 인자 길이(Byte)가 될 때까지 왼쪽부터 채움. | select lpad('123',10,'*') from dual union all select lpad('가나다',10,'*') from dual; |
| rpad | lpad와 달리 오른쪽을 채움 | |
| ltrim | 왼쪽부터 해당 문자 제거. 인자 값 없을 시 공백 제거 |
select ltrim(' xxAxBC') from dual; select ltrim('xxAxBC','x') from dual; à왼쪽부터 x가 아닌 문자를 만날 때까지 x를 제거한 후
select ltrim(' ') from dual; à null과 동일나머지를 리턴. |
| rtrim | ltrim과 달리 오른쪽부터 수행. | |
| trim | 왼쪽과 오른쪽의 공백을 제거 | select trim(' xxAxBC ') from dual; select trim('xxAxBCxx','x') from dual; à 오류 |
| ascii | 특정 값의 아스키코드를 리턴 | select ascii('A') from dual; |
| instr | 문자열에서 특정 문자의 위치를 리턴. 찾는 값이 없으면 0 리턴. 양쪽 % 사용 like 대체로 사용 가능. |
select instr('abcabcabc','c',1,2) from dual; select instr('abcabcabc','c',4,2) from dual; select * from ( select 'abcd' col1 from dual union all select 'bcd' from dual union all select 'xx' from dual ) where instr(col1,'d',1,1) >= 1 ; select * from ( select 'abcd' col1 from dual union all select 'bcd' from dual union all select 'xx' from dual ) where col1 like '%d%' ; |
숫자 함수
숫자 함수는 계산을 수행하는 데 필수적으로 사용됩니다. 예를 들어 'SUM' 함수는 지정된 열의 값을 더합니다.
| 함수 | 용도 | 예제 |
| round | 지정된 소수점 자리로 값을 반올림. | SELECT ROUND(45.923,2) ,ROUND(45.923,0) ,ROUND(45.923,-1) FROM DUAL ; |
| trunc | 지정된 소수점 자리까지 남기고 값 버리기 | SELECT TRUNC(45.923 , 2) ,TRUNC(45.923 ) ,TRUNC(45.923 , -2) FROM DUAL ; |
| mod | 나머지 값 | SELECT mod(10,3) FROM DUAL ; |
날짜 함수
날짜 작업은 데이터베이스에서 흔하게 일어나는 일입니다. 날짜 함수를 사용하면 필요에 따라 날짜 값의 형식을 지정할 수 있습니다.
| 함수 | 용도 | 예제 |
| add_months | 날짜에 달 수 더하기, 빼기 |
select add_months( to_date('20130105','yyyymmdd') ,1 ) from dual; select add_months( to_date('20130105','yyyymmdd') ,-1 ) from dual; |
| months_between | 두 날짜 간의 달 수 | select months_between( col2, col1 ) from ( select to_date('20130105','yyyymmdd') col1 , to_date('20130315','yyyymmdd') col2 from dual ) |
| last_day | 해당 달의 마지막 날 | select last_day( to_date('20130105','yyyymmdd') ) from dual ; |
| next_day | 입력일 이후에 해당 요일이 처음 돌아오는 일자 |
select next_day( to_date('20130101','yyyymmdd'), 1 ) from dual ; --> 1(일요일)부터 7(토요일)까지 가능 |
분석 함수
오라클 분석 함수는 데이터 집합 내에서 행 단위로 계산을 수행하여 결과를 반환하는 함수입니다. 즉, 각 행의 값을 기준으로 주변 행들의 값과 비교하거나, 특정 조건에 맞는 행들을 그룹화하여 계산을 수행할 수 있습니다.
1. SQL문
| 분석함수명(argument) OVER (partition by column_name order by column_name) |
2. 사용법
- 분석함수명 : 사용할 분석 함수의 이름 (예: ROW_NUMBER, RANK, DENSE_RANK, AVG, SUM 등)
- argument: 함수에 전달할 인수
- OVER 절
- PARTITION BY: 분석을 수행할 그룹을 지정합니다.
- ORDER BY: 데이터를 정렬하는 기준을 지정합니다.
| 분석 함수 | 설명 |
| ROW_NUMBER() | 각 행에 순번을 부여합니다. |
| RANK(), DENSE_RANK() | 순위를 부여하는데, RANK는 동일한 값에 대해 동일한 순위를 부여하지 않고 건너뛰며, DENSE_RANK는 동일한 값에 대해 동일한 순위를 부여합니다. |
| PERCENT_RANK() | 각 행이 전체 데이터에서 차지하는 백분율 순위를 계산합니다. |
| NTILE() | 데이터를 동일한 크기의 그룹으로 나누어 각 행이 속한 그룹 번호를 반환합니다. |
| LAG(), LEAD() | 현재 행에서 앞 또는 뒤의 행의 값을 가져옵니다. |
| AVG, SUM, COUNT, MIN, MAX | 집계 함수와 유사하게 사용되지만, OVER 절을 사용하여 더욱 유연하게 계산을 수행할 수 있습니다. |
ANY 함수
ANY 함수는 만족하는 값이 하나라도 있으면 결과가 'TRUE'를 출력합니다.
| 조건 | 결과 | 설명 |
| 1000 > ANY (500, 1000, 2000) | TRUE | ANY 값에 1000 보다 작은 500이 있으므로 TRUE |
| 1000 = ANY (500, 1000, 2000) | TRUE | ANY 값에 같은 값 1000이 있으므로 TRUE |
| 2000 < ANY (500, 1000, 2000) | FALSE | ANY 값에 2000 보다 큰 값이 없으므로 FALSE |
ALL 함수
ALL 함수는 모든 값이 만족해야 결과가 'TRUE'를 출력합니다.
| 조건 | 결과 | 설명 |
| 1000 > ALL (500, 1000, 2000) | FALSE | 1000이 ALL의 모든 값 보다 커야하므로 FALSE |
| 1000 = ALL (500, 1000, 2000) | FALSE | 1000이 ALL의 모든 값과 동일해야 하므로 FALSE |
| 3000 > ALL (500, 1000, 2000) | TRUE | 3000은 ALL의 모든 값 보다 크므로 TRUE |
마무리
SQL 함수는 관계형 데이터베이스의 데이터를 조작하고 분석하는 데 없어서는 안될 기능입니다. 이번 글에서는 단일행, 문자, 숫자, 날짜 함수를 이용하여 SQL 함수의 어떻게 활용하는지 간략하게 설명하였습니다. 함수들을 익혀 놓으면 SQL 활용하여 데이터를 분석에 좀 더 효과적일 수 있습니다.
마지막으로 ANY, ALL 함수는 WHER조건의 비교 연산자에서 사용하게 되는데요. 비교연산자 관련 내용은 아래 글을 참고하시기 바랍니다.
SQL 비교연산자(관계, 논리), IN, OR, AND, NOT, Exists
[SQLD] SQL 비교연산자(관계, 논리), IN, OR, AND, NOT, Exists
이번 포스팅에서는 SQL의 연사자들에 대해 알아보도록 하겠습니다. SQL은 관계, 논리, IN, OR, AND, NOT, Exists 등 연산자를 사용할 수 있는데요. 아래 연산자를 통해 WHERE절에서 반환되는 데이터 행을
bommbom.tistory.com
'데이터아키텍처' 카테고리의 다른 글
| [SQLD] 조인(Join) 종류(2) - Inner 조인 vs Outer 조인 (1) | 2023.11.22 |
|---|---|
| [SQLD] 조인(Join) 종류(1) - Equi, Non-Equi, 카테시안곱 (1) | 2023.11.22 |
| [SQLD] 데이터베이스 제약조건 및 형변환 (46) | 2023.11.17 |
| [SQLD] SQL 비교연산자(관계, 논리), IN, OR, AND, NOT, Exists (57) | 2023.11.16 |
| [SQLD] SQL의 이해 및 구문 (2) | 2023.11.15 |
