★ SQL Server 함수 표시 / 안되어 있으면 Oracle과 SQL Server 동일함

---

데이터베이스 함수는 벤더에서 제공하는 함수인 내장 함수(Built-in Function)과 사용자가 정의할 수 있는 사용자정의함수(User Defined Function)로 나눌 수 있다.

내장 함수

• 벤더에서 제공하는 함수
• 함수의 입력 행수에 따라단일행 함수, 다중행 함수로 구분

1) 단일행 함수(Single-Row Function)

• 단일행 함수는 select, where, order by , update set절에 사용 가능
• 문자형, 숫자형, 날짜형, 변환형, NULL관련 함수가 있다

1. 문자형 함수

함수	설명
lower(문자열)	문자열을 모두 소문자로 변경
upper(문자열)	문자열을 모두 대문자로 변경
ascii(문자)	문자를 아스키코드로 변경
concat(문자열 ... ) 없음	문자열을 연결한다. * Oracle의 '||' 연산과 SQL Server의 '+' 연산과 동일 * Oracle, MySQL에서 유효한 함수
substr(문자열, 시작점 [, 길이]) substring(문자열, 시작점 [, 길이])	문자열의 시작점부터 특정 길이만큼 자른다. * 길이가 생략된 경우 끝까지 자른다
langth(문자열) len(문자열)	문자열의 길이
ltrim(문자열 [, 특정문자]) ltrim(문자열)	문자열에서 가장 왼쪽에 위치한 특정문자를 제거 * 특정문자가 생략된 경우 왼쪽 공백 제거
rtrim(문자열 [, 특정문자]) rtrim(문자열)	문자열에서 가장 오른쪽에 위치한 특정문자를 제거 * 특정문자가 생략된 경우 오른쪽 공백 제거
trim([leading/trailing/both][, 특정문자] [ from ] 문자열) trim([leading/trailing/both][ from ] 문자열)	문자열에서 머리말/꼬리말/양쪽에 있는 특정문자를 제거 * [leading/trailing/both]이게 생략된 경우 both가 기본값이다.
replace(문자열, 특정문자열 [, 변경문자열])	문자열에서 특정문자열을 변경문자열로 치환 * 변경문자열이 생략된경우 제거만 진행

2. 숫자형 함수

함수	설명
abs(숫자)	숫자의 절대값
mod(수1, 수2)	수1을 수2로 나눈 나머지 * 수2가 0인 경우 결과값은 수1 * 두수가 음수인 경우 결과도 음수
round(수 [, 자릿수])	수를 소숫점 자릿수까지 반올림 * 자릿수 생략된 경우 기본값은 0
trunc(수 [, 자릿수])	수를 소숫점 자릿수까지 버림 * 자릿수 생략된 경우 기본값은 0
sign(수)	부호 (양수 : 1 / 음수 : -1 / 0 )
chr(아스키코드) char(아스키코드)	아스키코드를 문자로 변경
ceil(숫자) ceiling(숫자)	소수점 이하의 수를 올림한 정수
floor(숫자)	소수점 이하의 수를 버림한 정수

3. 날짜형 함수

함수	설명
sysdate getdate()	현재시각 (년월일시분초)
extract(특정단위 from 날짜데이터) datepart(특정단위, 날짜데이터)	날짜데이터에서 특정단위만 반환 * 특정단위 : year, month, day, hour, minute, second
add_months(날짜데이터, n) dateadd(month, n, 날짜데이터)	날짜데이터 + n월

⭐️ Oracle에서 날짜 연산 == 숫자연산

• 날짜데이터 + 1 == 날짜데이터 + 1일
• 날짜데이터 + 1/24 == 날짜데이터 + 1시간
• 날짜데이터 + 1/24/60 == 날짜데이터 + 1분
• 날짜데이터 + 1/24/(60/10) == 날짜데이터 + 10분

4. 변환형 함수

• 명시적 형변환 : 데이터베이스가 내부적으로 알아서 데이터 유형을 변환함
  SQL Server는 convert or cast를 사용하여 변환할 수 있다.
• 암시적 형변환 : 변환함수를 사용하여 데이터 유형변환을 명시적으로 나타냄

함수	설명
to_number(문자열)	문자열을 숫자로 변환
to_char(수 or 날짜 [, 포맷])	수 또는 날짜 데이터를 포맷형식의 문자열로 변환
to_date(문자열, 포맷)	문자열을 포맷형식으로 읽어서 날짜로 변환

to_number(to_char(수 or 날짜, 'YYYY'|'MM'|'DD')) = YEAR|MONTH|DAY

5. ⭐️ NULL 관련 함수

함수	설명
nvl(인수1, 인수2) isnull(인수1, 인수2)	인수1이 NULL이면 인수2를 반환, 그렇지 않으면 인수1을 반환
nullif(인수1, 인수2)	인수1과 인수2가 같으면 NULL을 반환, 그렇지 않은 경우 인수1을 반환
coalesce(인수 ...)	null아닌 최초 인수를 반환

6. ⭐️ CASE 문

• SERACHED_CASE_EXPRESSION 방식

  case 
      when 컬럼 = 조건값 then 결과값
      else 결과값
  end

• SIMPLE_CASE_EXPRESSION 방식

  case 컬럼
      when 조건값 then 결과값
      else 결과값
  end

• decode(컬럼, 조건값1, 결과값1 [, 조건값2, 결과값2] ... [, ELSE 결과값])

2) 다중행 함수(Multi-Row Function)

• 다중행 함수는 다시 집계 함수,그룹함수, 윈도우 함수로 구분
• 그룹 함수는 Group by 절과 함께 사용된다.

1. 집계 함수 (Aggregate function)

• NULL값을 제외하고 결과를 집계
• 여러행으로부터 하나의 결과값을 반환하는 함수이다.
• Select 구문에서만 사용가능

종류

• count(*)
• count([distinct] 컬럼)
• avg([distinct | all] 컬럼)
• min([distinct | all] 컬럼)
• max([distinct | all] 컬럼)
• sum([distinct | all] 컬럼)
• stdev([distinct | all] 컬럼) : 컬럼의 표준편차
• varian([distinct | all] 컬럼) : 컬럼의 분산

⭐️ 집계 함수는 Where에서 사용할 수 없다.
집계 함수는 Group by절과 함께 사용되며, 집계 함수를 사용할 수 있는 Group by절 보다 Where 절이 먼저 수행되기 때문이다.

2. ⭐️ 그룹 함수 (Group function)

• NULL값을 제외하고 결과를 집계
• 집계함수와 함께 사용된다.

종류

• ROLLUP(A,B ...)
  • AB집계 > A집계 > 총계
  • ⭐️ 컬럼 순서에 따라 결과가 다름
• CUBE(A,B,C ...)
  • ABC집계 > AB집계 > AC집계 > BC집계 > A집계 > B집계 > C집계 > 총계
  • 가능한 모든 경우의 수로 결합하여 집계 (다차원 집계)
  • 엄청난 시스템 부하
• ⭐️ GROUPING SETS
  • GROUPING SETS(A,B) : A집계 > B집계
  • GROUPING SETS((A,B)) : AB집계
  • GROUPING SETS((A,B),A) : AB집계 > A집계
  • GROUPING SETS(A,B,()) : A집계 > B집계 > 총계
  • GROUPING SETS(A, ROLLUP(B)) : A집계 > B집계 > 총계
  • GROUPING SETS(A,ROLLUP(B,C)) : A집계 > BC집계 > B집계 > 총계
  • GROUPING SETS(A,B,ROLLUP(C)) : A집계 > B집계 > C집계 > 총계

관련 함수

• GROUPING(컬럼) : 소계를 나타내는 ROW를 구분해줌 (소계행이면 1, 아니면 0)

3. 윈도우 함수 (Window Function)

• OVER 키워드와 함께 사용된다.

순위함수	집계함수	행 순서 함수	비율함수
RANK DENSE_RANK ROW_NUMBER	SUM AVG MAX MIN COUNT	FIRST_VALUE LAST_VALUE LAG LEAD	CUME_DIST PERCENT_RANK NTILE RATIO_TO_REPORT

사용되는 SQL문 양식

SELECT 
	윈도우함수 OVER([그룹옵션] [정렬옵션] [범위옵션])
FROM 테이블;

[그룹옵션]
PARTYTION BY 컬럼 ▶️ 컬럼별로 그룹핑
[정렬옵션]
ORDER BY 컬럼 [ASC/DESC] ▶️ 컬럼기준으로 정렬
[범위옵션]
[RANGE/ROWS] BETWEEN [범위] AND [범위] ▶️ default : RANGE UNBOUNDED PRECEDING
[범위옵션 > 범위]
UNBOUNDED PRECEDING : 첫번째 행
UNBOUNDED FOLLOWING : 마지막 행
CURRENT ROW : 현재 행
n PRECEDING : n개 이전 행
n FOLLOWIG : n개 이후 행

순위함수

종류	설명
RANK()	- 순위를 매기면서 같은 쉬위가 존재하면 존재하는 수 만큼 다음 순위를 건너뛴다. - 1,2,2,4,5 ...
DENSE_RANK()	- 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않어 이어서 매긴다. - 1,2,2,3,4 ...
ROW_NUMBER()	- 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다. - 그냥 행번호를 부여한다고 생각하면 된다.

SELECT 
       RANK( ) OVER (ORDER BY 컬럼 DESC), -- 컬럼 내림차순 순위 매김
       RANK( ) OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2 DESC) -- 컬럼1별로 컬럼2 내림차순 순위 매김
FROM 테이블;

집계 함수

• 종류 생략

SELECT
	SUM(컬럼1), -- 컬럼1 총합계
	SUM(컬럼1) OVER(PARTITION BY 컬럼2), -- 컬럼2별 컬럼1 합계
    SUM(컬럼1) OVER(PARTITION BY 컬럼2
    				ORDER BY 컬럼3 DESC
                    RANGE UNBOUNDED PRECEDING), -- [Oracle] 컬럼2별 컬럼3 내림차순 기준 컬럼1의 누적 합계
    SUM(컬럼1) OVER(ORDER BY 컬럼3 DESC), -- 컬럼3 내림차순 기준 컬럼1의 누적 합계
FROM 테이블;

행 순서 함수

종류	설명
FIRST_VALUE(컬럼)	- 파티션별 맨 첫번째 데이터 - SQL Server는 지원하지 않는다.
LAST_VALUE(컬럼)	- 파티션별 맨 마지막 데이터 - SQL Server는 지원하지 않는다.
LAG(컬럼, n [, 기본값])	- 파티션별 n행 이전 데이터를 구함 - n이 생략된 경우 1이다. - 가져온 값이 NULL인 경우 대체할 기본값을 설정할 수 있다. - SQL Server는 지원하지 않는다.
LEAD(컬럼, n [, 기본값])	- 파티션별 n행 이후 데이터를 구함 - n이 생략된 경우 1이다. - 가져온 값이 NULL인 경우 대체할 기본값을 설정할 수 있다. - SQL Server는 지원하지 않는다.

그룹 내 비율 함수

종류	설명
RATIO_TO_REPORT(컬럼)	- 파티션별 합계에서 차지하는 비율 - SQL Server는 지원하지 않는다.
PERCENT_RANK()	- 파티션별 순위에 대한 백분률 (0~1) - SQL Server는 지원하지 않는다.
CUME_DIST()	- 해당 파티션에서 누적 백분률 - SQL Server는 지원하지 않는다.
NTILE(n)	- 주어진 수만큼 행들을 n등분한 후 현재행에 해당하는 등급을 구한다.

---

연산 주의사항

⭐️ NULL 포함 연산

• SQL 사칙연산에 NULL이 포함되어 있으면 ▶️ 결과는 무조건 NULL이다.
• 집계함수 연산 데이터에 NULL이 포함되어 있으면 ▶️ NULL데이터는 연산대상에서 제외된다.
  ex. count(*) => NULL값은 제외하고 카운팅

⭐️ / 연산

• / 연산에서 분모가 0인 경우 ▶️ 에러 발생
• / 연산에서 분자가 0인 경우 ▶️ 결과는 무조건 0