Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:sql_oracle_pattern_matching

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:

 Example Data to understand Oracle SQL Pattern Matching

Wir wollen zum Beispiel genau diesen Datenbereich finden:  Example Data to understand Oracle SQL Pattern Matching

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

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
"Autor: Gunther Pipperr"
prog/sql_oracle_pattern_matching.txt · Zuletzt geändert: 2020/09/30 14:19 von gpipperr