Do it SQL__DO_IT_05_(based SQL Server)

chapter 05 다양한 SQL 함수 사용하기

05-1 문자열 함수

CONCAT 함수/ CAST, CONVERT 함수/ ISNULL,COALESCE함수/ 
LOWER, UPPER 함수/ LTRIM,RTRIM, TRIM 함수/ LEN함수/
CHARINDEX함수/ LEFT,RIGHT 함수/ SUBSTRING함수/
REPLACE함수/ REPLICATE함수/ SPACE 함수/
REVERSE 함수/ STUFF함수/ STR함수

CONCAT 함수  :문자열과 문자열 연결
CAST, CONVERT 함수 문자열과 숫자 또는 날짜 연결
ISNULL,COALESCE함수 :ISNULL 함수는  (열 이름, 대체할 값) COALESCE 함수는 (열 이름 1, 열 이름 2, .) / 마지막 인자까지 NULL일 경우 NULL 반환
LOWER, UPPER 함수 :LOWER 함수와 UPPER 함수는 각각 문자열을 소문자와 대문자로 변경
LTRIM,RTRIM, TRIM 함수 : 문자열 공백 제거
LEN함수 : 문자 개수를 셀 때 앞의 공 백은 포함하지만, 뒤의 공백은 포함하지 않음 : 문자열의 문자 개수를 반환
CHARINDEX함수 : 특정 문자까지 문자열 길이 반환
LEFT,RIGHT 함수 : 지정한 길이만큼 문자열 반환	SUBSTRING (expression, start, length) SELECT 'Do it! SQL', LEFT('Do it! SQL', 2), RIGHT('Do it! SQL', 2)
SUBSTRING함수 :지정한 범위의 문자열 반환 SUBSTRING (expression, Start, length) CHARINDEX 함수를 조합해 사용하는 경우가 많다.  - 특정 문자 위치를 계산한 다음, 그 값을 SUBSTRING 합수에 사용해 문자열을 검색
REPLACE함수 :특정 문자를 다른 문자로 변경 REPLACE (string_expression, string_pattern, string_replacement)
REPLICATE함수 : 특정 문자를 반복 REPLICATE (string_expression, integer_expression)	SELECT REPLICATE('0', 10)
SPACE 함수 :공백 문자 반복
REVERSE 함수 : 문자열 역순으로 표시 CHARINDEX 함수,  LEN 함수,  SUBSTRING 함수를 조합해 검색
STUFF함수 : 지정한 범위의 문자열 삭제, 새 문자열 삽입  STUFF 함수를 사용해 데이터를 검색할때는 검색하려는 열 길이보다 긴 문자열을 넣어도 뒷부분이 잘리지 않는다. 그 이유는 데이터를 검색한 뒤 결과를 보여 줄 때 변환하므로, 실제 저장된 데이터 길이에는 영향을 미치지않기 때문이다. 하지만 검색한 결과를 다시 저장하려고 할 때는 변환한 문자열 길이가 저장할수 있는 문자열 길이보다 길 경우 뒷부분이 잘리거나 오류가 발생할 수 있다
STR함수  : 숫자를 문자열로 변환

 

 

quiz
nasdaq_company 테이블에서 company_name 열의 데이터 중 마침표(.)를 포함하는 데이터를 검색해 .를 ****로 변경하세요

 

 05-2 날짜 함수 

 GETDATE, SYSDATETIME 함수/ GETUTCDATE, SYSUTCDATETIME함수/ DATEADD 함수/ DATEDIFF 함수/ DATEPART, DATENAME함수/ DAY, MONTH, YEAR 함수/ CONVERT함수

GETDATE, SYSDATETIME 함수 :서버의 현재 날짜, 시간 반환 소수점 3자리와7자리까지의 시간을반환 dateadd (datepart, year, date)
GETUTCDATE, SYSUTCDATETIME함수 : UTC  시간 확인
DATEADD 함수 : 날짜 가감 dateadd(datepart, year, date)
DATEDIFF 함수 : 날짜 차이 구하기 DATEDIFF (datepart, startdate, enddate)	DATEDIFF (datepart, startdate, enddate)
DATEPART, DATENAME함수 : 지정된 날짜 일부 반환하기 DATEPART (datepart, date) DATENAME (datepart, date) DATEPART 함수는 반환값이 월요일이라면 1과 같은 숫자 DATENAME 함수는 반환값이 금요일이라면 금요일 반환
DAY, MONTH, YEAR 함수 :  DAY 함수와 MONTH 함수, YEAR 함수는 각각 날짜에서 일,    월, 연도의 값을 가져옴    큰 의미에서는 DATEPART 합수와 같지만 부분으로 사용
CONVERT함수 - 스타일 매개변수 사용

 

 

QUIZ	SELECT CONVERT(NVARCHAR(10), DATEADD(day, -45, GETDATE()), 120) AS DATE, DATENAME(WEEKDAY, DATEADD(day, -45, GETDATE())) AS DATE_NAME
현재의 날짜에서 45일 이전의 날짜와 요일	select GETDATE(), DATEADD(day, -45, getdate()), DATENAME(day,DATEADD(day, -45, getdate())), DATENAME(weekday,DATEADD(weekday, -45, getdate()))

 

 

05-3 집계 함수

 COUNT, COUNT_BIG 함수/ SUM함수/ AVG함수/ MIN,MAX함수/ ROLLUP, CUBE함수/ STDEV,STDEVP함수

COUNT  COUNT 함수가 반환하는 값은 INT 범위
COUNT_BIG 함수 COUNT_BIG 함수가 반환하는 값은 BIGINT 범위 데이터 개수가 21억 개를 초과할 것으로 예상되면 사용
SUM함수 숫자나 돈 관련 값은 합할 때 사용 SUM 합수는 모든 행의 값을 합하지만 DISTINCT 문을 조합해 중복값은 무시하고 고윳값에만 SUM 합수를 적용 가능
AVG함수  AVG 함수는 평균을 구할 때 사용  AVG 합수의 특징은 NULL값은 무시한다는 것 그리고 앞에서 다른 함수에 DISTINCT 문을 조합했던 것 처럼 AVG 합수에도 DISTINCT 문을 조합해 중복값을 무시하고 고윳값에만 AVG 함수를 적용할 수 있음 GROUP BY와 조합	평균을 구할 때는 자료형에 유의 select 10/convert(float, 3)
MIN,MAX함수 특정열 대상  그룹별 대상 반환하는 값의 자료형은 연산 대상 열의 자료형과 동일 NULL 값은 무시
ROLLUP, CUBE함수 부분합과 총합을 구하려면 GROUP BY 문을 ROLLUP 함수와 CUBE 함수에 조합 CUBE 함수 역시 GROUP BY 문과 조합해 사용 모든 열 조합의 집계 그룹을 만듬 - GROUP BY CUBE (a, b)의 경우   (a, b), (NULL, b), (a, NULL), (NULL. NULL)의 고윳값을   집계하는 그룹을 만듬
STDEV, STDEVP함수 표준편차를 구하는 함수 STDEV 함수는 모든 값의 편차 STDEVP 함수는 모집단의 표준편차 DISTINCT 문을 조합하면 고윳값의 표준 편차 확인가능

 

 

QUIZ	SELECT MIN([close]) AS Low_Price, MAX([close]) AS Max_Price, AVG([close]) AS Avg_Price FROM stock WHERE symbol = 'MSFT' AND date >= '2021-01-01' AND date <'2021-02-01'
stock 테이블에서 symbol이 MSFT인 데이터의 2021년 1월 1일 ~2021년 1 월 31일까지의 주식 종가(close) 중에 최솟값과 최댓값, 평균값	from stock where symbol = 'MSFT' and close between 20210101 and 20210131 select min(close), max(close), avg(close)

 

 

 

05-4 수학 함수

 

/ ABS 함수/ SIGN 함수/ CEILING, FLOOR함수/ ROUND함수

/ LOG함수/ EXP함수/ POWER,SQUARE 함수/ SQRT 함수/ RAND함수/ COS, SIN, TAN, ATAN 함수

입력값을 실수형인 float형으로 자동 변환한 뒤 반환

ABS 함수	select open_price - close_price as diff, ABS(open_price - close_price) as abs_diff from nasdaq_company
SIGN 함수 지정한 값이나 식의 양수, 음수, 0을 판단해 1, -1, 0을 반환	SELECT SIGN(-125), SIGN(0), SIGN(564)  SELECT open_price - close_price, SIGN(open_price - close_price) FROM nasdaq_company
CEILING, FLOOR함수 CEILING : 천장값, 지정한 숫자보다 크거나 같은 최소 정수를 반환 FLOOR : 바닥값. 지정한 숫자보 다 작거나 같은 최대 정수를 반환	SELECT CEILING(123.45), CEILING(-123.45), CEILING($0.0);  SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45);
ROUND함수	SELECT ROUND(123.9994, 3), ROUND(123.9995, 3);
LOG함수 LOG (float_expression [, base]) float expression은 LOG 함수가 계산할 표현식 [, base]는 밀을 설정하는 같이며 float_expression는 float형으로 반환될 수 있는 표현식을 사용 Log 함수의 밑 기본값은 e이다.	SELECT LOG(10, 5) SELECT EXP(10) SELECT EXP(LOG(20)), LOG(EXP(20))
EXP함수
POWER,SQUARE 함수 POWER 함수와 SQUARE 함수는 각각 거듭제곱과 제곱값 기본 형식을 보면 POWER 함수는 float형 표현식과 거듭제곱한 값을 인자로 입력받으며 SQUARE 함수는 float형 표현식만 인자로 입력받는다. POWER, SQUARE 함수의 기본 형식 POWER (float_expression, y) SQUARE (float_expression)
SQRT 함수 SQRT 함수는 float형 표현식을 입력받아 제곱근을 반환
RAND함수  0~1 범위의 배타적 의사 난수 float형 값을 반환  RAND ([seed])  [seed] 인수에 전달하는 값의 자료형은 tinyint, smallint, int형	SELECT RAND(100), RAND(), RAND()
COS, SIN, TAN, ATAN 함수 삼각함수는 COS 함수부터 DEGREES 함수에 이르기까지 매우 다양함	SELECT COS(14.78); SELECT SIN(45.175643); SELECT TAN(PI()/2), TAN(45) SELECT ATAN(45.87) AS atanCalc1, ATAN(-181.01) AS atanCalc2, ATAN(0) AS atanCalc3, ATAN(0.1472738) AS atanCalc4, ATAN(197.1099392) AS atanCalc5;

 

 

quiz	SELECT a.symbol, a.company_name, a.industry, a.close_price  FROM nasdaq_company AS a  INNER JOIN ( SELECT symbol, ROW_NUMBER() OVER (PARTITION BY industry ORDER BY close_price desc) AS [RANK] FROM nasdaq_company ) AS b ON a.symbol = b.symbol WHERE b.[RANK] = 1
nasdaq_company 테이블에서 industry별로  최고가 symbol을 검색해 해당 데이터의 symbol company_name, industry, close_price를 검색

05-5 순위함수

ROW_NUMBER 함수/ RANK 함수/ DENSE_RANK 함수/ NTILE 함수

ROW_NUMBER 함수
RANK 함수
DENSE_RANK 함수
NTILE 함수

05-6 분석 함수

LAG,LEAD 함수/ CUME_DIST 함수/ PERCENT_RANK 함수/ PERCENTILE _CONT, PERCENTILE_DISC 함수/ FIRST_VALUE,LAST_VALUE함수

LAG,LEAD 함수
CUME_DIST 함수
PERCENT_RANK 함수
PERCENTILE _CONT, PERCENTILE_DISC 함수
FIRST_VALUE,LAST_VALUE함수