Rekursive SQL-Anweisung (PostgreSQL 9.1.4)

Question

Jul 19, 2012, 11:58 AM

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.