PostgreSQL 9.1

Sytuacja biznesowa

Każdego miesiąca jest przydzielana nowa partia kont do określonego procesu. Każda partia może być opisana według miesiąca, liczby kont i całkowitego salda kont. Celem tego procesu jest odzyskanie części salda od klientów. Każda partia jest śledzona oddzielnie w cyklu miesięcznym (kwota odzyskana w każdym miesiącu od momentu przekazania partii do procesu).

Cel

Moim celem jest przewidzenie, jaka kwota zostanie odzyskana w przyszłości.

Definicja danych

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

Przykładowe dane

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

Proces obliczania

Możesz sobie wyobrazić dane jako trójkątną macierz (wartości X mają być prognozowane):

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)

Algorytm

Moim celem jest prognozowanie wszystkich brakujących punktów (przyszłość). Aby zilustrować ten proces, jest to obliczenie dla punktu X1

1) Uzyskaj sumy wierszy za poprzednie trzy miesiące, używając odległości do 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) Uzyskaj sumy wierszy za poprzednie trzy miesiące, używając odległości do 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) Oblicz ważoną średnią szybkość działania dla odległości 3 i dystansu 4 (ważona wartością 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) Oblicz zmianę między odległością 3 a odległością 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) Oblicz sumy wierszy dla przewidywanego miesiąca za pomocą odległości do 3:

2012-04-30  1600+1500+4000=7100

6) Oblicz stawkę za pomocą entry_amount dla przewidywanego miesiąca

7100/180000 = 0.0394

7) Oblicz stawkę przewidywaną dla X1

0.0394 * PredictionRateForX1 = 0.05534

8) Oblicz kwotę dla X1

(0.05534-0.0394)*180000 = 2869.2

Problem

Problem polega na tym, jak obliczyć resztę macierzy (od x-1 do x13) za pomocą instrukcji SQL. Jest oczywiste, że będzie to wymagało pewnego rodzaju algorytmu rekurencyjnego.