Inhaltsverzeichnis
Oracle SQL Pattern Matching Funktion in der Praxis einsetzen
ab Oracle 12
Aufgabe
Auf einer Tabelle mit Kontodaten soll ermittelt werden, wie viele Tage ein Kunden von wann bis wann in der Vergangenheit im Verzug war.
(Fachliches Thema im Detail siehe ⇒ https://blogs.pwc.de/regulatory/aktuelles/finale-eba-leitlinien-und-rts-zur-definition-des-schuldnerausfalls/2132/
Mit PL/SQL kann nun zum Beispiel einfach ein Zähler über alle Stichtage auf der Konto Tabelle laufen und je nach Kontostand einen Zähler hochzählen.
Wie kann das aber für Analysen auch in SQL gelöst werden?
Hier bietet sich das Feature Oracle Pattern Matching an.
Wir suchen folgendes Muster in den Daten:
- Aktueller Stichtag Kontostand unter 0 ⇒ Start Zähler Kontostand unter 0
- Solange weitere Stichtage untersuchen, bis der Kontostand wieder über 0 beträgt
Ausgabe: Von wann bis wann war das Konto überzogen
Lösung mit Oracle SQL Pattern Matching:
SELECT * FROM konten MATCH_RECOGNIZE ( -- gruppiern nach kundennummer PARTITION BY KDNR ORDER BY datum -- Was messen wir MEASURES FIRST(STRT.datum) AS datum_before , FIRST(DowABSLimit.datum) AS first_Dow_ABSLimit , LAST(DowABSLimit.datum) AS last_DowABSLimit_datum , FIRST(UPABSLimit.datum) AS fist_Up_ABSLimit , LAST(UPABSLimit.datum) AS last_UPABSLimit_datum , NEXT(UPABSLimit.datum) AS next_Up_ABS_Limit , trunc(FIRST(UPABSLimit.datum)) - trunc(FIRST(DowABSLimit.datum)) AS days_down_under_limit , round(avg(DowABSLimit.BETRAG)) AS avg_BETRAG , MATCH_NUMBER() AS match_number , CLASSIFIER() AS classifier one ROW per MATCH -- for anlayse show all values between -- all rows per match after MATCH skip TO LAST UPABSLimit -- define the check pattern values pattern (STRT DowABSLimit+ UPABSLimit+ ) SUBSET STDN= (STRT, DowABSLimit) -- rules for pattern DEFINE DowABSLimit AS (BETRAG) < 0 , UPABSLimit AS NEXT(BETRAG) >= 1 ) KTO ORDER BY 2 DESC ;
Wie erstellen wir aber so ein komplexes SQL und wie stellen wir fest ob das so überhaupt funktioniert?
Lösungsweg
SQL Pattern Matching gehört nicht zu den selbsterklärenden Featuren der Oracle Datenbank. alleine einen Einsatzzweck oder gar ein Beispiel zu finden, das nicht die üblichen Börsenkurs oder Wetter Schwankungen beinhaltet, ist schwer.
Um das ganze besser zu verstehen habe ich mir einen Test Daten Satz für ein fiktives Konto erzeugt, die erzeugten Daten nach Excel geladen und visualisiert und dann per SQL Abfrage auf die Test Tabelle geprüft, ob das gewünschte Ergebnis auch erzielt werden kann.
Test Daten generieren
Anlegen einer Test Tabelle Konten und füllen mit zufälligen Werten:
CREATE TABLE Konten ( KDNR NUMBER(11) , datum DATE , betrag NUMBER(11,2) ) ; DECLARE v_val NUMBER(11,2):=1000; v_datum DATE:=sysdate; BEGIN FOR i IN 1 .. 1000 loop v_val:=v_val+(100*dbms_random.NORMAL); v_datum:=sysdate-i; IF MOD(i,50)=0 THEN v_val:=(v_val*dbms_random.NORMAL)+100; END IF; dbms_output.put_line(' -- Datum :: '||v_datum || ' => Betrag :: '||v_val ); INSERT INTO konten ( KDNR, datum, betrag) VALUES (1000, v_datum ,v_val ); END loop; END; /
CSV eerzugen um nach Excel zu laden:
SET echo off SET verify off SET feedback off SET heading off SET termout off SET markup CSV ON spool import_to_excel.csv SELECT to_char(datum,'dd.mm.yyyy') AS datum ,to_char(betrag,'99999999D99','NLS_NUMERIC_CHARACTERS=,.') AS betrag FROM konten ORDER BY datum DESC / spool off SET markup CSV OFF SET termout ON SET feedback ON SET heading ON SET echo ON SET verify ON
In Excel eine Bereich der Daten visualisieren:
Wir wollen zum Beispiel genau diesen Datenbereich finden:
Mit der SQL Query von oben klappt das.
Aber warum und wie funktioniert das ganze überhaupt?
Hier nochmal unsere Abfrage:
SELECT * FROM konten MATCH_RECOGNIZE ( -- gruppiern nach kundennummer und Zeilen nach Datum sortieren -- pro Row in der Partition wird das Pattern angewandt PARTITION BY KDNR ORDER BY datum -- Was messen wir wenn das Pattern gefunden werden kann MEASURES FIRST(STRT.datum) AS datum_before , FIRST(DowABSLimit.datum) AS first_Dow_ABSLimit , LAST(DowABSLimit.datum) AS last_DowABSLimit_datum , FIRST(UPABSLimit.datum) AS fist_Up_ABSLimit , LAST(UPABSLimit.datum) AS last_UPABSLimit_datum , NEXT(UPABSLimit.datum) AS next_Up_ABS_Limit , trunc(FIRST(UPABSLimit.datum)) - trunc(FIRST(DowABSLimit.datum)) AS days_down_under_limit , round(avg(DowABSLimit.BETRAG)) AS avg_BETRAG , MATCH_NUMBER() AS match_number , CLASSIFIER() AS classifier -- Pro Treffer eine Zeile anzeigen one ROW per MATCH -- for anlayse show all values between --all rows per match -- was soll nach dem ersten Match passieren after MATCH skip TO LAST UPABSLimit -- das eigentliche Pattern pattern (STRT DowABSLimit+ UPABSLimit+ ) -- für den Anfang der Daten sicherstellen das .. --SUBSET NAME_SUBSET= (STRT, DowABSLimit) -- Das Pattern definieren DEFINE DowABSLimit AS (BETRAG) < 0 , UPABSLimit AS NEXT(BETRAG) >= 1 ) KTO ORDER BY 2 DESC ;
Erläuterungen zum Einsatz von Oracle SQL Pattern Matching
Die Schritte beim Pattern Match
.. demnächst mehr
Quellen
Web:
Oracle:
Doku: