tag:blogger.com,1999:blog-36858946212434405212024-03-13T23:14:51.808+01:00Oracle SQL und PL/SQLRund um Oracle und die Verwendung von SQL und PL/SQL von Thomas Uhren...Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.comBlogger25125tag:blogger.com,1999:blog-3685894621243440521.post-31337599012237410602012-07-21T19:35:00.002+02:002012-07-21T19:38:30.627+02:00Data Mining mit Bordmitteln - Teil 1Mit diesem Post möchte ich eine Reihe starten, welche sich mit dem Thema Data Mining beschäftigt; konkret die Implementierung von Algorithmen mit Bordmitteln samt den Besonderheiten, die im Datenbankumfeld zu beachten sind.<br />
<br />
Als Beispiel dient ein Algorithmus zur Klassifikation auf der Basis von Entscheidungsbäumen - der ID3-Algorithmus. Doch bevor wir uns mit der Implementierung des Algorithmus beschäftigen, schauen wir uns zunächst das Ergebnis an. Zunächst wird der Algorithmus mit der Prozedur <code>init</code> initialisiert:
<pre name="code" class="sql">
exec dm_id3.init(1,10);
</pre>
Das erste Argument bestimmt das Projekt, während das zweite Argument die minimale Anzahl von Beobachtungen in einem Knoten festlegt; in diesem Fall muss jeder Knoten mindestens 10 Beobachtungen enthalten.
<br /><br />
Das Projekt nimmt Bezug auf eine Tabelle, welche die Daten des Data Sets "<a href="http://archive.ics.uci.edu/ml/datasets/Chess+%28King-Rook+vs.+King%29">Chess (King-Rook vs. King)</a>" enthält.
<br /><br />
Nach der erfolgreichen Initialisierung erfolgt ein Aufruf der Table-Funtion <code>gain</code>.
Man erhält den Gain bzw. den Gain Ratio, also wie sehr sich ein Attribut zur weiteren Zerlegung eignet:
<pre name="code" class="sql">
select * from table(dm_id3.gain(0)) order by gain desc;
</pre>
<pre>
NODE ATTRIBUTE INFO SPLIT_INFO GAIN
------ ------------------ --------- ---------- ----------
0 BLACK_KING_RANK 3.5041597 3.1896893 0.31446969
0 WHITE_KING_RANK 3.5041597 3.2139022 0.29025673
0 BLACK_KING_FILE 3.5041597 3.3190435 0.18511551
0 WHITE_KING_FILE 3.5041597 3.3388416 0.16531733
0 WHITE_ROOK_FILE 3.5041597 3.4540855 0.05007348
0 WHITE_ROOK_RANK 3.5041597 3.4579962 0.04616276
</pre>
Man sieht, dass das Attribut "BLACK_KING_RANK" am besten geeignet ist, um eine Zerlegung des Root-Knoten vorzunehmen.
<br /><br />
Um den Entscheidungsbaum zu erzeugen, wird die Prozedur <code>make_tree</code> aufgerufen:
<pre name="code" class="sql">
exec dm_id3.make_tree(0);
</pre>
Nun werfen wir einen Blick auf den erzeugten Entscheidungsbaum:
<pre name="code" class="sql">
select * from table(dm_id3.show_tree(1));
</pre>
<pre>
NODE SUPPORT PREDICTION CONFIDENCE DEPTH
--------------------------------- -------- ------------ ---------- ------
0: 1 = 1 28056 fourteen 4553 1
1: BLACK_KING_RANK = 1 3664 eight 591 2
9: WHITE_KING_FILE = a 372 eleven 89 3
49: WHITE_ROOK_RANK = 1 36 draw 10 4
50: WHITE_ROOK_RANK = 2 48 eight 19 4
51: WHITE_ROOK_RANK = 3 48 ten 25 4
52: WHITE_ROOK_RANK = 4 48 twelve 22 4
53: WHITE_ROOK_RANK = 5 48 eleven 13 4
54: WHITE_ROOK_RANK = 6 48 fourteen 13 4
55: WHITE_ROOK_RANK = 7 48 fourteen 13 4
56: WHITE_ROOK_RANK = 8 48 eleven 12 4
10: WHITE_KING_FILE = b 620 ten 163 3
...
</pre>
Man sieht unter anderem, dass das Attribut "BLACK_KING_RANK" tatsächlich für die Zerlegung des Root-Knoten verwendet wurde.
<br /><br />
So viel für heute... in den kommenden Postings erfolgt dann die eigentliche Implementierung der eben gezeigten Funktionen.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com1tag:blogger.com,1999:blog-3685894621243440521.post-83572807201583847872012-05-11T21:54:00.002+02:002012-05-11T21:56:32.429+02:00Alert-Log per XMLQuery auslesenHeute geht's nochmal um XQuery, um damit das Alert-Log, welches seit 11g auch im XML-Format vorliegt, in SQL verfügbar zu machen.
<br/><br/>
Dazu wird zunächst ein Directory angelegt, welches das Alert-Log im XML-Format enthält:
<pre class = "sql" name = "code">
create directory alert_log as '/u01/app/oracle/diag/rdbms/ora112/ora112/alert/';
</pre>
Leider ist das Alert-Log nicht well-formed, da es kein Root-Element besitzt; hier besteht also noch Handlungsbedarf.
<br/><br/>
Wenngleich man auch mit etwas PL/SQL ein Root-Element hinzufügen kann, habe ich mich hier dafür entschieden, zyklisch eine Kopie des Alert-Logs zu erstellen; diese Kopie wird dann mithilfe von Betriebssystem-Mitteln um ein Root-Element erweitert.
<br/><br/>
Unter Unix genügt es den Start- und End-Tag in jeweils einer Datei abzuspeichern, um dann eine Verkettung mit <code>cat</code> vorzunehmen.
<pre>
touch start_tag
echo "<root>" > start_tag
touch end_tag
echo "<root>" > start_tag
cat start_tag log.xml end_tag > log_well_formed.xml
</pre>
Jetzt kann man auf dieser Grundlage einen View erstellen, der mithilfe eines FLOWR-Ausdrucks, das Alert-Log relational aufbereitet:
<pre class = "sql" name = "code">
create view v_alert_log as
(
select
to_date(substr(x.log_time, 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS') as log_time
x.log_org_id,
x.log_comp_id,
x.log_msg_id,
x.log_type,
x.log_group,
x.log_level,
x.log_host_id,
x.log_host_addr,
x.log_pid,
x.log_version,
x.log_txt
from
xmltable
(
'for $i in //msg return $i'
passing
xmltype(bfilename('ALERT_LOG', 'log_well_formed.xml'), nls_charset_id('AL32UTF8'))
columns
"LOG_TIME" varchar2(255) path '/msg/@time',
"LOG_ORG_ID" varchar2(255) path '/msg/@org_id',
"LOG_COMP_ID" varchar2(255) path '/msg/@comp_id',
"LOG_MSG_ID" varchar2(255) path '/msg/@msg_id',
"LOG_TYPE" varchar2(255) path '/msg/@type',
"LOG_GROUP" varchar2(255) path '/msg/@group',
"LOG_LEVEL" integer path '/msg/@level',
"LOG_HOST_ID" varchar2(255) path '/msg/@host_id',
"LOG_HOST_ADDR" varchar2(255) path '/msg/@host_addr',
"LOG_PID" integer path '/msg/@pid',
"LOG_VERSION" varchar2(255) path '/msg/@version',
"LOG_TXT" varchar2(4000) path '/msg/txt'
) x
);
</pre>
Jede Änderung an der Quelle wird direkt durch den View reflektiert.
<br/><br/>
Eine mögliche Abfrage könnte zum Beispiel wie folgt aussehen:
<pre class = "sql" name = "code">
select
*
from
v_alert_log
where
log_txt like '%ORA-%' and
sysdate - log_time <= 3;
</pre>Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com1tag:blogger.com,1999:blog-3685894621243440521.post-43190425377103186332012-04-06T16:07:00.004+02:002012-04-08T10:54:40.742+02:00Ermittlung von Feiertagen per Table FunctionHeute geht's um die Ermittlung von Feiertagen in Deutschland; und das wieder mal per Table Function.<br />
<br />
Da viele Feiertage (z.B. Rosenmontag) eine Abhängigkeit zum Osterdatum besitzen, benötigt man zunächst eine Funktion zur Berechnung des Osterdatums; genauer gesagt zur Bestimmung von Ostersonntag:<br />
<pre name="code" class="sql">create or replace function easter_day
(
p_year_in in integer
)
return date
as
v_k integer;
v_m integer;
v_s integer;
v_a integer;
v_d integer;
v_r integer;
v_og integer;
v_sz integer;
v_oe integer;
v_os integer;
v_day integer;
v_month integer;
begin
v_k := floor(p_year_in / 100);
v_m := 15 + floor((3 * v_k + 3) / 4) - floor((8 * v_k + 13) / 25);
v_s := 2 - floor((3 * v_k + 3) / 4);
v_a := mod(p_year_in, 19);
v_d := mod((19 * v_a + v_m), 30);
v_r := floor(v_d / 29) + (floor(v_d / 28) - floor(v_d / 29)) * floor(v_a / 11);
v_og := 21 + v_d - v_r;
v_sz := 7 - mod((p_year_in + floor(p_year_in / 4) + v_s), 7);
v_oe := 7 - mod(v_og - v_sz, 7);
v_os := v_og + v_oe;
if (v_os <= 31) then
v_day := v_os;
v_month := 3;
else
v_day := v_os - 31;
v_month := 4;
end if;
return to_date(v_day || '.' || v_month || '.' || p_year_in, 'DD.MM.YYYY');
end easter_day;
</pre>
Dabei handelt es sich im Wesentlichen um die ergänzte Osterformel von Carl-Friedrich Gauß; diese wurde durch Hermann Kinkelin und Christian Zeller dahingehend ergänzt, dass Ausnahmeregeln in der Formel berücksichtigt werden. Siehe dazu auch den entsprechenden Eintrag auf der deutschsprachigen Seite von <a href="http://de.wikipedia.org/wiki/Gau%C3%9Fsche_Osterformel">Wikipedia</a>.
</br></br>
Kommen wir nun zur eigentlichen Table-Function, für die zuächst die Objekt-Typen erstellt werden müssen:
<pre name="code" class="sql">create type holiday_t as object
(
holiday_date date,
holiday_name varchar2(30),
holiday_desc varchar2(100)
);
/
create type holiday_tab as table of holiday_t;
/
</pre>Die eigentliche Table-Function liefert nun die "Feiertage" der jeweiligen Jahre:
<pre name="code" class="sql">create or replace function german_holidays
(
p_year_start_in in integer,
p_year_end_in in integer
)
return holiday_tab pipelined
as
v_easter_day date;
begin
for y in p_year_start_in .. p_year_end_in loop
v_easter_day := easter_day(y);
pipe row (
holiday_t(
to_date('01.01.' || y, 'DD.MM.YYYY'),
'Neujahrstag',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('06.01.' || y, 'DD.MM.YYYY'),
'Heilige Drei Könige',
'Nur BW, BY und ST'));
pipe row (
holiday_t(
v_easter_day - interval '52' day,
'Weiberdonnerstag',
'-'));
pipe row (
holiday_t(
v_easter_day - interval '48' day,
'Rosenmontag',
'-'));
pipe row (
holiday_t(
v_easter_day - interval '46' day,
'Aschermittwoch',
'-'));
pipe row (
holiday_t(
v_easter_day - interval '3' day,
'Gründonnerstag',
'-'));
pipe row (
holiday_t(
v_easter_day - interval '2' day,
'Karfreitag',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '1' day,
'Ostermontag',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('01.05.' || y, 'DD.MM.YYYY'),
'Tag der Arbeit',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '39' day,
'Christi Himmelfahrt',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '50' day,
'Pfingstmontag',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
v_easter_day + interval '60' day,
'Fronleichnam',
'Nur BW, BY, HE, NW, RP'));
pipe row (
holiday_t(
to_date('08.08.' || y, 'DD.MM.YYYY'),
'Augsburger Friedensfest',
'Nur im Stadtgebiet Augsburg'));
pipe row (
holiday_t(
to_date('08.08.' || y, 'DD.MM.YYYY'),
'Mariä Himmelfahrt',
'Nur SL und Teilen von BY'));
pipe row (
holiday_t(
to_date('03.10.' || y, 'DD.MM.YYYY'),
'Tag der Deutschen Einheit',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('31.10.' || y, 'DD.MM.YYYY'),
'Reformationstag',
'Nur BB, MV, SL, SN und TH'));
pipe row (
holiday_t(
to_date('01.11.' || y, 'DD.MM.YYYY'),
'Allerheiligen',
'Nur BW, BY, NW, RP und SL'));
pipe row (
holiday_t(
to_date('11.11.' || y, 'DD.MM.YYYY'),
'Karnevalsbeginn',
'-'));
pipe row (
holiday_t(
to_date('25.12.' || y, 'DD.MM.YYYY'),
'1. Weihnachtstag',
'Gesetzlicher Feiertag'));
pipe row (
holiday_t(
to_date('26.12.' || y, 'DD.MM.YYYY'),
'2. Weihnachtstag',
'Gesetzlicher Feiertag'));
end loop;
end german_holidays;
</pre>Für das Jahr 2012 erhält man dann:
<pre name="code" class="sql">select
*
from
table(german_holidays(2012, 2012));
</pre><pre>HOLIDAY_DATE HOLIDAY_NAME HOLIDAY_DESC
------------- ------------------------------ ----------------------------
01.01.12 Neujahrstag Gesetzlicher Feiertag
06.01.12 Heilige Drei Könige Nur BW, BY und ST
16.02.12 Weiberdonnerstag -
20.02.12 Rosenmontag -
22.02.12 Aschermittwoch -
05.04.12 Gründonnerstag -
06.04.12 Karfreitag Gesetzlicher Feiertag
09.04.12 Ostermontag Gesetzlicher Feiertag
01.05.12 Tag der Arbeit Gesetzlicher Feiertag
17.05.12 Christi Himmelfahrt Gesetzlicher Feiertag
28.05.12 Pfingstmontag Gesetzlicher Feiertag
07.06.12 Fronleichnam Nur BW, BY, HE, NW, RP
08.08.12 Augsburger Friedensfest Nur im Stadtgebiet Augsburg
08.08.12 Mariä Himmelfahrt Nur SL und Teilen von BY
03.10.12 Tag der Deutschen Einheit Gesetzlicher Feiertag
31.10.12 Reformationstag Nur BB, MV, SL, SN und TH
01.11.12 Allerheiligen Nur BW, BY, NW, RP und SL
11.11.12 Karnevalsbeginn -
25.12.12 1. Weihnachtstag Gesetzlicher Feiertag
26.12.12 2. Weihnachtstag Gesetzlicher Feiertag
</pre>Mit Bezug auf das vorherige Posting ergibt sich nun die Möglichkeit, die Zeitdimension um die Feiertage zu erweitern.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com3tag:blogger.com,1999:blog-3685894621243440521.post-64809992295105961092012-03-31T22:53:00.000+02:002012-03-31T22:53:00.611+02:00Zeitdimension per Table Function erstellenHeute geht's um die Erstellung einer Zeitdimension mithilfe einer Table-Function. Dazu verwendet man im Wesentlichen die Möglichkeiten der Funktion <code>to_char</code>, wie das folgende Beispiel zeigt.<br />
<br />
Zunächst wird der Objekt-Typ erstellt:<br />
<pre class="sql" name="code">create type time_dim_t as object
(
v_day date,
v_day_name varchar2(30),
v_day_of_week integer,
v_day_of_month integer,
v_day_of_year integer,
v_week integer,
v_week_start date,
v_week_end date,
v_iso_week integer,
v_iso_week_start date,
v_iso_week_end date,
v_month integer,
v_month_name varchar2(30),
v_month_start date,
v_month_end date,
v_month_days integer,
v_quarter integer,
v_quarter_name varchar(2),
v_quarter_start date,
v_quarter_end date,
v_quarter_days integer,
v_year integer,
v_year_start date,
v_year_end date,
v_year_days integer,
v_is_leap_year varchar2(1)
);
/
</pre>Hinzu kommt der Table-Typ:<br />
<pre class="sql" name="code">create type time_dim_tab as table of time_dim_t;
/
</pre>Damit sind alle Vorbereitungen getroffen, um die Table-Function zu erstellen:<br />
<pre class="sql" name="code">create or replace function time_dim
(
p_year_start_in in integer,
p_year_end_in in integer
)
return time_dim_tab pipelined
as
v_day date;
v_day_name varchar2(30);
v_day_of_week integer;
v_day_of_month integer;
v_day_of_year integer;
v_week integer;
v_week_start date;
v_week_end date;
v_iso_week integer;
v_iso_week_start date;
v_iso_week_end date;
v_month integer;
v_month_name varchar2(30);
v_month_start date;
v_month_end date;
v_month_days integer;
v_quarter integer;
v_quarter_name varchar(2);
v_quarter_start date;
v_quarter_end date;
v_quarter_days integer;
v_year integer;
v_year_start date;
v_year_end date;
v_year_days integer;
v_is_leap_year varchar2(1);
begin
begin
for y in p_year_start_in .. p_year_end_in loop
v_year := y;
v_year_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
v_year_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
v_year_days := v_year_end - v_year_start + 1;
if (v_year_days = 366) then
v_is_leap_year := 'Y';
else
v_is_leap_year := 'N';
end if;
for q in 1 .. 4 loop
v_quarter := q;
v_quarter_name := 'Q' || v_quarter;
case v_quarter_name
when 'Q1' then
v_quarter_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
v_quarter_end := to_date('31.03.' || v_year, 'DD.MM.YYYY');
when 'Q2' then
v_quarter_start := to_date('01.04.' || v_year, 'DD.MM.YYYY');
v_quarter_end := to_date('30.06.' || v_year, 'DD.MM.YYYY');
when 'Q3' then
v_quarter_start := to_date('01.07.' || v_year, 'DD.MM.YYYY');
v_quarter_end := to_date('30.09.' || v_year, 'DD.MM.YYYY');
when 'Q4' then
v_quarter_start := to_date('01.10.' || v_year, 'DD.MM.YYYY');
v_quarter_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
end case;
v_quarter_days := v_quarter_end - v_quarter_start + 1;
for m in to_number(to_char(v_quarter_start, 'MM')) ..
to_number(to_char(v_quarter_end, 'MM')) loop
v_month := m;
v_month_start := to_date('01.' || v_month || '.' || v_year, 'DD.MM.YYYY');
v_month_end := last_day(v_month_start);
v_month_days := v_month_end - v_month_start + 1;
v_month_name := to_char(v_month_start, 'MONTH');
for d in 1 .. v_month_days loop
v_day := to_date(d || '.' || v_month || '.' || v_year, 'DD.MM.YYYY');
v_day_of_week := to_number(to_char(v_day, 'D'));
v_day_of_month := to_number(to_char(v_day, 'DD'));
v_day_of_year := to_number(to_char(v_day, 'DDD'));
v_day_name := to_char(v_day, 'DAY');
v_week := to_number(to_char(v_day, 'WW'));
v_week_start := trunc(v_day, 'WW');
v_week_end := v_week_start + interval '6' day;
v_iso_week := to_number(to_char(v_day, 'IW'));
v_iso_week_start := trunc(v_day, 'IW');
v_iso_week_end := v_iso_week_start + interval '6' day;
pipe row
(
time_dim_t
(
v_day,
v_day_name,
v_day_of_week,
v_day_of_month,
v_day_of_year,
v_week,
v_week_start,
v_week_end,
v_iso_week,
v_iso_week_start,
v_iso_week_end,
v_month,
v_month_name,
v_month_start,
v_month_end,
v_month_days,
v_quarter,
v_quarter_name,
v_quarter_start,
v_quarter_end,
v_quarter_days,
v_year,
v_year_start,
v_year_end,
v_year_days,
v_is_leap_year
)
);
end loop;
end loop;
end loop;
end loop;
end;
end;
</pre>Ein Aufruf der Table-Function für die Jahre 2011 und 2012 sieht dann wie folgt aus:<br />
<pre class="sql" name="code">select * from table(time_dim(2011, 2012));
</pre>Auf der Grundlage dieser Table-Function kann dann eine Zeitdimension erstellt werden; sei es als Star- oder Snowflake-Schema, was durch entsprechende Projektionen erreicht werden kann. <br />
<br />
Eine Ergänzung wäre die Angabe von Feiertagen, welche oftmals im iCal-Format bereitstehen. Vielleicht widme ich ein weiteres Posting diesem Thema, um zu zeigen, wie man dieses Format in SQL bereitstellen kann.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-85503321582409462042012-02-13T21:29:00.002+01:002012-02-13T21:34:12.037+01:00Aggregatsfunktion zur Berechnung der EntropieHeute geht's um die Berechnung der Entropie mithilfe einer benutzerdefinierten Aggregatsfunktion. Die Entropie ist ein Maß aus der Informationstheorie und findet unter anderem Anwendung in Data Mining Algorithmen wie ID3 und dessen Nachfolger C4.5.<br />
<br />
Die Formel zur Berechnung lautet wie folgt:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWpkKY0kqA-sTBCjkOya431wIyf314tZKVV3pVQwPruGlyNMd6H0CmFTllmEOszC0Z8G6OGAfqLReNjUdvIiqkibKjS8BvzwYO5MZtd1dXAcTWs6M7PY2eAIG37nx__ktkYLk6IUcJu8dS/s1600/entropie1.jpg" imageanchor="1" style=""><img border="0" height="87" width="166" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWpkKY0kqA-sTBCjkOya431wIyf314tZKVV3pVQwPruGlyNMd6H0CmFTllmEOszC0Z8G6OGAfqLReNjUdvIiqkibKjS8BvzwYO5MZtd1dXAcTWs6M7PY2eAIG37nx__ktkYLk6IUcJu8dS/s400/entropie1.jpg" /></a></div><br />
Dabei bezeichnet <code>c</code> die Anzahl der Ausprägungen, <code>n<sub>i</sub></code> die Anzahl der Elemente der i-ten Ausprägung und <code>n</code> die Anzahl aller Elemente. <br />
<br />
Anhand folgender Daten soll die Entropie für das Attribut <code>credit_rating</code> berechnet werden.<br />
<pre>NAME CREDIT_RATING
-------- -------------
SMITH A
BLAKE B
ALLEN A
KING B
JONES B
CLARK B
JAMES B
</pre>Man erhält die Entropie zu:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcLFPTKs_CXqdF-ZXaJqYfXqLERuvyDYUr9xdkAQpvqX2ZzNjphc7sgVaTlqlTFLMhkTBzu52pZq_R5MEGx24K3vvj4JbS_xcBEDaMXuKyYBX2RfyuB3o39m1UHhjHgcHro8YOnC8l58tf/s1600/entropie3.jpg" imageanchor="1" style=""><img border="0" height="60" width="378" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcLFPTKs_CXqdF-ZXaJqYfXqLERuvyDYUr9xdkAQpvqX2ZzNjphc7sgVaTlqlTFLMhkTBzu52pZq_R5MEGx24K3vvj4JbS_xcBEDaMXuKyYBX2RfyuB3o39m1UHhjHgcHro8YOnC8l58tf/s400/entropie3.jpg" /></a></div><br />
Es sollte aufgefallen sein, dass die Formel nicht der Arbeitsweise von SQL und der von Aggregatsfunktionen entspricht, da die Gesamtanzahl in der Regel erst am Ende zur Verfügung steht.<br />
<br />
Durch einige Umformungen erhält man jedoch die gewünschte Form:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcztoMg-qyNvJL0YTL2nCFJR54IS7ETVCjqiJH8oy-En39M-YSD5hz-8f27Buu4JadT0tY3flwFJK-3JMD4O20NUiTor9bGhJ3sYSShwrQW0SBuu8rltiko4peCaHxnMf7fIihTw-YjIdW/s1600/entropie2.jpg" imageanchor="1" style=""><img border="0" height="497" width="399" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcztoMg-qyNvJL0YTL2nCFJR54IS7ETVCjqiJH8oy-En39M-YSD5hz-8f27Buu4JadT0tY3flwFJK-3JMD4O20NUiTor9bGhJ3sYSShwrQW0SBuu8rltiko4peCaHxnMf7fIihTw-YjIdW/s1600/entropie2.jpg" /></a></div><br />
Nun lassen sich die Bestandteile der Formel den Phasen von Aggregatsfunktionen zuordnen:<br />
<pre name="code" class="sql">create or replace type entropy_t as object
(
v_sum integer,
v_entropy number,
static function odciaggregateinitialize(
sctx in out entropy_t) return number,
member function odciaggregateiterate(
self in out entropy_t,
value in integer) return number,
member function odciaggregateterminate(
self in entropy_t,
returnvalue out number,
flags in number) return number,
member function odciaggregatemerge(
self in out entropy_t,
ctx2 in entropy_t) return number
);
/
create or replace type body entropy_t is
static function odciaggregateinitialize(
sctx in out entropy_t) return number is
begin
sctx := entropy_t(0,0.00);
return odciconst.success;
end;
member function odciaggregateiterate(
self in out entropy_t,
value in integer) return number is
begin
self.v_entropy := self.v_entropy - value * log( 2, value );
self.v_sum := self.v_sum + value;
return odciconst.success;
end;
member function odciaggregateterminate(
self in entropy_t,
returnvalue out number,
flags in number) return number is
begin
returnvalue := ( self.v_entropy / self.v_sum ) + log( 2, self.v_sum );
return odciconst.success;
end;
member function odciaggregatemerge(
self in out entropy_t,
ctx2 in entropy_t) return number is
begin
return odciconst.success;
end;
end;
/
</pre>Nun fehlt nur noch die entsprechende Funktion.<br />
<pre name="code" class="sql">create or replace function entropy (input integer) return number
parallel_enable aggregate using entropy_t;
</pre>Ein Aufruf der Funktion kann dann wie folgt erfolgen:<br />
<pre name="code" class="sql">select
entropy(count(*)) entropy
from
customer
group by
credit_rating;
</pre><pre>ENTROPY
---------
0.8631206
</pre>Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-38074182952807224702012-01-03T20:32:00.001+01:002012-01-03T20:46:29.951+01:00Dynamischer Rückgabetyp bei Table FunctionsHeut geht's um den Einsatz von Table Functions, die einen dynamischen Rückgabetyp besitzen.<br />
<br />
Der normale Fall sieht dabei wie folgt aus:<br />
<br />
Man erstellt einen Objekt-Typ und gibt die Attribute mit Namen und Typ an.<br />
<pre class="sql" name="code">create type val_t as object
(
v1 number,
v2 number,
v3 number
);
/
</pre>Jetzt fehlt noch der Table-Typ von dem gerade erstellen Objekt-Typ.<br />
<pre class="sql" name="code">create type val_tab as table of val_t;
/
</pre>Die Table-Function soll nun das Einmaleins bis 3 ausgeben.<br />
<pre class="sql" name="code">create or replace function multiplication_table return val_tab pipelined is
v_column_count integer := 3;
begin
for i in 1..v_column_count loop
pipe row ( val_t (i*1,i*2,i*3) );
end loop;
return;
end multiplication_table;
</pre>Soweit so gut, jedoch wollen wir auch das Einmaleins bis 5, 10, 100 oder einer anderen natürlichen Zahl n, ohne dafür jeweils eine eigene Table Function samt den notwendigen Objekt-Typen erstellen zu wollen.<br />
<br />
Dazu sind einige Funktionen zu implementieren, welche es erlauben, den Rückgabetyp dynamisch zu erstellen. Für die Aufgabe ergibt sich folgenden Struktur:<br />
<pre class="sql" name="code">create or replace type multiplication_table as object
(
v_row_types anytype,
v_column_count integer,
v_rows_processed integer,
static function show_table(
p_column_count_in in integer
) return anydataset pipelined using multiplication_table,
static function ODCITableDescribe(
rtype out anytype,
p_column_count_in in integer
) return number,
static function ODCITablePrepare(
sctx out multiplication_table,
tf_info SYS.ODCITabFuncInfo,
p_column_count_in in integer
) return number,
static function ODCITableStart(
sctx in out multiplication_table,
p_column_count_in in integer
) return number,
member function ODCITableFetch(
self in out multiplication_table,
nrows in number,
rws out anydataset
) return number,
member function ODCITableClose(
self in multiplication_table
) return number
);
</pre>Dabei wird der Rückgabetyp durch die Funktion <code>ODCITableDescribe</code> beschrieben, also die Anzahl und Typen der Attribute festgelegt. Konkret für die Lösung der Aufgabe bedeutet dies, das für das Einmaleins bis n auch n Attribute vorhanden sein müssen. Die Funktion <code>ODCITableFetch</code> liefert dann die Zeilen zurück, indem die Bestandteile des Objekt-Typs mit Werten gefüllt werden.<br />
<pre class="sql" name="code">create or replace type body multiplication_table as
static function ODCITableDescribe(
rtype out anytype,
p_column_count_in in integer
) return number as
v_record_structure anytype;
begin
anytype.begincreate(dbms_types.typecode_object, v_record_structure);
for i in 1 .. p_column_count_in loop
v_record_structure.addattr(
ANAME => '#' || to_char(i),
TYPECODE => dbms_types.typecode_number,
PREC => null,
SCALE => null,
LEN => null,
CSID => null,
CSFRM => null,
ATTR_TYPE => null
);
end loop;
v_record_structure.endcreate();
anytype.begincreate(dbms_types.typecode_table, rtype);
rtype.setinfo(
null,
null,
null,
null,
null,
v_record_structure,
dbms_types.typecode_object,
0);
rtype.endcreate();
return odciconst.success;
end ODCITableDescribe;
static function ODCITablePrepare(
sctx out multiplication_table,
tf_info SYS.ODCITabFuncInfo,
p_column_count_in in integer
) return number as
prec pls_integer;
scale pls_integer;
len pls_integer;
csid pls_integer;
csfrm pls_integer;
record_desc anytype;
aname varchar2(30);
dummy pls_integer;
begin
dummy := tf_info.RetType.GetAttrElemInfo(
null,
prec,
scale,
len,
csid,
csfrm,
record_desc,
aname);
sctx := multiplication_table(record_desc, p_column_count_in, 0);
return odciconst.success;
end ODCITablePrepare;
static function ODCITableStart(
sctx in out multiplication_table,
p_column_count_in in integer
) return number as
begin
return odciconst.success;
end ODCITableStart;
member function ODCITableFetch(
self in out multiplication_table,
nrows in number,
rws out anydataset
) return number as
begin
rws := null;
if (self.v_rows_processed < self.v_column_count) then
self.v_rows_processed := self.v_rows_processed + 1;
anydataset.begincreate(dbms_types.typecode_object, self.v_row_types, rws);
rws.addinstance;
rws.piecewise();
for i in 1 .. self.v_column_count loop
rws.setnumber(self.v_rows_processed * i);
end loop;
rws.endcreate;
end if;
return odciconst.success;
end ODCITableFetch;
member function ODCITableClose(
self in multiplication_table
) return number as
begin
return odciconst.success;
end ODCITableClose;
end;
</pre>
Ein Aufruf mit dem Argument 4 liefert dann:
<pre class="sql" name="code">select * from table(multiplication_table.show_table(4));
</pre><pre>#1 #2 #3 #4
------- ------- ------- -------
1 2 3 4
2 4 6 8
3 6 9 12
4 8 12 16
</pre>Während die Wahl des Arguments 6 einen Objekt-Typ mit zwei zusätzlichen Spalten zur Folge hat:
<pre class="sql" name="code">select * from table(multiplication_table.show_table(6));
</pre><pre>#1 #2 #3 #4 #5 #6
------- ------- ------- ------- ------- -------
1 2 3 4 5 6
2 4 6 8 10 12
3 6 9 12 15 18
4 8 12 16 20 24
5 10 15 20 25 30
6 12 18 24 30 36
</pre>Weitere Informationen dazu findet man im Data Cartridge Developer's Guide.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-38429605122814960982011-12-31T18:52:00.000+01:002011-12-31T18:52:03.974+01:00Etwas OLAP zum JahresendeIn diesem Post geht's um eine Aufgabenstellung und deren Lösung mit OLAP. Dabei wird angenommen, dass Kunden eine Umfrage, bestehend aus fünf Seiten, durchführen können. Nun ist man daran interessiert wie viele Kunden die Umfrage komplett durchgeführt haben und wie viele Kunden man bei jedem Schritt "verloren" hat.<br />
<br />
Zunächst die Struktur der Tabelle:<br />
<pre name="code" class="sql">create table survey
(
cust_id integer constraint pk_survey primary key,
last_page integer constraint nn_survey_last_page not null
);
</pre>Für den Datenbestand gehen wir davon aus, dass eine komplette Durchführung der Umfrage ein eher seltenes Ereignis ist; für die Simulation eignet sich somit die <a href="http://de.wikipedia.org/wiki/Poisson-Verteilung">Poisson-Verteilung</a>.<br />
<br />
Der folgende anonyme PL/SQL-Block erzeugt den Datenbestand:<br />
<pre name="code" class="sql">declare
v_lamda integer := 1;
v_max_page integer := 5;
v_page integer;
v_value number;
begin
for i in 1..10000 loop
v_page := 1;
v_value := 0.00;
loop
v_value := v_value + ( -( 1 / v_lamda ) * ( ln (1 - dbms_random.value) ) );
if (v_value < 1) then
v_page := v_page + 1;
else
exit;
end if;
end loop;
if (v_page > v_max_page) then
v_page := v_max_page;
end if;
insert into survey (cust_id, last_page)
values (i, v_page);
end loop;
end;
</pre>Dabei ist Lamda der Erwartungswert der Poisson-Verteilung; in diesem Fall 1. Man geht also davon aus, dass die meisten Kunden bereits nach der ersten Seite aussteigen. Die Lösung verwendet OLAP-Funktionalität, um erst die kumulierte Anzahl zu bestimmen und dann mithilfe von LAG den Verlust zu berechnen.<br />
<pre name="code" class="sql">with cumulative_count as
(
select
last_page,
sum( count(*) ) over
(order by last_page desc rows between unbounded preceding and current row) n
from
survey
group by
last_page
)
select
last_page,
n,
( lag(n) over (order by last_page) - n ) lost
from
cumulative_count;
</pre>Damit erhält man folgenden Output:<br />
<pre>LAST_PAGE N LOST
---------------------- ---------------------- ----------------------
1 10000
2 6255 3745
3 2638 3617
4 809 1829
5 188 621
</pre>Man kann sehr gut erkennen, dass die Simulation auf der Basis der Poisson-Verteilung eine realistische Datenbasis erzeugt hat und nur 188 Kunden den Prozess komplett durchgeführt haben.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-58872151119549218822011-11-08T20:31:00.002+01:002011-11-13T18:27:49.721+01:00XMLQuery und XMLTable im EinsatzHeute geht's um die Verwendung von XMLQuery und XMLTable an einem kleinen Beispiel. Dabei soll lediglich die grundsätzliche Verwendung demonstriert und die Einsatzmöglichkeiten aufgezeigt werden.<br />
<br />
Hier zunächst die Datenbasis:<br />
<pre name = "code" class = "sql">create table xml_test
(
company_id integer,
xml xmltype
);
insert into xml_test(company_id, xml)
values
(
1,
'<company>
<employees>
<emp empno="7839" ename="KING" sal="5000" deptno="10">
<emp empno="7566" ename="JONES" sal="2975" deptno="20">
<emp empno="7788" ename="SCOTT" sal="3000" deptno="20">
<emp empno="7876" ename="ADAMS" sal="1100" deptno="20"/>
</emp>
<emp empno="7902" ename="FORD" sal="3000" deptno="20">
<emp empno="7369" ename="SMITH" sal="800" deptno="20"/>
</emp>
</emp>
<emp empno="7782" ename="CLARK" sal="2450" deptno="10">
<emp empno="7934" ename="MILLER" sal="1300" deptno="10"/>
</emp>
<emp empno="7698" ename="BLAKE" sal="2850" deptno="30">
<emp empno="7499" ename="ALLEN" sal="1600" deptno="30"/>
<emp empno="7521" ename="WARD" sal="1050" deptno="30"/>
<emp empno="7654" ename="MARTIN" sal="1250" deptno="30"/>
<emp empno="7844" ename="TURNER" sal="1500" deptno="30"/>
<emp empno="7900" ename="JAMES" sal="950" deptno="30"/>
</emp>
</emp>
</employees>
<departments>
<dept deptno="10">
<dname>ACCOUNTING</dname>
<loc>NEW YORK</loc>
<budget>13000</budget>
</dept>
<dept deptno="20">
<dname>RESEARCH</dname>
<loc>DALLAS</loc>
<budget>15000</budget>
</dept>
<dept deptno="30">
<dname>SALES</dname>
<loc>CHICAGO</loc>
<budget>11000</budget>
</dept>
<dept deptno="40">
<dname>OPERATIONS</dname>
<loc>BOSTON</loc>
<budget>10000</budget>
</dept>
</departments>
</company>'
);
</pre>Dabei handelt es sich um eine Tabelle mit einer Spalte vom Typ <code>XMLType</code>. Diese Spalte enthält ein XML-Dokument mit der Mitarbeiter-Hierarchie samt Informationen über die Abteilungen. Nun betrachten wir drei Aufgabenstellungen:<br />
<br />
(1) Abteilungen relational speichern (ähnlich Tabelle DEPT)<br />
(2) Mitarbeiter relational speichern (ähnlich Tabelle EMP)<br />
(3) Mitarbeiter im XML-Dokument nicht in einer Hierarchie darstellen<br />
<br />
Wir beginnen mit der ersten und zugleich einfachsten Aufgabe. Mithilfe von <code>XMLTable</code> und einem XQuery-FLWOR-Ausdruck erhält man:<br />
<pre name = "code" class = "sql">select
x.deptno,
x.dname,
x.loc,
x.budget
from
xml_test,
xmltable
(
'
for $dept in //dept order by $dept/deptno return $dept
'
passing xml
columns
"DEPTNO" integer path '/dept/@deptno',
"DNAME" varchar2(15) path '/dept/dname',
"LOC" varchar2(15) path '/dept/loc',
"BUDGET" integer path '/dept/budget'
) x;
</pre><pre>DEPTNO DNAME LOC BUDGET
---------- --------------- --------------- ----------
10 ACCOUNTING NEW YORK 13000
20 RESEARCH DALLAS 15000
30 SALES CHICAGO 11000
40 OPERATIONS BOSTON 10000
</pre>Dabei handelt es sich bei <code>//dept</code> um eine Pfadangabe mit XPath, welche alle entsprechenden Department-Tags im XML-Dokument findet. Diese Tags werden separat durchlaufen und sodann zurückgegeben.<br />
<br />
Die nächste Aufgabe gestaltet sich etwas schwieriger, da die Elemente der Mitarbeiter geschachtelt sind, um die Hierarchie der Mitarbeiter abzubilden. Nun geht es also darum dies rückgängig zu machen, um eine Struktur, wie aus der EMP-Tabelle bekannt, zu erhalten.<br />
<br />
Der FLWOR-Ausdruck durchläuft im for-Teil zunächst alle Mitarbeiter-Tags; für jeden Mitarbeiter wird dann im let-Teil der Parent (sofern vorhanden) ermittelt; jetzt werden die Informationen in ein XML-Element mit den entsprechenden Attributen gepackt und zurückgegeben.<br />
<pre name = "code" class = "sql">select
x.empno,
x.ename,
x.mgr,
x.sal,
x.deptno
from
xml_test,
xmltable
(
'
for $emp in //emp
let $mgr := $emp/../@empno
return
element emp
{
attribute empno {$emp/@empno},
attribute ename {$emp/@ename},
attribute mgr {data($mgr)},
attribute sal {$emp/@sal},
attribute deptno {$emp/@deptno}
}
'
passing xml
columns
"EMPNO" integer path '/emp/@empno',
"ENAME" varchar2(15) path '/emp/@ename',
"MGR" integer path '/emp/@mgr',
"SAL" integer path '/emp/@sal',
"DEPTNO" integer path '/emp/@deptno'
) x;
</pre><pre>EMPNO ENAME MGR SAL DEPTNO
---------- --------------- ---------- ---------- ----------
7839 KING 5000 10
7566 JONES 7839 2975 20
7788 SCOTT 7566 3000 20
7876 ADAMS 7788 1100 20
7902 FORD 7566 3000 20
7369 SMITH 7902 800 20
7782 CLARK 7839 2450 10
7934 MILLER 7782 1300 10
7698 BLAKE 7839 2850 30
7499 ALLEN 7698 1600 30
7521 WARD 7698 1050 30
7654 MARTIN 7698 1250 30
7844 TURNER 7698 1500 30
7900 JAMES 7698 950 30
</pre>Man erhält damit die gewünschte Struktur.<br />
<br />
Die dritte Aufgabenstellungen zielt nun auf eine Transformation des XML-Dokuments; pro Mitarbeiter soll es nun ein XML-Element geben mit dem zusätzlichen Attribut für den Vorgesetzen. <br />
<br />
Die Lösung erfolgt wieder mithilfe von XQuery und der Funktion <code>XMLQuery</code>, die in der Select-Klausel zum Einsatz kommt. Der wesentliche Unterschied liegt darin, dass jetzt ein XML-Dokument zurückkommt.<br />
<pre name = "code" class = "sql">select
xmlquery
(
'
<company>
<employees>
{
for $emp in //emp
let $mgr := $emp/parent::emp/@empno
return
<emp empno = "{data($emp/@empno)}" mgr = "{$mgr}" >
<ename>{data($emp/@ename)}</ename>
<sal>{data($emp/@sal)}</sal>
<deptno>{data($emp/@deptno)}</deptno>
</emp>
}
</employees>
<departments>
{
for $dept in //dept
return $dept
}
</departments>
</company>
'
passing xml
returning content
) x
from
xml_test;
</pre><pre>X
----------------------------------
<company>
<employees>
<emp empno="7839" mgr="">
<ename>KING</ename>
<sal>5000</sal>
<deptno>10</deptno>
</emp>
<emp empno="7566" mgr="7839">
<ename>JONES</ename>
<sal>2975</sal>
<deptno>20</deptno>
</emp>
<emp empno="7788" mgr="7566">
<ename>SCOTT</ename>
<sal>3000</sal>
<deptno>20</deptno>
</emp>
<emp empno="7876" mgr="7788">
<ename>ADAMS</ename>
<sal>1100</sal>
<deptno>20</deptno>
</emp>
<emp empno="7902" mgr="7566">
<ename>FORD</ename>
<sal>3000</sal>
<deptno>20</deptno>
</emp>
<emp empno="7369" mgr="7902">
<ename>SMITH</ename>
<sal>800</sal>
<deptno>20</deptno>
</emp>
<emp empno="7782" mgr="7839">
<ename>CLARK</ename>
<sal>2450</sal>
<deptno>10</deptno>
</emp>
<emp empno="7934" mgr="7782">
<ename>MILLER</ename>
<sal>1300</sal>
<deptno>10</deptno>
</emp>
<emp empno="7698" mgr="7839">
<ename>BLAKE</ename>
<sal>2850</sal>
<deptno>30</deptno>
</emp>
<emp empno="7499" mgr="7698">
<ename>ALLEN</ename>
<sal>1600</sal>
<deptno>30</deptno>
</emp>
<emp empno="7521" mgr="7698">
<ename>WARD</ename>
<sal>1050</sal>
<deptno>30</deptno>
</emp>
<emp empno="7654" mgr="7698">
<ename>MARTIN</ename>
<sal>1250</sal>
<deptno>30</deptno>
</emp>
<emp empno="7844" mgr="7698">
<ename>TURNER</ename>
<sal>1500</sal>
<deptno>30</deptno>
</emp>
<emp empno="7900" mgr="7698">
<ename>JAMES</ename>
<sal>950</sal>
<deptno>30</deptno>
</emp>
</employees>
<departments>
<dept deptno="10">
<dname>ACCOUNTING</dname>
<loc>NEW YORK</loc>
<budget>13000</budget>
</dept>
<dept deptno="20">
<dname>RESEARCH</dname>
<loc>DALLAS</loc>
<budget>15000</budget>
</dept>
<dept deptno="30">
<dname>SALES</dname>
<loc>CHICAGO</loc>
<budget>11000</budget>
</dept>
<dept deptno="40">
<dname>OPERATIONS</dname>
<loc>BOSTON</loc>
<budget>10000</budget>
</dept>
</departments>
</company>
</pre>Natürlich gibt es zu XQuery noch viel mehr zu sagen und zu schreiben; in einem anderen Post. Dennoch sollen diese Beispiel die Einsatzmöglichkeiten und die grundsätzliche Verwendung innerhalb der Datenbank aufzeigen.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com1tag:blogger.com,1999:blog-3685894621243440521.post-36828229019211702802011-10-13T16:57:00.001+02:002011-10-13T16:57:46.612+02:00Binäre Genauigkeit bei der Verwendung von BINARY_DOUBLEHeute geht's um die Verwendung der Datentypen <code>BINARY_FLOAT</code> bzw. <code>BINARY_DOUBLE</code> und eine mögliche Fehlerquelle. Der folgende anonyme PL/SQL-Block demonstriert das Problem: <br />
<pre class="sql" name="code">declare
v_value binary_double := 0.0d;
begin
for i in 1 .. 10 loop
v_value := v_value + 0.1d;
end loop;
if (v_value = 1.0d) then
dbms_output.put_line('v_value = 1.0');
else
dbms_output.put_line('v_value != 1.0');
end if;
end;
/
</pre><pre>v_value != 1.0
</pre>Das Ergebnis verwundert, denn man erwartet, dass <code>10</code> mal <code>0,1</code> genau <code>1</code> ergibt. Die Ursache liegt in der binären Darstellung von <code>0,1</code>; die Datentypen besitzen eben "nur" eine binäre Genauigkeit.<br />
<br />
Statt einem Vergleich auf Gleichheit, der bei Gleitkommazahlen generell nicht zu empfehlen ist, überprüft man, ob die absolute Abweichung zum Vergleichswert sehr gering ist. Die gewählte Genauigkeit ist je nach Anwendung verschieden; zum Beispiel 10<sup>-8</sup>.<br />
<br />
Durch eine Anpassung des vorherigen PL/SQL-Blocks erhält man nun das erwartete Ergebnis:<br />
<pre class="sql" name="code">declare
v_value binary_double := 0.0d;
begin
for i in 1 .. 10 loop
v_value := v_value + 0.1d;
end loop;
if (abs(v_value - 1.0d) < 1e-8d) then
dbms_output.put_line('v_value = 1.0');
else
dbms_output.put_line('v_value != 1.0');
end if;
end;
/
</pre>
<pre>v_value = 1.0
</pre>Zur Vereinfachung könnte man diese Funktionalität in eine benannte Funktion auslagern:
<pre class="sql" name="code">create or replace function equal(
p_value_in in binary_double,
p_comparison_value_in in binary_double,
p_precision_in in binary_double default 1e-8d
)
return boolean is
begin
return abs(p_value_in - p_comparison_value_in) < p_precision_in;
end equal;
/
</pre>Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-2939815821722139042011-10-10T14:52:00.002+02:002011-10-10T19:16:14.754+02:00Zeichenketten mit PL/SQL zerlegenMit Bezug auf den vorherigen Post möchte ich heute eine Lösung mithilfe von PL/SQL aufzeigen und die beiden Möglichkeiten vergleichen; insbesondere hinsichtlich der Performance.<br />
<pre class="sql" name="code">create or replace function split_string_plsql(
p_list_in in varchar2,
p_delimiter_in in varchar2
)
return string_nt pipelined
is
v_before_pattern integer;
v_after_pattern integer;
v_list varchar2(32767) := p_list_in;
begin
loop
v_before_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 0, 'c');
if (v_before_pattern = 0) then
pipe row(v_list);
exit;
else
pipe row(substr(v_list, 1, v_before_pattern - 1));
end if;
v_after_pattern := regexp_instr(v_list, p_delimiter_in, 1, 1, 1, 'c');
v_list := substr(v_list, v_after_pattern);
exit when v_list is null;
end loop;
return;
exception
when others then
return;
end split_string_plsql;
/
</pre>Auch damit kann man Zeichenketten zerlegen, wobei das Trennzeichen auch durch einen regulären Ausdruck angegeben werden kann.<br />
<pre class="sql" name="code">select
column_value
from
table(split_string_plsql('a....b...c....d', '[.]+'));
</pre><pre>COLUMN_VALUE
-------------
a
b
c
d
</pre>Kommen wir nun zum Performance-Duell der beiden Varianten. Dafür steht eine Tabelle bereit, welche 100.000 Zeichenketten enthält, die jeweils durch ein Semikolon in fünf Felder unterteilt sind.<br />
<pre class="sql" name="code">select * from strings_to_split;
</pre><pre>STRING
------------------------------------------------------------------------------------
BnHZjfREKnUMdSbwOBflCRbqZISiKbLtraWEIuLpEKQCilFrRp;CZXLlbwZHuGcyCOqQlNnmTMwnlDhhryVH
hmwVoAdyOXKsTBhow;YrKdmqKbsSSvuPbtZTrPUPAkxfmNechcYrVkLSJfPnjvsaHwdk;iVxogWXxrQSsDRK
ofFhkZoHaKNqHgbPVjbQOywpNAdLMxabPAa;LgklKsjnwNxmTbriHCNABgisxWOjqLnBoRMaOSONbwiIosMS
acwjSJDzUBDWolfKMhtGwizwJpZvKMipMnuGKsRhbSpDzHHvqh;yIBOHkSVedWmpgoyczdxsoAiKvqGrzWYD
LbqZeIZjgeHOQRRDd;EAjqQJErVzkouioxcPLxYMgIEHERANYuGaqnhIDwpJTCIYeGWZ;NqejlJHoIQWcmca
acbqstRZeAhlKPbgSHexKEOPzEEkpYPkvez;QXYvDNXPtAkoGQvUnqqpagfbhShJZegBinXkgfurKoCVQAYX
ELbJayqFFdsDRcoHYmvqLxfsKqgDzNhmVBoHzYtcNYjLTzwaIL;qjVLHPBINaLXrbegLQAiklKeBexbTTCWn
VYoBXJBhLwlErEucT;hgMSqrOTmaiEfBvkMtynhtcwTodPZeyeIfltOCoLPkboCdwIfC;fmnEQBVqiQWNyuG
UxtDkwceZckXYFyqJBNiOgdWTJkPmGWFYiG;OslJbHXKdVALzjdFbeYcoGeTXCzjyKrlQqISRYXMkgpVDmpr
ztfMZluktZSOwtMIeQIBRLKNyrtUKogyddXQWLbCsjnTFwpTmL;nHDbrxlYhjabiWDpfSEnLWAvVXIzgkDhb
NPYwOBEAwmJZvZSmK;jApmGJeLaXioFJxpAelEugPrxkRZNIirzVWKqFYqweDHNymmoK;zUyCCgkGLURMCat
EzvOVgwIoLIeCeluQtMKkXkcieHSbRpjCDc;TTjxzLccEhSfXFgbxaKVndDhxymsrOlyWOjtcWoPSqPznLjP
LWgGKQSVpqaOabvBYAbUrPQaVbomxyaxOXVvPojYkDkPqNrozP;gafSCgxUsHPtTENvKpPfaJDMkhdIOVqUM
dRdeMsNDehCdxzvEF;sRxDbQIJaQCopFlgWxQGJOUwhDHDHonWZLYSlVmrugAiToLDAt;DJJZCaVEGjFvgAc
bGbaXYQeRodCgAixLMQOpxvPfBqQSrWHHJu;MkUeKsmMFCISPSzTldvrGIRrqQnmYxXPQUNHDzcTsGNeyBJC
OOPamDAHRipKbVWWaSScmdjIWFJbunDBBnXKTRceqsJEpOcnxQ;dwffwkUYBgqEJSWNUvnzAQKbgcsLwTWmY
ZSzmVMZqKWlxNFezc;OdEHsJZYrsEqLaGJEvnimLFJkxdQGgEGpAbOwxxJsqLXzwTbgk;wsqFmybhYypdegs
gEHXETxmyGmIZkQiZIwlrEQizNlbcqlXQeX;BmYSjCeyeWPJHgvbVjwbgSsygJiJPlJJMIjScmvQNUapgJMw
...
</pre>Nun sollen die Zeichenketten in jeweils fünf Zeichenketten zerlegt werden, wodurch eine Tabelle mit 500.000 Zeilen entsteht. Beginnen wir mit der Java-Variante aus dem vorherigen Post:<br />
<pre class="sql" name="code">create table sub_strings as
select
t.*
from
strings_to_split s,
table(split_string(s.string, ';')) t;
</pre><pre>Tabelle wurde erstellt.
Abgelaufen: <strong>00:04:37.35</strong>
</pre><pre class="sql" name="code">select count(*) from sub_strings;
</pre><pre>COUNT(*)
----------
500000
</pre>Jetzt zum Vergleich die PL/SQL-Variante:<br />
<pre class="sql" name="code">create table sub_strings_plsql as
select
t.*
from
strings_to_split s,
table(split_string_plsql(s.string, ';')) t;
</pre><pre>Tabelle wurde erstellt.
Abgelaufen: <strong>00:00:19.81</strong>
</pre><pre class="sql" name="code">select count(*) from sub_strings;
</pre><pre>COUNT(*)
----------
500000
</pre>Man sieht deutlich, dass die PL/SQL-Variante um ein Vielfaches schneller ist; mehr als 14 mal so schnell. <br />
<br />
An dieser Stelle gilt der Dank Carsten Czarski, der mich darauf aufmerksam gemacht hat, wie viel Zeit für das Context-Switching zwischen Java und SQL verloren geht.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com1tag:blogger.com,1999:blog-3685894621243440521.post-6453806225714314662011-10-06T10:12:00.002+02:002011-10-06T14:46:36.043+02:00Zeichenketten zerlegen mithilfe von Java in der DatenbankHeute geht's um die Zerlegung von Zeichenketten. Dabei soll die Trennung durch ein Trennzeichen vorgenommen werden, welches auch als regulärer Ausdruck angegeben werden kann. Die Zeichenkette 'a,b,c' enthält drei, durch Kommata getrennte, Elemente. Eine Zerlegung, mit dem Komma als Trennzeichen, soll dann die Elemente 'a', 'b' und 'c' enthalten.<br />
<br />
Die Elemente, die aus der Zerlegung hervorgehen, werden in einer Nested Table gespeichert:<br />
<pre class="sql" name="code">create or replace type string_nt as table of varchar2(100);
/
</pre>Natürlich kann die Aufgabe auch mit PL/SQL gelöst werden, jedoch enthält Java bereits eine Methode zur Lösung des Problems; die Methode <code>split</code> der Klasse <code>String</code>.<br />
<pre class="java" name="code">create or replace and compile java source named string_utilities as
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class StringUtilities
{
public static ARRAY split(String input, String delimiter) throws Exception
{
Connection connection = DriverManager.getConnection("jdbc:default:connection:");
ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("STRING_NT", connection);
String[] elements = input.split(delimiter);
return new ARRAY(aDesc, connection, elements);
}
}
/
</pre>Der Java-Code ruft die Methode <code>split</code> auf, um das erste Argument in seine Bestandteile zu zerlegen. Das zweite Argument beschreibt das Trennzeichen als regulären Ausdruck.<br />
<br />
Nun fehlt nur noch die Funktion in PL/SQL, um die Methode verwenden zu können.<br />
<pre class="sql" name="code">create or replace function split_string (
p_string_in in varchar2,
p_delimiter_in in varchar2
) return string_nt
is language java name
'StringUtilities.split(java.lang.String, java.lang.String) return oracle.sql.ARRAY';
/
</pre>Da der Rückgabetyp der Funktion eine Collection (Nested Table) ist, erfolgt der Aufruf im FROM-Teil des Select-Statements mit dem Table-Keyword.<br />
<pre class="sql" name="code">select
column_value
from
table(split_string('a,b,c,d', ','));
</pre><pre>COLUMN_VALUE
-------------
a
b
c
d
</pre>Wie bereits angedeutet, wird das Trennzeichen als regulärer Ausdruck interpretiert. Das folgende Beispiel trennt die Elemente bei einem '#' oder einem '/'.<br />
<pre class="sql" name="code">select
column_value
from
table(split_string('a#b/c#d', '(#|/)'));
</pre><pre>COLUMN_VALUE
-------------
a
b
c
d
</pre>Durch die Beschreibung als regulärer Ausdruck können auch sehr komplexe Trennzeichen beschrieben werden.<br />
<br />
Abschließend sei noch erwähnt, dass die Funktion natürlich auch in PL/SQL verwendet werden kann, wie das folgende Beispiel zeigt:<br />
<pre class="sql" name="code">declare
v_columns string_nt := split_string('empno;ename;job;sal', ';');
begin
for i in v_columns.first .. v_columns.last loop
dbms_output.put_line(i || ': ' || v_columns(i));
end loop;
end;
/
</pre><pre>1: empno
2: ename
3: job
4: sal
</pre>Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-76004969910775064732011-09-30T16:51:00.000+02:002011-09-30T16:51:33.581+02:00Data Warehouse - Modellierung auf der konzeptionellen EbeneDieser Post beschäftigt sich mit der Fragestellung, wie das Data Warehouse Modell auf der konzeptionellen Ebene aussieht. Diese Modellierung auf der Grundlage eines Entity Relationship Diagram ist natürlich unabhängig von der physischen Modellierung als Star- oder Snowflake-Schema.<br />
<br />
Eine Möglichkeit besteht darin, Dimensionen als Typ und die Ebenen innerhalb einer Dimensionen als Subtypen darzustellen. Die Hierarchie stellt sich dann als 1:n Beziehung zwischen den einzelnen Ebenen dar. Das folgende Beispiel zeigt die Modellierung einer Dimension für Produkte samt den Ebenen und deren Hierarchie:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0q3xuMlskLg6VpdKYtdYnVEKrrNNOhsLYb_epu9Qt_ruboyNz_HIMdw6aNHttCx2C842vnvYj1nE9g3TDJdkEjJPb36xBGKy-79pNZKah3ywSc4tI3tvNEKwI5WiuQ48EzhCOY5JTEgV8/s1600/dwh_1.jpg" imageanchor="1" style=""><img border="0" height="199" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0q3xuMlskLg6VpdKYtdYnVEKrrNNOhsLYb_epu9Qt_ruboyNz_HIMdw6aNHttCx2C842vnvYj1nE9g3TDJdkEjJPb36xBGKy-79pNZKah3ywSc4tI3tvNEKwI5WiuQ48EzhCOY5JTEgV8/s1600/dwh_1.jpg" /></a></div><br />
Der Cube wird dann mit der detailliertesten Ebene der Hierarchie verbunden (im Beispiel die Ebene PR_ITEM):<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhehLu5a-AuQObpkoR2rAYGqC9P_W1-ASaqwR6jPpNcJMykjIAFQCZ-qVDlAs3vRMovBC2BKQvcblzRKTGxlmqn-ZQnJJBidT9VyeLzZhk7etcvvL0oBE80qUZWc5KbeeUcpxb12ZW4nApw/s1600/dwh_2.jpg" imageanchor="1" style=""><img border="0" height="356" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhehLu5a-AuQObpkoR2rAYGqC9P_W1-ASaqwR6jPpNcJMykjIAFQCZ-qVDlAs3vRMovBC2BKQvcblzRKTGxlmqn-ZQnJJBidT9VyeLzZhk7etcvvL0oBE80qUZWc5KbeeUcpxb12ZW4nApw/s1600/dwh_2.jpg" /></a></div><br />
Dieses Modell ist somit die Grundlage für Star- und Snowflake-Schema. Für ein Snowflake-Shema ergibt sich für die Produkt Dimension dann die folgende physische Struktur:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhilS3s2QdYLUmBl0QKNKUOJ22Ma9n3x-_YZJ-StFG66VnKjUU2rNqSmcIRWmhbNRmpmZQ6U38YtJe4N43Vw46TyX_tyVCTB3lTnLUtOR17TT1_IBdbqmjDXhwa_lFVU-31pfM8hr0X1icU/s1600/dwh_3.jpg" imageanchor="1" style=""><img border="0" height="60" width="600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhilS3s2QdYLUmBl0QKNKUOJ22Ma9n3x-_YZJ-StFG66VnKjUU2rNqSmcIRWmhbNRmpmZQ6U38YtJe4N43Vw46TyX_tyVCTB3lTnLUtOR17TT1_IBdbqmjDXhwa_lFVU-31pfM8hr0X1icU/s1600/dwh_3.jpg" /></a></div><br />
Das zugehörige Star-Schema für die ausgewählte Dimension sieht wie folgt aus:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR94RsehpYa9u6Ic1icEHOzQ0ALXwTpKecFE9hxj_Ke6sGTSqwHJ1C672mIVxcvC8U67YFlhrmkcQAT4IWyonlXF_WTEZIyD-kIvflu237urV_HbKVRGlkAVigHLiKGCjYrsR0oE7IldEW/s1600/dwh_4.jpg" imageanchor="1" style=""><img border="0" height="135" width="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR94RsehpYa9u6Ic1icEHOzQ0ALXwTpKecFE9hxj_Ke6sGTSqwHJ1C672mIVxcvC8U67YFlhrmkcQAT4IWyonlXF_WTEZIyD-kIvflu237urV_HbKVRGlkAVigHLiKGCjYrsR0oE7IldEW/s400/dwh_4.jpg" /></a></div><br />
Damit wäre die Frage beantwortet, wie man ein Data Warehouse in Form eines Entity Relationship Diagrams, unabhängig von der physischen Implementierung, darstellen kann.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-11652122603551549242011-09-30T13:18:00.000+02:002011-09-30T13:18:21.918+02:00Objekttyp als Spaltentyp verwendenHeute geht's um die Verwendung von Objekttypen als Spaltentyp in einer Tabelle. Damit besteht die Möglichkeit einen Constraint an einen Typ statt an eine Tabelle zu binden. Das macht insbesondere dann Sinn, wenn der gleiche Typ mit den gleichen Restriktionen in mehreren Tabellen verwendet wird; im Normalfall ist der Constraint pro Tabelle zu erstellen, in der dieser Typ verwendet wird. Als Beispiel dient ein Typ für die Angabe von Stückzahlen, der wie folgt als Objekttyp erstellt wird:<br />
<pre class="sql" name="code">create type qty as object
(
q integer,
constructor function qty(q in integer) return self as result,
member function the_q return integer,
pragma restrict_references(the_q, wnds)
);
/
</pre>Dabei gilt die Restriktion, dass sich die Stückzahl zwischen 0 und 5000 (jeweils inklusive) befinden muss. Dieser Sachverhalt wird im Rahmen des Konstruktors überprüft und gegebenenfalls eine Exception ausgelöst.<br />
<pre class="sql" name="code">create type body qty is
constructor function qty(q in integer) return self as result is
begin
if (q >= 0 and q <= 5000) then
self.q := q;
return;
else
raise_application_error(-20010, 'This is not a valid value for type QTY.');
end if;
end;
member function the_q return integer is
begin
return self.q;
end;
end;
/
</pre>
Die zusätzliche Funktion <code>THE_Q</code> kann später dazu verwendet werden, die Stückzahl als skalaren Typ zu erhalten; im Beispiel einen Wert vom Typ <code>integer</code>. Nun kann dieser Objekttyp als Spaltentyp verwendet werden, wie das folgende Beispiel zeigt:
<pre class="sql" name="code">create table warehouse
(
product_id integer,
available qty,
constraint pk_warehouse primary key (product_id)
);
</pre>Bei jedem INSERT-Befehl wird dann der Konstruktor aufgerufen, welcher die notwendige Überprüfungen vornimmt und mitunter eine Exception auslöst; auch bei einem UPDATE-Befehl erfolgt eine enstprechende Überprüfung.
<pre class="sql" name="code">insert into warehouse (product_id, available)
values (1, qty(100));
-- 1 rows inserted
insert into warehouse (product_id, available)
values (2, qty(1000));
-- 1 rows inserted
insert into warehouse (product_id, available)
values (3, qty(10000));
-- SQL-Fehler: ORA-20010: This is not a valid value for type QTY.
</pre>Bei Abfragen kann man dann auf die Funktion <code>THE_Q</code> zurückgreifen, um die Stückzahl als <code>integer</code> zu erhalten.
<pre class="sql" name="code">select
w.product_id,
w.available.the_q() qty_int
from
warehouse w;
</pre>Natürlich kann der erstellte Typ in mehreren Tabellen verwendet werden. Somit besteht die Möglichkeit einen Constraint pro Typ statt pro Tabelle, in der dieser Typ verwendet wird, zu definieren.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-14483568847468480232011-09-20T16:05:00.000+02:002011-09-20T16:05:45.820+02:00Systematische Herleitung von SQL durch LogikHeute geht's um die Verwendung von Logik zur systematischen Herleitung von SQL. Gesucht wird eine Abfrage, um die Mitarbeiter auszugeben, welche an allen Projekten beteiligt sind. Dazu werden die bekannten Tabellen <code>EMP</code> und <code>DEPT</code> um die Tabellen <code>PROJECT</code> und <code>EMP_PROJECT</code> erweitert. Die Tabelle <code>PROJECT</code> speichert Projekte während die Tabelle <code>EMP_PROJECT</code> eine Zuordnung von Mitarbeitern zu Projekten vornimmt.<br />
<pre class="sql" name="code">create table project
(
pno integer not null,
pname varchar2(15) not null,
constraint pk_project primary key (pno)
);
create table emp_project
(
empno integer not null,
pno integer not null,
percentage number(5,2) not null,
constraint pk_emp_project primary key (empno, pno)
);
alter table emp_project
add constraint fk_emp_project_emp
foreign key (empno)
references emp (empno);
alter table emp_project
add constraint fk_emp_project_project
foreign key (pno)
references project (pno);
insert into project (pno, pname)
values (1, 'X100C');
insert into project (pno, pname)
values (2, 'P00A1');
insert into project (pno, pname)
values (3, 'P00A2');
insert into emp_project (empno, pno, percentage)
values (7369, 2, 30.00);
insert into emp_project (empno, pno, percentage)
values (7521, 1, 25.00);
insert into emp_project (empno, pno, percentage)
values (7566, 1, 10.00);
insert into emp_project (empno, pno, percentage)
values (7521, 2, 50.00);
insert into emp_project (empno, pno, percentage)
values (7521, 3, 50.00);
insert into emp_project (empno, pno, percentage)
values (7654, 3, 75.00);
insert into emp_project (empno, pno, percentage)
values (7654, 1, 5.00);
insert into emp_project (empno, pno, percentage)
values (7788, 1, 100.00);
insert into emp_project (empno, pno, percentage)
values (7844, 2, 65.00);
insert into emp_project (empno, pno, percentage)
values (7934, 2, 15.00);
insert into emp_project (empno, pno, percentage)
values (7900, 1, 80.00);
commit;
</pre>Kommen wir nun zurück zur Fragestellung, welche Mitarbeiter an allen Projekten beteiligt sind. Alternativ könnte man das auch so formulieren:<br />
<br />
"Ausgabe aller Mitarbeiter für die <strong>für alle</strong> Projekte eine entsprechende Projektzuordnung <strong>exstiert</strong>".<br />
<br />
Genau dafür gibt es den Allquantor (<code>FORALL</code>) und den Existenzquantor (<code>EXISTS</code>) aus der Prädikatenlogik.<br />
<br />
<code>FORALL x ( p( x ) )</code> ist genau dann wahr, wenn für alle <code>x</code> das Prädikat <code>p( x )</code> wahr ist. Also handelt es sich im Wesentlichen um eine Kurzschreibweise für eine UND-Verknüpfung der Form:<br />
<br />
<code>p( x<sub>1</sub> ) AND p( x<sub>2</sub> ) AND ... AND p( x<sub>n</sub> )</code><br />
<br />
<code>EXISTS x ( p( x ) )</code> ist genau dann wahr, wenn ein <code>x</code> existiert, für das dass Prädikat <code>p( x )</code> wahr ist. Also handelt es sich im Wesentlichen um eine ODER-Verknüpfung der Form:<br />
<br />
<code>p( x<sub>1</sub> ) OR p( x<sub>2</sub> ) OR ... OR p( x<sub>n</sub> )</code><br />
<br />
Eine mögliche Formulierung mit dem Allquantor (<code>FORALL</code>) und dem Existenzquantor (<code>EXISTS</code>) sieht dann so aus:<br />
<pre>{ E } WHERE
FORALL P (
EXISTS EP ( EP.EMPNO = E.EMPNO AND EP.PNO = P.PNO ) )
</pre>Da SQL kein FORALL unterstützt macht man sich folgende Regel zu Nutze:<br />
<pre>FORALL x ( p( x ) ) ≡ NOT EXISTS ( NOT p( x ) )
</pre>Eine Anwendung auf die erste Formulierung ergibt dann:<br />
<pre>{ E } WHERE
NOT EXISTS P (
NOT EXISTS EP ( EP.EMPNO = E.EMPNO AND EP.PNO = P.PNO ) )
</pre>Diese Variante kann man fast eins zu eins in SQL überführen:<br />
<pre class="sql" name="code">select *
from emp e
where not exists
(
select *
from project p
where not exists
(
select *
from emp_project ep
where ep.empno = e.empno and ep.pno = p.pno
)
);
</pre>Damit erhält man eine Lösung für die Problemstellung, auf die man so mitunter nicht direkt kommt.<br />
<br />
Mit etwas Übung und den notwendigen Kenntnissen aus dem Bereich der Logik kann man damit "trial and error" bei vielen Problemen vermeiden und eine Lösung systematisch aus einer logischen Formulierung herleiten. <br />
<br />
Als weiterführende Literatur zu diesem Thema sei das Buch "<a href="http://www.amazon.de/gp/product/0596523068/ref=s9_simh_gw_p14_d0_g14_i2?pf_rd_m=A3JWKAKR8XB7XF&pf_rd_s=center-2&pf_rd_r=1S501TTHXZ9HM5RWNMRD&pf_rd_t=101&pf_rd_p=463375173&pf_rd_i=301128">SQL and Relational Theory</a>" von Chris Date und das Buch "<a href="http://www.amazon.de/Applied-Mathematics-Database-Professionals-Experts/dp/1590597451/ref=pd_sim_eb3">Applied Mathematics for Database Professionals</a>" von Lex de Haan und Toon Koppelaars empfohlen.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-71007697050633128152011-09-06T17:26:00.003+02:002011-09-07T09:39:15.729+02:00Logik und SQLHeute geht's um die Verwendung von Kenntnissen aus der Aussagenlogik und wie man diese im Rahmen von SQL anwenden kann. Als Beispiel dient die Forderung, dass ein Analyst ein Gehalt von mindestens 2500 erhalten muss. <br />
<br />
Diese Forderung kann man wie folgt formulieren:<br />
<pre>WENN ( JOB = 'ANALYST' ) DANN ( SAL >= 2500 )
</pre>Das entspricht der Implikation in der Aussagenlogik:<br />
<pre>( JOB = 'ANALYST' ) -> ( SAL >= 2500 )
</pre>Die Implementierung umfasst die Definition eines Check-Constraints für die Tabelle <code>EMP</code>. Zwar existiert in SQL der CASE-Operator, jedoch kann dieser bei der Spezifikation der Bedindung nicht verwendet werden; dies gilt auch für die Verwendung von PL/SQL. Man kann die Forderung aber mithilfe der Operatoren <code>NOT</code> und <code>OR</code> angeben, welche beide von SQL unterstützt werden. Die folgende Warheitstabelle zeigt die Gültigkeit von:<br />
<pre>A → B ≡ ¬A ∨ B
</pre><table border="1"><col width="60"/> <col width="60"/> <col width="60"/> <col width="60"/> <thead align="center">
<tr> <th>A</th> <th>B</th> <th>A → B</th> <th>¬ A ∨ B</th> </tr>
</thead> <tbody align="center">
<tr> <td>T</td> <td>T</td> <td>T</td> <td>T</td> </tr>
<tr> <td>T</td> <td>F</td> <td>F</td> <td>F</td> </tr>
<tr> <td>F</td> <td>T</td> <td>T</td> <td>T</td> </tr>
<tr> <td>F</td> <td>F</td> <td>T</td> <td>T</td> </tr>
</tbody> </table><br />
Man kann erkennen, dass der Warheitswerteverlauf der dritten Spalte mit dem der vierten Spalte übereinstimmt. Das macht man sich nun zu Nutze, um die Bedingung umzuformen:<br />
<pre>NOT ( JOB = 'ANALYST' ) OR ( SAL >= 2500 )
</pre>Der erste Teil kann wie folgt vereinfacht werden:<br />
<pre>( JOB != 'ANALYST' ) OR ( SAL >= 2500 )
</pre>Diese Formulierung kann man nun im Rahmen des Check-Constraints verwenden:<br />
<pre class="sql" name="code">alter table emp
add constraint c_emp_analyst_sal
check ( job != 'ANALYST' or sal >= 2500 );
</pre>Schon dieses einfache Beispiel zeigt, dass grundlegende Kenntnisse aus dem Bereich der Logik von Vorteil sind. Natürlich gibt es dazu noch weitere interessante Themen, wie zum Beispiel die systematische Herleitung von SQL Abfragen durch die Verwendung von Prädikatenlogik; aber dazu mehr in einem anderen Post.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com2tag:blogger.com,1999:blog-3685894621243440521.post-23031305240428906732011-08-31T20:18:00.000+02:002011-08-31T20:18:55.466+02:00Locking mithilfe von DBMS_LOCKHeute geht's um die Verwendung von <code>DBMS_LOCK</code> zum Sicherstellen von Integritätsbedingungen. Zur Demonstration wollen wir sicherstellen, dass pro Abteilung nur maximal ein Manager existiert. Wie bereit im vergangenen Post wird ein Compound Trigger verwendet:<br />
<pre class="sql" name="code">create trigger check_manager_c_iu for insert or update of job, deptno on emp
when (new.job = 'MANAGER' or old.deptno != new.deptno)
compound trigger
type departments_nt is table of dept.deptno%type;
departments_tab departments_nt := departments_nt();
before each row is
begin
departments_tab.extend;
departments_tab( departments_tab.last ) := :new.deptno;
end before each row;
after statement is
v_manager_count integer := 0;
begin
departments_tab := set( departments_tab );
for i in 1 .. departments_tab.count loop
select
count(*)
into
v_manager_count
from
emp
where
job = 'MANAGER' and deptno = departments_tab(i);
if ( v_manager_count > 1 ) then
raise_application_error
(
-20010,
'No more than one manager allowed in department ' || departments_tab(i) || '.'
);
end if;
end loop;
end after statement;
end check_manager_c_iu;
/
</pre>Der <code>BEFORE EACH ROW</code> Teil speichert die Abteilungen in einer Nested Table, während der <code>AFTER STATEMENT</code> Teil die eigentliche Überprüfung vornimmt. Jedoch besteht immer noch die Möglichkeit, dass die Bedinung verletzt wird.<br />
<br />
Zur Demonstration dienen zwei Sessions (T1, T2), welche die folgenden Operationen ausführen:<br />
<br />
t<sub>0</sub> T1: <code>INSERT MANAGER 'X' IN DEPARTMENT 40</code><br />
t<sub>1</sub> T2: <code>INSERT MANAGER 'Y' IN DEPARTMENT 40</code><br />
t<sub>2</sub> T1: <code>COMMIT;</code><br />
t<sub>3</sub> T2: <code>COMMIT;</code><br />
<br />
Anmerkung: Wir nehmen an, dass Abteilung 40 vor dem Zeitpunkt t<sub>0</sub> keinen Manager besitzt. <br />
<br />
Der INSERT-Befehl zum Zeitpunkt t<sub>0</sub> funktioniert, da noch kein Manager für Abteilung 40 existiert. Jedoch geht auch der zweite INSERT-Befehl von T2 durch, da T1 noch kein <code>COMMIT</code> ausgeführt hat. Nach Zeitpunkt t<sub>3</sub> existieren somit zwei Manager in Abteilung 40; eine Verletzung der geforderten Bedingung. <br />
<br />
Eine mögliche Lösung für dieses Problem stellt <code>DBMS_LOCK</code> in Form der Prozedur <code>ALLOCATE_UNIQUE</code> und der Funktion <code>REQUEST</code> bereit. <code>ALLOCATE_UNIQUE</code> ordnet einem Namen eine eindeutige Nummer zu und gibt diese zurück. Die eigentliche Anfrage für diesen benannten Lock wird durch die Funktion <code>REQUEST</code> ausgeführt. Da die Prozedur <code>ALLOCATE_UNIQUE</code> einen impliziten COMMIT durchführt ist eine autonome Transaktion erforderlich.<br />
<br />
Das nachfolgende Code Listing enthält eine Funktion, welche <code>ALLOCATE_UNIQUE</code> in einer anderen Transaktion aufruft und den Lock Handle zurückliefert. Die Prozedur <code>REQUEST_LOCK</code> stellt dann die Anfrage für den Lock im exclusive mode.<br />
<pre class="sql" name="code">create function allocate_unique(p_lockname_in in varchar2) return varchar2
is
pragma autonomous_transaction;
v_lockhandle varchar2(128);
begin
dbms_lock.allocate_unique(
upper(p_lockname_in),
v_lockhandle,
60*10
);
return v_lockhandle;
end;
/
create procedure request_lock(p_lockname_in in varchar2)
is
v_lockhandle varchar2(128);
v_return_code number;
begin
v_lockhandle := allocate_unique(p_lockname_in);
v_return_code := dbms_lock.request(
lockhandle => v_lockhandle,
lockmode => dbms_lock.x_mode,
timeout => 10,
release_on_commit => true
);
if pl_return not in (0,4) then
raise_application_error
(
-20010,
'Unable to get the requested lock ' || p_lockname_in || '.');
end if;
end;
/
</pre>Eine Möglichkeit besteht nun darin, die Prozedur <code>REQUEST_LOCK</code> im <code>AFTER STATEMENT</code> Teil des Triggers aufzurufen; der Lock trägt den Namen CHECK_MANAGER.<br />
<pre class="sql" name="code">...
after statement is
v_manager_count integer := 0;
begin
request_lock('CHECK_MANAGER');
...
end after statement;
...
</pre>Damit ist das Problem zwar gelöst, jedoch kann das Locking noch granularer gestaltet werden. Dazu wird ein Lock pro Abteilung definiert, wie das folgende Listing zeigt.<br />
<pre class="sql" name="code">...
after statement is
v_manager_count integer := 0;
begin
departments_tab := set( departments_tab );
for i in 1 .. departments_tab.count loop
request_lock( 'CHECK_MANAGER_DEPT_' || departments_tab(i) );
...
end loop;
end after statement;
...
</pre>Das ganze soll nun mithilfe von zwei Sessions getestet werden.<br />
<br />
T1:<br />
<pre class="sql" name="code">insert into emp (empno, ename, job, mgr, hiredate, sal, deptno)
values (7974, 'CLARKSON', 'MANAGER', 7839, sysdate, 3750, 40);
-- 1 rows inserted
</pre>T2:<br />
<pre class="sql" name="code">insert into emp (empno, ename, job, mgr, hiredate, sal, deptno)
values (7975, 'JOHNSON', 'MANAGER', 7839, sysdate, 3500, 40);
-- WAIT (maximal 10 Sekunden, wie in der Prozedur REQUEST_LOCK angegeben)
</pre>T1:<br />
<pre class="sql" name="code">commit; -- weniger als 10 Sekunden nach dem INSERT
</pre>T2:<br />
<pre class="sql" name="code">--SQL-Fehler: ORA-20010: No more than one manager allowed in department 40.
--ORA-06512: in "EXAMPLE.CHECK_MANAGER_C_IU", Zeile 27
--ORA-04088: Fehler bei der Ausführung von Trigger 'EXAMPLE.CHECK_MANAGER_C_IU'
</pre>Das Beispiel und die Verwendung von DBMS_LOCK ist angelehnt an das Buch "<a href="http://www.amazon.de/Applied-Mathematics-Database-Professionals-Experts/dp/1590597451/ref=sr_1_2?ie=UTF8&qid=1314733194&sr=8-2">Applied Mathematics for Database Professionals</a>" von Lex de Haan und Toon Koppelaars. Dort wird das Ganze noch detaillierter und mit zahrleichen Beispielen samt der notwendigen Theorie behandelt.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-56747818638807841342011-08-26T11:06:00.000+02:002011-08-26T11:06:19.437+02:00Compound Trigger im Falle von Mutating TableHeute geht's um die Verwendung von Compund Triggern. Ein Compound Trigger fasst die verschiedenen Ereignisse (BEFORE STATEMENT, BEFORE EACH ROW...) in einem Block zusammen. Zudem enthält ein Compound Trigger einen Abschnitt zur Deklaration von Variablen der dann von allen Ereignissen verwendet werden kann. Genau dieser Abschnitt eigent sich dann für die Behandlung von Mutating Table Problemen. Zur Demonstration soll in der Tabelle <code>EMP</code> sichergestellt werden, dass die Summe der Gehält ein Budget der jeweiligen Abteilung nicht überschreitet.<br />
<br />
Zunächst wird dazu die Tabelle <code>DEPT</code> um die Spalte <code>SALBUDGET</code> ergänzt:<br />
<pre class="sql" name="code">alter table dept add salbudget integer;
</pre>Dieser Post verwendet die folgenden Werte für die Spalte <code>SALBUDGET</code>:<br />
<pre class="sql" name="code">select
deptno, salbudget
from
dept;
</pre><pre>DEPTNO SALBUDGET
-------------- --------------
10 13000
20 15000
30 11000
40 10000
</pre>Der Compound Trigger soll nun sicherstellen, dass das Budget der Abteilung nicht überschritten wird. Dazu werden die betroffenen Abteilungen im <code>BEFORE EACH ROW</code> Teil in einer Nested Table gespeichert, um diese anschließend im <code>AFTER STATEMENT</code> Teil zu überprüfen.<br />
<pre class="sql" name="code">create trigger check_salbudget_c_iu for insert or update of sal, deptno on emp
compound trigger
type dept_nt is table of dept.deptno%type;
v_dept_tab dept_nt := dept_nt();
before each row is
begin
if
(
inserting or (updating and (:new.sal > :old.sal or :new.deptno != :old.deptno))
)
then
v_dept_tab.extend;
v_dept_tab(v_dept_tab.last) := :new.deptno;
end if;
end before each row;
after statement is
v_difference integer;
begin
v_dept_tab := set(v_dept_tab);
for i in 1 .. v_dept_tab.count loop
select
(select sum(sal) from emp where deptno = v_dept_tab(i))
-
(select salbudget from dept where deptno = v_dept_tab(i))
into
v_difference
from
dual;
if (v_difference > 0) then
raise_application_error
(
-20010,
'Budget exceeded for department ' || v_dept_tab(i) || '.'
);
end if;
end loop;
end after statement;
end check_salbudget_c_iu;
</pre>Anmerkung: Die Funktion <code>SET</code> in Zeile 23 stellt sicher, dass eine Abteilung nicht mehrfach überprüft werden muss indem doppelte Einträge aus der Nested Table entfernt werden. Hinzu kommt, dass ein UPDATE nur dann zu einer Überschreitung des Budgets führen kann, wenn das Gehalt erhöht wird order sich die Abteilung ändert; dies wird in Zeile 12 überpüft.<br />
<br />
Nun sollen zum Test die Gehälter aller Personen in Abteilung 30 um 20% erhöht werden:<br />
<pre class="sql" name="code">update
emp
set
sal = sal * 1.20
where
deptno = 30;
</pre>Die Summe der Gehälter in Abteilung 30 beträgt nach dieser Veränderung 11040; somit eine Überschreitung des Budgets von 11000. Man erhält die entsprechende Fehlermeldung:<br />
<pre>SQL-Fehler: ORA-20010: Budget exceeded for department 30.
ORA-06512: in "EXAMPLE.CHECK_SALBUDGET_C_U", Zeile 26
ORA-04088: Fehler bei der Ausführung von Trigger 'EXAMPLE.CHECK_SALBUDGET_C_U
</pre>Ein Compound Trigger bietet also im Wesentlichen die Funktionalität, die man vorher (vor Oracle 11g) mithilfe von Package Variablen implementieren musste.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-72261448786329560672011-08-21T16:45:00.002+02:002011-08-21T16:48:19.273+02:00Berechnung der Potenzmenge mithilfe von POWERMULTISETHeute geht's um die Berechnung der Potenzmenge mithilfe der vorhandenen Table Function <code>POWERMULTISET</code>. Als Potenzmenge bezeichnet man die Menge aller Teilmengen einer gegebenen Grundmenge. Die Potenzmenge einer endlichen Menge mit n Elementen enthält 2<sup>n</sup> Elemente. Nehmen wir als Beispiel die Menge M = { 1, 2, 3 }. Da es sich um eine endliche Menge mit 3 Elementen handelt, enthält die Potenzmenge 2<sup>3</sup> = 8 Elemente, die da wären:<br />
<pre>1. { }
2. { 1 }
3. { 2 }
4. { 3 }
5. { 1, 2 }
6. { 1, 3 }
7. { 2, 3 }
8. { 1, 2, 3 }
</pre>Anmerkung: Eine Menge enthält keine Ordnung, sodass z.B. <code>{ 1, 2 } = { 2, 1 }</code> ist.<br />
<br />
Die Table Function <code>POWERMULTISET</code> erwartet als Argument eine Nested Table. Dazu erstellen wir zunächst den entsprechenden Typ:<br />
<pre class="sql" name="code">create type num_nt as table of integer;
/
</pre>Jetzt kann die Funktion aufgerufen werden:<br />
<pre class="sql" name="code">select
column_value
from
table(powermultiset(num_nt(1,2,3)))
order by
cardinality(column_value);
</pre>Man erhält nun die folgenden sieben Zeilen:<br />
<pre>COLUMN_VALUE
----------------------------
EXAMPLE.NUM_NT('1')
EXAMPLE.NUM_NT('2')
EXAMPLE.NUM_NT('3')
EXAMPLE.NUM_NT('1','2')
EXAMPLE.NUM_NT('1','3')
EXAMPLE.NUM_NT('2','3')
EXAMPLE.NUM_NT('1','2','3')
</pre>Man erkennt, dass eine Teilmenge fehlt: die leere Menge; somit verhält sich die Implementierung leider nicht ganz so wie in der Mathematik.<br />
<br />
Neben der Table Function <code>POWERMULTISET</code> gibt es auch die Table Function <code>POWERMULTISET_BY_CARDINALITY</code>, die alle Teilmengen zu einer gegebenen Kardinalität zurückgibt. Man erhält alle zwei-elementigen Teilmengen mithilfe der folgenden Abfrage:<br />
<pre class="sql" name="code">select
column_value
from
table(powermultiset_by_cardinality(num_nt(1,2,3),2));
</pre>Man erhält die folgenden drei Zeilen:<br />
<pre>COLUMN_VALUE
----------------------------
EXAMPLE.NUM_NT('1','2')
EXAMPLE.NUM_NT('1','3')
EXAMPLE.NUM_NT('2','3')
</pre>Also wiedermal ein nettes Feature, welches man mitunter bei einigen Problemen verwenden kann.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-88293158181857333752011-08-13T22:14:00.002+02:002011-08-13T22:29:59.629+02:00SQL*Loader und die Zeichensatzkonvertierung mit gVimHeute wollen wir eine, mit latin1 kodierte, Datei mithilfe von SQL*Loader in einer Datenbank, die mit UTF-8 arbeitet, bereitstellen. Die Datei mit dem Namen <code>mitarbeiter.data</code> enthält die folgenden Zeilen:<br />
<pre class="sql" name="code">ID,VORNAME,NACHNAME
1,Karl,Müller
2,Céline,Dupont
3,Henry,Weiß
</pre>Dazu bietet gVim eine einfache Möglichkeit an, die latin1 kodierte Datei UTF-8 kompatibel zu speichern. Nach dem Öffnen der Datei kann man sich diese zum einen in einer anderen Kodierung anzeigen lassen und zum anderen in einer anderen Kodierung abspeichern. <br />
<br />
Um sich die aktuelle Kodierung, auf der die Darstellung beruht, anzeigen zu lassen genügt der folgende Befehl:<br />
<pre class="sql" name="code">:set enc
</pre>Öffnet man die Datei mit gVim erhält man in diesem Beispiel:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZbo8JognYZS-zl7rBq_S9ammiQ9G1usg9VFIe8NvMQs3oVpu9ux2GDPZYSQFRhKguInYGrwW421XNy1MCmzicFksjUqKt4hs7NdvRb7U4RlBcNGRkHmFuvKfedyrSCINxhko5XnPCi8Zs/s1600/file_latin1.jpg" imageanchor="1" style=""><img border="0" height="157" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZbo8JognYZS-zl7rBq_S9ammiQ9G1usg9VFIe8NvMQs3oVpu9ux2GDPZYSQFRhKguInYGrwW421XNy1MCmzicFksjUqKt4hs7NdvRb7U4RlBcNGRkHmFuvKfedyrSCINxhko5XnPCi8Zs/s400/file_latin1.jpg" /></a></div><br />
Zum einen kann man sich nun die Datei UTF-8 kodiert anzeigen lassen; das geht mit dem Befehl:<br />
<pre class="sql" name="code">:set enc=utf8
</pre>Anmerkung: dies ändert nur die Anzeige. Man erhält dann:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj79Fa_mYmGHSP4F4GhG_2rrtACLM09eSh6T4OcGYgH5orjzVYvXvfmubZC9AU1KaD0tjwQ3rMEeIwFPnieTFMvmIbOHFJpsH5-ly_DmHDUhCGASDkCpCaxPScs3lqhdquUL57A7lp0sccr/s1600/file_utf8.jpg" imageanchor="1" style=""><img border="0" height="157" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj79Fa_mYmGHSP4F4GhG_2rrtACLM09eSh6T4OcGYgH5orjzVYvXvfmubZC9AU1KaD0tjwQ3rMEeIwFPnieTFMvmIbOHFJpsH5-ly_DmHDUhCGASDkCpCaxPScs3lqhdquUL57A7lp0sccr/s400/file_utf8.jpg" /></a></div><br />
Nun speichern wir die Datei UTF-8 kodiert mit dem Befehl:<br />
<pre class="sql" name="code">:w ++enc=utf8
</pre>Kommen wir nun zu SQL*Loader und dem entsprechendem Control File:<br />
<pre class="sql" name="code">OPTIONS(SKIP = 1, DIRECT = TRUE)
LOAD DATA
INFILE 'mitarbeiter.data'
BADFILE 'mitarbeiter.bad'
DISCARDFILE 'mitarbeiter.dsc'
TRUNCATE
INTO TABLE mitarbeiter
FIELDS TERMINATED BY ","
(
id integer external,
vorname char,
nachname char
)
</pre>Nachdem man in der Konsole die Umgebungsvariable <code>NLS_LANG</code> angepasst hat erhält man den folgenden Output:<br />
<pre name="code">D:\Oracle\blog_posting>set NLS_LANG = GERMAN_GERMANY.UTF8
D:\Oracle\blog_posting>sqlldr user@tns control=mitarbeiter.ctl
Kennwort:
SQL*Loader: Release 11.2.0.1.0 - Production on Sa Aug 13 21:44:18 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Ladevorgang beendet
</pre>Jetzt betrachten wir den Inhalt der Tabelle <code>mitarbeiter</code> und erhalten:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQZTsSV9AcTNTECqZquhlr08-dRVSx3SME8WTNMLCG9D4w7qDzVyjPL0fk6PUYR-A45zzuO7XfW9CbhhfHw_wMJ4jFrGxJQGTLLUzWPdeSkY10SS3ztSVkfRnj8KSh29Jvs7ce7Tx867lj/s1600/sqlplus_output.jpg" imageanchor="1" style=""><img border="0" height="166" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQZTsSV9AcTNTECqZquhlr08-dRVSx3SME8WTNMLCG9D4w7qDzVyjPL0fk6PUYR-A45zzuO7XfW9CbhhfHw_wMJ4jFrGxJQGTLLUzWPdeSkY10SS3ztSVkfRnj8KSh29Jvs7ce7Tx867lj/s400/sqlplus_output.jpg" /></a></div><br />
Anmerkung: die Umgebungsvariable <code>NLS_LANG</code> wurde zuvor auf <code>GERMAN_GERMANY.WE8PC850</code> gesetzt.<br />
<br />
Damit sind wir am Ende vom Post angelangt. Insbesondere die interaktive Ansicht der Datei in verschiedenen Kodierungen erscheint mir ein besonders nützliches Feature von gVim, das mir bis dato unbekannt war.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-39519412038125252882011-08-08T17:21:00.001+02:002011-08-08T17:42:25.187+02:00Multiple Regression mit UTL_NLAHeute geht's erneut um die multiple Regression, die in diesem Beispiel mithilfe von <code>UTL_NLA</code> durchgeführt wird. <code>UTL_NLA</code> stellt dazu <code>BLAS</code> (Basic Linear Algebra Subroutines) und <code>LAPACK</code> (Linear Algebra PACKage) bereit.<br />
<br />
Wie im vorherigen <a href="http://ora-sql-plsql.blogspot.com/2011/07/multiple-regression-mit-olsregression.html">Post</a> zu diesem Thema, werden die folgenden Daten verwendet:<br />
<br />
y = Nachgefragte Menge in 1000 Stück<br />
x<sub>1</sub> = Werbeausgaben in 100.000 Euro für Printmedien<br />
x<sub>2</sub> = Werbeausgaben in 100.000 Euro für Fernsehen<br />
x<sub>3</sub> = Preis pro Mengeneinheit in 100 Euro<br />
<br />
<table border="1" cellpadding="5" cellspacing="0" width="200"><thead align="left">
<tr><th align="left">y</th><th>x<sub>1</sub></th><th>x<sub>2</sub></th><th>x<sub>3</sub></th></tr>
</thead><tbody>
<tr> <td>500</td> <td>1</td> <td>1</td> <td>20</td> </tr>
<tr> <td>800</td> <td>3</td> <td>1</td> <td>20</td> </tr>
<tr> <td>1500</td> <td>3</td> <td>3</td> <td>18</td> </tr>
<tr> <td>2500</td> <td>6</td> <td>4</td> <td>15</td> </tr>
<tr> <td>3200</td> <td>6</td> <td>6</td> <td>12</td> </tr>
</tbody></table><br />
Man erhält die Koeffizienten der Regressionsfunktion in Form des Vektors b durch:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizrwTQqcY40uyU5zAoHmWaen-AMRLNSIzlUBtYCE3du_1fy-cXTmNcxxfOuwYn3O6jE_ILEvlXlyA8lItDCARgB-FdM25_LvZIWX3n8Dldh2IVhQok0fgzFye60nKEtDoZMwc6DUolFydV/s1600/b.jpg" imageanchor="1" style=""><img border="0" height="37" width="161" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizrwTQqcY40uyU5zAoHmWaen-AMRLNSIzlUBtYCE3du_1fy-cXTmNcxxfOuwYn3O6jE_ILEvlXlyA8lItDCARgB-FdM25_LvZIWX3n8Dldh2IVhQok0fgzFye60nKEtDoZMwc6DUolFydV/s400/b.jpg" /></a></div><br />
Die Matrix X im Beispiel:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsietEJDNT_1RqNLD-GSsQtO-2_soB9Z1jE3QtK9whkuxIr-l0Im2_rFuhhmNHLRT-hIzGPynHk3nmLddm5orwYDUkXbXIFvsoooU_Ny8kL3Q6v5M0p-iJoZPGxHBnjo2fmLyiP37ZvTHs/s1600/matrix_x.jpg" imageanchor="1" style=""><img border="0" height="110" width="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsietEJDNT_1RqNLD-GSsQtO-2_soB9Z1jE3QtK9whkuxIr-l0Im2_rFuhhmNHLRT-hIzGPynHk3nmLddm5orwYDUkXbXIFvsoooU_Ny8kL3Q6v5M0p-iJoZPGxHBnjo2fmLyiP37ZvTHs/s400/matrix_x.jpg" /></a></div><br />
Der Vektor y im Beispiel:<br />
<br />
<div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPkZIw6BqQb9ktBVavi7iTe_oL8fPWJUd7MqIVkzBuDtMBqOh_8QodaPeDStMeb5t19VXRo_iYhd_6ovE5GRXOzLl5bw_udA8UpeTUHH2ysoUqEALE2Z4mLgjNDAXuindQepRVGq8RlAhb/s1600/vector_y.jpg" imageanchor="1" style=""><img border="0" height="112" width="123" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPkZIw6BqQb9ktBVavi7iTe_oL8fPWJUd7MqIVkzBuDtMBqOh_8QodaPeDStMeb5t19VXRo_iYhd_6ovE5GRXOzLl5bw_udA8UpeTUHH2ysoUqEALE2Z4mLgjNDAXuindQepRVGq8RlAhb/s400/vector_y.jpg" /></a></div><br />
Die Berechnung des Vektors b umfasst also die Bestimmung der Inversen, die Multiplikation von Matrizen und die Multiplikation einer Matrix mit einem Vektor. All diese Operationen lassen sich mithilfe von <code>UTL_NLA</code> durchführen. Für die Multiplikation von Matrizen wird <code>BLAS_GEMM</code> verwendet, für die Multiplikation einer Matrix mit einem Vektor wird <code>BLAS_GEMV</code> verwendet und für die Lösung des Gleichungssystems kommt <code>LAPACK_GESV</code> zum Einsatz.<br />
<br />
Die Werte für die Matrizen werden entweder Zeilen- oder Spaltenweise angeben, je nachdem für welches Argument man sich für den Parameter PACK entscheidet. Im folgenden Beispiel wurde als Argument für den Parameter PACK 'R' gewählt. Die Deklaration stellt sich dann wie folgt dar:<br />
<pre class="sql" name="code">v_matrix_xt UTL_NLA_ARRAY_DBL := UTL_NLA_ARRAY_DBL( 1 , 1 , 1 , 1 , 1,
1 , 3 , 3 , 6 , 6,
1 , 1 , 3 , 4 , 6,
20, 20, 18, 15, 12 );
v_matrix_x UTL_NLA_ARRAY_DBL := UTL_NLA_ARRAY_DBL( 1 , 1 , 1 , 20,
1 , 3 , 1 , 20,
1 , 3 , 3 , 18,
1 , 6 , 4 , 15,
1 , 6 , 6 , 12 );
</pre>Der Aufruf der Prozedur <code>UTL_NLA.BLAS_GEMM</code> ermittelt nun die Matrix X<sup>T</sup>X.<br />
<pre class="sql" name="code">UTL_NLA.BLAS_GEMM(
transa => 'N',
transb => 'N',
m => 4,
n => 4,
k => 5,
alpha => 1,
a => v_matrix_xt,
lda => 5,
b => v_matrix_x,
ldb => 4,
beta => 0,
c => v_matrix_xtx,
ldc => 4,
pack => 'R');
</pre>Nun wird der Vektor X<sup>T</sup>y mithilfe von <code>UTL_NLA.BLAS_GEMV</code> bestimmt:<br />
<pre class="sql" name="code">UTL_NLA.BLAS_GEMV(
trans => 'N',
m => 4,
n => 5,
alpha => 1,
a => v_matrix_xt,
lda => 5,
x => v_vector_y,
incx => 1,
beta => 0,
y => v_vector_xty,
incy => 1,
pack => 'R');
</pre>Im finalen Schritt, zur Bestimmung der Koeffizienten, ist das folgende Gleichungssystem zu lösen:<br />
<br />
X<sup>T</sup>Xb=X<sup>T</sup>y<br />
<br />
Diese Aufgabe erledigt die Prozedur <code>UTL_NLA.LAPACK_GESV</code>, wobei sich das Ergebnis nach dem Aufruf in der Variablen befindet, die als Argument für den Parameter b übergeben wurde.<br />
<pre class="sql" name="code">UTL_NLA.LAPACK_GESV(
n => 4,
nrhs => 1,
a => v_matrix_xtx,
lda => 4,
ipiv => v_matrix_p,
b => v_vector_xty,
ldb => 1,
info => v_result,
pack => 'R');
</pre>Die Ausgabe der Koeffizienten ergibt:<br />
<pre class="sql" name="code">for i in 1..v_vector_xty.count loop
dbms_output.put_line('b' || (i-1) || ' = ' || to_number(v_vector_xty(i)));
end loop;
b0 = 1440.8163265303169
b1 = 168.36734693877926
b2 = 266.32653061226313
b3 = -69.38775510202751
</pre>Die Regressionsfunktion ergibt sich somit zu:<br />
<br />
y = 1440,8163 + 168,3673∙x<sub>1</sub> + 266,3265∙x<sub>2</sub> - 69,3878∙x<sub>3</sub><br />
<br />
Abschließend sei noch erwähnt, dass eine Matrix bzw. ein Vektor vom Typ <code>UTL_NLA_ARRAY_DBL</code> bzw. <code>UTL_NLA_ARRAY_FLT</code> maximal 1.000.000 Elemente enthalten kann.<br />
<br />
Natürlich befinden sich die Daten meist in einer Tabelle und man will die Matrix nicht manuell angeben. Vielmehr möchte man die Elemente durch SQL und PL/SQL gewinnen und in einer Variablen vom entsprechenden Typ speichern.<br />
<br />
Aber das ist was für einen anderen Post...Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com4tag:blogger.com,1999:blog-3685894621243440521.post-83800761697899265272011-08-03T14:16:00.001+02:002011-08-03T14:17:41.026+02:00BINARY_DOUBLE und die implizite Typ-KonvertierungHeute geht's um die Verwendung des Datentyps <code>BINARY_DOUBLE</code> bzw. <code>BINARY_FLOAT</code> und die Vermeidung von impliziter Typ-Konvertierung.<br />
<br />
Als Beispiel dient das <a href="http://de.wikipedia.org/wiki/Wallissches_Produkt">Wallis-Produkt</a> zur Berechnung der Kreiszahl Pi. Die Berechnung erfolgt durch den folgenden anonymen PL/SQL-Block:<br />
<pre class="sql" name="code">declare
v_pi binary_double := 1.0;
begin
for i in 1 .. 1000000 loop
v_pi := v_pi * (1.0 + (1.0 / (4.0 * i * i - 1.0)));
end loop;
v_pi := v_pi * 2.0;
dbms_output.put_line(v_pi);
end;
/
3,1415918681921307E+000
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:05.07
</pre>Die Berechnung dauert ungefähr fünf Sekunden und gibt die Zahl Pi bis auf fünf Stellen genau an. Doch an welcher Stelle befindet sich nun das Optimierungspotenzial?<br />
<br />
Literale vom Typ <code>BINARY_DOUBLE</code> bzw. <code>BINARY_FLOAT</code> enthalten den Buchstaben <code>d</code> (<code>D</code>) bzw. <code>f</code> (<code>F</code>). Ansonsten handelt es sich um Literale vom Typ <code>NUMBER</code>, die im vorherigen PL/SQL-Block zur Typ-Konvertierung geführt haben.<br />
<br />
Der angepasste PL/SQL-Block sieht dann wie folgt aus:<br />
<pre class="sql" name="code">declare
v_pi binary_double := 1.0d;
begin
for i in 1 .. 1000000 loop
v_pi := v_pi * (1.0d + (1.0d / (4.0d * i * i - 1.0d)));
end loop;
v_pi := v_pi * 2.0d;
dbms_output.put_line(v_pi);
end;
/
3,1415918681921489E+000
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:00.46
</pre>Jetzt reduziert sich die Dauer der Berechnung deutlich auf unter eine Sekunde.<br />
<br />
Also sollte man bei der Verwendung von <code>BINARY_DOUBLE</code> bzw. <code>BINARY_FLOAT</code> stets auf die korrekte Angabe von Literalen achten.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-63263784587089493342011-08-01T15:02:00.000+02:002011-08-01T15:02:24.068+02:00Verwendung von PL/SQL SubtypesHeute geht's um die Verwendung von Subtypen in PL/SQL.<br />
<br />
Dazu ein einfaches Beispiel:<br />
<pre class="sql" name="code">declare
subtype name_st is varchar2(30);
v1 name_st;
v2 name_st;
v3 name_st;
.
.
.
vn name_st;
begin
...
end;
</pre>Der Subtyp <code>NAME_ST</code> schränkt den Typ <code>VARCHAR2</code> auf eine Länge von 30 ein. Nun kann dieser zur Deklaration von Variablen verwendet werden. Zusätzlich dazu kann ein Subtyp um einen <code>NOT NULL</code> Constraint erweitert werden (in diesem Fall sind bei der Deklaration Werte vom entsprechenden Typ anzugeben):<br />
<pre class="sql" name="code">declare
subtype name_st is varchar2(30) not null;
v1 name_st := '1';
v2 name_st := '2';
v3 name_st := '3';
.
.
.
vn name_st := 'n';
begin
...
end;
</pre>Ein Subtyp vom Typ <code>BINARY_INTEGER</code> bzw. <code>PLS_INTEGER</code> kann zudem eine Angabe zum zulässigen Bereich enthalten.<br />
<pre class="sql" name="code">declare
subtype percentage_st is binary_integer range 0 .. 100;
v_percentage percentage_st;
begin
...
end;
</pre>Auch hier kann optional ein <code>NOT NULL</code> Constraint angegeben werden.<br />
<br />
Leider funktioniert die Angabe des Bereichs nicht für die Typen <code>BINARY_FLOAT</code> und <code>BINARY_DOUBLE</code>. Siehe dazu auch das Oracle White Paper <a href="http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf">PL/SQL conditional compilation</a> vom Oktober 2005, welches auf Seite 49 in Fußnote 58 eine solche Erweiterung vorschlägt.<br />
<br />
Besonders hilfreich erscheint mir die Verwendung von Subtypen im Kontext von Packages. Zum einen spart man sich mitunter einige Zeilen PL/SQL Code durch die Angabe eines zulässigen Bereichs und zum anderen kann der Subtyp an mehreren Stellen im Package verwendet werden. Als Typ für die Deklaration einer Variablen, wie als Typ für einen Parameter von Funktionen oder Prozeduren.<br />
<br />
Mehr dazu findet man in der <a href="http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/datatypes.htm#LNPLS00302">Dokumentation</a>.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-45374499689852778972011-07-29T09:29:00.002+02:002011-07-30T18:00:27.043+02:00Korrekte Implementierung der Aggregatsfunktion SUMHeute geht es um die bekannte Aggregatsfunktion <code>SUM</code> und deren nicht ganz optimales Verhalten. Damit meine ich im Wesentlichen die Rückgabe der Funktion, im Falle einer leeren Tabelle.<br />
<br />
Betrachten wir dazu das folgende Beispiel:<br />
<pre class="sql" name="code">create table empty_tab
(
val number
);
select sum(val) sum_val from empty_tab;
</pre>Das Ergebnis ist eine Tabelle, die eine Spalte mit dem Namen <code>SUM_VAL</code> enthält deren "Wert" <code>NULL</code> ist; in der Session wurde zuvor <code>set null ?</code> ausgeführt.<br />
<pre>SUM_VAL
-----------------------
?
</pre>Und genau an dieser Stelle befindet sich der Fehler in der Implementierung, denn korrekt wäre die Rückgabe von 0. Eine Summe ohne Summanden (die leere Summe) ergibt in der Mathematik 0.<br />
<br />
Natürlich kann man z.B. die Funktion <code>COALESCE</code> verwenden, um das gewünschte Ergebnis zu erhalten.<br />
<pre class="sql" name="code">select coalesce(sum(val),0) sum_val from empty_tab;
</pre><pre>SUM_VAL
-----------------------
0
</pre>Aber glücklicherweise kann man mithilfe von User-Defined-Aggregates eine eigene Funktion, die ich <code>RSUM</code> nenne, implementieren, die ein korrektes Verhalten zeigt.<br />
<br />
Dazu zunächst der Objekt-Typ:<br />
<pre class="sql" name="code">create or replace type sum_t as object
(
v_sum number,
static function ODCIAggregateInitialize(
sctx IN OUT sum_t) return number,
member function ODCIAggregateIterate(
self IN OUT sum_t,
value IN number) return number,
member function ODCIAggregateTerminate(
self IN sum_t,
returnValue OUT number,
flags IN number) return number,
member function ODCIAggregateMerge(
self IN OUT sum_t,
ctx2 IN sum_t) return number
);
</pre>Die Variable <code>v_sum</code> dient der Speicherung der Summe. Der nun folgende Rumpf des Objek-Typs enthält dann die korrekte Initialisierung mit 0 (siehe Zeile 6 des folgenden Listings).<br />
<pre class="sql" name="code">create or replace type body sum_t is
static function ODCIAggregateInitialize(
sctx IN OUT sum_t) return number is
begin
sctx := sum_t(0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(
self IN OUT sum_t,
value IN number) return number is
begin
self.v_sum := self.v_sum + value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(
self IN sum_t,
returnValue OUT number,
flags IN number) return number is
begin
returnValue := self.v_sum;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(
self IN OUT sum_t,
ctx2 IN sum_t) return number is
begin
self.v_sum := self.v_sum + ctx2.v_sum;
return ODCIConst.Success;
end;
end;
</pre>Jetzt fehlt nur noch die Funktion, welche den Objekt-Typ verwendet.<br />
<pre class="sql" name="code">create or replace function rsum (input number) return number
deterministic parallel_enable aggregate using sum_t;
</pre>Eine Ausführung der Funktion <code>RSUM</code> auf einer leeren Tabelle liefert dann das korrekte Ergebnis 0 zurück. Anmerkung: Da die Methode ODCIAggregateIterate nur für Nicht-NULL's aufgerufen wird, gilt dies auch für eine Tabelle, die in der entsprechenden Spalte nur NULL's enthält.<br />
<pre class="sql" name="code">select rsum(val) rsum_val from empty_tab;
</pre><pre>RSUM_VAL
-----------------------
0
</pre>Ähnlich verhält es sich übrigens bei der Aggregatsfunktion <code>AVG</code> zur Berechnung des arithmetischen Mittels. Wendet man die Funktion auf eine leere Tabelle an erhält man als Rückgabe <code>NULL</code>. Eine Fehlermeldung der Art "divisor is equal to zero" erschien mir in diesem Falle schlüssiger.Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-86818163894295772982011-07-28T12:03:00.005+02:002011-07-28T15:20:21.429+02:00Multiple Regression mit OLS_REGRESSIONZur Demonstration dienen die folgenden Daten:<br />
<br />
y = Nachgefragte Menge in 1000 Stück<br />
x<sub>1</sub> = Werbeausgaben in 100.000 Euro für Printmedien<br />
x<sub>2</sub> = Werbeausgaben in 100.000 Euro für Fernsehen<br />
x<sub>3</sub> = Preis pro Mengeneinheit in 100 Euro<br />
<br />
<table border="1" cellpadding="5" cellspacing="0" width="200"><thead align="left">
<tr><th align="left">y</th><th>x<sub>1</sub></th><th>x<sub>2</sub></th><th>x<sub>3</sub></th></tr>
</thead><tbody>
<tr> <td>500</td> <td>1</td> <td>1</td> <td>20</td> </tr>
<tr> <td>800</td> <td>3</td> <td>1</td> <td>20</td> </tr>
<tr> <td>1500</td> <td>3</td> <td>3</td> <td>18</td> </tr>
<tr> <td>2500</td> <td>6</td> <td>4</td> <td>15</td> </tr>
<tr> <td>3200</td> <td>6</td> <td>6</td> <td>12</td> </tr>
</tbody></table><br />
Man möchte mithilfe der multiple Regression überprüfen, ob und welche Abhängigkeiten zwischen dem Absatz und den Werbeausgaben für Printmedien, den Werbeausgaben für Fernsehen sowie dem Preis pro Mengeneinheit bestehen.<br />
<br />
Um <code>OLS_REGRESSION</code> verwenden zu können ist ein Skript zu starten, das sich nach der Installation der Examples im Verzeichnis <code>$ORACLE_HOME/plsql/demo</code> befindet und den Namen <code>olstype.sql</code> trägt. Nun wird <code>OLS_REGRESSION</code> dazu verwendet, die oben genannte Fragestellung zu beantworten.<br />
<br />
Die Daten befinden sich in einer Tabelle mit dem Namen <code>REGRESSION_SAMPLE</code>:<br />
<br />
<pre>NACHFRAGE WERBUNG_PRINT WERBUNG_TV PREIS
----------- -------------- ------------ ------
500 1 1 20
800 3 1 20
1500 3 3 18
2500 6 4 15
3200 6 6 12
</pre>Zunächst wird ein View erstellt, der eine Spalte vom Typ <code>OLS_REGRESSION</code> enthält. Beim Aufruf des Konstruktors sind die folgenden Argumente für die Parameter zu übergeben:<br />
<br />
mean_y:<br />
<br />
Arithmetisches Mittel der abhängigen Variable; in diesem Fall die Nachfrage.<br />
<br />
Argument: <code>avg(nachfrage)</code><br />
<br />
variance_y:<br />
<br />
Varianz der abhängigen Variable; in diesem Fall die Nachfrage. Es spielt dabei keine Rolle, ob man zur Berechnung der Varianz die Funktion <code>VAR_POP</code> oder <code>VAR_SAMP</code> verwendet. Jedoch ist zu beachten, dass man sich für eine der Funktionen entscheidet und diese dann in allen Argumenten verwendet. Bei der Funktion <code>VAR_SAMP</code> handelt es sich um die Stichprobenvarianz, welche mit (n-1) als Divisor gebildet wurde, während die Funktion <code>VAR_POP</code> n als Divisor verwendet.<br />
<br />
Argument: <code>var_pop(nachfrage)</code><br />
<br />
MV:<br />
<br />
Mittelwerte der unabhängigen Variablen in Form eines Vektors.<br />
<br />
Argument:<br />
<pre><code>utl_nla_array_dbl(
avg(werbung_print),
avg(werbung_tv),
avg(preis))</code></pre>VCM:<br />
<br />
Varianz-Kovarianz-Matrix. Aufgrund der Symmetrie der Matrix erfolgt nur die Angabe der Elemente im oberen Dreieck.<br />
<br />
Allgemeine Form der Varianz-Kovarianz-Matrix:<br />
<br />
<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8RtRG26eMVIgE3HZiT4TRWqJaoPWB-aCrTfAwn0JRtTS8oNEs-TWPTbVchFdOEJ_yg8IQVXAhjwXbBbwYJkcwsAc4w8cTVuexpZqIr5F0_fx0IKs8FUQPFy8TS4eMyvtH7gu4KIOgpHBw/s1600/vcm_1.jpg"><img style="cursor:pointer; cursor:hand;width: 323px; height: 80px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8RtRG26eMVIgE3HZiT4TRWqJaoPWB-aCrTfAwn0JRtTS8oNEs-TWPTbVchFdOEJ_yg8IQVXAhjwXbBbwYJkcwsAc4w8cTVuexpZqIr5F0_fx0IKs8FUQPFy8TS4eMyvtH7gu4KIOgpHBw/s400/vcm_1.jpg" alt="" id="BLOGGER_PHOTO_ID_5634362975270024066" border="0" /></a><br />
<br />
Da COV(X,X)=VAR(X) kann für alle Elemente der Hauptdiagonalen die Funktion für die Varianz verwendet werden. Zudem ist zu beachten, dass die Angabe Zeilenweise erfolgt. Im Beispiel stellt sich die gesamte Matrix wie folgt dar:<br />
<br />
<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixhNWr58Wgnmg_E1z73eSessWKCG-LLtrmxAPoZMgD2zGv4kC0N4iwwEv23oV2Mr3o6y7E0T8hzs5mrhLx35UnqLbQrFuJakQtvZNDRo_xuqFLwrmKhyphenhyphenNCC-VZj3Gy59TDSV_H7w4-WVWL/s1600/vcm_2.jpg"><img style="cursor:pointer; cursor:hand;width: 400px; height: 83px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixhNWr58Wgnmg_E1z73eSessWKCG-LLtrmxAPoZMgD2zGv4kC0N4iwwEv23oV2Mr3o6y7E0T8hzs5mrhLx35UnqLbQrFuJakQtvZNDRo_xuqFLwrmKhyphenhyphenNCC-VZj3Gy59TDSV_H7w4-WVWL/s400/vcm_2.jpg" alt="" id="BLOGGER_PHOTO_ID_5634363121476977154" border="0" /></a><br />
<br />
Ersetzt man auf der Hauptdiagonale noch die Kovarianz durch die Varianz erhält man:<br />
<br />
<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1rZu0vducwMEaiMmqPAtqbypM3goSnYSL1FBqpfM0EBZCEZCZvnRMURAWBT_5Qbk02ka5edvngg38-fJn5bRhXlCQsvuftZHFPBtIqQ__Yhjv9_aGAbZysw8kpu4uws5UNKQ3_JheHiXe/s1600/vcm_3.jpg"><img style="cursor:pointer; cursor:hand;width: 400px; height: 81px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1rZu0vducwMEaiMmqPAtqbypM3goSnYSL1FBqpfM0EBZCEZCZvnRMURAWBT_5Qbk02ka5edvngg38-fJn5bRhXlCQsvuftZHFPBtIqQ__Yhjv9_aGAbZysw8kpu4uws5UNKQ3_JheHiXe/s400/vcm_3.jpg" alt="" id="BLOGGER_PHOTO_ID_5634363226210956162" border="0" /></a><br />
<br />
Die Angabe erfolgt nun Zeilenweise in der Form:<br />
<br />
VAR(X<sub>1</sub>),COV(X<sub>1</sub>,X<sub>2</sub>),COV(X<sub>1</sub>,X<sub>3</sub>),VAR(X<sub>2</sub>),COV(X<sub>2</sub>,X<sub>3</sub>),VAR(X<sub>3</sub>)<br />
<br />
Argument:<br />
<pre><code>utl_nla_array_dbl(
var_pop(werbung_print),
covar_pop(werbung_print,werbung_tv),
covar_pop(werbung_print,preis),
var_pop(werbung_tv),
covar_pop(werbung_tv,preis),
var_pop(preis))</code></pre>CV:<br />
<br />
Kovarianz-Vektor der unabhängigen und abhängigen Variablen in Form eines Vektors:<br />
<br />
Argument:<br />
<pre><code>utl_nla_array_dbl(
covar_pop(nachfrage,werbung_print),
covar_pop(nachfrage,werbung_tv),
covar_pop(nachfrage,preis))</code></pre>Der View stellt sich dann wie folgt dar:<br />
<pre class="sql" name="code">create or replace view ols_view as
select
count(*) n,
ols_regression
(
avg(nachfrage),
var_pop(nachfrage),
utl_nla_array_dbl
(
avg(werbung_print),
avg(werbung_tv),
avg(preis)
),
utl_nla_array_dbl
(
var_pop(werbung_print),
covar_pop(werbung_print,werbung_tv),
covar_pop(werbung_print,preis),
var_pop(werbung_tv),
covar_pop(werbung_tv,preis),
var_pop(preis)
),
utl_nla_array_dbl
(
covar_pop(nachfrage,werbung_print),
covar_pop(nachfrage,werbung_tv),
covar_pop(nachfrage,preis)
)
) ols_object
from
regression_sample;
</pre>Als zusätzliche Spalte wurde die Anzahl der Zeilen aufgenommen, welche nachher zur Berechnung des korrigierten Bestimmtheitsmaßes verwendet wird. Aufbauend auf dieser View lassen sich die Koeffizienten der Regressionsfunktion und das Bestimmtheitsmaß ermitteln:<br />
<pre class="sql" name="code">select
o.ols_object.getEquation(3) funktionsgleichung,
round(o.ols_object.getCoefficient(0),3) b0,
round(o.ols_object.getCoefficient(1),3) b1,
round(o.ols_object.getCoefficient(2),3) b2,
round(o.ols_object.getCoefficient(3),3) b3,
round(o.ols_object.getCorrelation(),6) r,
round(power(o.ols_object.getCorrelation(),2),6) r_quadrat,
round((1-((1-power(o.ols_object.getCorrelation(),2))*((o.n-1)/(o.n-1-o.ols_object.betacount)))),3) korrigiertes_r_quadrat
from
ols_view o;
</pre><pre> B0 B1 B2 B3 R R2 K_R2
-------- -------- -------- -------- -------- -------- ---------
1440,816 168,367 266,327 -69,388 ,999842 ,999685 ,999
</pre>Zusätzlich zu den Koeffizienten kann es von Interesse sein, die standardisierten Koeffizienten zu ermitteln. Diese geben Auskunft über den Einfluss der Variablen auf die Zielgröße. Man erhält die so genannten Beta-Faktoren oder Z-Scores durch folgende Abfrage:<br />
<pre class="sql" name="code">with sd_query as
(
select
stddev(werbung_print) sd_x1,
stddev(werbung_tv) sd_x2,
stddev(preis) sd_x3,
stddev(nachfrage) sd_y
from
regression_sample
)
select
round((o.ols_object.getCoefficient(1)*sd.sd_x1/sd.sd_y),6) beta_x1,
round((o.ols_object.getCoefficient(2)*sd.sd_x2/sd.sd_y),6) beta_x2,
round((o.ols_object.getCoefficient(3)*sd.sd_x3/sd.sd_y),6) beta_x3
from
ols_view o, sd_query sd;
</pre><pre>BETA_X1 BETA_X2 BETA_X3
---------------------- ---------------------- ----------------------
0.320754 0.496462 -0.211222
</pre>Man stellt nun zum Beispiel fest, dass die Variable für TV-Werbung mehr als doppelt so viel Einfluss auf die Nachfrage besitzt, wie die Variable für den Preis.<br />
<br />
Will man nun die Regressionsfunktion nutzen, um die Nachfrage zu prognostizieren kann man die Methode <code>PREDICT</code> verwenden. Ein Argument vom TYP <code>UTL_NLA_ARRAY_DBL</code> enthält dabei die Werte für die Regressionsfunktion; in diesem Beispiel genau drei Stück für die Variablen Werbung-Print, Werbung-TV und Preis. Die folgende Abfrage vergleicht die tatsächliche Nachfrage mit dem Wert der Regressionfunktion.<br />
<pre class="sql" name="code">select
nachfrage,
o.ols_object.predict(utl_nla_array_dbl(werbung_print,werbung_tv,preis)) prediction
from
ols_view o, regression_sample;
</pre><pre>NACHFRAGE PREDICTION
---------------------- ----------------------
500 487.7551020409137
800 824.48979591844341
1500 1495.918367346951072
2500 2475.51020408157385
3200 3216.32653061211845
</pre>Will man nun die Nachfrage prognostizieren, wenn die Werbeausgaben für Printmedien 400.000€, die Werbeausgaben für TV 350.000€ und der Preis pro Mengeneinheit 1.600€ beträgt erhält man:<br />
<pre class="sql" name="code">select o.ols_object.predict(utl_nla_array_dbl(4,3.5,16)) prediction
from ols_view o;
</pre><pre>PREDICTION
----------------------
1936.22448979589853
</pre>Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0tag:blogger.com,1999:blog-3685894621243440521.post-38703496499892338052011-07-28T11:15:00.015+02:002011-07-30T17:58:42.032+02:00Dijkstra-Algorithmus mit PL/SQLDer Algorithmus von Edsger W. Dijkstra dient der Ermittlung der kürzesten Pfade ausgehend von einem Startknoten zu allen anderen Knoten in einem Graphen.<br />
<br />
Die Darstellung des Graphen erfolgt in Form einer einfachen Tabelle mit den Spalten <code>ID</code>, <code>SOURCE</code>, <code>DESTINATION</code> und <code>DISTANCE</code>.<br />
<br />
Als Beispiel dient der folgende Graph:<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilyY79uTmVvLQY_PCjHT9vURtDPQBF_mHNW3fuH_yTxBYnKdtSjY1o-XykHvEddspG6bY_r1OEOPXA2w4nnYKLmFUEyP4uZ6MKlmNaLcFju8NHm2AklL1uJWynZe7oPeh6szVcIiSSoL-A/s1600/graph.png" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5634339844883690050" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilyY79uTmVvLQY_PCjHT9vURtDPQBF_mHNW3fuH_yTxBYnKdtSjY1o-XykHvEddspG6bY_r1OEOPXA2w4nnYKLmFUEyP4uZ6MKlmNaLcFju8NHm2AklL1uJWynZe7oPeh6szVcIiSSoL-A/s400/graph.png" style="cursor: hand; cursor: pointer; height: 206px; width: 400px;" /></a><br />
<br />
Die zugehörige Tabelle stellt sich dann wie folgt dar:<br />
<pre> ID SOURCE DESTINATION DISTANCE
---------- ---------- ----------- --------
1 1 2 4
1 1 3 6
1 1 4 8
1 2 5 7
1 2 3 1
1 3 4 2
1 3 5 5
1 3 6 4
1 4 6 5
1 5 7 6
1 6 5 1
1 6 7 8
</pre>Auf dieser Grundlage sollen jetzt die kürzesten Wege ausgehend von Knoten 1 gefunden werden, wenngleich von jedem Knoten begonnen werden kann.<br />
<br />
Der Aufruf der PL/SQL-Table-Function geht so:<br />
<pre class="sql" name="code">select * from table(dijkstra(1,1)) order by vertex;
</pre><pre> VERTEX DISTANCE PREDECESSOR PATH
---------- -------- ----------- -----------------------
1 0 1
2 4 1 1 -> 2
3 5 2 1 -> 2 -> 3
4 7 3 1 -> 2 -> 3 -> 4
5 10 3 1 -> 2 -> 3 -> 5
6 9 3 1 -> 2 -> 3 -> 6
7 16 5 1 -> 2 -> 3 -> 5 -> 7
</pre>Die Spalte <code>DISTANCE</code> gibt die minimale Distanz zum jeweiligen Knoten an, die Spalte <code>PREDECESSOR</code> enthält den direkten Vorgänger und die Spalte <code>PATH</code> enthält die Knoten, welche auf dem kürzesten Weg zurückgelegt wurden.<br />
<br />
Nach dieser kurzen Demonstration der Funktionsweise hier der zugehörige Code:<br />
<pre class="sql" name="code">-- Tabelle
drop table graph;
create table graph
(
id integer,
source integer,
destination integer,
distance number(38,2) not null
);
alter table graph
add constraint pk_graph
primary key (id, source, destination);
alter table graph
add constraint c_graph_source
check (source > 0);
alter table graph
add constraint c_graph_destination
check (destination > 0);
alter table graph
add constraint c_graph_distance
check (distance >= 0);
-- Daten
insert into graph(id,source,destination,distance)
values(1,1,2,4);
insert into graph(id,source,destination,distance)
values(1,1,3,6);
insert into graph(id,source,destination,distance)
values(1,1,4,8);
insert into graph(id,source,destination,distance)
values(1,2,5,7);
insert into graph(id,source,destination,distance)
values(1,2,3,1);
insert into graph(id,source,destination,distance)
values(1,3,4,2);
insert into graph(id,source,destination,distance)
values(1,3,5,5);
insert into graph(id,source,destination,distance)
values(1,3,6,4);
insert into graph(id,source,destination,distance)
values(1,4,6,5);
insert into graph(id,source,destination,distance)
values(1,5,7,6);
insert into graph(id,source,destination,distance)
values(1,6,5,1);
insert into graph(id,source,destination,distance)
values(1,6,7,8);
commit;
-- Type
drop type dijkstra_tab;
drop type dijkstra_t;
create type dijkstra_t as object
(
vertex integer,
distance binary_double,
predecessor integer,
path varchar2(4000)
);
/
create type dijkstra_tab as table of dijkstra_t;
/
-- Table function
create or replace
function dijkstra(p_graph_in in binary_integer, p_vertex_in in binary_integer) return dijkstra_tab pipelined
is
graph_not_found exception;
vertex_not_found exception;
type unchecked_tab is table of binary_integer index by binary_integer;
type predecessor_tab is table of binary_integer index by binary_integer;
type distance_tab is table of binary_double index by binary_integer;
cursor init_cur is
select source vertex
from graph
where id = p_graph_in
union
select destination vertex
from graph
where id = p_graph_in;
cursor distance_cur(pc_vertex_in in binary_integer) is
select destination, distance
from graph
where id = p_graph_in and source = pc_vertex_in;
i binary_integer;
v_dummy varchar(10);
v_unchecked unchecked_tab;
v_predecessor predecessor_tab;
v_distance distance_tab;
v_minimum binary_integer;
v_alternative binary_double;
v_path varchar2(4000);
begin
begin
select 'TRUE' into v_dummy
from dual
where exists
(
select *
from graph
where id = p_graph_in
);
exception
when no_data_found then
raise graph_not_found;
end;
begin
select 'TRUE' into v_dummy
from dual
where exists
(
select *
from graph
where id = p_graph_in and (source = p_vertex_in or destination = p_vertex_in)
);
exception
when no_data_found then
raise vertex_not_found;
end;
begin
for init_rec in init_cur loop
v_unchecked(init_rec.vertex) := null;
v_predecessor(init_rec.vertex) := null;
v_distance(init_rec.vertex) := binary_double_infinity;
end loop;
v_distance(p_vertex_in) := 0;
end;
begin
while (v_unchecked.count > 0) loop
v_minimum := null;
i := v_unchecked.first;
while (i is not null) loop
if (v_minimum is null) then
v_minimum := i;
else
if (v_distance(i) < v_distance(v_minimum)) then
v_minimum := i;
end if;
end if;
i := v_unchecked.next(i);
end loop;
v_unchecked.delete(v_minimum);
for distance_rec in distance_cur(v_minimum) loop
if (v_unchecked.exists(distance_rec.destination)) then
v_alternative := v_distance(v_minimum) + distance_rec.distance;
if (v_alternative < v_distance(distance_rec.destination)) then
v_distance(distance_rec.destination) := v_alternative;
v_predecessor(distance_rec.destination) := v_minimum;
end if;
end if;
end loop;
if (v_distance(v_minimum) = binary_double_infinity) then
v_path := '';
else
v_path := v_minimum;
end if;
i := v_predecessor(v_minimum);
while (i is not null) loop
v_path := i || ' -> ' || v_path;
i := v_predecessor(i);
end loop;
pipe row( dijkstra_t (
v_minimum,
v_distance(v_minimum),
v_predecessor(v_minimum),
v_path ) );
end loop;
end;
exception
when graph_not_found then
raise_application_error(-20010, 'DIJKSTRA: The graph was not found.');
when vertex_not_found then
raise_application_error(-20011, 'DIJKSTRA: The vertex to start the algorithm was not found.');
when others then
raise_application_error(-20012, 'DIJKSTRA: Unexpected error: ' || substr(1,200,SQLERRM));
end;
</pre>Thomas Uhrenhttp://www.blogger.com/profile/11653952009800365647noreply@blogger.com0