Etwas OLAP zum Jahresende

In 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.

Zunächst die Struktur der Tabelle:

create table survey
(
 cust_id integer constraint pk_survey primary key,
 last_page integer constraint nn_survey_last_page not null
);

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 Poisson-Verteilung.

Der folgende anonyme PL/SQL-Block erzeugt den Datenbestand:

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;

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.

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;

Damit erhält man folgenden Output:

LAST_PAGE              N                      LOST                   
---------------------- ---------------------- ---------------------- 
1                      10000                                         
2                      6255                   3745                   
3                      2638                   3617                   
4                      809                    1829                   
5                      188                    621                    

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.

Multiple Regression mit UTL_NLA

Heute geht's erneut um die multiple Regression, die in diesem Beispiel mithilfe von UTL_NLA durchgeführt wird. UTL_NLA stellt dazu BLAS (Basic Linear Algebra Subroutines) und LAPACK (Linear Algebra PACKage) bereit.

Wie im vorherigen Post zu diesem Thema, werden die folgenden Daten verwendet:

y = Nachgefragte Menge in 1000 Stück
x₁ = Werbeausgaben in 100.000 Euro für Printmedien
x₂ = Werbeausgaben in 100.000 Euro für Fernsehen
x₃ = Preis pro Mengeneinheit in 100 Euro

y	x1	x2	x3
500	1	1	20
800	3	1	20
1500	3	3	18
2500	6	4	15
3200	6	6	12

Man erhält die Koeffizienten der Regressionsfunktion in Form des Vektors b durch:

Die Matrix X im Beispiel:

Der Vektor y im Beispiel:

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 UTL_NLA durchführen. Für die Multiplikation von Matrizen wird BLAS_GEMM verwendet, für die Multiplikation einer Matrix mit einem Vektor wird BLAS_GEMV verwendet und für die Lösung des Gleichungssystems kommt LAPACK_GESV zum Einsatz.

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:

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 ); 

Der Aufruf der Prozedur UTL_NLA.BLAS_GEMM ermittelt nun die Matrix X^TX.

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');

Nun wird der Vektor X^Ty mithilfe von UTL_NLA.BLAS_GEMV bestimmt:

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');

Im finalen Schritt, zur Bestimmung der Koeffizienten, ist das folgende Gleichungssystem zu lösen:

X^TXb=X^Ty

Diese Aufgabe erledigt die Prozedur UTL_NLA.LAPACK_GESV, wobei sich das Ergebnis nach dem Aufruf in der Variablen befindet, die als Argument für den Parameter b übergeben wurde.

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');

Die Ausgabe der Koeffizienten ergibt:

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

Die Regressionsfunktion ergibt sich somit zu:

y = 1440,8163 + 168,3673∙x₁ + 266,3265∙x₂ - 69,3878∙x₃

Abschließend sei noch erwähnt, dass eine Matrix bzw. ein Vektor vom Typ UTL_NLA_ARRAY_DBL bzw. UTL_NLA_ARRAY_FLT maximal 1.000.000 Elemente enthalten kann.

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.

Aber das ist was für einen anderen Post...

Multiple Regression mit OLS_REGRESSION

Zur Demonstration dienen die folgenden Daten:

y = Nachgefragte Menge in 1000 Stück
x₁ = Werbeausgaben in 100.000 Euro für Printmedien
x₂ = Werbeausgaben in 100.000 Euro für Fernsehen
x₃ = Preis pro Mengeneinheit in 100 Euro

y	x1	x2	x3
500	1	1	20
800	3	1	20
1500	3	3	18
2500	6	4	15
3200	6	6	12

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.

Um OLS_REGRESSION verwenden zu können ist ein Skript zu starten, das sich nach der Installation der Examples im Verzeichnis $ORACLE_HOME/plsql/demo befindet und den Namen olstype.sql trägt. Nun wird OLS_REGRESSION dazu verwendet, die oben genannte Fragestellung zu beantworten.

Die Daten befinden sich in einer Tabelle mit dem Namen REGRESSION_SAMPLE:

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

Zunächst wird ein View erstellt, der eine Spalte vom Typ OLS_REGRESSION enthält. Beim Aufruf des Konstruktors sind die folgenden Argumente für die Parameter zu übergeben:

mean_y:

Arithmetisches Mittel der abhängigen Variable; in diesem Fall die Nachfrage.

Argument: avg(nachfrage)

variance_y:

Varianz der abhängigen Variable; in diesem Fall die Nachfrage. Es spielt dabei keine Rolle, ob man zur Berechnung der Varianz die Funktion VAR_POP oder VAR_SAMP verwendet. Jedoch ist zu beachten, dass man sich für eine der Funktionen entscheidet und diese dann in allen Argumenten verwendet. Bei der Funktion VAR_SAMP handelt es sich um die Stichprobenvarianz, welche mit (n-1) als Divisor gebildet wurde, während die Funktion VAR_POP n als Divisor verwendet.

Argument: var_pop(nachfrage)

MV:

Mittelwerte der unabhängigen Variablen in Form eines Vektors.

Argument:

utl_nla_array_dbl(
 avg(werbung_print),
 avg(werbung_tv),
 avg(preis))

VCM:

Varianz-Kovarianz-Matrix. Aufgrund der Symmetrie der Matrix erfolgt nur die Angabe der Elemente im oberen Dreieck.

Allgemeine Form der Varianz-Kovarianz-Matrix:



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:



Ersetzt man auf der Hauptdiagonale noch die Kovarianz durch die Varianz erhält man:



Die Angabe erfolgt nun Zeilenweise in der Form:

VAR(X₁),COV(X₁,X₂),COV(X₁,X₃),VAR(X₂),COV(X₂,X₃),VAR(X₃)

Argument:

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))

CV:

Kovarianz-Vektor der unabhängigen und abhängigen Variablen in Form eines Vektors:

Argument:

utl_nla_array_dbl(
 covar_pop(nachfrage,werbung_print),
 covar_pop(nachfrage,werbung_tv),
 covar_pop(nachfrage,preis))

Der View stellt sich dann wie folgt dar:

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;

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:

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;

      B0       B1       B2       B3        R       R2      K_R2
-------- -------- -------- -------- -------- -------- ---------
1440,816  168,367  266,327  -69,388  ,999842  ,999685      ,999

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:

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;

BETA_X1                BETA_X2                BETA_X3
---------------------- ---------------------- ----------------------
0.320754               0.496462               -0.211222

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.

Will man nun die Regressionsfunktion nutzen, um die Nachfrage zu prognostizieren kann man die Methode PREDICT verwenden. Ein Argument vom TYP UTL_NLA_ARRAY_DBL 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.

select
 nachfrage,
 o.ols_object.predict(utl_nla_array_dbl(werbung_print,werbung_tv,preis)) prediction
from
 ols_view o, regression_sample;

NACHFRAGE              PREDICTION
---------------------- ----------------------
500                    487.7551020409137
800                    824.48979591844341
1500                   1495.918367346951072
2500                   2475.51020408157385
3200                   3216.32653061211845

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:

select o.ols_object.predict(utl_nla_array_dbl(4,3.5,16)) prediction
from ols_view o;

PREDICTION
----------------------
1936.22448979589853