윈도우 함수

윈도우 함수란

행과 행간의 관계를 정의하거나 행과행간을 비교, 연산하는 함수이며
윈도우는 데이터의 집합을 나타내는 개념이다.

윈도우 함수의 종류는 크게 5가지로 분류가 된다.
1. 그룹 내 순위 관련 함수
RANK, DENSE_RANK, ROW_NUMBER
2. 그룹 내 집계
SUM, MAX, MIN, COUNT
3. 그룹 내 행 순서 관련 함수
FIRST_VALUE, LAST_VALUE, LAG, LEAD
4. 비율 관련 함수
RATIO_TO_REPORT, PERCENT_RANK, CUM_DIST, NTILE
5. 선형 분석을 포함한 통계 분석 관련 함수
->SQLD 시험에서 안나옴

---

OVER 절

OVER 절은 윈도우 함수에서 사용되는 절로, 함수의 작동 범위를 지정하는데 사용된다.
윈도우 함수는 행들에 대해 계산을 수행하고, OVER절은 해당 계산이 수행될 윈도우를 정의하는 역할을 한다.

• 형식

OVER ([PARTITION BY partition_expression] 
ORDER BY sort_expression [ASC|DESC] ROWS/RANGE 
BETWEEN frame_start AND frame_end)

그룹 내 순위 함수

RANK

RANK 함수는 ORDER BY를 포함한 QUERY문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다.
-> 동일한 값에 대해서는 동일한 순위를 부여함
EX) 1,2,2,4

• 형식

RANK() OVER([PARTITION BY partition_expr] 
ORDER BY sort_expr[ASC | DESC])

-예제
사원 데이터에서 급여가 높은 순서와 JOB별 급여가 높은 순서

SELECT JOB, ENAME, SAL,
RANK() OVER(ORDER BY SAL DESC) ALL_RANK, 
RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK 
FROM EMP;

-> 결과

DENSE_RANK

DENSE_RANK 함수는 RANK와 비슷하지만, 동일한 순위를 하나의 등수로 간주한다는 점이 다르다
EX) 1,2,2,3

• 형식

DENSE_RANK() OVER ([PARTITION BY partition_expression] 
ORDER BY sort_expression [ASC|DESC])

// RANK의 형식에서 RANK() -> DENSE_RANK()가 됐을 뿐이다.

• 예제

SELECT JOB, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK
,DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK 
FROM EMP;

-> 결과

급여를 기준으로 순위를 부여한 것은 같으나 결과가 약간 다른 것을 볼 수 있다.

ROW_NUMBER

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

• 형식

ROW_NUMBER() OVER([PARTITION BY partition_expr]
ORDER BY sort_expr [ASC | DESC])
// 얘도 그냥 ROW_NUMBER() 로 바뀌었을 뿐이다.

• 예제

SELECT JOB, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK
,ROW_NUMBER() OVER(ORDER BY SAL DESC) ROW_NUMBER 
FROM EMP;

-> 결과

MARTIN과 WARD 의 순위를 보면 차이가 있는 것을 볼 수 있다.

위 예제의 경우 같은 SAL에 대해서는 어떤 순서가 정해질지 알 수 없기 때문에 (Oracle 의 경우 rowid가 적은 행이 먼저 나옴)

만일 동일 값에 대한 순서까지 관리 하고 싶으면
ROW_NUMBER( ) OVER (ORDER BY SAL DESC, ENAME) 같이 ORDER BY 절을 이용해 추가적인 정렬 기준을 정의해야 한다.

---

일반 집계 함수

기능 자체는 앞서 다룬 집계 함수와 동일하지만, 파티션에서 사용했을 때의 차이점을 보자

SUM

파티션별 윈도우의 합을 구할 수 있다.
ex) 같은 매니저를 두고 있는 사원들의 월급 합

• 예제

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

MAX / MIN

파티션별 윈도우의 최대/최소 값을 구할 수 있다.
ex) 같은 매니저들 두고 있는 사원들 중 급여 최대값/최소값

• 예제

SELECT MGR, ENAME, MAX(SAL) OVER(PARTITION BY MGR) AS MGR_MAX 
FROM EMP;

SELECT MGR, ENAME, MIN(SAL) OVER(PARTITION BY MGR) AS MGR_MAX 
FROM EMP;

AVG

원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있다.
ex) 같은 매니저 내에서 앞의 사번과 뒤의 사번의 평균

SELECT MGR, ENAME, HIREDATE, SAL, ROUND(AVG(SAL)
OVER(PARTITION BY MGR ORDER BY HIREDATE
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS MGR_AVG
FROM EMP;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
-> 현재 행을 기준으로 파티션 내에서 앞의 1건,
현재행, 뒤의 1건을 범위로 지정

COUNT

조건에 맞는 데이터에 대한 통계값을 구할 수 있다.
ex)본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수

SELECT ENAME, SAL, COUNT(*) 
OVER(ORDER BY SAL 
RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS SIM_CNT
FROM EMP;

RANGE BETWEEN 50 PRECEDING AND 150
-> 현재 행을 기준으로 -50 에서 +150 범위 내를 범위로 지정

---

행 순서 관련 함수

SQL Sever는 지원하지 않는다.

FIRST_VALUE

파티션별 윈도우의 처음 값을 구한다.

• 예제
  부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력

SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME)
OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;

 ROWS UNBOUNDED PRECEDING 
 -> 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

LAST_VALUES

파티션별 윈도우의 마지막 값

• 예제
  부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력

SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME)
OVER(PARTITION BY DEPTNO 
ORDER BY SAL DESC ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
AS DEPT_POOR
FROM EMP;

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-> 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를지정

LAG

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

• 예제
  직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력

SELECT ENAME, HIREDATE, SAL, LAG(SAL)
OVER(ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB='SALESMAN';

LEAD

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

LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데, 두 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고 (DEFAULT 1), 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 바꾸어 줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다.

• 예제
  직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력

SELECT ENAME, HIREDATE, LEAD(HIREDATE, 1)
OVER(ORDER BY HIREDATE) AS "NEXTHIREDATE"
FROM EMP;

---

그룹 내 비율 함수

그룹 내 비율 함수의 결과 값은 모두 0 ~ 1 에 값을 가짐
[NTILE 함수 제외]

RATIO_TO_REPORT

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

• 예제
  JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력

SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER(), 2) AS R_R 
FROM EMP
WHERE JOB='SALESMAN';

PERCENT_RANK

파티션별 윈도우에서 처음 값을 0, 마지막 값을 1로 하여 행의 순서별 백분율을 구함.

• 예제
  같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력

SELECT DEPTNO, ENAME, SAL, PERCENT_RANK()
OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)
AS P_R 
FROM EMP;

CUME_DIST

현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다.

• 예제
  같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1 사이의 값으로 출력

SELECT DEPTNO, ENAME, SAL, CUME_DIST()
OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST
FROM EMP;

NTILE

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

• 예제
  전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다.

SELECT ENAME, SAL, NTILE(4)
OVER(ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;