DATOR


3. Window 함수 ANSI 표준SQL


4.  WINDOW FUNCTION 

 

 기존 관계형 데이터베이스는 칼럼과 칼럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면 행과 행간의 관계를 정의하거나, 행과 행간을 비교, 연산하는 것을 하나의 SQL문으로 처리하는 것은 매우 어려운 작업이었다.

 대부분 이러한 작업은 PL/SQL이나 응용 프로그램을 이용하여 절차적으로 작성하거나, 혹은 INLINE VIEW 이용해 데이터를 가공하여 보여주었으나, 이러한 행과 행간의 관계를 쉽게 정의하기 위해 만들어진 함수가 WINDOW FUNCTION이다.

 WINDOW FUNCTION 다양한 측면에서 분석용도로 사용할 있으며, DW환경에서 유용하게 사용 있는 기능이다.

 

 WINDOW FUNCTION 크게 그룹 순위를 나타내는 RANKING함수 (RANK, DENSE_RANK, ROW_NUMBER), 그룹 집계(Aggregate) 내는 집계 함수 (SUM, MAX, MIN, AVG, COUNT), 그룹 비율 관련 함수 (CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT) 함수가 지원 된다.

 WINDOW 함수는 기본 적으로 아래와 같이 사용 있다.

 

SELECT WINDOW_FUNCTION (ARGS) OVER ([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING])

FROM 테이블 ;

 

- WINDOW_FUNCTION : 분석에 사용될 함수

- ARGS : 함수에 필요한 인자

- PARTITION BY : 전체 집합 소그룹을 나눌 기준 컬럼

- ORDER BY : PARTITION BY 지정 소그룹에서 정렬된 순서 (PARTITION BY 없을 전체)

- WINDOWING : 함수의 대상이 되는 기준의 범위 지정

  (1) BETWEEN 사용 타입

  ROWS | RANGE BETWEEN

   UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

    AND

   UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

 

  (2) BETWEEN 미사용 타입

  ROWS | RANGE

   UNBOUNDED PRECEDING | CURRENT_ROW | VALUE_EXPR PRECEDING

 

 (1) 그룹 순위 함수

 

그룹 순위와 관련 함수로는 RANK, DENSE_RANK, ROW_NUMBER함수를 사용한다.

 

RANK

 RANK 함수는 ORDER BY 포함한 QUERY문에서 특정 컬럼에 대한 순위를 구하는 함수이다. 특정 범위(PARTITION)내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 있다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , RANK() OVER (ORDER BY SALARY DESC)  AS ALL_RANK

  , RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC)  AS DEPT_RANK

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_RANK

DEPT_RANK

50

Adam

8200

2

1

50

Matthew

8000

3

2

50

Payam

7900

4

3

50

Shanta

6500

5

4

50

Kevin

5800

7

5

50

Nandita

4200

10

6

50

Alexis

4100

12

7

50

Sarah

4000

13

8

50

Britney

3900

14

9

50

Kelly

3800

15

10

50

Renske

3600

16

11

50

Jennifer

3600

16

11

50

Trenna

3500

18

13

50

Julia

3400

19

14

50

Laura

3300

20

15

50

Jason

3300

20

15

50

Julia

3200

22

17

50

Samuel

3200

22

17

50

Winston

3200

22

17

50

Stephen

3200

22

17

50

Alana

3100

26

21

……………….

50

TJ

2100

50

45

60

Alexander

9000

1

1

60

Bruce

6000

6

2

60

David

4800

8

3

60

Valli

4800

8

3

60

Diana

4200

10

5

 PARTITION BY 지정하지 않은 ALL_RANK 전체 사원에 급여에 대한 RANKING 부여 되었고, PARTITION BY 지정한 DEPT_RANK 각각의 부서별로 순위를 부여하였다.

 RANK함수에서는 같은 값을 가지는 값에 대해 같은 순위를 부여하되, 다음 순위에서는 자신 보다 앞선 순위의 값들에 대해서는 개별로 취급하여 순위가 부여 된다.

 , 부서별 급여순위가 11위인 Renske Jennifer 다음 순위의 급여를 받는 Trenna 13위가 되고, 급여 순위가 15위인 Laura Jason보다 낮은 급여를 받는 Julia, Samuel, Winston, Stephen 모두 16 이며, 이보다 낮은 순위인 Alana 21위가 된다.

 또한, 50 부서의 가장 작은 급여를 받는 TJ 50 부서의 인원 수에 해당하는 45위에 랭크 된다.

 

 

DENSE_RANK

 DENSE_RANK 함수는 ORDER BY 포함한 QUERY문에서 특정 컬럼에 대한 순위를 구하는 함수이다. 특정 범위(PARTITION)내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 있다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , DENSE_RANK() OVER (ORDER BY SALARY DESC)  AS ALL_RANK

, DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID

ORDER BY SALARY DESC)  AS DEPT_RANK

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_RANK

DEPT_RANK

50

Adam

8200

2

1

50

Matthew

8000

3

2

50

Payam

7900

4

3

50

Shanta

6500

5

4

50

Kevin

5800

7

5

50

Nandita

4200

9

6

50

Alexis

4100

10

7

50

Sarah

4000

11

8

50

Britney

3900

12

9

50

Kelly

3800

13

10

50

Renske

3600

14

11

50

Jennifer

3600

14

11

50

Trenna

3500

15

12

50

Julia

3400

16

13

50

Laura

3300

17

14

50

Jason

3300

17

14

50

Julia

3200

18

15

50

Samuel

3200

18

15

50

Winston

3200

18

15

50

Stephen

3200

18

15

50

Alana

3100

19

16

……………….

50

TJ

2100

28

25

60

Alexander

9000

1

1

60

Bruce

6000

6

2

60

David

4800

8

3

60

Valli

4800

8

3

60

Diana

4200

9

4

 

 PARTITION BY 지정하지 않은 ALL_RANK 전체 사원에 급여에 대한 RANKING 부여 되었고, PARTITION BY 지정한 DEPT_RANK 각각의 부서별로 순위를 부여하였다.

 DENSE_RANK함수에서는 같은 값을 가지는 값에 대해 같은 순위를 부여하되, 다음 순위에서는 자신 보다 앞선 순위의 값들에 대해서는 하나로 취급하여 순위가 부여 된다.

 , 부서별 급여순위가 11위인 Renske Jennifer 다음 순위의 급여를 받는 Trenna 12위가 되고, 급여 순위가 14위인 Laura Jason보다 낮은 급여를 받는 Julia, Samuel, Winston, Stephen 모두 15 이며, 이보다 낮은 순위인 Alana 16위가 된다.

 또한, 50 부서의 가장 작은 급여를 받는 TJ 50 부서의 인원 급여 수를 DISTINCT 수인 25개의 값을 가진다.

 

SELECT

  COUNT (DISTINCT SALARY)

FROM   employees

WHERE DEPARTMENT_ID = 50

 

 

COUNT(DISTINCTSALARY) 

----------------------

25

 

ROW_NUMBER

 ROW_NUMBER 함수는 ORDER BY 포함한 QUERY문에서 순번을 구하는 함수이다. 특정 범위(PARTITION)내에서 순위를 구할 수도 있고 전체 데이터에 대한 순번을 구할 있다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , ROW_NUMBER() OVER (ORDER BY SALARY DESC)  AS ALL_RANK

, ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID

ORDER BY SALARY DESC)  AS DEPT_RANK

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_RANK

DEPT_RANK

50

Adam

8200

2

1

50

Matthew

8000

3

2

50

Payam

7900

4

3

50

Shanta

6500

5

4

50

Kevin

5800

7

5

50

Nandita

4200

11

6

50

Alexis

4100

12

7

50

Sarah

4000

13

8

50

Britney

3900

14

9

50

Kelly

3800

15

10

50

Renske

3600

16

11

50

Jennifer

3600

17

12

50

Trenna

3500

18

13

50

Julia

3400

19

14

50

Laura

3300

20

15

50

Jason

3300

21

16

50

Julia

3200

25

17

50

Samuel

3200

24

18

50

Winston

3200

23

19

50

Stephen

3200

22

20

50

Alana

3100

26

21

……………….

50

TJ

2100

50

45

60

Alexander

9000

1

1

60

Bruce

6000

6

2

60

David

4800

9

3

60

Valli

4800

8

4

60

Diana

4200

10

5

 

 PARTITION BY 지정하지 않은 ALL_RANK 전체 사원에 급여에 대한 채번을 하였고,, PARTITION BY 지정한 DEPT_RANK 각각의 부서별로 채번을 하였다.

 ROW_NUMBER 함수도 RANKING 함수들과 비슷하게 정렬 되는 순서대로 채번을 하지만, 같은 값을 가지는 값들에 대해서 유일한 으로 채번을 한다.

 

 

(2) 그룹내 집계 함수

 

 그룹 내 집계 함수는 GROUP BY절에서 일반 적으로 사용하는 SUM, COUNT, MIN, MAX등 의 함수를 사용할 수 있다. GROUP BY와는 다르 게 행들을 GROUPING 하는 것 이 아니라 행 들을 그대로 리턴하면서 행 별로 PARTITION BY된 컬럼의 그룹의 값을 GROUPING한다.

 

COUNT

 

 WINDOW FUNCTION에서 COUNT함수는 PARTITION BY구문에 포함 된 컬럼을 기준으로 GROUPING 하여 COUNT를 세며 PARTITION BY를 입력하지 않을 시 전체를 대상으로 COUNT하고, ORDER BY 절에 있는 값을 기준으로 데이터를 정렬한다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , COUNT(*) OVER ()  AS ALL_CNT

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

ALL_CNT

50

Alana

50

50

Martha

50

………………

60

Valli

50

60

Diana

50

 

 

50번 부서와 60번 부서의 전체 사원의 수는 50명이고, GROUPING 된 결과의 값이 모든 ROW에 포함되어 결과를 리턴하였다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , COUNT(*) OVER (PARTITION BY DEPARTMENT_ID)  AS ALL_CNT

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

ALL_CNT

50

Alana

45

50

Martha

45

…………………

60

Bruce

5

60

David

5

 50번 부서는 총 45, 60번 부서는 총 5명으로 각각의 ROW에 모두 부서별 인원 수 COUNT를 구하였다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , COUNT(*) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY)  AS ALL_CNT

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_CNT

50

TJ

2100

1

50

Steven

2200

3

50

Hazel

2200

3

50

Ki

2400

5

50

James

2400

5

50

Martha

2500

10

50

Peter

2500

10

………………..

50

Adam

8200

45

60

Diana

4200

1

60

David

4800

3

60

Valli

4800

3

60

Bruce

6000

4

60

Alexander

9000

5

 

ORDER BY 구문을 이용하면, 정렬 순서를 기준으로 COUNT 를 부여한다.

 부서와 급여를 기준으로 ORDER BY 했을 시 가장 급여가 적은 TJ를 읽을 시 COUNT 1이고, 그 다음 으로 급여가 작은 Steven Hazel까지 읽었을 때 COUNT 3이 된다. 이 때 주의해서 봐야 할 점은 급여가 같을 시 두 값을 모두 읽어 2는 없고 바로 3을 가진 값이 2개가 되는 것을 알 수 있다.

 부서를 기준으로 PARTITION 했기 때문에 60번 부서로 넘어가면 다시 1번부터 시작 됨을 알 수 있다.

 

SUM

 

 WINDOW FUNCTION에서 SUM함수는 PARTITION BY구문에 포함 된 컬럼을 기준으로 GROUPING 하여 합산을 하며 PARTITION BY를 입력하지 않을 시 전체를 기준으로 합산하고, ORDER BY 절에 있는 값을 기준으로 데이터를 정렬한다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER ()  AS ALL_SUM

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

Donald

2600

185200

50

Kevin

3000

185200

………………..

60

Bruce

6000

185200

60

Alexander

9000

185200

 

50번 부서와 60번 부서의 전체 사원의 의 급여 합계는 185,200이고 GROUPING 된 결과의 값이 모든 ROW에 포함되어 결과를 리턴하였다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID)  AS DEPT_SUM

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

Donald

2600

185200

50

Kevin

3000

185200

………………..

60

Bruce

6000

185200

60

Alexander

9000

185200

 

50번 부서의 급여 합계는 156400,  60번 부서의 급여 합계는 28800으로 각각의 ROW에 모두 부서별 급여 합계를 구하였다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY)  AS DEPT_SUM

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Steven

2200

6500

50

Hazel

2200

6500

50

Ki

2400

11300

50

James

2400

11300

50

Martha

2500

23800

50

Peter

2500

23800

………………..

50

Adam

8200

156400

60

Diana

4200

4200

60

David

4800

13800

60

Valli

4800

13800

60

Bruce

6000

19800

60

Alexander

9000

28800

 

SUM의 경우도 위의 경우와 마찬가지로 ORDER BY를 사용시 현재까지 읽은 값들의 누적 합계를 내는 것을 볼 수 있다.

 

AVG

 

 WINDOW FUNCTION에서 AVG함수는 PARTITION BY구문에 포함 된 컬럼을 기준으로 GROUPING 하여 가장 평균 값을 나타내며, PARTITION BY를 사용하지 않을 시 전체의 평균 값을 나타내고, ORDER BY 절에 있는 값을 기준으로 데이터를 정렬한다.

 

SELECT

  DEPARTMENT_ID

, FIRST_NAME

, SALARY

  , AVG(SALARY) OVER ()  AS ALL_MIN

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

Donald

2600

3704

50

Kevin

3000

3704

………………..

60

Bruce

6000

3704

60

Alexander

9000

3704

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID)  AS DEPT_AVG

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

Donald

2600

3475.55

50

Kevin

3000

3475.55

………………..

60

Bruce

6000

5760

60

Alexander

9000

5760

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY)  AS DEPT_AVG

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Steven

2200

2166.666667

50

Hazel

2200

2166.666667

50

Ki

2400

2260

50

James

2400

2260

50

Martha

2500

2380

50

Peter

2500

2380

………………..

50

Adam

8200

3475.555556

60

Diana

4200

4200

60

David

4800

4600

60

Valli

4800

4600

60

Bruce

6000

4950

60

Alexander

9000

5760

 

 

MIN

 

 WINDOW FUNCTION에서 MIN함수는 PARTITION BY구문에 포함 된 컬럼을 기준으로 GROUPING 하여 가장 작은 값을 나타내며, PARTITION BY를 사용하지 않을 시 전체 중 가장 작은 값을 나타내고, ORDER BY 절에 있는 값을 기준으로 데이터를 정렬한다.

 

SELECT

  DEPARTMENT_ID

, FIRST_NAME

, SALARY

  , MIN(SALARY) OVER ()  AS ALL_MIN

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

Donald

2600

2100

50

Kevin

3000

2100

………………..

60

Bruce

6000

2100

60

Alexander

9000

2100

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID)  AS DEPT_MIN

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

Donald

2600

2100

50

Kevin

3000

2100

………………..

60

Diana

4200

4200

60

Bruce

6000

4200

60

Bruce

6000

4200

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY)  AS DEPT_MIN

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Steven

2200

2100

50

Hazel

2200

2100

50

Ki

2400

2100

50

James

2400

2100

50

Martha

2500

2100

50

Peter

2500

2100

………………..

50

Adam

8200

2100

60

Diana

4200

4200

60

David

4800

4200

60

Valli

4800

4200

60

Bruce

6000

4200

60

Alexander

9000

4200

 

 

 

 

 

 

MAX

 

 WINDOW FUNCTION에서 MAX함수는 PARTITION BY구문에 포함 된 컬럼을 기준으로 GROUPING 하여 가장 큰 값을 구하고, PARTITION BY를 입력하지 않을 시 전체 중 가장 큰 값을 구하며, ORDER BY 절에 있는 값을 기준으로 데이터를 정렬한다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , MAX(SALARY) OVER ()  AS ALL_MAX

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_MAX

50

Donald

2600

9000

50

Kevin

3000

9000

………………..

60

Alexander

9000

9000

60

Bruce

6000

4200

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID)  AS DEPT_MAX

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_MAX

50

Donald

2600

8200

50

Kevin

3000

8200

………………..

60

Bruce

6000

9000

60

Alexander

9000

9000

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY)  AS DEPT_MAX

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Steven

2200

2200

50

Hazel

2200

2200

50

Ki

2400

2400

50

James

2400

2400

50

Martha

2500

2500

50

Peter

2500

2500

………………..

50

Adam

8200

8200

60

Diana

4200

4200

60

David

4800

4800

60

Valli

4800

4800

60

Bruce

6000

6000

60

Alexander

9000

9000

 

 

 

(3) ROWS/RANGE 활용

 

 ROWS/RANGE 활용은 행간 연산 범위를 지정 할 때 활용 된다. 기본적으로 지정하지 않으면 PARTITION BY로 지정한 전체 범위를 처리하지만, ROWS/RANGE를 지정할 경우 지금까지의 누적합계, 최근 몇 달 간의 합계 등의 연산이 가능하다.

 ROWS/RANGE 사용시는 아래와 같은 명령어들이 활용 가능하다.

 

ROWS : 현재 행을 기준으로 몇 개의 행을 포함 할지 지정.

RANGE : 현재 행을 기준으로 어떤 값의 범위를 포함하는 지 지정.

UNBOUNDED : 한계를 두지 않고 해당 파티션의 끝까지 지정.

PRECENDING : 현재 행을 기준으로 앞에 몇 행을 기준으로 연산할지를 지정한다. 예를 들어 3 PRECENDING이라 지정하면 3행 전, UNBOUNDED PRECENDING이라 지정 하면 지금까지 읽은 모든 행을 포함한다.

FOLLOWING : 현재 행을 기준으로 뒤에 몇 행을 기준으로 연산할지를 지정한다. 예를 들어 3 FOLLOWING이라하며 3행 뒤, UNBOUNDED PRECENDING이라 하면 앞으로 읽을 모든 행을 포함한다.

 

RANGE

 

(1) UNBOUNDED

UNBOUNDED는 한계를 두지 않고 파티션의 끝까지를 지정하기 때문에 파티션의 최초의 값, 최종 값을 기준으로 집계를 하고자 할 때 사용 한다.

 

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY

  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DEPT_SAL

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

156400

50

Steven

2200

156400

………………..

60

Bruce

6000

28800

60

Alexander

9000

28800

 

 앞선 예제에서 ORDER BY절을 이용했을 경우는 현재 까지 읽은 값들에 대한 누적 값을 구하였다.

 하지만 RANGE의 범위를 파티션의 최초 값(UNBOUNDED PRECEDING) 부터 최종 값(UNBOUNDED FOLLOWING)으로 지정하였기 때문에 각각 파티션 별의 총합으로 값이 출력 되었다.

 , RANGE UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 사용 시 결과는 아래와 같다.

SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID)

 

한가지 주의 할 점은 RANGE BETWEEN구문을 사용 시에는 반드시 ORDER BY 구문이 있어야 한다.

 ORDER BY구문이 없을 시 아래와 같은 에러가 발생한다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID

  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DEPT_SAL

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

ORA-30485: 윈도우 지정에 ORDER BY 표현식이 없습니다

30485. 00000 -  "missing ORDER BY expression in the window specification"

*Cause:    Either the ORDER BY expression is mandatory for this function, or

           there is an aggregation group without any ORDER by expression.

*Action:

5, 5열에서 오류 발생

 

 이와 같은 오류가 발생하는 이유는 RANGE BETWEEN을 현재 행을 기준으로 이전/이후 값에 대한 정렬 된 기준 값이 필요하기 때문이다.

 

(2) CURRENT ROW

 CURRENT ROW는 의미 그대로 현재 행을 기준으로 집계하고자 할 때 사용한다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DEPT_SAL

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Steven

2200

6500

50

Hazel

2200

6500

50

Ki

2400

11300

50

James

2400

11300

50

Martha

2500

23800

50

Peter

2500

23800

………………..

50

Adam

8200

156400

60

Diana

4200

4200

60

David

4800

13800

60

Valli

4800

13800

60

Bruce

6000

19800

60

Alexander

9000

28800

50

Irene

2700

37000

 

UNBOUNDED PRECEDING 부터 CURRENT ROW는 즉, PARTITION의 최초 값부터 현재 값까지의 합계를 의미 한다.

위 값은 앞선 예제에서 RANGE BETWEEN을 사용하지 않았을 경우와 동일하다.

, Window 함수에서 ORDER BY절을 기술하였을 경우의 Default 값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 인 것 을 알 수 있다.

 

 

ROWS

 

RANGE 구문을 사용하였을 시 값의 범위에 대한 지정이기 때문에 같은 값을 가지는 ROW들에 대해서는 하나의 범위로 지정하였으나, ROWS 구문을 사용할 경우에는 바로 이전 패치 된 행에 현재 행의 값을 더한다.

 

(1) 현재 행을 기준으로 누적 합 계산

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY

  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS DEPT_SAL

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Steven

2200

4300

50

Hazel

2200

6500

50

Ki

2400

8900

50

James

2400

11300

50

Martha

2500

13800

50

Peter

2500

16300

………………..

50

Adam

8200

156400

60

Diana

4200

4200

60

David

4800

9000

60

Valli

4800

13800

60

Bruce

6000

19800

60

Alexander

9000

28800

 

RANGE와 비교하여 누적 합을 보여주는 부분은 같으나, RANGE의 경우 같은 값을 하나의 범위로 처리 한 반면, ROWS의 경우 값이 같더라도 바로 이전 행의 값에 대해서만 누적된 값을 가지며 패치 되는 순서에 따라 값이 다르게 나온 것을 알 수 있다.

 그렇기 때문에 정렬 순서가 다르게 되면 아래와 같이 같은 ROW에 대해 다른 값이 나올 수 있다.

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Hazel

2200

4300

50

Steven

2200

6500

50

James

2400

8900

50

Ki

2400

11300

50

James

2500

13800

50

Joshua

2500

16300

………………..

50

Adam

8200

156400

60

Diana

4200

4200

60

David

4800

9000

60

Valli

4800

13800

60

Bruce

6000

19800

60

Alexander

9000

28800

 

 같은 2200의 급여를 받는 Hazel Steven의 경우, 이름에 대한 정렬이 없을 경우에는 임의로 패치 해 오는 순서에 따라 Steven이 먼저 패치 되었고, 그에 따라 Steven의 누적 합 값이 4300, Hazel의 누적 합 값이 6500이었으나, 정렬에 이름을 추가하자 알파벳 순서에 따라 Hazel이 먼저 패치 되었고, Hazel의 누적 합이 4300, Steven의 누적 합이 6500이 되었다.

 이 결과는 즉, 같은 값이 있다면, 패치 되는 순서에 따라 값이 달라질 수 있다는 점 이다.

 

(2) 상수 값을 이용한 전 후 행의 계산

 

UNBOUNDED를 이용할 경우 최초, 혹은 최종 값을 기준으로 값을 구하지만, 상수 값을 부여하여 이전 몇 행, 이 후 몇 행의 값을 구할 수 있다.

 

n  바로 이전 행과 현재 행의 합

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY

  ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS DEPT_SAL

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

2100

50

Steven

2200

4300

50

Hazel

2200

4400

50

Ki

2400

4600

50

James

2400

4800

50

Martha

2500

4900

50

Peter

2500

5000

………………..

50

Adam

8200

16200

60

Diana

4200

4200

60

David

4800

9000

60

Valli

4800

9600

60

Bruce

6000

10800

60

Alexander

9000

15000

 

PRECEDING 앞에 상수 1을 기술하여 바로 전행과 현재 행 까지의 합을 구하였다.

파티션 별 가장 먼저 패치 된 TJ Diana의 경우 이전 행이 없기 때문에 자기의 값이 그대로 나왔지만, 이 후 패치 되는 행들은 바로 이전에 패치 된 행의 급여와 자신의 급여만 합한 결과가 나타난 것을 알 수 있다.

 

n  현재 행을 기준으로 이전/이후 행의 합

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY

  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS DEPT_SAL

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

ALL_SUM

50

TJ

2100

4300

50

Steven

2200

6500

50

Hazel

2200

6800

50

Ki

2400

7000

50

James

2400

7300

50

Martha

2500

7400

50

Peter

2500

7500

………………..

50

Adam

8200

16200

60

Diana

4200

9000

60

David

4800

13800

60

Valli

4800

15600

60

Bruce

6000

19800

60

Alexander

9000

15000

 

 1 PRECEDING으로 이전 행을, 1 FOLLOWING으로 다음 행을 가져와 이전 행, 현재 행, 다음 행의 합을 구하였다.

 이 처럼 ROWS BETWEEN 기능을 이용해 행과 행간의 연산을 수월하게 할 수 있음을 알아보았다.

 

 

(4) 순서 함수

 순서 함수는 그룹 내 행간 순서의 대한 값을 알 수 있는 함수이다. 순서 함수는 해당 파티션에서 가장 최초로 패치 된 값, 마지막으로 패치 된 값과 이전 몇 행전의 값, 이후 몇 행의 값 등을 알 수 있다.

 

FIRST_VALUE

 

 FIRST_VALUE는 파티션 별 정렬 된 값 중 가장 먼저 패치 된 값을 가져온다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , FIRST_VALUE(FIRST_NAME) OVER (PARTITION BY DEPARTMENT_ID ) AS F_FIRST_NAME

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

F_FIRST_NAME

50

Donald

2600

Donald

50

Kevin

3000

Donald

50

Douglas

2600

Donald

50

Alana

3100

Donald

50

Vance

2800

Donald

50

Samuel

3200

Donald

50

Britney

3900

Donald

………………..

50

Sarah

4000

Donald

60

Diana

4200

Diana

60

Valli

4800

Diana

60

David

4800

Diana

60

Bruce

6000

Diana

60

Alexander

9000

Diana

 

 위의 쿼리의 결과를 보자면, 파티션은 나누었지만, 특별히 정렬 되는 값이 없이 패치가 되었고, FIRST_VALUE를 기준으로 추출한 F_FIRST_NAME의 값은 50번 부서의 Donald, 60번 부서의 Diana가 추출되었다.

 이대로 쿼리의 결과를 보자면 패치 되는 순서는 DBMS의 파일 시스템의 구조나 쿼리의 힌트 등에 따라 결과가 다르게 추출될 수 있다.

 간단한 테스트를 하나 더 진행해 보도록한다.

SELECT /*+ FULL(A) PARALLEL(A, 2) */

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , FIRST_VALUE(FIRST_NAME) OVER (PARTITION BY DEPARTMENT_ID ) AS F_FIRST_NAME

FROM   employees A

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

F_FIRST_NAME

50

Matthew

8000

Matthew

50

Adam

8200

Matthew

50

Donald

2600

Matthew

50

Kevin

3000

Matthew

50

Alana

3100

Matthew

50

Vance

2800

Matthew

50

Samuel

3200

Matthew

………………..

50

Douglas

2600

Matthew

60

Alexander

9000

Alexander

60

Bruce

6000

Alexander

60

Diana

4200

Alexander

60

Valli

4800

Alexander

60

David

4800

Alexander

 

위 쿼리는 쿼리의 내용은 완전히 동일하나, HINT를 이용해 병렬처리를 하니 F_FIRST_NAME의 값이 완전히 틀려졌다. 이는 하나의 프로세스가 작업하는 것과 두 개의 프로세스가 작업하는 방식이 틀려 패치의 순서가 변경 된 것이고, 그렇기 때문에 최초의 패치가 되는 값이 변경 되었기 때문이다.

패치가 되는 순서는 파일 구조가 변경되거나, 실행 계획이 변경되면 결과가 달라질 수 있기 때문에 그룹 내 행 순서 함수에서는 ORDER BY 절을 기술해 주는 것을 권장한다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , FIRST_VALUE(FIRST_NAME) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS F_FIRST_NAME

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

F_FIRST_NAME

50

TJ

2100

TJ

50

Hazel

2200

TJ

50

Steven

2200

TJ

50

James

2400

TJ

50

Ki

2400

TJ

50

James

2500

TJ

50

Joshua

2500

TJ

………………..

50

Adam

8200

TJ

60

Diana

4200

Diana

60

David

4800

Diana

60

Valli

4800

Diana

60

Bruce

6000

Diana

60

Alexander

9000

Diana

 

 위의 쿼리는 ORDER BY절을 추가하니, 파티션 별로 급여 순으로 정렬이 되었고, 정렬 된 값 중 가장 먼저 패치 된 ROW는 실행계획이나 파일 시스템의 상태에 관계 없이 50번 부서의 TJ 60번 부서의 Diana가 출력이 될 것 이다..

 

LAG

 

 LAST_VALUE 는 파티션 별 정렬 된 값 중 가장 마지막으로 패치 된 값을 가져온다.

 

SELECT

  DEPARTMENT_ID

  , FIRST_NAME

  , SALARY

  , LAST_VALUE(FIRST_NAME) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS L_FIRST_NAME

FROM   employees

WHERE DEPARTMENT_ID BETWEEN 50 AND 60

ORDER BY DEPARTMENT_ID;

 

DEPARTMENT_ID

FIRST_NAME

SALARY

L_FIRST_NAME

50

TJ

2100

TJ

50

Hazel

2200

Steven

50

Steven

2200

Steven

50

James

2400

Ki

50

Ki

2400

Ki

50

James

2500

Randall

50

Joshua

2500

Randall

………………..

50

Adam

8200

Adam

60

Diana

4200

Diana

60

David

4800

Valli

60

Valli

4800

Valli