[SQL Server] Do it SQL 입문 5장 다양한 SQL 함수 사용하기

교재 : Do it SQL 입문 (이지스퍼블리싱, 강성욱)

 

5.1 문자열 함수

1) 문자열과 문자열 연결하기

- + 

SELECT symbol + ' : ' + company_name FROM nasdaq_company

- CONCAT 함수

SELECT CONCAT('I', 'LOVE', 'SQL')

2) 문자열과 숫자 또는 날짜 연결하기

- CAST, CONVERT 함수로 문자열 자료형으로 변경 후 + 로 연결하기

- CAST 함수

CAST(expression AS datatype(length))

- CONVERT 함수

CONVERT (data_type [(length)], expression [, style])

- 함수 사용 시 문자열의 길이를 지정할 수 있으며, 지정한 값이 실제 변경할 문자열의 길이보다 짧으면 문자열이 잘린다.

- 숫자형을 문자열로 변환하되 문자열 크기를 작게 지정하면 산술 오버플로 오류가 발생한다.

3) NULL과 문자열 연결하기

- 문자열과 연결하려는 값이 NULL이면 결과는 NULL로 반환된다. 따라서 NULL을 문자형이나 숫자형으로 변경해줘야 한다.

 - ISNULL

ISNULL (check_expression, replace_value)

- COALESCE : 첫 번째 인자로 전달한 값이 NULL이면 쉼표로 구분해 입력한 N개의 인자를 순차 대입한다. 

COASLESCE(첫 번째 인자, 두 번째 인자,... N 번째 인자)

4) 문자열을 소문자나 대문자로 변경하기

- UPPER, LOWER

SELECT UPPER('do IT sql!')

5) 문자열 공백 제거하기 

- LTRIM : 문자열의 왼쪽 공백 제거

- RTRIM : 문자열의 오른쪽 공백 제거

- TRIM : 문자열 양쪽의 공백, 마침표 제거

SELECT TRIM('.,! ' FROM '          # DO IT sql         .' )

6) 문자열 길이 반환하기 : LEN

SELECT LEN('DO IT SQL')

7) 특정 문자열까지  문자열 길이 반환하기

- 예시 : 느낌표가 있는 문자까지의 길이 반환

- 지정한 문자가 탐색 대상 문자열에 없으면 0을 반환한다.

SELECT CHARINDEX('!', 'do it!! sql') -- 결과는 6

8) 지정한 길이만큼 문자열 반환하기 

- LEFT : 문자열 왼쪽에서 시작해 정의한 위치까지 문자열을 반환한다.

- RIGHT : 문자열 오른쪽에서 시작해 정의한 위치까지 문자열을 반환한다.

- 문자열 위치 시작값은 1이다.

SELECT LEFT('DO IT SQL', 2), RIGHT('DO IT SQL', 2)

9) 지정한 범위까지의 문자열 반환하기

- 2번째 인자에는 시작 범위를, 3번째 위치에는 반환할 문자 개수가 들어간다.

SUBSTRING(expression, start, length)

10) 특정 문자를 다른 문자로 변경하기 

- 2번째 인자에는 변경하려는 문자열을, 3번째 인자에는 변경 문자열이 들어간다.

REPLACE (string_expression, string_pattern, string_replacement)

11) 문자 반복하기

- REPLICATE : 반복할 문자와 반복 횟수를 인자로 한다.

REPLICATE (string_expression, integer_expression)

- SPACE : 공백 문자 반복하기 

SPACE(10)

12) 문자열 역순으로 표시하기 

REVERSE('DO IT SQL')

13) 지정한 범위의 문자열을 삭제하고 새 문자열 끼워넣기

SELECT STUFF ( 'Do it! XX SQL Server', 8, 2, N'마이크로소프트') 
-- 결과 : Do it! 마이크로소프트 SQL Server
-- 8번째 자리부터 2개의 문자를 제거하고 그 자리에 문자열 삽입

14) 숫자를 문자열로 변환하기

- 2번째 인자로 전달하는 변환 길잇값이 변경할 문자열 길이보다 짧은 경우 오른쪽 맞춤으로 정렬한다.

- 3번째 인자로 전달하는 소수 부분 길이에 맞게 반올림해 표현한다.

SELECT STR(123.45, 6, 1)

 

 

5.2 날짜 함수

1) 서버의 현재 날짜, 시간 반환하기

- GETDATE() : 소수점 3자리까지 시간 표현

- SYSDATETIME() : 소수점 7자리까지 시간 표현

- 실행할 때마다 다른 값을 반환하는 비결정적 함수에 속한다.

2) 서버의 현재 UTC 날짜, 시간 반환하기

- GETUTCDATE() : 소수점 3자리까지 시간 표현

- SYSUTCDATETIME() : 소수점 7자리까지 시간 표현

3) 날짜 더하기

- 날짜 형식, 숫자, 더하거나 뺄 대상 날짜 순으로 인자로 한다.

DATEADD (datatype, year, date)

4) 날짜 차이 구하기

- 날짜 형식, 시작 날짜, 종료 날짜를 받는다.

DATEDIFF (datapart, startdate, enddate)

5) 지정된 날짜 일부 반환하기

- DATEPART : 숫자로 반환 (ex. 월요일이면 1 반환)

- DATENAME : 실젯값으로 반환

DATEPART (datepart, date)
DATENAME (datepart, date)

6) 날짜에서 일, 월, 연도 반환하기

- DAY(날짜), MONTH(날짜), YEAR(날짜)

7) 날짜 자료형 또는 형태 반환하기

- CONVERT

ex) yyyy-mm-dd hh:mm:ss.sss 형태를 yy/mm/dd 형태로 바꾸기

SELECT symbol, last_crawel_date,
    CONVERT(nvarchar(10), year(last_crawel_date)) + '/' +
    CONVERT(nvarchar(10), month(last_crawel_date)) + '/' +
    CONVERT(nvarchar(10), day(last_crawel_date))
FROM nasadaq_company

-- 스타일 매개변수 사용
SELECT CONVERT(nvarchar(10), year(last_crawel_date), 111)

 

 

5.3 집계 함수

1) 조건에 맞는 데이터 개수 세기

- COUNT(열 이름) : INT 범위 반환

- COUNT_BIG(열 이름) : BIGINT  범위 반환 (약 21억 초과 시 사용)

- 특정 열만 지정해서 사용 시 NULL값은 제외한다.

- DISTINCT 문을 조합하면 고윳값의 데이터 개수를 얻을 수 있다.

2) 데이터 합 구하기

- SUM(열 이름)

- 모든 행의 값을 합하지만 DISTINCT 문을 조합해 중복값을 무시하고 고윳값에만 SUM함수를 적용할 수 있다.

SELECT SUM(close_price) FROM nasadaq_company
SELECT SUM(DISTINCT close_price) FROM nasadaq_company

- 함수의 반환값이 반환 자료형을 초과하면 오류가 발생한다.

3) 데이터 평균 구하기

- AVG(열 이름)

- NULL 값은 무시하고, DISTINCT 문을 사용하면 중복값을 제외하고 고윳값에만 AVG 함수를 적용할 수 있다.

4) 최댓값, 최솟값 구하기

- MAX(열 이름), MIN(열 이름)

- NULL 값을 무시하고, 데이터베이스 속성에 정의된 데이터 정렬 순서에 따라 다르게 출력된다.

5) 부분합, 총합 구하기

- GROUPBY문을 ROLLUP 함수와 CUBE 함수에 조합한다.

- ROLLUP : GROUP BY ROLLUP(...)에 입력한 열 기준으로 오른쪽에서 왼쪽으로 열을 이동하며 부분합과 총합을 구한다.

- CUBE : GROUP BY 문과 조합해 사용하며 모든 열 조합의 집계 그룹을 만든다.

6) 모든 값의 표준편차 구하기

- STDEV(열 이름) : 모든 값의 편차를 구한다.

- STDEVP(열 이름) :  모집단의 표준편차를 구한다.

 

 

5.4 수학 함수

1) 절댓값 구하기 

- abs(값)

- 인수로 식을 입력할 수 있다.

2) 양수, 음수 여부 판단하기

- SIGN(값)

- 양수일 시 1, 음수일 시 -1, 0이면 0을 반환한다.

- 기본적으로 인자로 입력한 자료형을 반환하지만, smallint, tinyint와 같은 자료형은 근사치 자료형인 int와 같은 방식으로 반환한다.

3) 천장값, 바닥값 구하기

- CEILING(값) : 지정한 숫자보다 크거나 같은 최소 정수를 반환한다.

- FLOOR(값) : 지정한 숫자보다 작거나 같은 최대 정수를 반환한다.

4) 반올림 구하기

ROUND (numeric_expression, length [, function])

- numeric_expression :  bit 자료형을 제외한 정확한 수치나 근사자료형 범위의 값을 사용한다.

- length : numeric_expression을 반올림해 표현한 자릿수

• tinyint, smallint, int 자료형을 허용한다.
• length에는 양수나 음수를 지원할 수 있다.
• 양수이면 소수부부터 반올림하고, 음수면 정수부부터 반올림한다.
• 음수를 전달할 경우, 정수부 길이보다 절댓값이 큰 값을 전달하면 0을 반환한다.
• 정수부의 길이와 절댓값이 같은 음수를 전달하면 산술 오버플로가 발생한다.

- [, function] : 수행할 연산의 종류

• tinyint, smallint, int형 값을 사용하며 생략 시 기본값 0을 적용한다.
• 기본값 0은 반올림 결괏값을 자르지 않으며 0 이외의 값을 입력하면 해당 값만큼 반오림 결괏값을 자른다.

5) 로그 구하기

LOG (float_expression [, base])

- float_expression :  LOG 함수가 계산한 표현식으로, float 형으로 변환될 수 있는 표현식을 사용해야 한다.
- [, base] : 밑 기본값은 e이다.

6) e의 n제곱값 구하기 : EXP 함수

EXP (float_expression)

7) 제곱하기

- POWER : 거듭제곱을 구한다.

• float형 표현식과 거듭제곱할 값을 인자로 받는다.
• y는 bit형을 제외한 정확한 수치 또는 근사 자료형의 값이다.
• 입력 자료형과 반환 자료형이 다르다.

- SQUARE : 제곱값을 구한다.

POWER (float_expression, y)
SQUARE (float_expression)

8) 제곱근 구하기

SQRT (float_expression)

9) 난수 구하기

RAND ([seed])

- 0~1 범위의 메타적 의사 난수 float형 값을 반환한다.

- [seed] 인수

• 자료형은 smallint, tinyint, int형이다.
• 지정하지 않으면 데이터베이스 엔진이 임의적으로 초깃값을 설정한다.
• 난수 종류를 결정하는 값이다.

10) 삼각함수

COS (float_expression)
SIN (float_expression)
ATAN (float_expression)

 

 

5.5 순위 함수

- 결과에 순위를 부여하는 함수이다.

1) 유일값으로 순위 부여하기

- 모든 행에 유일값으로 순위를 부여한다.

ROW_NUMBER ( )
    OVER ([PARTITION BY 열, ..., [n]] order by 열)

2) 같은 순위 개수 고려해 순위 부여하기 

- RANK 함수는 같은 순위일 때 같은 값을 부여한다는 점에서 ROW_NUMBER  함수와 다르다.

RANK ( ) OVER ([partition_by_clause] order_by_clause)

3) 같은 순위 개수 무시하고 순위 부여하기

- ex) 1위가 3개이면 그 다음은 2위가 된다.

DENSE_RANK( ) OVER ([partition_by_clause] order_by_clause)

4) 그룹화해 순위 부여하기

- 인자로 지정한 값만큼 데이터 행을 그룹화해 그룹별 순위를 부여한다.

- 각 그룹은 1부터 순위가 매겨지며 순위는 각 행의 순위가 아닌 행이 속한 그룹의 순위이다.

NTILE (integer_expression) OVER ([partition_by_clause] order_by_clause)

 

 

5.6 분석 함수

1) 앞 또는 뒤의 행 참조하기

- LAG, LEAD

LAG[LEAD] (scalar_expression [, offset], [default]) OVER ([partiition_by_caluse] order_by_caluse)

• LAG 함수는 현재 행에서 바로 앞의 행에 접근한다.
• LEAD 함수는 현재 행에서 바로 뒤의 행에 접근한다.
• offset는 이전/이후 몇 번째 행의 데이터를 참조할지 결졍할 값이다. 기본값은 1이다.

2) 누적 분포 계산하기

- 그룹에서 지정한 값의 상대 위치를 계산한다.

CUME_DIST( ) OVER ([partition_by_clause] order_by_clause)

3) 상대 순위 계산하기

RERCENT_RANK( ) OVER ([partition_by_clause] order_by_clause)

4) 중앙값 계산하기

PERCENT_CONT [PERCENT_DISC] (numeric_literal)
    WITHIN GROUP (ORDER BY order_by_expression [ASC | DESC])
    OVER ([<partition_by_clause>])

5) 정렬된 첫 번째/마지막 행의 값 구하기

- FIRST_VALUE([열])

- LAST_VALUE([열])