Oracle 21c에서 분석 함수의 기능이 개선되었습니다. 이번 글에서 관련 내용을 살펴보겠습니다.

 

준비

테스트 버전은 아래와 같습니다.

-- 1
SELECT version_full FROM product_component_version;
 
VERSION_FULL
------------
21.3.0.0.0
 
1 row selected.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 2
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
 
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 1);
INSERT INTO t1 VALUES (3, 2);
INSERT INTO t1 VALUES (4, 2);
INSERT INTO t1 VALUES (5, 4);
INSERT INTO t1 VALUES (6, 4);
COMMIT;

 

GROUPS 키워드

첫 번째로 분석 함수의 WINDOWING 절에 그룹 단위로 윈도우를 설정할 수 있는 GROUPS 키워드가 추가되었습니다.

{ ROWS | RANGE | GROUPS } {BETWEEN ...}

 

ROWS, RANGE, GROUPS 키워드는 각각 아래와 같이 동작합니다.

  • ROWS: 로우 단위로 윈도우를 설정 (표현식 = 로우 수)
  • RANGE: 값의 범위로 윈도우를 설정 (표현식 = 값을 가감)
  • GROUPS: 그룹 단위로 윈도우를 설정 (표현식 = 그룹 개수)

 

아래의 쿼리의 결과에서 세 가지 키워드의 동작을 확인할 수 있습니다.

-- 3
SELECT c1, c2
     , COUNT (*) OVER (ORDER BY c2 ROWS   BETWEEN 1 PRECEDING AND CURRENT ROW) AS count_rows
     , COUNT (*) OVER (ORDER BY c2 RANGE  BETWEEN 1 PRECEDING AND CURRENT ROW) AS count_range
     , COUNT (*) OVER (ORDER BY c2 GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS count_groups_1
     , COUNT (*) OVER (ORDER BY c2 GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW) AS count_groups_2
  FROM t1;
 
C1 C2 COUNT_ROWS COUNT_RANGE COUNT_GROUPS_1 COUNT_GROUPS_2
-- -- ---------- ----------- -------------- --------------
 1  1          1           2              2              2
 2  1          2           2              2              2
 3  2          2           4              4              4
 4  2          2           4              4              4
 5  4          2           2              4              6
 6  4          2           2              4              6
 
6 rows selected.

 

EXCLUDE 옵션

두 번째로 분석 함수의 WINDOWING 절에 윈도우 내에서 로우를 제외할 수 있는 EXCLUDE 옵션이 추가되었습니다.

{ ROWS | RANGE | GROUPS } {BETWEEN ...} [EXCLUDE { CURRENT ROW | GROUPS | TIES | NO OTHERS }]

 

아래의 네 가지 EXCLUDE 옵션을 사용할 수 있습니다.

  • EXCLUDE CURRENT ROW: 현재 로우를 제거
  • EXCLUDE GROUP: 현재 로우를 포함한 현재 로우와 값이 같은 모든 로우를 제거
  • EXCLUDE TIES: 현재 로우를 제외한 현재 로우와 값이 같은 모든 로우를 제거
  • EXCLUDE NO OTHERS: 로우를 제거하지 않음 (기본값)

 

아래 쿼리의 결과에서 네 가지 EXCLUDE 옵션의 동작을 확인할 수 있습니다.

-- 4
SELECT c1, c2
     , COUNT (*) OVER (ORDER BY c2 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS exclude_current_row
     , COUNT (*) OVER (ORDER BY c2 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE GROUP)       AS exclude_group
     , COUNT (*) OVER (ORDER BY c2 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE TIES)        AS exclude_ties
     , COUNT (*) OVER (ORDER BY c2 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS)   AS exclude_no_others
  FROM t1;
 
C1 C2 EXCLUDE_CURRENT_ROW EXCLUDE_GROUP EXCLUDE_TIES EXCLUDE_NO_OTHERS
-- -- ------------------- ------------- ------------ -----------------
 1  1                   0             0            1                 1
 2  1                   1             0            1                 2
 3  2                   1             1            2                 2
 4  2                   1             0            1                 2
 5  4                   1             1            2                 2
 6  4                   1             0            1                 2
 
6 rows selected.

 

WINDOW 절

마지막으로 SELECT 문에 WINDOWING 절을 선언할 수 있는 WINDOWS 절이 추가되었습니다. WINDOWS 절로 선언한 윈도우 명은 분석 함수와 다른 WINDOWS 절에서 사용할 수 있습니다. 참고로 WINDOWS 절은 HAVING 절 뒤, ORDER BY 절 앞에 위치해야 합니다.

WINDOW window_name AS (window_specification) [, window_name AS (window_specification)]...

 

아래 쿼리는 2개의 WINDOWS 절을 사용합니다.

-- 5
SELECT c1, c2
     , COUNT (*) OVER (w1 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS count_w1
     , COUNT (*) OVER w2 AS count_w2
  FROM t1
WINDOW w1 AS (ORDER BY c2)
     , w2 AS (w1 GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW);
 
C1 C2 COUNT_W1 COUNT_W2
-- -- -------- --------
 1  1        1        2
 2  1        2        2
 3  2        2        4
 4  2        2        4
 5  4        2        4
 6  4        2        4
 
6 rows selected.

 

관련 링크

저작자표시 비영리 변경금지

티스토리툴바