메뉴 건너뛰기

bysql.net

제6절_윈도우_함수(WINDOW_FUNCTION)

2011.10.27 21:07

suspace 조회 수:20530

1. WINDOW FUNCTION 개요

   행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

 

·      WINDOW FUNCTION 종류

 

-      순위(RANK) 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER 
(ANSI/ISO SQL
표준 / Oracle / SQL Server 등 대부분 DBMS 지원)

-      집계(AGGREGATE) 관련 함수 : SUM, MAX, MIN, AVG, COUNT 
(ANSI/ISO SQL
표준 / Oracle / SQL Server 등 대부분 DBMS지원)
* SQL Server
의 경우 집계 함수는 OVER 절 내의 ORDER BY 구문을 지원하지 않는다.

-      행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD 
(Oracle
에서만 지원되는 함수)

-      비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT, RATIO_TO_REPORT
(CUME_DIST, PERCENT_RANK : ANSI/ISO SQL
표준/Oracle 지원
 NTILE : Oracle / SQL Server
지원
 RATIO_TO_REPORT: Oracle
지원)

-      선형 분석을 포함한 통계 분석 관련 함수

(참조) Oracle의 통계 관련 함수
CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP, VARIANCE, VAR_POP, VAR_SAMP, REGR_(LINEAR REGRESSION), REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY

·        

  WINDOW FUNCTION SYNTAX

 

- WINDOW 함수에는 OVER 문구 필수
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY
칼럼] [ORDER BY ] [WINDOWING ] )
FROM
테이블 명;

- ARGUMENTS (인수)  : 함수에 따라 0 ~ N개의 인수
- PARTITION BY
: 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
- ORDER BY
: 어떤 항목에 대해 순위를 지정할 지 ORDER BY 절을 기술한다.
- WINDOWING
: WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다.
(
다만, WINDOWING 절은 SQL Server에서는 지원하지 않는다.)

BETWEEN 사용 타입
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING


BETWEEN
미사용 타입
ROWS | RANGE
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

2. 그룹 내 순위 함수

. RANK 함수 - 특정 항목(칼럼) / 특정 범위(PARTITION) / 전체 데이터에 대한 순위를 구하는 함수
-
동일한 값에 대해서는 동일한 순위

[예제] 사원 데이터에서 급여가 높은 순서와 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;

JOB

ENAME

SAL

ALL_RANK

JOB_RANK

PRESIDENT

KING

5000

1

1

ANALYST

FORD

3000

2

1

ANALYST

SCOTT

3000

2

1

MANAGER

JONES

2975

4

1

MANAGER

BLAKE

2850

5

2

MANAGER

CLARK

2450

6

3

SALESMAN

ALLEN

1600

7

1

SALESMAN

TURNER

1500

8

2

CLERK

MILLER

1300

9

1

SALESMAN

WARD

1250

10

3

SALESMAN

MARTIN

1250

10

3

CLERK

ADAMS

1100

12

2

CLERK

JAMES

950

13

3

CLERK

SMITH

800

14

4

- ORDER BY SAL DESC 조건과 PARTITION BY JOB 조건이 충돌 : JOB 별로는 정렬 X, ORDER BY SAL DESC 조건으로 정렬(ALL_RANKJOB_RANK의 순서와는 상관없음)

* SELECT JOB, ENAME, SAL, RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK FROM EMP;
-
업무 구분이 없는 ALL_RANK 칼럼에서 같은 값이 있을 경우 동일 순번 반환
-
동일 순번일 때 다음 순위를 뛰어넘고 출력. ) 1, 2, 2, 4

JOB

ENAME

SAL

ALL_RANK

PRESIDENT

KING

5000

1

ANALYST

FORD

3000

2

ANALYST

SCOTT

3000

2

MANAGER

JONES

2975

4

MANAGER

BLAKE

2850

5

MANAGER

CLARK

2450

6

SALESMAN

ALLEN

1600

7

SALESMAN

TURNER

1500

8

CLERK

MILLER

1300

9

SALESMAN

WARD

1250

10

SALESMAN

MARTIN

1250

10

CLERK

ADAMS

1100

12

CLERK

JAMES

950

13

CLERK

SMITH

800

14

* SELECT JOB, ENAME, SAL, RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;
-
업무를 PARTITION으로 구분한 JOB_RANK의 경우 같은 업무 내 범위에서만 순위를 부여

JOB

ENAME

SAL

JOB_RANK

ANALYST

FORD

3000

1

ANALYST

SCOTT

3000

1

CLERK

MILLER

1300

1

CLERK

ADAMS

1100

2

CLERK

JAMES

950

3

CLERK

SMITH

800

4

MANAGER

JONES

2975

1

MANAGER

BLAKE

2850

2

MANAGER

CLARK

2450

3

PRESIDENT

KING

5000

1

SALESMAN

ALLEN

1600

1

SALESMAN

TURNER

1500

2

SALESMAN

MARTIN

1250

3

SALESMAN

WARD

1250

3

. DENSE_RANK 함수

- RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 틀린 점

[예제] 사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력한다.
SELECT JOB, ENAME, SAL,
RANK( ) OVER (ORDER BY SAL DESC) RANK,
DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

JOB

ENAME

SAL

RANK

DENSE_RANK

PRESIDENT

KING

5000

1

1

ANALYST

FORD

3000

2

2

ANALYST

SCOTT

3000

2

2

MANAGER

JONES

2975

4

3

MANAGER

BLAKE

2850

5

4

MANAGER

CLARK

2450

6

5

SALESMAN

ALLEN

1600

7

6

SALESMAN

TURNER

1500

8

7

CLERK

MILLER

1300

9

8

SALESMAN

WARD

1250

10

9

SALESMAN

MARTIN

1250

10

9

CLERK

ADAMS

1100

12

10

CLERK

JAMES

950

13

11

CLERK

SMITH

800

14

12

. ROW_NUMBER 함수

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

[예제] 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력한다.
SELECT JOB, ENAME, SAL,
RANK( ) OVER (ORDER BY SAL DESC) RANK,
ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;

JOB

ENAME

SAL

RANK

ROW_NUMBER

PRESIDENT

KING

5000

1

1

ANALYST

FORD

3000

2

2

ANALYST

SCOTT

3000

2

3

MANAGER

JONES

2975

4

4

MANAGER

BLAKE

2850

5

5

MANAGER

CLARK

2450

6

6

SALESMAN

ALLEN

1600

7

7

SALESMAN

TURNER

1500

8

8

CLERK

MILLER

1300

9

9

SALESMAN

WARD

1250

10

10

SALESMAN

MARTIN

1250

10

11

CLERK

ADAMS

1100

12

12

CLERK

JAMES

950

13

13

CLERK

SMITH

800

14

14

- SALARY에서는 어떤 순서가 정해질지 알 수 없다. 데이터베이스 별로 틀린 결과가 나올 수 있다. (Oracle의 경우 rowid가 적은 행이 먼저 나온다)
-
동일 값에 대한 순서까지 관리하고 싶으면 ROW_NUMBER( ) OVER (ORDER BY SAL DESC, ENAME) 같이 ORDER BY 절을 이용해 추가적인 정렬 기준을 정의

3. 일반 집계 함수

. SUM 함수 파티션별 윈도우의 합

 [예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구한다.
SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP;

MGR

ENAME

SAL

MGR_SUM

7566

FORD

3000

6000

7566

SCOTT

3000

6000

7698

JAMES

950

6550

7698

ALLEN

1600

6550

7698

WARD

1250

6550

7698

TURNER

1500

6550

7698

MARTIN

1250

6550

7782

MILLER

1300

1300

7788

ADAMS

1100

1100

7839

BLAKE

2850

8275

7839

JONES

2975

8275

7839

CLARK

2450

8275

7902

SMITH

800

800

 

KING

5000

5000

 

[예제] OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지의 누적값을 출력한다.
(SQL Server
의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.)

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM FROM EMP
* RANGE UNBOUNDED PRECEDING :
현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정

MGR

ENAME

SAL

MGR_SUM

7566

SCOTT

3000

6000

7566

FORD

3000

6000

7698

JAMES

950

950

7698

WARD *

1250

3450

7698

MARTIN *

1250

3450

7698

TURNER

1500

4950

7698

ALLEN

1600

6550

7782

MILLER

1300

1300

7788

ADAMS

1100

1100

7839

CLARK

2450

2450

7839

BLAKE

2850

5300

7839

JONES

2975

8275

7902

SMITH

800

800

 

KING

5000

5000

* 표시된 7698-WARD 7698-MARTIN의 급여가 같으므로, 같은 ORDER로 취급하여 950+1250+1250=3450의 값이 되었다.
7698-TURNER
의 경우 950+1250+1250+1500=4950의 누적합을 가진다.

. MAX 함수 파티션별 윈도우의 최대값

 [예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최대값을 같이 구한다.
SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX  FROM EMP;

MGR

ENAME

SAL

MGR_MAX

7566

FORD

3000

3000

7566

SCOTT

3000

3000

7698

JAMES

950

1600

7698

ALLEN

1600

1600

7698

WARD

1250

1600

7698

TURNER

1500

1600

7698

MARTIN

1250

1600

7782

MILLER

1300

1300

7788

ADAMS

1100

1100

7839

BLAKE

2850

2975

7839

JONES

2975

2975

7839

CLARK

2450

2975

7902

SMITH

800

800

 

KING

5000

5000

 

[예제] 추가로, INLINE VIEW를 이용해 파티션별 최대값을 가진 행만 추출할 수도 있다.
SELECT MGR, ENAME, SAL
FROM (
        SELECT MGR, ENAME, SAL, MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL
        FROM EMP)
WHERE SAL = IV_MAX_SAL ;

MGR

ENAME

SAL

7566

FORD

3000

7566

SCOTT

3000

7698

ALLEN

1600

7782

MILLER

1300

7788

ADAMS

1100

7839

JONES

2975

7902

SMITH

800

 

KING

5000

. MIN 함수 파티션별 윈도우의 최소값

[예제] 사원들의 급여와 같은 매니저를 두고 있는 사원들을 입사일자를 기준으로 정렬하고, SALARY 최소값을 같이 구한다.
SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN FROM EMP;

MGR

ENAME

HIREDATE

SAL

MGR_MIN

7566

FORD

1981/12/03 00:00:00

3000

3000

7566

SCOTT

1987/07/13 00:00:00

3000

3000

7698

ALLEN

1981/02/20 00:00:00

1600

1600

7698

WARD

1981/02/22 00:00:00

1250

1250

7698

TURNER

1981/09/08 00:00:00

1500

1250

7698

MARTIN

1981/09/28 00:00:00

1250

1250

7698

JAMES

1981/12/03 00:00:00

950

950

7782

MILLER

1982/01/23 00:00:00

1300

1300

7788

ADAMS

1987/07/13 00:00:00

1100

1100

7839

JONES

1981/04/02 00:00:00

2975

2975

7839

BLAKE

1981/05/01 00:00:00

2850

2850

7839

CLARK

1981/06/09 00:00:00

2450

2450

7902

SMITH

1980/12/17 00:00:00

800

800

 

KING

1981/11/17 00:00:00

5000

5000

* SELECT MGR, ENAME, HIREDATE, SAL, MIN(SAL) OVER(PARTITION BY MGR) as MGR_MIN FROM EMP;

MGR

ENAME

HIREDATE

SAL

MGR_MIN

7566

FORD

1981/12/03 00:00:00

3000

3000

7566

SCOTT

1987/07/13 00:00:00

3000

3000

7698

JAMES

1981/12/03 00:00:00

950

950

7698

ALLEN

1981/02/20 00:00:00

1600

950

7698

WARD

1981/02/22 00:00:00

1250

950

7698

TURNER

1981/09/08 00:00:00

1500

950

7698

MARTIN

1981/09/28 00:00:00

1250

950

7782

MILLER

1982/01/23 00:00:00

1300

1300

7788

ADAMS

1987/07/13 00:00:00

1100

1100

7839

BLAKE

1981/05/01 00:00:00

2850

2450

7839

JONES

1981/04/02 00:00:00

2975

2450

7839

CLARK

1981/06/09 00:00:00

2450

2450

7902

SMITH

1980/12/17 00:00:00

800

800

 

KING

1981/11/17 00:00:00

5000

5000

 

. AVG 함수

[예제] EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만을 대상으로 한다.
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 :
현재 행을 기준으로 파티션 내에서 앞의 한 건, 현재 행, 뒤의 한 건을 범위로 지정한다. (ROWS는 현재 행의 앞뒤 건수를 말하는 것임)

MGR

ENAME

HIREDATE

SAL

MGR_AVG

7566

FORD

1981/12/03 00:00:00

3000

3000

7566

SCOTT

1987/07/13 00:00:00

3000

3000

7698

ALLEN

1981/02/20 00:00:00

1600

1425

7698

WARD

1981/02/22 00:00:00

1250

1450

7698

TURNER

1981/09/08 00:00:00

1500

1333

7698

MARTIN

1981/09/28 00:00:00

1250

1233

7698

JAMES

1981/12/03 00:00:00

950

1100

7782

MILLER

1982/01/23 00:00:00

1300

1300

7788

ADAMS

1987/07/13 00:00:00

1100

1100

7839

JONES

1981/04/02 00:00:00

2975

2913

7839

BLAKE

1981/05/01 00:00:00

2850

2758

7839

CLARK

1981/06/09 00:00:00

2450

2650

7902

SMITH

1980/12/17 00:00:00

800

800

 

KING

1981/11/17 00:00:00

5000

5000

- ALLEN의 경우 파티션 내에서 첫 번째 데이터이므로 본인의 데이터와 뒤의 한 건으로 평균값을 구한다.
  (1600 + 1250) / 2 = 1425
- TURNER
의 경우 앞의 한 건과, 본인의 데이터와, 뒤의 한 건으로 평균값을 구한다.
  (1250 + 1500 + 1250) / 3 = 1333
- JAMES
의 경우 파티션 내에서 마지막 데이터이므로 뒤의 한 건을 제외한, 앞의 한 건과 본인의 데이터를 가지고 평균값을 구한다.
  (1250 + 950) / 2 = 1100

 

. COUNT 함수

[예제] 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 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 FOLLOWING :
현재 행의 급여값을 기준으로 급여가 -50에서 +150의 범위 내에 포함된 모든 행이 대상이 된다. (RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위를 표시하는 것임)

ENAME

SAL

SIM_CNT

SMITH

800

2

JAMES

950

2

ADAMS **

1100

3

WARD

1250

3

MARTIN

1250

3

MILLER

1300

3

TURNER

1500

2

ALLEN

1600

1

CLARK

2450

1

BLAKE

2850

4

JONES

2975

3

SCOTT

3000

3

FORD

3000

3

KING

5000

1

- 파티션이 지정되지 않았으므로 모든 건수를 대상으로 -50 ~ +150 기준에 맞는지 검사
- **
표시된 ADAMS의 경우 자기가 가지고 있는 SALARY 1100을 기준으로 -50에서 +150까지 값을 가진 1050에서 1250까지의 값을 가진 JAMES(950), ADAMS(1100), WARD(1250) 3명의 데이터 건수를 구할 수 있다.

4. 그룹 내 행 순서 함수

. FIRST_VALUE 함수 - SQL Server에서는 지원하지 않는 함수

파티션별 윈도우에서 가장 먼저 나온 값을 구한다. MIN 함수를 활용하여 같은 결과를 얻을 수도 있다.

[예제] 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다.
SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH
FROM EMP;

RANGE UNBOUNDED PRECEDING :
현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

DEPTNO

ENAME

SAL

DEPT_RICH

10

KING

5000

KING

10

CLARK

2450

KING

10

MILLER

1300

KING

20

SCOTT *

3000

SCOTT

20

FORD *

3000

SCOTT

20

JONES

2975

SCOTT

20

ADAMS

1100

SCOTT

20

SMITH

800

SCOTT

30

BLAKE

2850

BLAKE

30

ALLEN

1600

BLAKE

30

TURNER

1500

BLAKE

30

MARTIN

1250

BLAKE

30

WARD

1250

BLAKE

30

JAMES

950

BLAKE

- 같은 부서 내에 최고 급여를 받는 사람이 둘 있는 경우, 어느 사람이 최고 급여자로 선택될지는 위의 SQL 문만 가지고는 판단할 수 없다.
- FIRST_VALUE
는 공동 등수를 인정하지 않고 처음 나온 행만을 처리.
공동 등수가 있을 경우에 의도적으로 세부 항목을 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 절에 칼럼을 추가해야 한다.

 

[예제] 앞의 SQL 문장에서 같은 값을 가진 FIRST_VALUE를 처리하기 위해 ORDER BY 정렬 조건을 추가한다.
SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP FROM EMP;

DEPTNO

ENAME

SAL

RICH_EMP

10

KING

5000

KING

10

CLARK

2450

KING

10

MILLER

1300

KING

20

FORD

3000

FORD

20

SCOTT

3000

FORD

20

JONES

2975

FORD

20

ADAMS

1100

FORD

20

SMITH

800

FORD

30

BLAKE

2850

BLAKE

30

ALLEN

1600

BLAKE

30

TURNER

1500

BLAKE

30

MARTIN

1250

BLAKE

30

WARD

1250

BLAKE

30

JAMES

950

BLAKE

- 부서번호 20의 최고 급여자가 이전의 SCOTT 값에서 ASCII 코드가 적은 값인 FORD로 변경 된 것을 확인할 수 있다.

. LAST_VALUE 함수 - SQL Server에서는 지원하지 않는 함수

파티션별 윈도우에서 가장 나중에 나온 값을 구한다. MAX 함수를 활용하여 같은 결과를 얻을 수도 있다.

[예제] 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다.
SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR FROM EMP;
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING:
현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

DEPTNO

ENAME

SAL

DEPT_POOR

10

KING

5000

MILLER

10

CLARK

2450

MILLER

10

MILLER

1300

MILLER

20

SCOTT

3000

SMITH

20

FORD

3000

SMITH

20

JONES

2975

SMITH

20

ADAMS

1100

SMITH

20

SMITH

800

SMITH

30

BLAKE

2850

JAMES

30

ALLEN

1600

JAMES

30

TURNER

1500

JAMES

30

MARTIN

1250

JAMES

30

WARD

1250

JAMES

30

JAMES

950

JAMES

- 공동 등수를 인정하지 않고 가장 나중에 나온 행만을 처리.
공동 등수가 있을 경우를 의도적으로 정렬하고 싶다면 별도의 정렬 조건을 가진 INLINE VIEW를 사용하거나, OVER () 내의 ORDER BY 조건에 칼럼을 추가해야 한다.

. LAG 함수 - SQL Server에서는 지원하지 않는 함수

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

[예제] 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력한다.
SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP WHERE JOB = 'SALESMAN' ;

ENAME

HIREDATE

SAL

PREV_SAL

ALLEN

1981/02/20 00:00:00

1600

 

WARD

1981/02/22 00:00:00

1250

1600

TURNER

1981/09/08 00:00:00

1500

1250

MARTIN

1981/09/28 00:00:00

1250

1500

 

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

SELECT ENAME, HIREDATE, SAL, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL FROM EMP
WHERE JOB = 'SALESMAN'

ENAME

HIREDATE

SAL

PREV_SAL

ALLEN

1981/02/20 00:00:00

1600

0

WARD

1981/02/22 00:00:00

1250

0

TURNER

1981/09/08 00:00:00

1500

1600

MARTIN

1981/09/28 00:00:00

1250

1250

- LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리

. LEAD 함수 - SQL Server에서는 지원하지 않는 함수

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

[예제] 직원들을 입사일자가 빠른 기준으로 정렬을 하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력한다.
SELECT ENAME, HIREDATE, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED" FROM EMP;

ENAME

HIREDATE

NEXTHIRED

SMITH

1980/12/17 00:00:00

1981/02/20 00:00:00

ALLEN

1981/02/20 00:00:00

1981/02/22 00:00:00

WARD

1981/02/22 00:00:00

1981/04/02 00:00:00

JONES

1981/04/02 00:00:00

1981/05/01 00:00:00

BLAKE

1981/05/01 00:00:00

1981/06/09 00:00:00

CLARK

1981/06/09 00:00:00

1981/09/08 00:00:00

TURNER

1981/09/08 00:00:00

1981/09/28 00:00:00

MARTIN

1981/09/28 00:00:00

1981/11/17 00:00:00

KING

1981/11/17 00:00:00

1981/12/03 00:00:00

JAMES

1981/12/03 00:00:00

1981/12/03 00:00:00

FORD

1981/12/03 00:00:00

1982/01/23 00:00:00

MILLER

1982/01/23 00:00:00

1987/07/13 00:00:00

SCOTT

1987/07/13 00:00:00

1987/07/13 00:00:00

ADAMS

1987/07/13 00:00:00

 

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

5. 그룹 내 비율 함수

. RATIO_TO_REPORT 함수 - SQL Server에서는 지원하지 않는 함수

파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 결과 값은 > 0 & <= 1 의 범위를 가진다. 그리고 개별 RATIO의 합을 구하면 1이 된다.

[예제] JOB SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다.
SELECT ENAME, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';

ENAME

SAL

R_R

ALLEN

1600

0.29

WARD

1250

0.22

MARTIN

1250

0.22

TURNER

1500

0.27

- 실행 결과에서 전체 값은 1650 + 1250 + 1250 + 1500 = 5600이 되고, RATIO_TO_REPORT 함수 연산의 분모로 사용된다. 그리고 개별 RATIO의 전체 합을 구하면 1이 되는 것을 확인할 수 있다. 0.29 + 0.22 + 0.22 + 0.27 = 1

. PERCENT_RANK 함수 - SQL Server에서는 지원하지 않는 함수

파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구한다. 결과 값은 >= 0 & <= 1 의 범위를 가진다.

[예제] 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0 1 사이의 값으로 출력한다.
SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R FROM EMP;

DEPTNO

ENAME

SAL

P_R

10

KING

5000

0

10

CLARK

2450

0.5

10

MILLER

1300

1

20

SCOTT

3000

0

20

FORD

3000

0

20

JONES

2975

0.5

20

ADAMS

1100

0.75

20

SMITH

800

1

30

BLAKE

2850

0

30

ALLEN

1600

0.2

30

TURNER

1500

0.4

30

MARTIN

1250

0.6

30

WARD

1250

0.6

30

JAMES

950

1

- DEPTNO 20의 경우 5건이므로 구간은 4개가 된다. 0 1 사이를 4개의 구간으로 나누면 0, 0.25, 0.5, 0.75, 1이 된다.
- SCOTT, FORD
WARD, MARTIN의 경우 ORDER BY SAL DESC 구문에 의해 급여가 같으므로 같은 ORDER로 취급한다.

. CUME_DIST 함수 - SQL Server에서는 지원하지 않는 함수

파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율. 결과 값은 > 0 & <= 1 의 범위를 가진다.

[예제] 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0 1 사이의 값으로 출력한다.
SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST FROM EMP;

DEPTNO

ENAME

SAL

CUME_DIST

10

KING

5000

0.333333333

10

CLARK

2450

0.666666667

10

MILLER

1300

1

20

SCOTT *

3000

0.4

20

FORD *

3000

0.4

20

JONES

2975

0.6

20

ADAMS

1100

0.8

20

SMITH

800

1

30

BLAKE

2850

0.166666667

30

ALLEN

1600

0.333333333

30

TURNER

1500

0.5

30

MARTIN **

1250

0.833333333

30

WARD **

1250

0.833333333

30

JAMES

950

1

- DEPTNO 10인 경우 윈도우가 전체 3건이므로 0.3333 단위의 간격을 가진다. , 0.3333, 0.6667, 1의 값이 된다.
- *
표시가 있는 SCOTT, FORD ** 표시가 있는 WARD, MARTIN의 경우 ORDER BY SAL에 의해 SAL 이 같으므로 같은 ORDER로 취급한다. 동일 순서면 뒤 행의 함수 결과값을 기준으로 한다.

. NTILE 함수

파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과

[예제] 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류
SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE FROM EMP

ENAME

SAL

QUAR_TILE

KING

5000

1

FORD

3000

1

SCOTT

3000

1

JONES

2975

1

BLAKE

2850

2

CLARK

2450

2

ALLEN

1600

2

TURNER

1500

2

MILLER

1300

3

WARD

1250

3

MARTIN

1250

3

ADAMS

1100

4

JAMES

950

4

SMITH

800

4

- NTILE(4) : 14명의 팀원을 4개 조로 나눈다는 의미.
전체 14명을 4개의 집합으로 나누면 몫이 3, 나머지가 2명이 된다. 나머지 두 명은 앞의 조부터 할당한다.
, 4 + 4 + 3 + 3명으로 조를 나누게 된다.

 

번호 제목 글쓴이 날짜 조회 수
47 5 연습문제 RYUM 2014.08.07 9534
46 5 연습문제 RYUM 2014.08.07 7509
45 5_장_요약 balto 2011.11.16 6977
44 SQL 문제 [2] file balto 2011.11.15 26818
43 제3절_조인_수행_원리 노랑배 2011.11.10 6378
42 제1절_옵티마이저와_실행계획 실천하자 2011.11.08 5274
41 제1절 옵티마이저와 실행계획 실천하자 2011.11.07 5989
40 4_연습문제 monsterRachel 2011.11.03 10834
39 4_장_요약 suspace 2011.11.03 6423
» 제6절_윈도우_함수(WINDOW_FUNCTION) suspace 2011.10.27 20530
37 제8절_절차형_SQL monsterRachel 2011.10.27 8682
36 제7절_DCL(DATA_CONTROL_LANGUAGE) file DB지기 2011.10.26 8522
35 제2절_인덱스_기본4 balto 2011.10.24 7707
34 .. balto 2011.10.14 4840
33 제1절_표준_조인(STANDARD_JOIN) 노랑배 2011.10.13 15659
32 3_연습문제 DB지기 2011.10.13 7076
31 3_장_요약 file DB지기 2011.10.13 6066
30 제3절_계층형_질의와_셀프_조인 [1] 실천하자 2011.10.12 11483
29 제2절_집합_연산자(SET_OPERATOR) [1] 실천하자 2011.10.11 13864
28 제5절_그룹_함수(GROUP_FUNCTION) file balto 2011.10.08 11424