[SQLD][책없이 공부]2과목 02. SQL 활용_05 윈도우 함수

1. 윈도우 함수

- 데이터웨어하우스에서 발전한 기능 -> 순위, 합계, 평균, 행 위치 등 조작 가능

 SELECT WINDOW_FUNCTION(ARGUMENTS)

OVER(PARTITION BY 칼럼 ORDER BY WINDOWING 절)

FROM 테이블명;

- 중첩해서 사용할 수 없음 / 서브쿼리로 사용 가능

- OVER 키워드 필수!

- 행과 행 간의 관계를 정의하기 위해 제공되는 함수 

* 윈도우 함수 구조

ARGUMENTS	- 윈도우 함수에 따라서 0 ~ N개의 인수 설정
PARTITION BY	- 전체 집합을 기준에 의해 소그룹으로 나눔
ORDER BY	- 어떤 항목에 대해 정렬. 어떤 항목에 대해 순위 지정할 지 기술
WINDOWING	- 행 기준의 범위.  - ROW : 물리적 결과의 행 수 / RANGE : 논리적 값에 의한 범위

PARTITION BY DEPTNO ORDER BY SAL -> 파티션 구분은 DEPTNO로 하지만 윈도우 함수에 사용할 범위는 SAL임

DEPTNO 파티션 안에서 SAL로 랭크 짓고, 집계하고 하는 것

* WINDOWING

ROWS	- 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정
RANGE	- 논리적 주소에 의해 행 집합을 지정
BETWEEN ~ AND	- 윈도우의 시작과 끝 위치 지정
UNBOUNDED PRECEDING	- 윈도우 시작 위치가 첫 번째 행임을 의미
UNBOUNDED FOLLOWING	- 윈도우 마지막 위치가 마지막 햄임을 의미
CURRENT ROW	- 윈도우 시작 위치가 현재 행임을 의미

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER (ORDER BY SAL

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTSAL FROM EMP;

시작이 첫 행부터 마지막이 끝 행 사이의 물리적 행을 SAL 기준으로 정렬해서 SUM해라

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER (ORDER BY SAL

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TOTSAL FROM EMP;

시작이 첫 행부터 현재 행 사이의 물리적 행을 SAL 기준으로 정렬해서 SUM 해라

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER (ORDER BY SAL

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) TOTSAL FROM EMP;

시작이 현재 행부터 마지막 행이 끝행 사이의 물리적 행을 SAL 기준으로 정렬해서 SUM 해라

RANGE UNBOUNDED PRECENFING : 현재 행 기준으로 파티션 내의 첫번째 행까지 범위 지정

ROWS BETWEEN 1 PRECENDING AND 1 FOLLOWING : 현재 행(위치)기준로 파티션 내에서 앞의 한 건 뒤의 한 건을 묶음으로 지정(ROWS: 물리적 행)

RANGE BETWEEN 50 PRECENDING AND 150 FOLLOWING : 현재 행(값)기분으로 값 -50 값_150 범위 내 포함된 모든 행이 범위

2. 윈도우 함수의 종류

1) 순위 함수 (RANK FUNCTION)

- 특정 항목과 파티션에 대해 순위를 계산 

- 특정 범위 혹은 전체 데이터에 대한 순위를 계산

RANK	- 동일한 순위는 동일한 값 부여	순희 100 영희 100 철수 90 영수 80 => 순희,영희 다른 건수 ->순희 1, 영희 1, 철수 3(앞에 2건), 영수 4(앞에 3건)
DENSE_RANK	- 동일한 순위를 하나의 건수로 계산  - 동순위 같은 숫자 부여, 차순위 숫자는 바로 다음 숫자	순희 100 영희 100 철수 90 영수 80 => 순희,영희 하나 건수 ->순희 1, 영희 1, 철수 2(앞에 1건), 영수 3(앞에 2건)
ROW_NUMBER	- 동일한 순위에 대해 고유의 순위 부여	순희 100 영희 100 철수 90 영수 80 ->순희 1, 영희 2, 철수 3, 영수 4    (순희, 영희에 대해 다른 순위 부여 방식 존재)

2) 집계 함수 (AGGREGATE FUNCTION)

SUM	- 파티션별 윈도우 합계 계산
AVG	- 파티션별 윈도우 평균 계산
COUNT	- 파티션별 윈도우 행수 계산
MAX, MIN	- 파티션별 최대값 최소값 계산

 SELECT ENAME, SAL, SUM(SAL) OVER (PARTITION BY DEPTNO) FROM EMP;

 SELECT ENAME, SAL, SUM(SAL) 

OVER (PARTITION BY DEPTNO ORDER BY SAL RANGE UNBOUNED PRECENDING) FROM EMP; (현재행~첫째행 까지 누산)

SELECT ENAME, SAL, AVG(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECENDING AND 1 FOLLOWING) FROM EMP;

SELECT ENAME, SAL, COUNT(*) OVER (PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN 50 PRECENDING AND 150 FOLLOWING) FROM EMP;

SELECT ENAME, SAL, MAX(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECENDING AND 1 FOLLOWING) FROM EMP;

3) 행 순서 관련 함수

- 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있음

- 특정 위치의 행을 출력할 수 있음 (ARGUMENTS 에 설정 가능)

FIRST_VALUE	- 파티션에서 가장 처음 나오는 값 구함  - MIN 함수 활용해 같은 결과 도출  - 공동 등수 인정 X, 처음 나온 행만 처리(공동 등수 처리 시 세부 항목으로 정렬하기 위해 ORDER BY 사용)
LAST_VALUE	- 파티션에서 가장 나중에 나오는 값 구함  - MAX 함수 활용해 같은 결과 도출  - 공동 등수 인정 X, 마지막에 나온 행만 처리(공동 등수 처리 시 세부 항목으로 정렬하기 위해 ORDER BY 사용)
LAG	- 이전 및 몇 번째 행의 값 가져옴 (SQL SERVER에서 지원 X)  - 기본값 : 1  - ARGUMENT 3개 사용 가능  - ISNULL, NVL 기능 함	LAG(A, B, C)  A : 값 가져올 컬럼  B : 몇 번째 앞의 값 가져올 지 지정할 숫자  C : 가져온 값이 NULL일 경우 반환할 값
LEAD	- 윈도우에서 특정 위치 행 가지고 옴 (SQL SERVER에서 지원 X)  - 기본값 : 1  - ARGUMENT 3개 사용 가능  - ISNULL, NVL 기능 함	LEAD(A, B, C)  A : 값 가져올 컬럼  B : 몇 번째 뒤의 값 가져올 지 지정할 숫자  C : 가져온 값이 NULL일 경우 반환할 값

4) 그룹 내 비율 함수 

RATIO_TO_REPORT	- 파티션 내 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회  - 결과값 범위 : 0 < 결과값 <= 1  /  RATIO의 합 = 1  - SQL SERVER 지원 X
PERCENT_RANK	- 파티션에서 제일 먼저 나온 것을 0~ 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율 조회 - 결과값 범위 : 0 <= 결과값 <= 1  (석차별 백분율.. 등급제 나누는거 같은거 인듯)  - SQL SERVER 지원 X
CUME_DIST	- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회  - 누적 분포상 위치를 0 ~ 1 사이의 값 가짐 30% / 55%(30+25) / 90%(30+25+35) / 100%(30+25+35+10)  - SQL SERVER 지원 X
NTILE	- 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과 조회  - ARGUMENT 값이 3이면 3등분 하라는 것 (등분 안에 동일한 ROW가 들어가겠지.. 동비율)