[함수-다중행 함수]윈도우 함수

윈도우 함수

1)윈도우 함수의 기본 구문

SELECT 윈도우 함수(인자) OVER([PARTITION BY 컬럼][ORDER BY절][WINDOWING 절])
FROM 테이블명

❗️윈도우 함수는 반드시 OVER 키워드와 함께 사용된다.

• 인자 : 함수에 따라 0~N개의 인자를 지정 가능
• PARTITION BY : 전체 집합을 소그룹으로 지정하여 나눌 수 있다.
• ORDER BY절 : 어떤 항목에 대해 순위를 지정할 지 ORDER BY절을 기술한다.
• WINDOWING절 : 집계하려는 데이터의 범위를 지정할 수 있다. 오라클에서만 사용 가능하다.

 

*WINDOWING절 

범위	의미
UNBOUNDED PRECEDING	위쪽 끝 행
UNBOUNDED FOLLOWING	아래쪽 끝 행
CURRENT ROW	현재 행
n PRECEDING	현재 행에서 위로 n만큼 이동
n FOLLOWING	현재 행에서 아래로 n만큼 이동

기준	의미
ROWS	행 자체가 기준이 된다.
RANGE	행이 가지고 있는 데이터 값이 기준이 된다.

*WINDOWING절 기본 구문

--처음부터 현재 행 까지
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

--현재 행부터 끝까지
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

 

 

2)윈도우 함수의 분류

윈도우 함수는 역할에 따라 크게 4가지로 나눌 수 있다.

분류	종류
순위 함수	RANK DENSE_RANK ROW_NUMBER
집계 함수	SUM MAX MIN AVG COUNT
행 순서 함수(오라클)	FIRST_VALUE LAST_VALUE LEG LEAD
비율 함수(오라클)	PERCENT_RANK RATIO_TO_PEPORT CUME_DIST NTILE

 

 

 

 

1️⃣순위 함수

• 순위를 구하는데 사용하는 함수.
• 세 개의 함수 모두 동일한 동작을 수행 하지만 공동 순위 처리 방식이 다르다.

순위 함수	설명
RANK	공동 순위를 개별적인 존재로 취급하고 존재하는 수 만큼 다음 순위를 건너뛴다.   EX) 1위 (2위) (2위) 4위 5위
DENSE_RANK	RANK와 동일한 동작을 하지만 공동 순위에 대한 처리 방식이 다르다. 공동 순위를 하나의 묶음으로 취급하고 다음 순위를 건너뛰지 않고 이어서 매긴다.   EX) 1위 (2위 2위) 3위 4위
ROW_NUMBER	공동 순위가 있더라도 각기 다른 유니크한 순위를 부여한다. ORDER BY절을 통해서 동일한 값에 대한 순서를 관리할 수 있다.

예제

1.사원 테이블에서 급여가 가장 높은 순서를 구하기

SELECT E_NAME,RANK()OVER(ORDER BY SAL DESC)
FROM EMP;

2.PARTITION BY절을 사용하여 그룹 내 순위를 구할 수 있다.

SELECT E_NAME,RANK()OVER(PARTITION BY JOB ORDER BY SAL DESC)
FROM EMP;

 

 

2️⃣집계 함수

집계 함수	설명
SUM	파티션별 합을 구할 수 있다.
MAX	파티션별 최대값을 구할 수 있다.
MIN	파티션별 최소값을 구할 수 있다.
AVG	파티션별 평균을 구한다.
COUNT	파티션별 카운트

❗️일반 집계함수 와 윈도우함수의 집계함수

• 일반 적인 집계함수는 전체 테이블의 데이터를 집계하여 하나의 결과를 반환한다.
• 윈도우 함수의 경우 데이터를 행 단위로 분석을 하면서 각 행에 대한 개별적인 계산을 한다. 

예제

점수 테이블

1.일반적인 집계함수의 사용

SELECT SUM(SCORE) as TOTAL_SCORE
FROM REVIEW;

2.윈도우 함수의 사용

SELECT   
    user_name,
    movie,
    score,
    SUM(SCORE) OVER(partition by user_name)as TOTAL_SCORE
FROM REVIEW;

 

3.오라클에서는 ORDER BY절을 사용해서 누적값을 구할 수 있다.

SELECT 
    user_name,
    movie,
    score,
    SUM(SCORE) OVER(partition by user_name ORDER BY SCORE DESC)as TOTAL_SCORE
FROM REVIEW;

 

 

3️⃣행 순서 함수

오라클에서만 사용 가능하다. 

행 순서 함수	설명
FIRST_VALUE	파티션별 윈도우에서 가장 먼저 나온 값을 구한다. MIN함수를 사용하여 같은 결과를 얻을 수 있다.  동일한 값이 존재한다면 OVER안에 ORDER BY 정렬 조건을 추가한다.)
LAST_VALUE	파티션별 윈도우에서 가장 나중에 나온 값을 구한다. MAX함수를 사용하여 같은 결과를 얻을 수 있다.
LAG	파티션별 이전 몇 번째 행의 값을 가져올 수 있다. LAG(컬럼[,앞에서 가져올 행][,NULL일경우 지정할 값])   -두 번째 인자 : 몇 번째 앞의 행을 가져올지 결정 Default값은 1이다.   -세 번째 인자 : 파티션의 첫 번째 행은 앞에 값이 없기 때문에 NULL일 경우 들어올 값을 지정
LEAD	파티션별 이후 몇 번째 행의 값을 가져올 수 있다.  LEAD(컬럼[,뒤에서 가져올 행][,NULL일경우 지정할 값])   LAG와 마찬가지로 세 개의 인자를 받고 동일한 역할.

 

4️⃣비율 함수

오라클에서만 사용 가능

비율 함수	설명
RATIO_TO_REPORT	파티션별 합계에서 차지하는 비율을 구하는 함수
PERCENT_RANK	파티션별 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 한다. 값이 아닌 행의 순서별 백분율을 구한다.
CUME_DIST	파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 구한다.
NTILE	주어진 수 만큼 행들을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수