1. 데이터 형식 변환 함수
가장 일반적으로 사용되는 데이터 형식 변환은 CAST()와 CONVERT(), TRY_CONVERT() 함수
- CAST (형변환대상 as 데이터형식)
- CONVERT (데이터형식, 형변환대상)
- TRY_CONVERT (데이터형식, 형변환대상)
-- 평균 구매 개수
SELECT AVG(amount) '평균구매개수' FROM buyTbl;
SELECT AVG( CAST(amount AS FLOAT)) '평균구매개수' FROM buyTbl;
SELECT AVG( CONVERT(FLOAT, amount)) FROM buyTbl;
SELECT AVG(TRY_CONVERT(FLOAT, amount)) '평균구매개수' FROM buyTbl;
-- 단가 / 수량
SELECT price, amount, price/amount '단가/수량' FROM buyTbl;
SELECT price, amount, CAST( cast(price AS FLOAT) / amount AS DECIMAL(10,2)) '단가/수량'
FROM buyTbl;
-- 명시적 형 변환
-- 문자열에서 날짜/시간 또는 숫자 형식으로 변환해야 하는 경우 PARSE() / TRY_PARSE()
-- PARSE (문자열 AS 데이터형식)
-- TRY_PARSE (문자열 AS 데이터형식)
SELECT PARSE(N'2020년 07월 30일' AS DATE) '날짜';
SELECT PARSE('123.45' AS FLOAT);
SELECT PARSE('123.45' AS INT);
오류 내용 : culture ''을(를) 사용하여 문자열 값 '123.45'을(를) int 데이터 형식으로 변환하는 중 오류가 발생했습니다.
SELECT TRY_PARSE('123.45' AS INT);
-- 암시적 형 변환 : 암시적 형 변환의 규칙은 복잡하므로, 명시적 형 변환을 권장함
DECLARE @myVar1 CHAR(3)
SET @myVar1 = '100'
SELECT @myVar1 + '200' --문자 + 문자
SELECT @myVar1 + 200 --문자 + 정수
SELECT @myVar1 + 200.0 --문자 + 실수
-- 명시적 형변환
declare @myVar2 char(3)
set @myVar2 = '100'
select @myVar2 + '200'
select cast(@myVar2 as int) + 200
select cast(@myVar2 as decimal(5,1)) + 200.0
-- 형 변환 시 주의할 사항, 숫자에서 문자로 변환할 때 등 자리 수
↓잘못된 크기 지정으로 인한 오버플로우 발생
DECLARE @myvar2 DEMICAL(10,5);
SET @myvar2 = 10.2345;
SELECT CAST(@myvar2 AS NCHAR(5));
↑10.2345의 크기가 NCHAR(5)로는 부족하므로 오버플로우 에러가 난다.
↓NCHAR(8)로 바꾸어 주었다.
↓잘못된 형식 지정으로 인해 실수를 정수로 변환 시 자릿수가 잘릴 수 있다는 점 고려
DECLARE @myVar3 DECIMAL(10,5) ;
SET @myVar3 = 10.12345 ;
SELECT CAST(@myVar3 AS INT) ;
DECLARE @myVar4 DECIMAL(10,5) ;
SET @myVar4 = 10.12345 ;
SELECT CAST(@myVar4 AS DECIMAL(10,2)) ;
스칼라 함수 : 단일 값에 적용되어 단일 값의 결과를 돌려주는 함수들
select @@VERSION; -- 현재 설치된 sql server의 버전 정보
select GETDATE(); -- 현재의 날짜와 시간
select ABS(-100); -- 절대값
SELECT DB_ID(N'AdventureWorks'); --DB의 ID
SELECT DB_NAME(5); --DB의 이름
SELECT LEFT('SQL Server 2008', 3) AS "왼쪽에서 3글자",
RIGHT('SQL Server 2008', 4) AS "오른쪽에서 4글자";
SELECT REPLICATE ('SQL', 5); -- 문자열을 지정 수만큼 반복
SELECT UNICODE('가'), NCHAR(44032); -- 유니코드 변환
|
순위 함수 : 결과에 순번 또는 순위(등수)를 매기는 역할을 하는 함수 -- RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() 등 -- 순위 함수의 가장 큰 장점은 구문이 단순해진다 -> 단순한 구문은 코드를 명확하게 만듦, 쉽게 수정 가능, 오류가 적어지며 안전한(보다 보안이 좋은) 코딩 -- 효율성(성능)도 더 향상됨
|
-- 형식 : 순위함수이름() OVER (ORDER BY 대상)
-- 키가 큰 순으로 순위를 정하고 싶은 경우 : ROW_NUMBER()
SELECT NAME, HEIGHT FROM userTbl ORDER BY height DESC;
SELECT ROW_NUMBER() OVER (ORDER BY HEIGHT DESC) '키큰 순위', NAME, ADDR, HEIGHT FROM userTbl;
-- 키가 같은 경우 이름 순으로 정렬
SELECT ROW_NUMBER() OVER (ORDER BY HEIGHT DESC, NAME ASC) '키큰순위', NAME, ADDR, HEIGHT FROM userTbl;
-- 각 지역별로 키큰 순위 -PARTITION BY
-- 순위함수이름() OVER [분할(PARTITION) 대상] ORDER BY [대상]
SELECT ADDR, ROW_NUMBER() OVER(PARTITION BY ADDR ORDER BY HEIGHT) '키큰순위', NAME, HEIGHT FROM userTbl;
-- 같은 값일 때 동일한 등수로 표시 : DENSE_RANK()
SELECT DENSE_RANK() OVER (ORDER BY HEIGHT DESC) '키큰순위', NAME, ADDR, HEIGHT FROM userTbl;
-- 동일 등수를 빼고 순위를 매길 때 (동점자 다음 등수로 표시) : RANK()
SELECT RANK() OVER (ORDER BY HEIGHT DESC) '키큰순위', NAME, ADDR, HEIGHT
FROM userTbl ORDER BY HEIGHT DESC;
-- 몇 개의 그룹으로 분할하고 싶은 경우 : NTILE (나눌 그룸 개수)
-- 전체 인원을 키순으로 세운 후에, 2개의 그룹으로 분할
SELECT NTILE(2) OVER(ORDER BY HEIGHT DESC) '반번호', NAME, ADDR, HEIGHT
FROM userTbl ORDER BY HEIGHT DESC;
|
PIVOT / UNPIVOT 연산자 - PIVOT은 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환하여 테이블 반환 식을 회전, 필요시 집계도 수행 - UNPIVOT은 PIVOT의 반대되는 연산 수행
PIVOT : 한눈에 테이블 내용을 파악할 수 있음 UNPIVOT : PIVOT테이블은 값이 칼럼으로 변한 것과는 반대로 칼럼이 값으로 변함, 즉 PIVOT되기 이전의 상태로
|
↓PIVOT
PIVOT ( 집계함수(열) FOR 새로운 열로 변경할 열이름 IN (열 목록) AS 피벗이름)
USE tempdb;
CREATE TABLE pivotTest ( uName NCHAR(3), season NCHAR(2), amount INT );
INSERT INTO pivotTest VALUES (N'김범수' , N'겨울', 10) , (N'윤종신' , N'여름', 15) ,
(N'김범수' , N'가을', 25) , (N'김범수' , N'봄', 3) , (N'김범수' , N'봄', 37) ,
(N'윤종신' , N'겨울', 40) , (N'김범수' , N'여름', 14) ,
(N'김범수' , N'겨울', 22) , (N'윤종신' , N'여름', 64) ;
SELECT * FROM pivotTest;
SELECT * FROM pivotTest
PIVOT ( SUM(amount)
FOR season
IN ([봄],[여름],[가을],[겨울])) AS resultPivot ;
SELECT * FROM pivotTest;
SELECT * FROM pivotTest PIVOT ( SUM(amount) FOR season IN ([봄],[여름],[가을],[겨울])) AS resultPivot ;
↓UNPIVOT
CREATE TABLE 주문정보 ( 회원명 nchar(20), 자전거 int, 카메라 int, 노트북 int );
-- 데이터 입력
INSERT INTO 주문정보 VALUES(N'이장래', 2, 3, 1)
INSERT INTO 주문정보 VALUES(N'한국인', 1, 2, 3)
INSERT INTO 주문정보 VALUES(N'오감자', 3, 2, 2)
select * from 주문정보;
-- UNPIVOT 연산자 사용
SELECT 회원명, 상품, 수량 FROM dbo.주문정보
UNPIVOT (수량 FOR 상품 IN ([자전거],[카메라],[노트북])) AS UnPVT ;
