Rekursive SQL-Anweisung (PostgreSQL 9.1.4)
PostgreSQL 9.1
Geschäftslage
Jeden Monat wird einem bestimmten Prozess ein neuer Stapel von Konten zugewiesen. Jede Charge kann nach Monat, Anzahl der Konten und Gesamtkontostand beschrieben werden. Ziel des Prozesses ist es, einen Teil des Gleichgewichts von den Kunden zurückzugewinnen. Jede Charge wird dann monatlich separat nachverfolgt (Betrag, der jeden Monat seit der Übertragung der Charge in den Prozess zurückgefordert wurde).
Tor
Mein Ziel ist es, vorherzusagen, welcher Betrag in Zukunft wieder eingezogen wird.
Datendefinition
create table vintage_data (
granularity date, /* Month when account entered process*/
distance_in_months integer, /* Distance in months from date when accounts entered process*/
entry_accounts integer, /* Number of accounts that entered process in a given month*/
entry_amount numeric, /* Total amount for account that entered process in a given month*/
recovery_amount numeric /* Amount recovered in Nth month on accounts that entered process in a given month */
);
Beispieldaten
insert into vintage_data values('2012-01-31',1,200,100000,1000);
insert into vintage_data values('2012-01-31',2,200,100000,2000);
insert into vintage_data values('2012-01-31',3,200,100000,3000);
insert into vintage_data values('2012-01-31',4,200,100000,3500);
insert into vintage_data values('2012-01-31',5,200,100000,3400);
insert into vintage_data values('2012-01-31',6,200,100000,3300);
insert into vintage_data values('2012-02-28',1,250,150000,1200);
insert into vintage_data values('2012-02-28',2,250,150000,1600);
insert into vintage_data values('2012-02-28',3,250,150000,1800);
insert into vintage_data values('2012-02-28',4,250,150000,1200);
insert into vintage_data values('2012-02-28',5,250,150000,1600);
insert into vintage_data values('2012-03-31',1,200,90000,1300);
insert into vintage_data values('2012-03-31',2,200,90000,1200);
insert into vintage_data values('2012-03-31',3,200,90000,1400);
insert into vintage_data values('2012-03-31',4,200,90000,1000);
insert into vintage_data values('2012-04-30',1,300,180000,1600);
insert into vintage_data values('2012-04-30',2,300,180000,1500);
insert into vintage_data values('2012-04-30',3,300,180000,4000);
insert into vintage_data values('2012-05-31',1,400,225000,2200);
insert into vintage_data values('2012-05-31',2,400,225000,6000);
insert into vintage_data values('2012-06-30',1,100,60000,1000);
Berechnungsprozess
Sie können sich die Daten als Dreiecksmatrix vorstellen (X-Werte sollen vorhergesagt werden):
distance_in_months 1 2 3 4 5 6
granularity entry_accounts entry_amount
2012-01-31 200 100000 1000 2000 3000 3500 3400 3300
2012-02-28 250 150000 1200 1600 1800 1200 1600 (X-1)
2012-03-31 200 90000 1300 1200 1400 1000 (X0) (X4)
2012-04-30 300 180000 1600 1500 4000 (X1) (X5) (X8)
2012-05-31 400 225000 2200 6000 (X2) (X6) (X9) (X11)
2012-06-30 100 60000 1000 (X3) (X7) (X10) (X12 (X13)
Algorithmus
Mein Ziel ist es, alle fehlenden Punkte (die Zukunft) vorherzusagen. Zur Veranschaulichung des Prozesses ist dies die Berechnung für den Punkt X1
1) Ermitteln Sie die Zeilensummen der letzten drei Monate mit einer Entfernung von bis zu 4:
2012-01-31 1000+2000+3000+3500=9500 (d4m3)
2012-02-28 1200+1600+1800+1200=5800 (d4m2)
2012-03-31 1300+1200+1400+1000=4900 (d4m1)
2) Ermitteln Sie die Zeilensummen der letzten drei Monate mit einer Entfernung von bis zu 3:
2012-01-31 1000+2000+3000=6000 (d3m3)
2012-02-28 1200+1600+1800=4600 (d3m2)
2012-03-31 1300+1200+1400=3800 (d3m1)
3) Berechnen Sie die gewichtete durchschnittliche Laufrate für Distanz 3 und Distanz 4 (gewichtet mit entry_amount):
(d4m3+d4m2+d4m1)/(100000+150000+90000) = (9500+5800+4900)/(100000+150000+90000) = 20200/340000 = 0.0594
(d3m3+d3m2+d3m1)/(100000+150000+90000) = (6000+4600+3800)/(100000+150000+90000) = 14400/340000 = 0.0424
4) Berechnen Sie die Änderung zwischen Abstand 3 und Abstand 4
((d4m3+d4m2+d4m1)/(100000+150000+90000))/((d3m3+d3m2+d3m1)/(100000+150000+90000)) =
= (20200/340000)/(14400/340000) =
= 0.0594/0.0424 = 1.403 (PredictionRateForX1)
5) Berechnen Sie die Zeilensummen für den vorhergesagten Monat mit einer Entfernung von bis zu 3:
2012-04-30 1600+1500+4000=7100
6) Berechnen Sie die Rate mit entry_amount für den vorhergesagten Monat
7100/180000 = 0.0394
7) Berechnen Sie die für X1 vorhergesagte Rate
0.0394 * PredictionRateForX1 = 0.05534
8) Berechnen Sie den Betrag für X1
(0.05534-0.0394)*180000 = 2869.2
Problem
Das Problem ist, wie der Rest der Matrix (von x-1 bis x13) mit der SQL-Anweisung berechnet wird. Es ist offensichtlich, dass dies eine Art rekursiven Algorithmus erfordert.