=====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:
{{ :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:
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:
* https://modern-sql.com/de/feature/match_recognize
Oracle:
* 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/en/database/oracle/oracle-database/19/dwhsg/sql-pattern-matching-data-warehouses.html#GUID-E088DB07-41E8-4103-BEAE-7EC1448C5D69