[SQL] 윈도우 함수

윈도우 함수

: 행과 행 간에 비교, 연산, 정의하기 위한 함수

 

윈도우 함수 종류

-순위 함수 : RANK, DENSE_RANK, ROW_NUMBER

-집계 함수 : SUM, MAX, MIN, AVG, COUNT

-그룹 내 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD

-그룹 내 비율 함수 : RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE

 

SELECT 함수명() OVER( [PARTITION BY절] [ORDER BY절] [WINDOWING절] )
FROM 테이블명;

 

 

PARTITION BY절

: 전체에서 소그룹을 나눔

 

ORDER BY절

: 어떤 컬럼으로 순위 정할지

 

WINDOWING절

: 행 범위 지정

 

 

 

순위 함수

RANK

: 동일 순위 존재 시 후순위로 넘어감(1위, 1위, 3위)

 

DENSE_RANK

: 동일 순위 존재 시 후순위로 안넘김(1위, 1위, 2위)

 

ROW_NUMBER

: 동일 순위를 연속 번호로 부여(1위, 2위, 3위)

 

SELECT RANK() OVER(PARTITION BY 컬럼 ORDER BY 컬럼) 
FROM 테이블;

 

 

 

집계 함수

SUM, MAX, MIN, AVG, COUNT

 

SELECT DEPTNO
    , MAX(SAL) OVER (PARTITION BY DEPTNO) 부서별최대급여
FROM EMP;

 

 

 

그룹 내 행 순서 관련 함수

sql server에서 지원하지 않음

 

FIRST_VALUE

: 파티션별 윈도우에서 가장 먼저 나온 값

 

LAST_VALUE

: 파티션별 윈도우에서 가장 나중에 나온 값

 

LAG

: 파티션별 윈도우에서 이전 몇 번째 행의 값 가져오기

 

LAG(SAL, 2, 0)

: 두 행 앞의 급여 가져오고 값이 없으면 0으로 처리

 

LEAD

: 파티션별 윈도우에서 이후 몇 번째 행의 값 가져오기

 

LAG(SAL, 2, 0)

: 두 행 뒤의 급여 가져오고 값이 없으면 0으로 처리

 

 

SELECT ENAME, DEPTNO, SAL
    , FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) 부서별첫번째
FROM EMP;

 

 

 

그룹 내 비율 함수

RATIO_TO_REPORT

: 파티션 내 전체 SUM() 값에 대한 행별 값의 백분율

 

PERCENT_RANK

: 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로

제일 늦게 나오는 것을 1

행의 순서 별 백분율

 

CUME_DIST

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

 

NTILE

: N개로 나눠서 등급을 매김