데이터 형식(2)

변수의 사용

일시적으로 사용되며, 실행 후에는 바로 소멸됨
사용 형식

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

예제

-- 1
DECLARE @myVar INT;

-- 2
SET @myVar = 3;

-- 3
SELECT TOP(@myVar) NAME, height FROM userTBL ORDER BY height;

INT 타입 변수 @myVar 선언
변수(@myVar)에 3 대입
키(height) 순서로 정렬된 userTBL 테이블에서 이름과 키를 상위 3명(SELECT TOP(3)) 조회

데이터형 변환 함수

데이터의 형식을 변환해 주는 함수

CAST(expression AS 데이터형식[(길이)])
CONVERT(데이터형식[(길이)], expression[ , 스타일])
TRY_CONVERT(데이터형식[(길이)], expression[ , 스타일])
PARSE(문자열 AS 데이터형식)
TRY_PARSE(문자열 AS 데이터형식)

예제

-- 정수를 실수로 변환

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

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

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

-- 문자열을 날짜로 변환
SELECT PARSE('2024년 10월 27일' AS DATE);

명시적 형변환 / 암시적 형변환

명시적 형변환

CAST() 또는 CONVERT()를 사용해서 형을 변환하는 것

암시적 형변환

CAST() 또는 CONVERT()를 사용하지 않고,
내부적으로 자동 형 변환이 되는 것

ex)
'문자 + 정수 = 정수'
'문자 + 실수 = 실수' 등의 변환

착오로 인해 엉뚱한 결과가 나올 수 있으므로 주의

스칼라 함수

스칼라함수의 일부 예

함수명	설명
`@@VERSION`	현재 설치된 SQL Server의 버전 정보를 알려줌
`GETDATE`	현재 날짜와 시간을 돌려줌 `SELECT GETDATE()`
`ABS`	수식의 절대값을 돌려줌 `SELECT ABS(-100)`
`DB_ID` `DB_NAME`	DB의 ID 또는 DB의 이름을 돌려줌 ex) `SELECT DB_ID(N'AdventureWorks');` `SELECT DB_NAME(6);` `DB_ID()`는 AdventureWorks DB의 id인 6을 돌려주고, `DB_NAME()`은 6번 DB의 이름인 AdventureWorks을 돌려준다
`LEFT`,`RIGHT`	왼쪽 / 오른쪽 / 지정 위치부터 지정한 수만큼 돌려줌 ex) `SELECT LEFT('SQL Server2016', 3);`, `RIGHT('SQL Server2016', 4);` 'SQL Server2016'에서 왼쪽 세 글자 'SQL'과 오른쪽 네 글자 '2016'을 돌려줌
`EOMONTH()`	입력한 날짜에 포함된 달의 마지막 날을 돌려줌 ex) `SELECT EOMONTH('2024-10-01');` 2024년의 마지막 날짜를 돌려줌

MAX 데이터 형식 활용

-- MAX형 데이터 정의
CREATE TABLE maxTBL ( col1 VARCHAR(MAX), col2 NVARCHAR(MAX) );

-- 큰 데이터(1백만 개 문자) 입력
INSERT INTO maxTBL VALUES (
  REPLICATE('A', 1000000),
  REPLICATE('가', 1000000)
);

-- 입력 결과 조회
SELECT LEN(col1), LEN(col2) FROM maxTBL;

실행 결과

col1	col2
8000	4000

REPLICATE 로 입력한 'AAAA~', '가가가가~' 의 데이터 형식은 MAX 형식이 아니기 때문에
'A' 는 CHAR 형식의 최대값인 8000개가 입력되었고,
'가' 는 VARCHAR 형식의 최대값인 8000 / 2byte 로 계산된 4000개가 입력되었다.

CAST() 함수를 사용

해서 위 코드를 고쳐보자

CREATE TABLE maxTBL ( col1 VARCHAR(MAX), col2 NVARCHAR(MAX) );

/* AS-IS
INSERT INTO maxTBL VALUES (
  REPLICATE('A', 1000000),
  REPLICATE('가', 1000000)
); */
INSERT INTO maxTBL VALUES (
  REPLICATE( CAST('A' AS VARCHAR(MAX) ), 1000000),
  REPLICATE( CAST('가' AS NVARCHAR(MAX) ), 1000000)
);

SELECT LEN(col1), LEN(col2) FROM maxTBL;

실행 결과

col1	col2
1000000	1000000

REPLACE() 함수를 사용

해서 데이터를 바꿔보자

CREATE TABLE maxTBL ( col1 VARCHAR(MAX), col2 NVARCHAR(MAX) );

INSERT INTO maxTBL VALUES (
  REPLICATE( CAST('A' AS VARCHAR(MAX) ), 1000000),
  REPLICATE( CAST('가' AS NVARCHAR(MAX) ), 1000000)
);

-- REPLACE() 함수를 활용해 기존 데이터를 수정
UPDATE maxTBL SET COL1 = REPLACE( (SELECT col1 FROM maxTBL), 'A', 'B' ),
                  COL2 = REPLACE( (SELECT col2 FROM maxTBL), '가', '나' );

-- REVERSE() 함수로 데이터를 뒤집어서 조회
SELECT REVERSE( (SELECT col1 FROM maxTBL) );

-- SUBSTRING() 함수로 일부만 조회 (999991 부터 10 글자만)
SELECT SUBSTRING( (SELECT col2 FROM maxTBL), 999991, 10 );

실행 결과

col1
BBBBBBBB...

col2
나나나나나나나나나나

요약

MAX형 데이터 정의

큰 데이터(1백만 개 문자) 입력 및 오류 원인 파악

CAST(), CONVERT() 이용한 형 변환

문자열 함수의 활용과 관련 함수의 성능 비교

순위함수

순번(또는 순위)를 처리하기 위한 함수 순번을 처리할 때 필요했던 복잡한 과정들을 단순화시켜서 쿼리의 작성 시간을 단축시킴

ROW_NUMBER()
DENSE_RANK()
RANK()
NTILE()

구문 형식

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

키 순서로 순위를 부여 : ROW_NUMBER()
그룹별로 키 순서로 순위 부여 : PARTITION BY 절 사용
동일한 점수를 같은 등수로 처리 : DENSE_RANK()
동일 점수 시에 등수를 건너 뜀 : RANK()
몇 개의 그룹으로 분할 : NTILE()

테이블 생성

CREATE TABLE Students (
  ClassNumber INT,
  Name VARCHAR(50),
  Address VARCHAR(50),
  Height INT
);

INSERT INTO Students (ClassNumber, Name, Address, Height) VALUES
(1, '오상욱', '서울', 180),
(1, '김지연', '서울', 165),
(1, '남현희', '서울', 175),
(1, '구본길', '전남', 160),
(1, '박상영', '서울', 170),
(2, '김정환', '경북', 155),
(2, '정진선', '경남', 185),
(2, '최병철', '경기', 150),
(2, '전하영', '경남', 190),
(2, '김영호', '경기', 145);

SELECT * FROM Students;

실행 결과

반번호	name	addr	height
1	오상욱	서울	180
1	김지연	서울	165
1	남현희	서울	175
1	구본길	전남	160
1	박상영	서울	170
2	김정환	경북	170
2	정진선	경남	185
2	최병철	경기	150
2	전하영	경남	190
2	김영호	경기	145

`ROW_NUMBER()`

-- 키 순서로 순위를 부여 : `ROW_NUMBER()`
SELECT ROW_NUMBER() OVER( ORDER BY height DESC ), name, address, height FROM Students;

실행 결과

순위	이름	주소	키
1	전하영	경남	190
2	정진선	경남	185
3	오상욱	서울	180
4	남현희	서울	175
5	박상영	서울	170
6	김정환	경북	170
7	김지연	서울	165
8	구본길	전남	160
9	최병철	경기	150
10	김영호	경기	145

-- 키 순서로 순위를 부여, 같은 키라면 이름 순서로: `ROW_NUMBER() + name ASC`
SELECT ROW_NUMBER() OVER( ORDER BY height DESC, name ASC), name, address, height FROM Students;

실행 결과

순위	이름	주소	키
1	전하영	경남	190
2	정진선	경남	185
3	오상욱	서울	180
4	남현희	서울	175
5	김정환	경북	170 👈
6	박상영	서울	170 👈
7	김지연	서울	165
8	구본길	전남	160
9	최병철	경기	150
10	김영호	경기	145

기존 데이터 조회에서 같은 키의 경우 조회된 순서대로 순위를 할당하지만, OVER() 함수에 name ASC 를 추가하여 같은 순위인 경우 이름 순서대로 순위가 할당 되었다

-- 키 순서로 순위를 부여, 지역별로 그룹화 : `ROW_NUMBER()`
SELECT address ROW_NUMBER() OVER( PARTITION BY addr ORDER BY height DESC, name ASC ),
       name, height FROM Students;

실행 결과

주소	순위	이름	키
경기	1	최병철	150
경기	2	김영호	145
경남	1	전하영	190
경남	2	정진선	185
경북	1	김정환	170
서울	1	오상욱	180
서울	2	남현희	175
서울	3	박상영	170
서울	4	김지연	165
전남	1	구본길	160

`DENSE_RANK()`

-- 같은 비교대상(키)이라면 같은 등수로 처리한다
SELECT DENSE_RANK() OVER( ORDER BY height DESC ), name, address, height FROM Students;

실행 결과

순위	이름	주소	키
1	전하영	경남	190
2	정진선	경남	185
3	오상욱	서울	180
4	남현희	서울	175
5 👈	김정환	경북	170
5 👈	박상영	서울	170
6	김지연	서울	165
7	구본길	전남	160
8	최병철	경기	150
9	김영호	경기	145

`RANK()`

-- 같은 비교대상(키)이라면 같은 등수로 처리 후 처리된 등수는 건너 뜀
SELECT RANK() OVER( ORDER BY height DESC ), name, address, height FROM Students;

실행 결과

순위	이름	주소	키
1	전하영	경남	190
2	정진선	경남	185
3	오상욱	서울	180
4	남현희	서울	175
5 👈	김정환	경북	170
5 👈	박상영	서울	170
7 👈	김지연	서울	165
8	구본길	전남	160
9	최병철	경기	150
10	김영호	경기	145

`NTILE()`

N 개 인수의 그룹으로 분할

-- 2 개의 그룹으로 분할
SELECT NTILE(2) OVER( ORDER BY height DESC ), name, address, height FROM Students;

실행 결과

group	이름	주소	키
1	전하영	경남	190
1	정진선	경남	185
1	오상욱	서울	180
1	남현희	서울	175
1	박상영	서울	170
2	김정환	경북	170
2	김지연	서울	165
2	구본길	전남	160
2	최병철	경기	150
2	김영호	경기	145

-- 4 개의 그룹으로 분할
SELECT NTILE(4) OVER( ORDER BY height DESC ), name, address, height FROM Students;

실행 결과

group	이름	주소	키
1	전하영	경남	190
1	정진선	경남	185
1	오상욱	서울	180
2	남현희	서울	175
2	박상영	서울	170
2	김정환	경북	170
3	김지연	서울	165
3	구본길	전남	160
4	최병철	경기	150
4	김영호	경기	145

10 / 4 가 되는 그룹으로 나누었고, 나머지 2명은 1 그룹부터 순서대로 포함된다

1 그룹 : 3명 (1, 2, 3)
2 그룹 : 3명 (4, 5, 6)
3 그룹 : 2명 (7, 8)
4 그룹 : 2명 (9 , 10)

분석함수

집계함수와 같이 행 그룹을 기반으로 계산되지만, 여러 개의 행을 반환 이동 평균, 백분율, 누계 등의 결과를 계산할 수 있음

LEAD()
FIRST_VALUE()
CUME_DIST()
PERCENTILE_COUNT()
LEG()
PERCENTILE_DISC()
LAST_VALUE()
PERCENT_RAND()

`LEAD()`

-- LEAD() 함수를 사용하여 <다음 데이터> 와 '키차이'를 분석 합니다
SELECT name, address, height,
    height - (LEAD(height, 1, 0) OVER(ORDER BY height DESC))
FROM Students;

실행 결과

이름	주소	키	키차이
전하영	경남	190	5
정진선	경남	185	5
오상욱	서울	180	5
남현희	서울	175	5
박상영	서울	170	0
김정환	경북	170	5
김지연	서울	165	5
구본길	전남	160	10
최병철	경기	150	5
김영호	경기	145	145

`FIRST_VALUE()`

-- FIRST_VALUE() 함수를 사용하여 <그룹의 첫번째 데이터> 와 '키차이'를 분석 합니다
SELECT address, name, height,
    (FIRST_VALUE(height) OVER(PARTITION BY address ORDER BY height DESC)) - height
FROM Students;

실행 결과

주소	이름	키	키차이
경기	최병철	150	0 👈 경기 첫번째
경기	김영호	145	5
경남	전하영	190	0 👈 경남 첫번째
경남	정진선	185	5
경북	김정환	170	0 👈 단독
서울	오상욱	180	0 👈 서울 첫번째
서울	남현희	175	5
서울	박상영	170	10
서울	김지연	165	15
전남	구본길	160	0 👈 단독

주소에 해당하는 비교값이 단독이거나 1순위라면, 키차이는 0 이고,
나머지 값들은 주소의 1순위와 비교합니다.

`CUME_DIST()`

-- CUME_DIST() 함수를 사용하여 <그룹의 1/n> 으로 누적 백분율을 계산 후 x 100
SELECT address, name, height,
    (CUME_DIST() OVER(PARTITION BY address ORDER BY height DESC)) * 100
FROM Students;

실행 결과

주소	이름	키	누적 백분율
경기	최병철	150	50
경기	김영호	145	100
경남	전하영	190	50
경남	정진선	185	100
경북	김정환	170	100
서울	오상욱	180	25
서울	남현희	175	50
서울	박상영	170	75
서울	김지연	165	100
전남	구본길	160	100

`PERCENTILE_COUNT()`

-- PERCENTILE_COUNT() 함수를 사용하여 <그룹의 1/n> 으로 누적 백분율을 계산 후 x 100
SELECT DISTINCT address,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY height) OVER (PARTITION BY address)
FROM Students;

실행 결과

주소	평균
경기	147.5
경남	187.5
경북	170
서울	172.5
전남	160

PIVOT / UNPIVOT 연산자

PIVOT 연산자

한 열에 포함된 여러 값을 출력하고,
이를 여러 열로 변환하여 테이블 반환 식을 회전하고 필요하면 집계까지 수행함

UNPIVOT 연산자

PIVOT의 반대되는 연산을 수행함,

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

예제 테이블

CREATE TABLE pivotTest
(userName NCHAR(3), season NCHAR(2), amount INT);

INSERT INTO pivotTest VALUES
    ('김민수', '겨울', 10),
    ('배유민', '여름', 15),
    ('김민수', '가을', 25),
    ('김민수', '봄', 3),
    ('김민수', '봄', 37),
    ('배유민', '겨울', 40),
    ('김민수', '여름', 14),
    ('김민수', '겨울', 22),
    ('배유민', '여름', 64);

SELECT * FROM pivotTest;

실행 결과

userName	season	amount
김민수	겨울	10
배유민	여름	15
김민수	가을	25
김민수	봄	3
김민수	봄	37
배유민	겨울	40
김민수	여름	14
김민수	겨울	22
배유민	여름	64

PIVOT 조회

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

실행 결과

userName	봄	여름	가을	겨울
김민수	40	14	25	32
배유민	NULL	79	NULL	40

변수의 사용​

예제​

데이터형 변환 함수​

명시적 형변환 / 암시적 형변환​

스칼라 함수​

MAX 데이터 형식 활용​

CAST() 함수를 사용​

REPLACE() 함수를 사용​

순위함수​

테이블 생성​

ROW_NUMBER()​

DENSE_RANK()​

RANK()​

NTILE()​

분석함수​

LEAD()​

FIRST_VALUE()​

CUME_DIST()​

PERCENTILE_COUNT()​

PIVOT / UNPIVOT 연산자​

예제 테이블​

PIVOT 조회​

변수의 사용

예제

데이터형 변환 함수

명시적 형변환 / 암시적 형변환

스칼라 함수

MAX 데이터 형식 활용

CAST() 함수를 사용

REPLACE() 함수를 사용

순위함수

테이블 생성

`ROW_NUMBER()`

`DENSE_RANK()`

`RANK()`

`NTILE()`

분석함수

`LEAD()`

`FIRST_VALUE()`

`CUME_DIST()`

`PERCENTILE_COUNT()`

PIVOT / UNPIVOT 연산자

예제 테이블

PIVOT 조회