递归SQL语句（PostgreSQL 9.1.4）

天黑有灯

PostgreSQL 9.1
经营状况
每个月，都会为特定的流程分配一批新的帐户。可以按月份，帐户数和帐户总余额来描述每批。该过程的目标是从客户那里收回一些余额。然后每月分别跟踪每个批次（自将批次转移到流程以来，每个月回收的金额）。
目标
我的目标是预测未来将回收多少。
资料定义
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 */);样本数据
insert into vintage_data values('2012-01-31、1、2000、10000、1000)；insert into vintage_data values('2012-01-31、200，10000，2000)；insert into vintage_data values('2012-01-31、3、200、10000、3000)；insert into vintage_data values('2012-01-31、4，200，10000，3500)；insert into vintage_data values('2012-01-31、5，200，10000，3400)；insert into vintage_data values('2012-01-31、6、200，10000，3300)；insert into vintage_data values('2012-02-28',1,250,150000,1200);insert into vintage_data values('2012-02-28、250、15000、1600)；insert into vintage_data values('2012-02-28、3、250、15000、1800)；insert into vintage_data values('2012-02-28',4,250,150000,1200);insert into vintage_data values('2012-02-5.250，15000，1600)；insert into vintage_data values('2012-03-31、1、200、90000、1300)；insert into vintage_data values('2012-03-31、200，9000，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-1.300，18000，1600)；insert into vintage_data values('2012-04-30，2，300，18000，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);计算过程
可以将数据想象成三角形矩阵(预测)X值）：
distance_in_months                                     5                                                                                                                                                                                                                                                                                                                            granularity entry_accounts  entry_amount2012-01-31 2000                100000000000 200000  30000 35000 34000 3300020120000000    333000000200020002012012000000                                                 30000000000000000000000000000                                                                                  -02-28 250          15000000    1600  1800  1200 12000 16000                            12000000    160000 1600                                                 120000000                                                                                                                                                                                                                                                                                                                                                                                                                                  (X-1)2012-03-31 200             1300  1200  14000  10000                (X0) (X4)2012-04-300 3000                1600 15000 40000                                     300000(X1) (X5) (X8)2012-05-31 400             22500000    220000 600000                                        50000000(X2) (X6) (X9) (X11)2012-06-300 1000                      100000             (X3) (X7) (X10)  (X12 (X13)算法
我的目标是预测所有遗漏点(未来)。为了解释这个过程，这是对点X1的计算
1)前三个月使用不超过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)前三个月使用不超过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)计算距离3和距离4的加权平均运行率(由entry_amount加权）：
(d4m3 d4m2 d4m1)(100000 150000 90000= (9500 5800 4900)= 20200/340000 = 0.0594(d3m3 d3m2 d3m1)(100000 150000 90000= (6000 4600 3800)/(10000 150000 90000)= 14400/340000 = 0.04244)计算距离3和距离4之间的变化
((d4m3 d4m2 d4m1)/(100000 150000 90000)((d3m3 d3m2 d3m1)(100000 150000 90000)== == 0.0594/0.0424 = 1.403 (PredictionRateForX1)5）使用不超过3的距离计算预测月份的行总计
2012-04-30  1600 1500 4000=71006）使用entry_amount计算预测月的费率
7100/180000 = 0.03947）计算X1的预测速率
0.0394 * PredictionRateForX1 = 0.055348）计算X1的数量
(0.05534-0.0394)*180000 = 2869.2问题
如何使用问题SQL语句计算矩阵的其余部分（从x-1到x13)。显然，这需要一些递归算法。

解决方案:
                                                            这是一项艰巨的任务，拆分它，使它更容易管理。我会把它放在一个位置plpgsql函数中RETURN TABLE：
[ol]使用交叉表查询为计算过程矩阵创建临时表tablefunc为此安装模块。运行（每个数据库一次）：
CREATE EXTENSION tablefunc;临时表逐字段更新。
退货表。
[/ol]下面的演示功能已经完整，已经在PostgreSQL 9.1.4.测试。基于问题中提供的表定义：
-- DROP FUNCTION f_forcast();CREATE OR REPLACE FUNCTION f_forcast()  RETURNS TABLE (  granularity date ,entry_accounts numeric ,entry_amount numeric ,d1 numeric ,d2 numeric ,d3 numeric ,d4 numeric ,d5 numeric ,d6 numeric) AS$BODY$BEGIN--== Create temp table with result of crosstab() ==--CREATE TEMP TABLE matrix ON COMMIT DROP ASSELECT *FROM crosstab SELECT granularity,entry_accounts,entry_amount    distance_in_months,recovery_amount       FROM vintage_data       ORDER  BY       SELECT DISTINCT distance_in_months       FROM vintage_data       ORDER  BY 1')AS tbl (  granularity date ,entry_accounts numeric ,entry_amount numeric ,d1 numeric ,d2 numeric ,d3 numeric ,d4 numeric ,d5 numeric ,d6 numeric );ANALYZE matrix; -- update statistics to help calculations--== Calculations ==---- I implemented the first calculation for X1 and leave the rest to you.-- Can probably be generalized in a loop or even a single statement.UPDATE matrix mSET d4 =  SELECT (sum(x.d1) sum(x.d2) sum(x.d3) sum(x.d4))          (sum(x.d1) sum(x.d2) sum(x.d3)) - 1          -- removed redundant sum(entry_amount) from equation FROM    SELECT *       FROM matrix a       WHERE  a.granularity 称呼：
SELECT * FROM f_forcast();我在计算中简化了很多，删除了一些多余的步骤。
该解决方案采用了多种先进技术。您需要了解PostgreSQL使用方法。

递归SQL语句（PostgreSQL 9.1.4）

天黑有灯 LV1