EXCELSIOR

Chap04. 데이터 형식(타입), 변환, 순위, 분석 함수 본문

DataBase/SQL Server

Chap04. 데이터 형식(타입), 변환, 순위, 분석 함수

Excelsior-JH 2018. 11. 1. 15:54

Chap04. 데이터 형식, 시스템 함수

이전 포스팅에서는 SELECT, INSERT/UPDATE/DELETE에 대해 살펴보았다. 이번 포스팅에서는 SQL Server에서 제공하는 데이터 형식과 변수의 사용, 데이터 형식과 관련된 함수에 대해 살펴보도록 하자.


이번 포스팅에서 이루어지는 실습은 Chap03-1.Transact-SQL 기본에서 생성한 sqlDB를 사용하기 때문에, 먼저 해당 링크를 통해 sqlDB를 생성해야 한다.


1. SQL Server의 데이터 형식

SQL Server의 데이터 형식에는 그 종류가 30개 가까이 된다고 한다. 이러한 데이터 형식의 종류를 다외우는 것은 무리가 있으며, 거의 사용하지 않는 데이터 형식도 있기 때문에, 다 외울 필요는 없다. 자주 사용되는 데이터 형식은 별표(★)로 구분 되어 있다.


1.1 숫자 데이터 형식

숫자형 데이터 형식은 정수, 실수 등의 숫자를 표현한다.

데이터 형식바이트 수숫자 범위설명
★ BIT10 또는 1 또는 NULLBoolean 형인 참(True, 1), 거짓(False, 0)에 사용
★ TINYINT10 ~ 255양의 정수
★ SMALLINT2-32,768 ~ 32,767정수
★ INT4약 -21억 ~ + 21억정수
★ BIGINT8 ~ 정수
★ DECIMAL(p, [s])5 ~ 17 ~ +고정 정밀도(p)와 배율(s)을 가진 숫자형. 예) decimal(5, 2)는 전체 자릿수를 5자리로 하되, 그 중 소수점 이하를 2자리로 하겠다는 의미
NUMERIC5 ~ 17 ~ +DECIMAL과 같은 데이터 형식
★ FLOAT[(p)]4 ~ 8p가 25미만이면 4바이트, 25 이상이면 8 바이트의 크기를 할당
REAL4FLOAT(24)와 같음
MONEY8 ~ +화폐 단위로 사용
SMALLMONEY4약 -21억 ~ +21억화폐 단위로 사용


1.2 문자 데이터 형식

데이터 형식바이트 수설명
★ CHAR[(n)]0 ~ 8,000고정길이 문자형, Character의 약자
★ NCHAR([n])0 ~ 8,000글자로는 0~4,000자. 유니코드 고정길이 문자형. National Character의 약자
★ VARCHAR[(n | max)]0 ~ (2GB)가변길이 문자형. n을 사용하면 1 ~ 8,000까지 크기를 지정할 수 있고, max로 지정하면 최대 2GB, 크기를 지정할 수 있음. Variable Character의 약자
★ NVARCHAR[(n | max)]0 ~ 유니코드 가변길이 문자형. n을 사용하면 1~4,000까지 크기를 지정할 수 있고, max로 지정하면 최대 2GB, 크기를 지정할 수 있음. National Variable Character의 약자
BINARY[(n)]0 ~ 8,000고정길이의 이진 데이터 값
★ VARBINARY[(n | max)]0 ~ 가변길이의 이진 데이터 값. n을 사용하면 1 ~ 8,000까지 크기를 지정할 수 있고, max로 지정하면 최대 2GB, 크기를 지정할 수 있음. 이미지/동영상 등을 저장하기 위해 사용됨. Variable Binary의 약자

CHAR형식은 고정길이 문자형이므로 자릿수가 고정되어 있다. 예를들어, CHAR(100)'ABC' 3바이트만 저장해도, 전체 100바이트에서 3바이트만 사용하고, 나머지 97바이트는 사용하지 않기때문에 공간이 낭비가 되게 된다. 이에 반해, VARCHAR형식은 가변길이 문자형이기 때문에 VARCHAR(100)'ABC' 3바이트를 저장할 경우 3바이트만 사용하게 된다.

한글(유니코드)을 저장하기 위해서 CHAR/VARCHAR 형식을 사용하게 되면 2배의 자리수를 설정해야 한다. 예를들어, 최대 3글자의 한글을 저장하기 위해서는 CHAR(6)/VARCHAR(6)으로 설정해야하는데, 그 이유는 한글이 2바이트를 차지하기 때문이다. 따라서, 영어가 아닌 한글과 같은 다른 언어에 대해서는 NCHAR(3)/NVARCHAR(3) 형식을 사용하면 된다. 만약, 해당 컬럼이 한글을 사용하지 않고 영어만 사용할 경우에는 NCHAR/NVARCHAR보다 CHAR/VARCHAR가 더 공간을 적게 차지하기 때문에 CHAR/VARCHAR를 사용하는 것이 좋다.


1.3 날짜와 시간 데이터 형식

데이터 형식바이트 수설명
DATETIME8날짜는 1753-1-1~9999-12-31까지 저장되며, 시간은 00:00:00~23:59:59.997까지 저장됨. 정확도는 밀리초(1/1000초) 단위까지 인식. "YYYY-MM-DD 시:분:초"로 사용.
★ DATETIME26 ~ 8날짜는 0001-1-1~9999-12-31까지 저장되며, 시간은 00:00:00~23:59:59.9999999까지 저장됨. 정확도는 100나노초 단위까지 인식. 형식은 "YYYY-MM-DD 시:분:초"로 사용.
★ DATE3날짜는 0001-1-1~9999-12-31까지 저장. 날짜 형식만 사용됨. "YYYY-MM-DD"형식으로 사용됨
★ TIME500:00:00.00000000~23:59:59.9999999까지 저장. 정확도는 100나노 초 단위까지 인식됨. "시:분:초"형식으로 사용


위의 데이터 날짜 데이터 형식을 예를 통해서 알아보자.

-- DATETIME
SELECT CAST('2018-10-22 12:35:29.123' AS datetime) AS 'datetime'
-- DATETIME2
SELECT CAST('2018-10-22 12:35:29.1234567 +12:15' AS datetime2) AS 'datetime2'
-- DATE
SELECT CAST('2018-10-22 12:35:29.1234567 +12:15' AS date) AS 'date'
-- TIME
SELECT CAST('2018-10-22 12:35:29.1234567 +12:15' AS time) AS 'time'



1.4 VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX)

SQL Server는 LOB(Large Object: 대량의 데이터)를 저장하기 위해 VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX) 데이터 형식을 지원한다. 약 2GB 크기의 파일을 하나의 데이터로 저장할 수 있으며, 예를들어 소설과 같은 큰 텍스트 파일일 경우 NVARCHAR(MAX)형식으로, 동영상과 같은 큰 바이너리 파일이라면 VARBINARY(MAX)형식으로 하나의 컬럼 하나의 로우에 데이터를 넣을 수 있다.



2. 변수의 사용

T-SQL 또한 일반적인 프로그래밍 언어처럼 변수(variable)를 선언하고 사용할 수 있는데, 변수의 선언과 값의 대입은 다음과 같은 형식을 따른다.

변수의 선언: DECLARE @변수이름 데이터형식;
변수에 값 대입: SET @변수이름 = 변수의 값;
변수의 값 출력: SELECT @변수이름;


아래의 예제는 sqlDB에서 변수를 지정하고, 값을 대입한 후에 출력하는 예제이다. 아래의 쿼리에서도 볼 수 있듯이 변수는 일반적인 SELECT ... FROM문과도 같이 사용할 수 있다.

USE sqlDB;

-- 변수 선언
DECLARE @myVar1 INT;
DECLARE @myVar2 SMALLINT, @myVar3 DECIMAL(5,2);
DECLARE @myVar4 NCHAR(20);

-- 변수 초기화
SET @myVar1 = 5;
SET @myVar2 = 3;
SET @myVar3 = 4.25;
SET @myVar4 = '가수 이름==> ';

SELECT @myVar1;
SELECT @myVar2 + @myVar3;

SELECT @myVar4, Name FROM userTbl WHERE height > 180;



3. 데이터 형식과 관련된 시스템 함수들

3.1 데이터 형식 변환 함수

가장 일반적으로 사용되는 데이터 형식 변환은 CAST()CONVERT()함수가 있으며, 이 둘은 형식은 다르지만 비슷한 역할을 한다. SQL Server2012 부터 TRY_CONVERT(), PARSE(), TRY_PARSE() 3개의 함수가 추가되었다.

  • TRY_CONVERT() : CONVERT()와 같지만 변환에 실패할 경우 Null값을 반환한다.

  • PARSE()/TRY_PARSE() : 문자열에서 날짜/시간 및 숫자 형식으로 변환하는 경우에 사용한다. TRY_PARSE()또한 변환에 실패할 경우 Null값을 반환한다.


CAST, COVERT, TRY_CONVERT, PARSE, TRY_PARSE의 사용법음 다음과 같다.

-- CAST Syntax:  
CAST ( expression AS data_type [ ( length ) ] )  

-- CONVERT Syntax:  
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

-- TRY_CONVERT Syntax:
TRY_CONVERT ( data_type [ (length) ], expression [, style ] )

-- PARSE Syntax:
PARSE ( string_value AS data_type [ USING culture ] )  

-- TRY_PARSE Syntax:
TRY_PARSE ( string_value AS data_type [ USING culture ] )


아래의 예제는 sqlDBbuyTbl테이블을 이용해 데이터 형식 변환 함수를 적용한 예제이다.

-- CAST
SELECT AVG( CAST(amount AS FLOAT) ) AS [평균구매개수] FROM buyTbl;

-- CONVERT
SELECT AVG( CONVERT(FLOAT, amount) ) AS [평균구매개수] FROM buyTbl;

-- TRY_CONVERT
SELECT AVG( TRY_CONVERT(FLOAT, amount) ) AS [평균구매개수] FROM buyTbl;



아래의 예제는 PARSE, TRY_PARSE를 이용해 문자열을 DATEINT로 변환하는 예제이다.

-- String → Date
SELECT PARSE ('2018년 11월 1일' AS DATE) AS [str → Date];

-- String → INT
SELECT PARSE ('1234' AS INT) AS [str → int];



3.1.1 암시적인 형 변환

형 변환 방식에는 다음과 같이 두 가지 변환이 있다.

  • 명시적인 변환(Explicit conversion) : CAST(), CONVERT()등과 같은 변환 함수를 이용해 데이터 형식을 변환하는 것을 말한다.

  • 암시적인 변환(Implicit conversion) : 따로 변환 함수를 사용하지 않고도 형이 변환되는 것을 말한다.


아래의 예제는 @myVar1='100'이라는 문자열 변수에 문자, 정수, 실수를 더했을 때의 암시적인 변환에 대한 예제이다.

-- 변수 선언 및 초기화
DECLARE @myVar1 char(3);
SET @myVar1 = '100';

-- String + String
SELECT @myVar1 + '200' AS [String];
-- String + INT
SELECT @myVar1 + 200 AS [INT];
-- String + FLOAT
SELECT @myVar1 + 200.0 AS [FLOAT];



위의 쿼리문에서 두 번째와 세 번째 쿼리는 '문자+숫자'이므로 두 값을 더할 수 없지만, 암시적인 형 변환으로 인해 '문자+정수=정수', '문자+실수=실수'의 결과가 나오게 된다. 위의 코드를 명시적인 변환으로 나타낼 경우 아래의 쿼리문과 같다.

-- 변수 선언 및 초기화
DECLARE @myVar1 char(3);
SET @myVar1 = '100';

-- String + String
SELECT @myVar1 + '200' AS [String];
-- String + INT
SELECT CAST(@myVar1 AS INT) + 200 AS [INT];
-- String + FLOAT
SELECT CAST(@myVar1 AS DECIMAL(5,1)) + 200.0 AS [FLOAT];



위에서 살펴 보았듯이 암시적인 형 변환이 타이핑 해야하는 쿼리량이 줄어들었지만, 명시적인 변환을 사용하는것이 가독성이나 혹시 모를 에러를 방지하는 데 더 낫다. 따라서, 암시적인 형 변환 보다는 명시적으로 형 변환 함수를 사용하는 것이 좋다. 아래의 표는 SQL Server 2014에서 암시적인 변확과 명시적인 변환에 관련된 표이다. 추후에 필요할 경우 참고하면 된다.




3.1.2 데이터 형 변환시 주의점

데이터 형 변환시 주의해야할 점은 숫자에서 문자로 변환할 때 문자의 자릿수를 고려해야 한다는 것이다. 예를 들어 실수 10.12345를 문자로 변환할 때 자릿수를 고려하지 않으면 다음과 같은 에러가 난다.

DECLARE @myVar2 DECIMAL(10,5);
SET @myVar2 = 10.12345;
SELECT CAST(@myVar2 AS NCHAR(5));
메시지 8115, 수준 16, 상태 2, 줄 3
expression을(를) 데이터 형식 nvarchar(으)로 변환하는 중 산술 오버플로 오류가 발생했습니다.

10.12345을 문자로 변환하게 되면 8글자로 변환되기 때문에 문자열의 길이가 8자리 이상 필요하다.

DECLARE @myVar2 DECIMAL(10,5);
SET @myVar2 = 10.12345;
SELECT CAST(@myVar2 AS NCHAR(8)) AS [Float → String];



3.2 스칼라 함수

스칼라(Scalar) 함수란 단일 값에 적용되어 단일 값의 결과를 돌려주는 함수들을 말한다. SQL Server에서 제공하는 스칼라 함수는 아래의 표와 같으며, 추후에 필요할 경우 표의 링크를 참고하면 된다.

함수 범주설명
구성 함수현재 구성에 대한 정보를 반환
변환 함수데이터 형식 캐스팅 및 변환을 지원
커서 함수커서에 대한 정보를 반환
날짜 및 시간 데이터 형식 및 함수날짜 및 시간 입력 값에 대한 작업을 수행하며 문자열, 숫자 또는 날짜와 시간 값을 반환
JSON 함수JSON 데이터를 검증, 쿼리 또는 변경
논리 함수논리 연산을 수행
수치 연산 함수함수에 매개 변수로 제공되는 입력 값을 기반으로 하여 계산 작업을 수행하고 숫자 값을 반환
메타데이터 함수데이터베이스와 데이터베이스 개체에 대한 정보를 반환
보안 함수사용자와 역할에 대한 정보를 반환
문자열 함수문자열(char 또는 varchar) 입력 값에 대한 작업을 수행하고 문자열이나 숫자 값을 반환
시스템 함수작업을 수행하고 SQL Server 인스턴스의 값, 개체 및 설정에 대한 정보를 반환
시스템 통계 함수시스템에 대한 통계 정보를 반환
텍스트 및 이미지 함수텍스트 또는 이미지 입력 값이나 열에 대한 작업을 수행하고 그 값에 대한 정보를 반환


3.3 순위 함수

순위 함수(Ranking Function)는 SQL Server 2005부터 RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() 4가지를 제공하며, 순위(등수)를 매기는 역할을 하는 함수이다. 순위 함수의 형식은 다음과 같다.

<<순위함수이름>>() OVER(
[PARTITION BY <partition_by_list>]
ORDER BY <order_by_list>)


3.3.1 ROW_NUMBER

아래의 예제는 sqlDBuserTbl테이블에서 키가 큰 순으로 ROW_NUMBER()함수를 이용해 순위를 매기는 예제이다.

USE sqlDB;
SELECT ROW_NUMBER() OVER(
ORDER BY height DESC) [키큰순위],
name, addr, height
FROM userTbl;


위의 결과에서 볼 수 있듯이, '임재범'과 '이승기'의 키가 같을 경우에는 따로 출력 순서를 지정해주지 않았다. 아래의 예제는 키가 같을 경우 이름순(가나다 순)으로 정렬하는 쿼리이다.

USE sqlDB;
SELECT ROW_NUMBER() OVER(
ORDER BY height DESC, name ASC) [키큰순위],
name, addr, height
FROM userTbl;


이번에는 userTbl테이블에서 지역별로 키가 큰 순위를 보여주는 예제이다. 이 경우에는 PARTITION BY절을 사용한다.

SELECT addr, ROW_NUMBER() OVER(
PARTITION BY addr ORDER BY height DESC, name ASC) [지역별키큰순위],
name, height
FROM userTbl;



3.3.2 DENSE_RANK

3.3.1의 ROW_NUMBER에서는 '이승기'와 '임재범'의 키가 같음에도 불구하고, 순위가 2등과 3등으로 나뉘었다. 이렇게 같은 값을 가질 경우 같은 순위(등수)로 처리하는 함수가 있는데, 바로 DENSE_RANK()함수이다. 아래의 예제는 3.3.1의 예제를 DENSE_RANK()로 변경한 예제이다.

SELECT DENSE_RANK() OVER(ORDER BY height DESC) [키큰순위],
name, addr, height
FROM userTbl;



3.3.3 RANK

3.3.2의 DENSE_RANK()의 결과에서 '임재범' 과 '이승기'는 키가 같으므로 2등으로 출력되었고, '김경호'는 2등 뒤이기 때문에 3등으로 나왔다. 하지만, 이러한 경우에 2등이 두명일 경우, 2등, 2등, 4등 과 같이 3등을 빼고 4등부터 순위를 매겨야할 때도 있다. 이럴 때 RANK()함수를 사용하면 된다.

SELECT RANK() OVER(ORDER BY height DESC) [키큰순위],
name, addr, height
FROM userTbl;



3.3.4 NTILE

NTILE() 함수는 정렬된 행을 지정된 수의 그룹으로 나누는 역할을 하며, 그룹에는 1부터 시작하는 번호가 매겨진다. 아래의 예제는 키 큰 순으로 정렬한 뒤에 2개의 그룹으로 나누는 예제이다.

SELECT NTILE(2) OVER(ORDER BY height DESC) [그룹번호],
name, addr, height
FROM userTbl;



3.4 분석 함수

분석 함수(Analytic Functions)는 SQL Server2012 부터 제공된 함수이며, 집계 함수와 같이 행 그룹을 기반으로 계산되지만, 여러 개의 행을 반환할 수 있다. 분석 함수의 종류로는 아래의 표와 같다.

SQL Server는 다음과 같은 분석 함수를 지원합니다.

분석함수 종류설명
CUME_DIST(Transact-SQL)각 그룹 내에서 값의 누적 분포를 계산
FIRST_VALUE(Transact-SQL)정렬된 집합의 첫 번째 값을 반환
LAG(Transact-SQL)현재 행의 값을 이전 행의 값과 비교
LAST_VALUE(Transact-SQL)정렬된 집합의 마지막 값을 반환
LEAD(Transact-SQL)현재 행의 값을 다음 행의 값과 비교
PERCENTILE_CONT(Transact-SQL)컬럼 값의 연속 분포를 기반으로 백분위수를 계산
PERCENTILE_DISC(Transact-SQL)컬럼 값의 불연속 분포를 기반으로 백분위수를 계산
PERCENT_RANK(Transact-SQL)CUME_DIST함수와 유사하며, 집합 내에서 값의 상대 순위를 평가


3.4.1 LEAD & LAG

LEAD()함수는 현재 행과 다음 행의 값을 비교하는 역할을 하는 함수다.

LEAD ( scalar_expression [ ,offset ] , [ default ] )   
  OVER ( [ partition_by_clause ] order_by_clause )
  • offset : 현재 행 뒤에 있는 행의 수로, 그 수만큼 뒤에 있는 행에서 값을 가져온다. 디폴트는 1이다.

  • default : offsetscalar_expressionNULL일 경우 반환할 값이다. 즉, 해당 값이 NULL이거나 마지막 행일 경우 비교할 값이 없기 때문에 디폴트로 비교할 값을 넣어준다.

아래의 예제는 userTbl 테이블에서 키가 큰 순으로 정렬한 후에 LEAD()함수를 사용해 다음 사람과 키 차이를 비교하는 쿼리이다.

USE sqlDB;
SELECT name, addr, height AS [키],
height - (LEAD(height, 1, 0) OVER (ORDER BY height DESC))
AS [다음 사람과 키 차이]
FROM userTbl;


LAG() 함수는 LEAD()와 달리, 현재 행과 이전 행의 값을 비교하는 함수이다. 형식은 다음과 같다.

LAG (scalar_expression [,offset] [,default])  
  OVER ( [ partition_by_clause ] order_by_clause )

아래의 예제는 위의 LEAD()함수를 LAG()함수로 바꾼 예제이다.

USE sqlDB;
SELECT name, addr, height AS [키],
height - (LAG(height, 1, 0) OVER (ORDER BY height DESC))
AS [다음 사람과 키 차이]
FROM userTbl;



3.4.2 FIRST_VALUE

FIRST_VALUE() 함수는 정렬된 집합의 첫 번째 값을 반환하는 함수이며, 형식은 다음과 같다.

FIRST_VALUE ( [scalar_expression ] )   
  OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

아래의 예제는 지역별로 가장 키가 큰 사람과의 차이를 FIRST_VALUE()함수를 이용한 예제이다.

SELECT addr, name, height AS [키],
height - (FIRST_VALUE(height) OVER (
PARTITION BY addr ORDER BY height DESC)) AS [지역별 가장 큰 키차이]
FROM userTbl;



3.4.3 CUME_DIST

CUME_DIST()함수는 각 그룹 내에서 값의 누적 분포를 계산하는 함수이다.

CUME_DIST( )  
  OVER ( [ partition_by_clause ] order_by_clause )  

아래의 예제는 키가 큰 순으로 정렬한 뒤 CUME_DIST()함수를 이용해 누적 백분율을 구하는 예제이다.

SELECT name, height AS [키],
(CUME_DIST() OVER (ORDER BY height DESC)) * 100 AS [누적인원 백분율%]
FROM userTbl;



3.4.4 PERCENTILE_CONT

PERCENTILE_CONT()함수는 0.0 ~ 1.0 사이의 값을 인자로 받으며, 이에 해당하는 백분율 값을 반환한다.

PERCENTILE_CONT ( numeric_literal )   
  WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )  
  OVER ( [ <partition_by_clause> ] )
  • WITHIN GROUP에는 정렬할 컬럼을 지정해야 한다.

아래의 예제는 userTbl테이블의 지역별 키에 대해 중앙값(median, 0.5)을 구하는 예제이다.

SELECT DISTINCT addr,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY height) OVER
(PARTITION BY addr) AS [지역별 키의 중앙값]
FROM userTbl;



3.5 PIVOT/UNPIVOT 연산자

PIVOTUNPIVOT 은 테이블 반환 식을 다른 테이블로 변경할 수 있다. PIVOT은 식의 한 컬럼에 포함된 여러 고유 값을 출력에서 여러 컬럼으로 변환하여 테이블 반환 식을 회전하고 최종 출력에서 남은 컬럼 값 중 원하는 값에 대해 필요에 따라 집계를 수행한다. UNPIVOT은 PIVOT과 반대되는 연산을 수행한다. PIVOT의 형식은 다음과 같다.

PIVOT ( 집계함수(컬럼) 
    FOR 새로운 컬럼으로 변경할 컬럼이름
    IN (컬럼 목록) ) AS 피벗이름;


아래의 예제는 pivotTest라는 테이블을 임의로 만든 다음 PIVOT연산자를 수행한 예제이다. 먼저 pivotTest 테이블을 만든 후에 데이터를 삽입한다.

USE sqlDB;
-- 테이블 생성
CREATE TABLE pivotTest
( uName NCHAR(3),
season NCHAR(2),
amount INT);

-- 데이터 INERT
INSERT INTO pivotTest VALUES
('김범수', '겨울', 10), ('윤종신', '여름', 15),
('김범수', '가을', 25), ('김범수', '봄', 3),
('김범수', '봄', 37), ('윤종신', '겨울', 40),
('김범수', '여름', 14), ('김범수', '겨울', 22),
('윤종신', '여름', 64);

-- 테이블 조회
SELECT * FROM pivotTest;


그런 다음, season별로 amount의 합계(SUM)을 구하는 PIVOT연산을 수행한다.

SELECT * FROM pivotTest
PIVOT( SUM(amount)
  FOR season
  IN ([봄], [여름], [가을], [겨울])) AS resultPivot



4. 마무리

이번 포스팅에서는 SQL Server2014의 데이터 형식(타입)과 시스템 함수인 변환함수, 스칼라함수, 분석함수, PIVOT 연산자에 대해 알아보았다.

Comments