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.