Functions for manipulating data in SQL server

파이썬과 같은 다른 언어 도구 없이 오직 SQL만으로 기초적인 분석 작업 시행할 때 알면 좋을 것 같다.

Introduction

Whole numbers	Decimal numbers
smallint	numeric
tinyint	decimal
int	money
bigint	smallmoney

time types

Remember, the most common date/time data types are:

• date
• time
• datetime
• datetime2
• smalldatetime

other data types

• binary
• image
• cursor
• rowversion
• uniqueidentifier
• xml
• spatial geometry / geography types

Functions that return system date and time

common mistakes when working with dates and time

• inconsistent date time formats or patterns
• arthmetic operations
• issues with time zones

Functions that return the date and time of the operationg system

Higher-precision	Lower-precesion
SYSDATETIME()	GETDATE()
SYSUTCDATETIME()	GETUTCDATE()
SYSDATETIMEOFFSET()	CURRENT_TIMESTAMP

utc datetime? 협정 세계시
1972년 1월 1일부터 시행된 국제 표준시로, 70년 1월 1일 자정을 0 밀리초로 설정하여 그 이후를 밀리초로 계산한 것
https://ko.wikipedia.org/wiki/%ED%98%91%EC%A0%95_%EC%84%B8%EA%B3%84%EC%8B%9C

SELECT 
	CAST(SYSUTCDATETIME() AS time) AS HighPrecision,
	CAST(GETUTCDATE() AS time) AS LowPrecision;

• RETURN:
  

-DATEFROMPARTS():

SELECT 
	first_name,
	last_name,
   	-- Extract the month number of the first vote
	datepart(MONTH, first_vote_date) AS first_vote_month1,
	-- Extract the month name of the first vote
    datename(MONTH, first_vote_date) AS first_vote_month2,

Performing arithmetic operations on dates

Arithmetic operations

• +, -
• DATEDIFF() : DATEDIFF(datepart, startdate, enddate)
• DATEADD() : DATEADD(datepart, number, date)

Validating if an expression is a date

• ISDATE() : ISDATE(expression)
  

SET DATEFORMAT [ymd, myd, dmy, ...]
SELECT ISDATE()~

• SET LANGUAGE: SET LANGUAGE French;

Functions for positions

position funcs

• LEN()
• CHARINDEX() : looks for a character expression in a given string
• PATINDEX() : Returns the starting position of a pattern in an expression

-- look for last_names that do not contain the letter "z"
CHARINDEX('z', last_names) = 0

-- look for the 'dan' expression in the first_name
CHARINDEX('dan', first_name)

-- look for first names that start with C and the third letter is r
PATINDEX('C_r%', first_name) > 0

Funcs for string transformation

• LOWER()
• UPPER()
• LEFT()
• RIGHT()
• LTRIM()
• RTRIM()
• TRIM()
• REPLACE()
• SUBSTRING()

Functions manipulationg groups of strings

• CONCAT() : CONCAT(st1, st2, [st N])
• CONCAT_WS() : CONCAT_WS(separator, st1, [st N])
• STRING_AGG() : STRIMG_AGG(expression, separator)
  • STRING_AGG() with GROUP BY

• STRING_SPLIT(): STRING_SPLIT(st, separator)

Analytic functions

• FIRST_VALUE() : returns the first value in an ordered set and can use over opt

• LAST_VALUE() : returns the last val in an ordered set

• LAG() : LAG() OVER (partition by, order by)

• LEAD() : LEAD() OVER (partition by, order by)

Mathematical functions

• ABS() : returns the absolute value of an expression
• SIGN() : returns the sign of an expression, as an int

Rounding functions

• CEILING() : returns the smallest int greater than or equal to the expression
• FLOOR() : returns the largest int less than or equal to the expression

Exponential functions

• POWER(num_exp, power) : returns the expression raised to the specified power
• SQUARE(num_exp) : returns the square of the expression
• SQRT(): returns the square root of the expression

Keep in mind: expression type 은 float이어야 한다