Neu in Oracle 26ai: FILTER in Gruppenfunktionen

Die Oracle Datenbankversion 26ai stellt ein weiteres äußerst nützliches Feature in Verbindung mit Gruppenfunktionen zur Verfügung. Jede in der SELECT-Liste verwendete Gruppenfunktion kann mit einer FILTER-Klausel erweitert werden. Die FILTER-Klausel enthält eine WHERE-Klausel mit einer oder mehreren Bedingungen, die für die Ermittlung einer einzelnen Kennzahl verwendet wird.

Beispiel:

Wir wollen folgende Kennzahlen aus der Tabelle employees ermitteln:

  - Anzahl aller Beschäftigten
  - Anzahl der Programmierer
  - Anzahl der Verkäufer
  - Anzahl aller Manager

  ANZ_ALLE    ANZ_PROG   ANZ_VERK    ANZ_MGR 
---------- ----------- ---------- ---------- 
       107           5         30         14

Vor 26ai:

Um das Ergebnis zu erhalten, kann mit Unterabfragen für die Ermittlung jeder einzelnen Kennzahl gearbeitet werden. Diese Variante ist auf die Performance bezogen eine ungünstige Lösung.

SELECT
   (SELECT COUNT(*) FROM employees)                                           AS anz_alle,
   (SELECT COUNT(*) FROM employees WHERE job_id LIKE '%PROG')                 AS anz_prog,
   (SELECT COUNT(*) FROM employees WHERE job_id = 'SA_REP')                   AS anz_verk,
   (SELECT COUNT(*) FROM employees WHERE substr(job_id,4) IN ('MAN', 'MGR'))  AS anz_mgr
FROM  dual;

Alternativ kann in den Gruppenfunktionen mit CASE-Ausdrücken gearbeitet werden, was zu einer deutlich besseren Performance führt, aber die Verständlichkeit des Codes erschwert.

SELECT COUNT(*)                                                        AS anz_alle,
       COUNT(CASE WHEN job_id LIKE '%PROG' THEN 1 END)                 AS anz_prog,
       COUNT(CASE WHEN job_id = 'SA_REP' THEN 1 END)                   AS anz_verk,
       COUNT(CASE WHEN substr(job_id,4) IN ('MAN', 'MGR') THEN 1 END)  AS anz_mgr
FROM   employees;

In 26ai:

Die neu unterstützte, ANSI-konforme Syntax mit der FILTER-Klausel macht die Anweisung deutlich übersichtlicher und besser wartbar. Die interne Abarbeitung der Anweisung gleicht der mit der Verwendung von CASE-Ausdrücken in den Gruppenfunktionen.

SELECT COUNT(*) AS anz_alle,
       COUNT(*) FILTER (WHERE job_id LIKE '%PROG') AS anz_prog,
       COUNT(*) FILTER (WHERE job_id = 'SA_REP') AS anz_verk,
       COUNT(*) FILTER (WHERE substr(job_id,4) IN ('MAN', 'MGR')) AS anz_mgr
FROM   employees;

Weitere Einsatzmöglichkeiten:

Selbstverständlich kann die FILTER-Klausel in allen Aggregatfunktionen, aber auch in analytischen Funktionen zum Einsatz kommen.

Im folgenden Beispiel sollen für jeden Mitarbeiter neben den Spalteninformationen Abteilungsnummer, Mitarbeiternummer, Mitarbeitername und aktuelles Gehalt auch folgende Daten sortiert nach der Rangfolge ausgegeben werden

  - Rangposition innerhalb seiner Abteilung bezogen auf das Gehalt
  - Anzahl aller Mitarbeiter innerhalb seiner Abteilung
  - Anzahl der Top-Verdiener seiner Abteilung mit einem Gehalt über 10.000
  - Anzahl der Mittelfeld-Verdiener seiner Abteilung mit einem Gehalt ab 4.000 und unter 10.000

Für die Berechnung der letzten beiden Kennzahlen kommt die FILTER-Klausel zum Einsatz.

SELECT department_id, employee_id, last_name, salary,
       RANK()   OVER (PARTITION BY department_id ORDER BY salary DESC)   AS rang,
       COUNT(*) OVER (PARTITION BY department_id)                        AS anz_alle,
       COUNT(*) FILTER (WHERE salary > 10000)
	        OVER (PARTITION BY department_id)                        AS anz_top_verdiener,
       COUNT(*) FILTER (WHERE salary >= 4000 and salary < 10000)
	        OVER (PARTITION BY department_id)                        AS anz_mittel_verdiener
FROM   employees
ORDER BY  rang;

DEPARTMENT_ID  EMPLOYEE_ID  LAST_NAME		SALARY	    RANG   ANZ_ALLE  ANZ_TOP_VERDIENER  ANZ_MITTEL_VERDIENER
-------------  -----------  -------------  -----------  -------- ----------  -----------------  --------------------
           10          200  Whalen                4400         1          1                  0                     1
           20          201  Martinez             13000         1          2                  1                     1
           30          114  Li                   11000         1          6                  1                     0
           40          203  Jacobs                6500         1          1                  0                     1
           50          121  Fripp                 8200         1         45                  0                     8
           60          103  James                 9000         1          5                  0                     5
           70          204  Brown                10000         1          1                  0                     0
           80          145  Singh                14000         1         34                  8                    23
           90          100  King                 24000         1          3                  3                     0
          100          108  Gruenberg            12008         1          6                  1                     5
          110          205  Higgins              12008         1          2                  1                     1
          . . .

Zusammenfassung:

Die FILTER-Klausel ist ein praktisches Feature in Verbindung mit Gruppenfunktionen und analytischen Funktionen, welche den Code kompakter und deutlich verständlicher macht.
Die Fehlerwahrscheinlichkeit sinkt, da Unterabfragen, komplexe Ausdrücke sowie Joins entfallen. 
Sie eignet sich ideal für Abfragen mit mehreren Kennzahlen.