QUALIFY-Klausel erweitert analytische Funktionen ab Oracle 26ai

Die logische Reihenfolge für die Abarbeitung der einzelnen Klauseln von SELECT-Anweisungen mit analytischen Funktionen ist folgende

• JOIN-Operationen
• WHERE-Klausel
• GROUP BY-Klausel
• HAVING-Klausel
• Analytische Funktionen aus der SELECT-Liste
• ORDER BY-Klausel

Das Problem

Die Zeilenfilter der WHERE-Klausel werden grundsätzlich vor den analytischen Funktionen ausgewertet. Sollen aber die Rückgabewerte der analytischen Funktion gefiltert werden, musste das SQL in eine Unterabfrage eingebettet werden und die Hauptabfrage enthielt die notwendigen Filterbedingungen.

Ein Beispiel:

Klassische Top-1-Abfrage pro Gruppe: Wer ist der am besten verdienende Mitarbeiter pro Abteilung

SELECT *
FROM (SELECT last_name, department_id, salary,
		RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
			AS dept_rank
	FROM employees)
WHERE dept_rank = 1;

Das Ergebnis:

Der Nachteil:

Die Filterlogik wandert nach außen. Der Code wird aufgebläht und damit weniger verständlich.

Die Lösung:

Oracle stellt mit der Version 26ai die QUALIFY-Klausel bereit. Die Klausel enthält eine oder mehrere Bedingungen, mit denen das Ergebnis der analytischen Funktionen direkt gefiltert werden kann, ohne eine zusätzliche Unterabfrage zu nutzen.

Die Syntax sieht wie folgt aus:

QUALIFY {<spalte> | <ausdruck> | <alias>} <vergleichsoperator> <vergleichswert> 
	[ AND | OR <bedingung2> ... ]

In der SELECT-Anweisung steht die QUALIFY-Klausel direkt vor der ORDER BY-Klausel, bzw. ist sie die letzte Klausel, falls keine Sortierung gewünscht ist.

Das Beispiel mit QUALIFY:

SELECT employee_id, department_id, salary, 
		RANK() OVER (PARTITION BY department_id 
			ORDER BY salary DESC) AS dept_rank 
FROM employees 
QUALIFY dept_rank = 1;

Falls im Ergebnisbericht der Rückgabewert der analytischen Funktion nicht gefordert ist, muss diese nicht mehr in die SELECT-Liste aufgenommen werden.

SELECT employee_id, department_id, salary 
FROM employees 
QUALIFY RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) = 1;

Die Vorteile:

Die Anweisung wird kürzer und verständlicher. Ein Wechsel zu Oracle wird erleichtert, da Oracle nun auch die Klausel unterstützt.

Ein umfangreicheres Beispiel:

Aus dem SH-Schema sollen Produkte mit gut lesbaren Umsatzzahlen aufgelistet werden, die zu den drei umsatzstärksten im jeweiligen Jahr gehörten, aber auch die, die im Vergleich zum Vorjahr einen Umsatzeinbruch von mehr als 75 % hatten.

Benötigt werden hierfür die Tabellen sales, products und times, gruppiert wird nach dem Jahr und dem Produkt, angezeigt werden die formatierten Umsatzzahlen und die prozentuale Umsatzveränderung zum Vorjahr, die QUALIFY-Klausel arbeitet in den Bedingungen mit numerischen Vergleichswerten.

SELECT t.calendar_year AS year, p.prod_name,
		RANK() OVER (PARTITION BY t.calendar_year
			ORDER BY SUM(s.amount_sold) DESC) AS rank,
		TO_CHAR(SUM(s.amount_sold), 'fm999g999g990d99') AS sales_act_year_f,
		TO_CHAR(LAG(SUM(s.amount_sold))
			OVER (PARTITION BY p.prod_id
				ORDER BY t.calendar_year), 'fm999g999g990d99')
			AS sales_prev_year_f,
		ROUND(
			((SUM(s.amount_sold) - LAG(SUM(s.amount_sold)) OVER (PARTITION BY p.prod_id ORDER BY t.calendar_year))
			/ LAG(SUM(s.amount_sold)) OVER (PARTITION BY p.prod_id ORDER BY t.calendar_year)) * 100, 2)
				AS sales_change_percent
FROM sales s
JOIN products p ON s.prod_id = p.prod_id
JOIN times t ON s.time_id = t.time_id
GROUP BY t.calendar_year, p.prod_id, p.prod_name
QUALIFY prod_rank <= 3
	OR TO_NUMBER(sales_act_year_f, 'fm999g999g990d99')
		< TO_NUMBER(sales_prev_year_f, 'fm999g999g990d99')*0.25
ORDER BY calendar_year;

Das Ergebnis: