18. WINDOWS함수 연습문제

원래의 DATASET

SELECT ENAME, JOB, SAL
FROM EMP
ORDER BY JOB, SAL DESC;

WINDOW 적용

: 부서별 누적 연봉

SELECT JOB, SUM(SAL) OVER (PARTITION BY JOB
ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING) --WINDOWING절
AS SUM_SAL 
FROM EMP;

 

 

 

 

ROWS

행의 위치를 기준으로 연산에 참여할 행을 선택한다.

ROWS예제

: 각 부서별 칼럼SAL의 누적 합을 구하는 칼럼을 생성해라.

SELECT JOB, ENAME, SAL,
SUM(SAL) -- SAL 칼럼으로 SUM연산을 할건데,
 OVER (PARTITION BY JOB -- JOB을 기준으로 나누고,
ORDER BY SAL -- 파티션 내부 안에서 SAL로 정렬할건데
ROWS UNBOUNDED PRECEDING) -- 파티션 맨 위에 위치해있는 행부터 현재 행까지 연산해
AS CUME_SAL
FROM EMP;

* 새로운 JOB마다 UNBOUNDED PRECEDING 시작

* 파티션에서 벗어나는 계산을 만들어내지는 않는다

 

범위 지정 예제
ROWS UNBOUNDED PRECEDING : 윈도우 함수의 연산을 맨 위에서부터 현재 행까지
ROWS UNBOUNDED FOLLOWING : 윈도우 함수 연산을 현재 행에서 맨 아래 행까지 포함해 계산
ROWS 1 PRECEDING : 윈도우 함수의 연산은 한 칸 위 행부터 현재 행까지 포함해서 계산
ROWS 2 FOLLOWING : 윈도우 함수의 연산을 현재 행부터 두 칸 아래까지 포함해서 계산

ROWS BETWEEN A AND B
SELECT JOB, ENAME, SAL,
SUM(SAL)
OVER (ORDER BY SAL
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS CUME_SAL
FROM EMP;

 

ROWS 범위 지정 예제

: 위 행의 SAL (1PRECEDING)과 현재 행의 SAL 칼럼 (CURRENT ROW)의 합을 구해라

SELECT JOB, ENAME, SAL,
SUM(SAL)
OVER (ORDER BY SAL
ROWS 1 PRECEDING)
AS CUME_SAL
FROM EMP;

 

범위 지정 예제
ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
A. 최고 위의 행부터 두 칸 아래 행까지 포함해서 계산

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

= ROWS 2 PRECEDING
A. 두칸 위의 행부터 현재 행까지 계산

ROWS 1 PRECEDING AND UNBOUNDED FOLLOWING
A. 한 칸 위의 행부터 최하단 행까지 계산

ROWS 1 FOLLOWING AND 3 FOLLOWING
A. 현재 행으로부터 한 칸 아래 행부터 세 칸 아래 행까지 총 세 개 행에 대해 계산 (나는 연산에 참여하지 못함)

 

 

 

 

 

RANGE

칼럼의 '값'을 기준으로 연산에 참여할 행을 선택한다.

 

RANGE 예제

SELECT JOB, ENAME, SAL,
SUM(SAL) OVER(ORDER BY SAL
RANGE 150 PRECEDING) -- 현재 칼럼보다 작기는 한데, 그 차이가 "150이하" 인 것들
AS CUME_SAL
FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'SALESMAN';

* JAMES : SAL=950, SMITH의 800이 150보다 차이가 크지 않으므로 연산에 참여시켜준다. 800+950 = 1750
* TURNER : SAL = 1500, 1500-150=1350이상이어야 연산에 참여시킬 수 있는데, MILLER의 SAL이 1300이므로 연산의 대상이 되지 못했다.

 

범위 지정 예제

RANGE 150 PRECEIDNG

A. 현재 칼럼을 기준으로 150이하로 차이나는 행을 선택적으로 계산한다.

 

RANGE UNBOUNDED PRECEIDNG

A. 현재 칼럼의 값을 포함하고, 기준으로 삼아 작은 값들을 모두 선택하여 계산한다.

 

RANGE BETWEEN 150 PRECEDING AND 150 FOLLOWING

A. 현재 칼럼보다 150이하 ~ 150이상까지 값을 갖는 행을 선택하여 계산한다.

RANGE BETWEEN UNBOUNDED PRECEIDNG AND CURRENT ROW

= RANGE UNBOUNDED PRECEDING

A. 가장 작은 값을 가지는 행부터 현재 행의 값까지 선택하여 계산한다.

 

 

 

 

 

순위함수

① RANK()
중복 값이 있는 행에게 공동 등수를 부여하고, 다음 등수를 제거한다.
그러므로 연속적이지 않은 등수가 나올 수 있다.

SELECT ENAME, SAL, RANK()
OVER (ORDER BY SAL) -- ORDER BY SAL로 기준을 삼아 RANK를 매긴다
AS RANK
FROM EMP;

예문

SELECT ENAME, JOB, SAL, RANK()
OVER (PARTITION BY JOB ORDER BY SAL DESC) RANK -- JOB을 기준으로 파티션처리하고, 각각의 파티션에서 SAL칼럼을 다시 내림차순으로 정렬하여 RANK함수를 적용한다
FROM EMP;

 

② DENSE_RANK() * DENSE : 농도가 짙은, 빽빽한
동일 등수를 가진 사람의 수와 상관없이 그 다음 등수 생성이 가능하다.
그러므로 등수는 무조건 연속적이다.
* RANK함수는 1등 동점자가 99명이면 바로 100등이 나온다 -> 등수의 의미가 퇴색

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

③ ROW_NUMBER()
행의 번호를 의미한다.
순위를 정하기는 하지만 같은 값을 가져도 행의 번호가 같을 수는 없다.

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

 

 

 

 

 

그룹 내 행 순서 함수

① FIRST_VALUE : 나누어진 파티션에서 가장 먼저 나온 값을 출력한다.

SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) --파티션에서 나눈 값을 ENAME기준으로 
OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING)
AS ENAME_FV
FROM EMP;

 

② LAST_VALUE : 나누어진 파티션에서 가장 나중에 나온 값을 출력한다.

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

 

③ LAG : 해당 행을 기준으로 위의 행을 선택한다 * lag : 지연된

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

 

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

 

NULL값 처리

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

 

④ LEAD : 해당 행을 기준으로 아래에 위치한 행의 값을 가져온다

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

* LEAD(SAL, 2) : 두 행을 아래로 내려가 칼럼SAL의 값을 가져온다

* LEAD(SAL, 2) : 더이상 읽을 행이 없어 NULL값이 생성되는 경우, 1000으로 대체한다.

 

 

 

 

 

그룹 내 비율함수

① RATIO_TO_REPORT

값의 비율을 나타낸다. 함수가 적용되는 칼럼 공간의 값의 합 중에서 해당 행이 차지하는 비율을 나타낸다. 0이상 1이하의 수로 나타낸다.

SELECT ENAME, JOB, SAL,
ROUND(RATIO_TO_REPORT (SAL) OVER(), 2) -- 전체 행에서 SAL칼럼이 어느정도 비율을 차지하고 있는가
AS RATIO
FROM EMP
WHERE JOB='SALESMAN';

 

② PERCENT_RANK

제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 처리하여 값이 아닌 행의 순서별 백분율을 구한다.

SELECT DEPTNO, ENAME, JOB, SAL,
PERCENT_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) -- 전체 행에서 SAL칼럼이 어느정도 비율을 차지하고 있는가
AS PR
FROM EMP;

* 0~1을 항목 수 만큼 등분한다. (3항목이면 2번 컷팅)

1을 2번 컷팅해서 3항목이 값을 나눠가지면 0 > 0.5 > 1

 

③ CUME_DIST

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

SELECT DEPTNO, ENAME, JOB, SAL,
ROUND(CUME_DIST() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC), 2) -- 전체 행에서 SAL칼럼이 어느정도 비율을 차지하고 있는가
AS CD
FROM EMP;

 

④ NTILE함수 

전체 그룹을 N등분한다.

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

[해설] NTILE(4)는 4개의 타일에 배정된다. 14개의 행이 존재하므로 4로 배분하면 2가 남는다.

이 남는 두 개의 행은 상위 타일(그룹)부터 하나씩 배정한다. 따라서 1,2번 그룹은 항목이 4개씩 배정되고, 3,4번 그룹은 3개씩 배정된다.