Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:sql_oracle_pattern_matching

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste ÜberarbeitungBeide Seiten der Revision
prog:sql_oracle_pattern_matching [2019/07/29 22:42] – gpipperrprog:sql_oracle_pattern_matching [2019/08/06 20:42] – gpipperr
Zeile 1: Zeile 1:
 +=====Oracle SQL Pattern Matching Funktion in der Praxis einsetzen=====
  
 +
 +===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:
 +<code sql>
 +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               
 +  ;
 +
 +</code>
 +
 +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:
 +<code sql>
 +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;
 +/
 +</code>
 +
 +CSV eerzugen um nach Excel zu laden:
 +<code sql>
 +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
 +
 +</code>
 +
 +In Excel eine Bereich der Daten visualisieren:
 +
 +
 +{{ :prog:oracle_sql_pattern_matching_example_v01.png?800 | Example Data to understand Oracle SQL Pattern Matching}}
 +
 +
 +Wir wollen zum Beispiel genau diesen Datenbereich finden:
 +{{ :prog:oracle_sql_pattern_matching_example_v02.png?800 | 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:
 +<code sql>
 +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               
 +  ;
 +</code>
 +
 +
 +
 +
 +----
 +
 +==== Erläuterungen zum Einsatz von Oracle SQL Pattern Matching ====
 +
 +Die Schritte beim Pattern Match 
 +
 +.. demnächst mehr
 +
 +----
 +
 +
 +==== Quellen ====
 +
 +
 +  * https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/misc/sql_pattern/sql_pattern.html
 +  * https://livesql.oracle.com/apex/livesql/file/tutorial_E4DB2E0Z0D5ZTUBGN6JWUPKAU.html
 +  * https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1
 +
 +Doku:
 +  * https://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG8956
prog/sql_oracle_pattern_matching.txt · Zuletzt geändert: 2020/09/30 14:19 von gpipperr