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.
관련 링크