[TIL Day 11] WINDOW FUNTION과 자주 쓰는 함수들

[SELECT 절에서 사용되는 윈도우 함수 기본 문법]

SELECT WINDOW_FUNTION () OVER (PARTITION BY CAL1 ORDER BY CAL2 ASC/DESC)
FROM TABLE

 

[특징]

- GROUP BY와 병행하여 사용할 수 없지만 서브쿼리에는 사용할 수 있다.

 

종류	특징	함수
순위 ***	같으면 같은 등수 부여 후 건너뛰고 순차적 번호 부여 1,1,3,4,4,6,7, ... (ORDER BY 필수)	RANK()
순위	같으면 같은 등수 부여 후 순차적 번호 부여 1,1,2,2,3,3, ... (ORDER BY 필수)	DENSE_RANK()
순위 ***	같아도 정렬 기준에 따라 연속된 번호 부여 1,2,3,4,5,6,7,8, ... (ORDER BY 필수)	ROW_NUMBER()
집계	집계함수와 동일	SUM, MAX, MIN, AVG, COUNT
순서	PARTITION 한 것에서 ORDER BY를 했을 때 제일 먼저 뜨는 값을 호출	FIRST_VALUE(칼럼명)
순서	PARTITION 한 것에서 ORDER BY를 했을 때 제일 마지막 뜨는 값을 호출	LAST_VALUE(칼럼명)
순서 ***	지정 칼럼에서 이전 n 번째의 행 값 호출 (ORDER BY 필수)	LAG(칼럼, n) / n 기본 값 (1), 최대 (3)
순서 ***	지정 칼럼에서 다음 n 번째의 행 값 호출 (ORDER BY 필수)	LEAD(칼럼, n) / n 기본 값 (1), 최대 (3)
비율	(ORACLE 에서만) (ORDER BY 불가) 행의 값을 전체 합한 것에서의 해당 값의 비율 표현	RATIO_TO_REPORT(칼럼)
비율 ***	최소 값을 0 ~ 최대 값을 1로 두고 백분위 수 출력 (0.25, 0.98 등, 소숫 점) (ORDER BY 필수)	PERCENT_RANK() OVER(ORDER BY())
비율	파티션/ORDER BY 별로 값들을 누적 백분율 행이 3개면 1/3으로 각각 표현 (ORDER BY 필수)	CUME_DIST() OVER(ORDER BY())
비율	파티션/ORDER BY 별 전체 건수를 계산한 값으로 n 등분한 결과 (ORDER BY 필수)	NTILE(n) OVER(ORDER BY())

 

 

[CAST () 의 TYPE들]

 

[String 함수]

# X와 Y, Z를 순차적으로 합친다. (단, STRING 이어야 함)
CONCAT(X, Y, Z, ...)

# 중간에 '/' 혹은 ',' 를 넣어서 두 문자열 사이에 병합 문자열을 지정할 수 있음
CONCAT(X, '/', Y)

# 문자열 혹은 X 대상에서 n 번째 부터 m 개의 STR 을 자를 때
SUBSTR(X, n, m)

# 해당 문자열 혹은 X 대상에서 구분 기호('/')를 기준으로 n 번째 값을 호출
SUBSTR_INDEX(X, '/', n)

# X 대상의 문자열을 뒤집는다.
REVERSE(X)

# X 대상에서 n개의 STR를 추출 (좌측, 우측)
LEFT(X,n) / RIGHT(X,n)

 

[Math 함수]

# 절대 값
ABS(n)

# 반올림
ROUND(X,n)

# 소숫 점 올림/내림
CEILING(X) / FLOOR(X)

# n 번째 자릿 수에서 버림
TRUNCAT(X,n)

# 0 ~ 1 사이 실수 중 랜덤 출력
RAND()

# 0 ~ 100 사이 정수 중 랜덤 출력
ROUND(RAND()*100, 0)

 

[DATE 함수들]

# 연도, 월, 일 출력 (단, X의 TYPE이 DATE 일 것)
YEAR(X) / MONTH(X) / DAY(X)

# 현재 DATE 출력 (연-월-일 시:분:초)
NOW()
SYSDATE()
CURRENT_TIMESTAMP()

# X 로부터 (n = YEAR, MONTH, DAY, HOUR, MINUTE, SECOND) 합과 차
DATE_ADD(X, INTERVAL n)
DATE_SUB(X, INTERVAL n)

# X와 Y 간의 날짜 차이
DATEDIFF(X, Y)  # X가 Y보다 커야됨

# START X 에서 END Y 까지의 n 값 출력 (n = YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)
TIMESTAMPDIFF(n, X, Y)

# 현재 시간을 UNIXTIME으로 출력
UNIX_TIMESTAMP()

# DATE FORMAT 함수 ('' 안에는 여러 옵션들이 있음 밑에 그림 참고)
DATE_FORMAT(X, '%Y-%m-&d')

 

[DATE_FORMAT(칼럼, '값')] 에서 '값'에 쓰이는 요소들