17. 분석함수, 순위함수

분석함수?

테이블에 있는 데이터를 특정 용도로 분석하여 결과를 반환하는 함수

 

복잡한 계산을 단순하게 처리해주는 함수

쿼리 결과Set을 대상으로 계산을 수행하는 함수

SELECT 절에서 수행

FROM, WHERE, GROUP BY 절에서 사용 불가

ORDER BY 구문에서는 사용 가능

 

분석함수	집계함수
분석 함수는 집계 결과를 각 행마다 보여준다.	집계함수는 여러행 또는 테이블 전체 행으로부터 그룹별로 집계하여 결과를 반환한다.

 

 

 

 

 

순위함수?

순위를 구하는 함수.

 

RANK함수?

특정 범위(PARTITION) 내에서 순위를 구할 수도 있고, 전체 데이터에 대한 순위를 구할 수도 있다. 

동일한 값에 대해서는 동일한 순위를 부여하게 된다. 

 

DENSE_RANK함수?

RANK와 유사하나, 동일한 순위를 하나의 건수로 취급한다.

RANK는 1, 2, 3순위로 표기하지만, DENSE_RANK는 1, 1, 3 순위를 부여한다. (1위가 두개이니 2위 없이 3위로 넘어감)

 

ROW_NUMBER함수?

RANK, DENSE_RANK가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, ROW_NUMBER는 동일한 값이라도 고유한 순위를 부여한다. 

같은 값에 대해서 어떤 결과가 먼저 나올지 순서를 정하고 싶다면 ORDER BY를 같이 기재하면 된다.

 

 

* 순위함수의 특징

ORDER BY는 생략할 수 없다.

WINDOWING 절은 사용 할 수 없다.

 

 

 

 

 

 

그룹 내 집계(AGGREGATE) 관련 함수

분석함수로도 사용 가능하다.

 

SUM : 파티션별 윈도우의 누적합계를 구할 수 있다. 

* 분석함수 OVER절 안에서 ORDER BY절을 사용하면 ORDER BY 절의 컬럼을 기준으로 누적되어 계산 된다

MAX : 파티션별 윈도우의 최대값을 구할 수 있다. 

MIN : 파티션별 윈도우의 최소값을 구할 수 있다. 

AVG : 파티션별 통계값을 구할 수 있다. 

COUNT : 

 

 

 

 

 

window함수? (=분석함수, 순위함수)

분석함수 중에서 윈도우절(WINDOWNING 절)을 사용하는 함수

윈도우절을 사용하면 PARTITION BY 절에 명시된 그룹을 좀 더 세부적으로 그룹핑 할 수 있다.

윈도우절은 분석함수중에서 일부(AVG, COUNT, SUM, MAX, MIN)만 사용 할 수 있다.

GROUP BY 구문과 병행하여 사용할 수 없다 (서브쿼리에는 사용 가능)

 

종류

1. 그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER

2. 그룹 내 집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (sql server는 OVER 절의 OREDER BY 지원 X)

3. 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클에서만 지원)

4. 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

5. 선형 분석을 포함한 통계 분석 함수

 

형식 

SELECT WINDOW_FUNCTION(ARGUMENTS:인수)
OVER (PARTITION BY 칼럼 ORDER BY WINDOWING절)
FROM 테이블명;

ARGUMENTS(인수) : 함수에 따라 0 ~ N개 인수가 지정될 수 있다. 

OVER : 분석함수임을 나타내는 키워드 =~에게

PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다. =group by와 유사한 기능

WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (sql server 에서는 지원하지 않음)

 

 

 

 

 

WINDOWING절

① ROWS : 행의 수를 선택

UNBOUNDED PRECEDING : 무한한 상위 행

PRECEDING : 이전의 행

CURRENT ROW : 현재 행일 경우

FOLLOWING : 다음 나오는 행

UNBOUNDED FOLLOWING : 무한한 하위 행

 

 

② RANGE : 값의 범위 선택

 

 

 

 

 

 

 

 

 

 

그룹 내 행 순서 관련 함수

*oracle에서만 지원

FIRST_VALUE

파티션별 윈도우에서 가장 먼저 나온 값을 구할 수 있다.

sql server 에서는 지원하지 않음.

MIN 함수를 이용해도 같은 결과를 얻을 수 있음.

FIRST_VALUE 는 공동 등수를 인정하지 않고 처음 나온 행을 처리하기 때문이다. 

 

LAST_VALUE

파티션별 윈도우에서 가장 먼저 나중에 나온 값을 구할 수 있다.

sql server 에서는 지원하지 않음.

MAX 함수를 이용해도 같은 결과를 얻을 수 있음.

 

LAG

파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.

sql server 에서는 지원하지 않음.

LAG 함수는 3개의 인수까지 사용할 수 있다. 

LAG(SAL, 2, 0) <-- 두번째 인수는 몇 번째 앞의 행을 가져올지 결정하는 것이고 (디폴트는 1. 여기서는 2을 지정했으니까 2번째 앞에 있는 행을 가져오는 것), 세번째 인수는 파티셧 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데, 이 경우 다른 값으로 바꾸어줄 수 있다. NVL/ ISNULL 과 같은 기능.

 

LEAD

파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.

sql server 에서는 지원하지 않음.

LAG 처럼 LEAD 함수도 3개의 인수까지 사용할 수 있다. 

 

 

 

 

 

그룹 내 비율 관련 함수

CUME_DIST

파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다.

sql server 에서는 지원하지 않음.

 

PERCENT_RANK

파티션별 함수를 이용해서 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 행의 순서별 백분율을 구한다. (값이 아니라 행의 순서별 백분율이다....)

sql server 에서는 지원하지 않음.

 

NTILE

파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다. 

NTILE(4) 의 의미는 14명의 팀원을 4개 조로 나눈다는 의미이다.

14명을 4개의 집합으로 나누면 몫이 3, 나머지가 2가 된다. 

나머지 두 명의 앞의 조부터 할당되어 4명 + 4명 + 3명 + 3명 으로 조를 나누게 된다. 

 

RATIO_TO_REPORT

파티션 내 전체 SUM(컬럼) 값에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있다.

결과값은 > 0 & <= 1 의 범위를 가진다. 

개별 ratio 의 합을 구하면 1이 된다. 

sql server 에서는 지원하지 않음.

 

 

 

참고 강의 : https://youtu.be/2ec7S5tlGSI

http://www.gurubee.net/lecture/2671