Erweiterungen zu Analytischen Funktionen in Oracle 21c

Analytische Funktionen

Auf Analytische Funktionen im Allgemeinen wird hier nicht mehr näher eingegangen. Eine generelle Einführung zum Thema finden Sie u .a. hier.

Anmerkung:
Wenn im Weiteren von „Fenster“ die Rede ist, so ist damit die in obigem Artikel beschriebene „Windowing-Klausel“ gemeint, nicht das hier neu vorgestellte Schlüsselwort WINDOW.

Neuerungen

Version 21c brachte drei Syntax-Erweiterungen, die nachfolgend beschrieben werden:

• WINDOW
• GROUPS
• EXCLUDE

WINDOW
Bei window handelt es sich nicht um eine Erweiterung der Funktionalität, sondern um eine Syntax-Variante, die intern durch "query transformation" in die bekannte Syntax umgewandelt wird. Sie ist besonders dann hilfreich, wenn man (annähernd) identische Klauseln für mehr als eine Spalte benötigt. Sie kann aber auch einfach die Lesbarkeit erhöhen.
Ein Beispiel anhand der altbekannten Tabelle SCOTT.EMP:

select ename, deptno,
   min(sal) over(partition by deptno) min_sal,
   max(sal) over(partition by deptno) max_sal,
   trunc(avg(sal) over(partition by deptno)) avg_sal,
   row_number() over (partition by deptno order by sal desc) rang
from emp

ENAME          DEPTNO    MIN_SAL    MAX_SAL    AVG_SAL       RANG
---------- ---------- ---------- ---------- ---------- ----------
KING               10       1300       5000       2916          1
CLARK              10       1300       5000       2916          2
MILLER             10       1300       5000       2916          3
SCOTT              20        800       3000       2175          1
FORD               20        800       3000       2175          2
JONES              20        800       3000       2175          3
ADAMS              20        800       3000       2175          4
SMITH              20        800       3000       2175          5
BLAKE              30        950       2850       1566          1
ALLEN              30        950       2850       1566          2
TURNER             30        950       2850       1566          3
MARTIN             30        950       2850       1566          4
WARD               30        950       2850       1566          5
JAMES              30        950       2850       1566          6

Hier wird für alle vier analytischen Funktionen die identische Angabe partition by deptno benötigt. Ab Version 21c kann das umformuliert werden zu:

select ename, deptno,
   min(sal) over w1 min_sal,
   max(sal) over w1 max_sal,
   trunc(avg(sal) over w1) avg_sal,
   row_number() over (w1 order by sal desc) rang
from emp
window w1 as (partition by deptno)

Wie man bei row_number sieht, ist es auch möglich, die window-Angabe in der Spaltenliste noch zu erweitern. Oder man gibt mehr als ein window an:

select ename, deptno,
   min(sal) over w1 min_sal,
   max(sal) over w1 max_sal,
   trunc(avg(sal) over w1) avg_sal,
   row_number() over (w2) rang
from emp
window w1 as (partition by deptno),
       w2 as (partition by deptno order  by sal desc)

Die Ergebnismenge ist in allen drei Schreibweisen identisch.
Wer es je mit komplexeren Ausdrücken zu tun hatte, bei denen umfangreiche analytische Klauseln vielleicht noch kombiniert werden mit klassischen Standalone-Funktionen wie NVL o. ä., wird diese neue Syntax-Variante zu würdigen wissen.

GROUPS
Fenster konnten bisher über rows (Zeilen) oder range (Werte) definiert werden. Mit Version 21c kam als dritte Variante  groups dazu, wobei identische Werte eine Gruppe bilden:

select row_number() over (order by deptno, sal) nr,
   ename, deptno, sal,
   trunc(avg(sal) over w_row) avg_sal_row,
   trunc(avg(sal) over w_range) avg_sal_range,
   trunc(avg(sal) over w_group) avg_sal_group
from emp
window w_row as (order by deptno rows between 1 preceding and current row),
     w_range as (order by deptno range between 1 preceding and current row),
     w_group as (order by deptno groups between 1 preceding and current row)      
order by deptno, sal

NR ENAME     DEPTNO    SAL AVG_SAL_ROW AVG_SAL_RANGE AVG_SAL_GROUP
--- -------- ------- ------ ----------- ------------- -------------
  1 MILLER        10   1300        1300          2916          2916
  2 CLARK         10   2450        1875          2916          2916
  3 KING          10   5000        3725          2916          2916
  4 SMITH         20    800        2900          2175          2453
  5 ADAMS         20   1100         950          2175          2453
  6 JONES         20   2975        2037          2175          2453
  7 SCOTT         20   3000        2987          2175          2453
  8 FORD          20   3000        3000          2175          2453
  9 JAMES         30    950        1975          1566          1843
 10 MARTIN        30   1250        1100          1566          1843
 11 WARD          30   1250        1250          1566          1843
 12 TURNER        30   1500        1375          1566          1843
 13 ALLEN         30   1600        1550          1566          1843
 14 BLAKE         30   2850        2225          1566          1843

rows ist rein zeilenorientiert: In diesem Beispiel wird in avg_sal_row immer der Durchschnitt gebildet zwischen den Werten zweier aufeinander folgender Zeilen.
Da range sich ausschließlich an den Inhalten orientiert und die Abteilungsnummern weiter als 1 auseinander liegen, entspricht avg_sal_range immer den Abteilungs-Durchschnitt.
groups ist eine Mischung aus beidem: Bei avg_sal_group werden alle Werte innerhalb der Gruppe (= aktuelle deptno) mit einbezogen, plus alle Werte der vorherigen Gruppe (= vorherige deptno).

EXCLUDE
Mit exclude können Zeilen oder Gruppen ausgenommen werden. Angegeben werden kann:

• EXCLUDE CURRENT ROW: Die aktuelle Zeile wird ausgeschlossen
• EXCLUDE GROUP: Die aktuelle Gruppe wird ausgeschlossen, also alle Zeilen mit dem gleichen Wert wie die aktuelle Zeile, inklusive der aktuellen Zeile
• EXCLUDE TIES: alle Zeilen mit dem gleichen Wert wie die aktuelle Zeile werden ausgeschlossen, jedoch nicht die aktuelle Zeile selber
• EXCLUDE NO OTHERS (Default): Es wird nichts ausgeschlossen
   

select   ename, sal,
   max(sal) over (w_row rows between 3 preceding and current row) avg_sal,
   max(sal) over (w_row rows between 3 preceding and current row EXCLUDE current row) max_ohne_curr,
   max(sal) over (w_row rows between 3 preceding and current row EXCLUDE group) max_ohne_grp,
   max(sal) over (w_row rows between 3 preceding and current row EXCLUDE ties) max_ohne_ties
from emp
window  w_row as (order by sal)
order by   sal   

ENAME       SAL    AVG_SAL MAX_OHNE_CURR MAX_OHNE_GRP MAX_OHNE_TIES
-------- ------ ---------- ------------- ------------ -------------
SMITH       800        800                                      800
JAMES       950        950           800          800           950
ADAMS      1100       1100           950          950          1100
WARD       1250       1250          1100         1100          1250
MARTIN     1250       1250          1250         1100          1250
MILLER     1300       1300          1250         1250          1300
TURNER     1500       1500          1300         1300          1500
ALLEN      1600       1600          1500         1500          1600
CLARK      2450       2450          1600         1600          2450
BLAKE      2850       2850          2450         2450          2850
JONES      2975       2975          2850         2850          2975
SCOTT      3000       3000          2975         2975          3000
FORD       3000       3000          3000         2975          3000
KING       5000       5000          3000         3000          5000

Hier stößt die oben beschriebene Erweiterung von window an Grenzen: Die Angabe von max(sal) over (w_row exclude…) ist (zumindest in 21c) anscheinend nicht möglich. Daher musste auch rows between 3 preceding and current row in der Spaltenliste angegeben werden und konnte nicht in die window-Klausel verlagert werden.

exclude kann hinsichtlich der Fenster-Angabe mit rows, range und groups kombiniert werden.