1. 데이터 형식을 변환해주는 함수
(1)명시적 형변환
- CAST( expression AS 데이터형식 [(길이)])
- CONVERT( 데이터형식 [(길이)], expression[ , 스타일])
- TRY_CONVERT(데이터형식 [(길이)], expression[ , 스타일])
: 오류 발생 시 체크가 됨
문자 날자를 DATE형으로 변경
- PARSE(문자열 AS 데이터형식)
- TRY_PARSE(문자열 AS 데이터형식)
-> 오류처리가 됨
(2)암시적 형변환
CAST()나 CONVERT()를 사용하지 않아도 자동으로 형변환 되는것
내가 원하는 결과가 안나올 수 있으니, 왠만하면 명시적형변환을 사용해라!!!
EX)
'문자+정수=정수'
'문자+실수=실수'
2. 스칼라함수
단일값에 적용되어 단일값의 결과를 돌려주는 함수
-
구성함수, 커서함수,날짜/시간함수, 수치함수 문자열함수 등
-
@@VERSION : 현재 설치된 SQL Server 버전정보 알려줌
-
GETDATE: 현재 날자,시간을 돌려줌
EX) SELECT GETDATE();
-
ABS: 수식의 절대값을 돌려줌
EX)SELECT ABS(-100)->100
-
DB_ID AND DB_NAME : DB 의 ID 또는 DB의 이름을 돌려줌
EX)
SELECT DB_ID(N'AdventureWorks');
SELECT DB_NAME(6);
-LEFT AND RIGHT: 왼쪽/ 오른쪽 지정위치부터 지정한 수마늠을 돌려줌
EX)
SELECT LEFT('SQL Server2016,3); --> SQL
SELECT RIGHT('SQL Server2016',4); -->2016 -
EOMONTH(): 입력한 날자에 포함된 마지막 날을 돌려줌
EX)
SELECT EOMONTH('2019-3-3'); --> 2019-03-31
3. VARCHAR(MAX) / NVARCHAR(MAX)
VARCHAR최대 데이터 8000바이트 (한글자에 1바이트)
NVARCHR최대 데이터 4000바이트(한글자에 2바이트)
EX)
CREATE TABLE maxTBL(col1 VARCHAR(MAX), col2 NVARCHAR(MAX));
-> 테이블을 생성할때 데이터 타입을 VARCHAR(MAX)나 NVARCHAR(MAX)로 지정했다고 해서 그냥 INSERT문을 사용하면 적용이 안됨
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));
4.UPDATE문
쿼리 내용 변경할 때 사용
문법:
UPDATE [변경할 쿼리가 포함된 테이블이름] SET [변경할 열이름] = [업데이트 할 값] WHERE [조건: 어떠한 곳의 데이터를 업데이트 할건지의 조건]
5.순위함수
: 순번 또는 순위를 처리하기 위한 함수
- RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER()가 있음
- 순번을 처리할 떄 필요했던 복잡한 과정들을 단순화 시켜 쿼리 작성시간 단출
문법::
<순위함수이름>() OVER(
[PARTITION BY <partition_by_list>]
ORDER BY<order_by_list>);
사용 예시 ->
-
키 순서로 순위 부여하는 것 :: ROW_NUMBER()
ex)
SELECT ROW_NUMBER() OVER (ORDER BY height DESC, name ASC),name, addr, height FROM userTBL;
->같은 순서가 있어도 조건에 따라 순서가 나뉨 -
그룹별로 키 순서로 순위 부여 :: PARTITION BY 절 사용
ex)
SELECT addr, ROW_NUMBER() OVER (PARTITION BY addr ORDER BY height DESC),name, height FROM userTBL;
->그룹을 나누고 그 안에서 등수를 먹이기 위해 사용 -
동일한 점수를 같은 등수로 처리 :: DENSE_RANK()
ex)
SELECT DENSE_RANK() OVER( ORDER BY height DESC), name, addr, height FROM userTBL;
-->공동순위도 가능 -
동일 점수 시에 등수를 건너뜀 : RANK()
ex)
SELECT RANK() OVER(ORDER BY height DESC), name, addr, height FROM userTBL;
->공동순위가 있으면 그 다음 등수를 한번 건너뜀(공동 2위면 그다음 사람은 3등이 아니라 4위) -
몇개의 그룹으로 분할 : NTILE()
ex)
SELECT NTILE(2) OVER(ORDER BY height DESC), name, addr, height FROM userTBL;
--> n개의 그룹으로 나누고 싶을때 사용(NTILE(n)
6. 분석함수
- 집계함수와 같이 행 그룹을 기반으로 게산되지만, 여러개의 행을 반환
- 이동 평균, 백분율, 누계 등의 결과를 계산할 수 있음
- 다음과 같은 함수가 포함됨
1) CUME_DIST()
2) LAG()
3) LEAD()
4) PERCENTILE_DISE()
5) FIRST_VALUE()
6) LAST_VALUE()
7) PERCENTILE_CONT()
8) PERCENT_RANK()
사용 예시 ->
-
다음사람과의 키차이,이전사람과의 키차이: LEAD() , LAG()
ex)
-LEAD()
::SELECT name, addr, height ,height - (LEAD(height,1,0) OVER(ORDER BY height DESC)) FROM userTBL;
-LAG()
::SELECT name, addr, height ,height - (LAG(height,1,0) OVER(ORDER BY height)) FROM userTBL; -
지역별로 가장 키가 큰 사람과의 차이: FIRST_VALUE()
ex)
SELECT addr, name, height, height - (FIRST_VALUE(height) OVER (PARTITION BY addr ORDER BY height DESC)) FROM userTBL; -
현 지역에서 자신과 키가 같거나 자신보다 큰 인원의 백분율 : CUME_DIST()
ex)
SELECT addr, name, height, (CUME_DIST() OVER (PARTITION BY addr ORDER BY height DESC))* 100 FROM userTBL;
4.지역별로 키의 중앙값 : PERCENTILE_CONT()
ex)
SELECT DISTINCT addr,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY height) OVER(PARTITION BY addr) FROM userTBL; -> 조회 되는 숫자의 중앙값이 없으면 그 숫자의 평균값을 출력
-->SELECT DISTINCT addr,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY height) OVER(PARTITION BY addr)
FROM userTBL;
---->조회되는 숫자 중 중간값이 없으면 그 둘중 하나를 선택해서 반환
7. PIVOT / UNPIVOT 연산자
사용문법 ->
PIVOT (집계함수(열))
FOR 새로운 열로 변경할 열이름
IN(열 목록) AS 피벗이름)
1)PIVOT연산자
:: 한 열에 포함된 여러 값을 출력하고, 이를 여러 열로 변환해 테이블 반환식을 회전하고 필요하면 집계까지 수행
ex)
SELECT * FROM pivotTest
PIVOT(SUM(amount)
FOR season
IN (봄,여름,가을,겨울)) AS resultPivot;
2)UNPIVOT 연산자
::PIVOT의 반대되는 연산 수행
