数据库内核月报－ 2019/05 - PgSQL · 应用案例 · PostgreSQL KPI分解，目标设定之 - 等比数列 - 《数据库内核月报》

背景
什么是等比数列？
等比数列的应用
KPI 分解设定举例
小结
参考

背景

https://baike.baidu.com/item/%E7%AD%89%E6%AF%94%E6%95%B0%E5%88%97

什么是等比数列？

根据历史传说记载，国际象棋起源于古印度，至今见诸于文献最早的记录是在萨珊王朝时期用波斯文写的．据说，有位印度教宰相见国王自负虚浮，决定给他一个教训．他向国王推荐了一种在当时尚无人知晓的游戏．国王当时整天被一群溜须拍马的大臣们包围，百无聊赖，很需要通过游戏方式来排遣郁闷的心情．
国王对这种新奇的游戏很快就产生了浓厚的兴趣，高兴之余，他便问那位宰相，作为对他忠心的奖赏，他需要得到什么赏赐．宰相开口说道：请您在棋盘上的第一个格子上放1粒麦子，第二个格子上放2粒，第三个格子上放4粒，第四个格子上放8粒……即每一个次序在后的格子中放的麦粒都必须是前一个格子麦粒数目的两倍，直到最后一个格子第64格放满为止，这样我就十分满足了。“好吧！”国王哈哈大笑，慷慨地答应了宰相的这个谦卑的请求。
这位聪明的宰相到底要求的是多少麦粒呢？稍微算一下就可以得出：1+2+2^2+2^3+2^4+……+2^63=2^64-1，直接写出数字来就是18,446,744,073,709,551,615粒，这位宰相所要求的，竟是全世界在两千年内所产的小麦的总和！

等比数列的应用

等比数列在生活中也是常常运用的。如：银行有一种支付利息的方式——复利。即把前一期的利息和本金加在一起算作本金，在计算下一期的利息，也就是人们通常说的“利滚利”。按照复利计算本利和的公式：本利和=本金*(1+利率)^存期。
随着房价越来越高，很多人没办法像这样一次性将房款付清，总是要向银行借钱，既可以申请公积金也可以申请银行贷款，但是如果还款到一定时间后想了解自己还得还多少本金时，也可以利用数列来自己计算。众所周知，按揭贷款（公积金贷款）中一般实行按月等额还本付息。下面就来寻求这一问题的解决办法。若贷款数额 a0 元，贷款月利率为 p，还款方式每月等额还本付息 a 元，设第 n 月还款后的本金为 an，那么有：a1=a0(1+p)-a；a2=a1(1+p)-a；a3=a2(1+p)-a；……an+1=an(1+p)-a,…. 将其变形，得（an+1-a/p）/（an-a/p）=1+p。由此可见，{an-a/p} 是一个以 a1-a/p 为首项，1+p 为公比的等比数列。
其实类似的还有零存整取、整存整取等银行储蓄借贷，甚至还可以延伸到生物界的细胞分裂。

等比数列的图例：

例如增长比例为1.03，初始值为1，生成100个等比值，并绘图。

postgres=# select id,1*1.03^id from generate_series(1,100) id;  
 id  |      ?column?         
-----+---------------------  
   1 |  1.0300000000000000  
   2 |  1.0609000000000000  
   3 |  1.0927270000000000  
   4 |  1.1255088100000000  
   5 |  1.1592740743000000  
   6 |  1.1940522965290000  
   7 |  1.2298738654248700  
   8 |  1.2667700813876161  
   9 |  1.3047731838292446  
  10 |  1.3439163793441219  
  11 |  1.3842338707244456  
..............  
  79 | 10.3309617052720353  
  80 | 10.6408905564301964  
  81 | 10.9601172731231023  
  82 | 11.2889207913167953  
  83 | 11.6275884150562992  
  84 | 11.9764160675079882  
  85 | 12.3357085495332278  
  86 | 12.7057798060192246  
  87 | 13.0869532001998014  
  88 | 13.4795617962057954  
  89 | 13.8839486500919693  
  90 | 14.3004671095947284  
  91 | 14.7294811228825702  
  92 | 15.1713655565690473  
  93 | 15.6265065232661187  
  94 | 16.0953017189641023  
  95 | 16.5781607705330254  
  96 | 17.0755055936490161  
  97 | 17.5877707614584866  
  98 | 18.1154038843022412  
  99 | 18.6588660008313085  
 100 | 19.2186319808562477  
(100 rows)

图式为非线性曲线。

在对KPI进行分解时，也可以使用等比数列的方法。

例如当前的用户数有1万个，年底要做到2万个，阶段性的目标如何设定是比较合理的？

又比如一个新产品，从无到有，一年后要做到1000万，每个月应该需要设定多少的目标？

KPI 分解设定举例

1、某产品2019-01-01的用户数有1万个，2019-12-31要做到2万个，每个月目标如何设定是比较合理的？

第一天：10000  
第二天：10000*x  
10000*x*x  
10000*x*x*x  
...  
第一个月的目标: 10000*(x^30)  
第二个月的目标: 10000*(x^60)  
...  
第12个月的目标: 10000*(x^360)

求X:

10000*(x^360) = 20000  
使用科学计算器即可：  
x^360 = 2  
x = 2 开360方根 = 1.0019272636246980060446500191489

求每个月的目标：

postgres=# select i, 10000*(1.0019272636246980060446500191489^(30*i)) from generate_series(1,12) i;  
 i  |               ?column?                  
----+---------------------------------------  
  1 | 10594.6309435929526456182529494640000  
  2 | 11224.6204830937298143353304967930000  
  3 | 11892.0711500272106671749997056060000  
  4 | 12599.2104989487316476721060727850000  
  5 | 13348.3985417003436483083188118480000  
  6 | 14142.1356237309504880168872421010000  
  7 | 14983.0707687668149879928073203030000  
  8 | 15874.0105196819947475170563927290000  
  9 | 16817.9283050742908606225095246710000  
 10 | 17817.9743628067860948045241118180000  
 11 | 18877.4862536338699328382631333600000  
 12 | 20000.0000000000000000000000000110000  
(12 rows)

2、一个新产品，从无到有（从1个细胞开始），一年后要做到1000万，每个月应该需要设定多少的目标？

第一天：1  
第二天：1*x  
1*x*x  
1*x*x*x  
...  
第一个月的目标: 1*(x^30)  
第二个月的目标: 1*(x^60)  
...  
第12个月的目标: 1*(x^360)

求X:

1*(x^360) = 10000000  
使用科学计算器即可：  
x^360 = 10000000  
x = 10000000 开360方根 = 1.045789903003930609038766911147

求每个月的目标：

select i, 1*(1.045789903003930609038766911147^(30*i)) from generate_series(1,12) i;  
postgres=# select i, 1*(1.045789903003930609038766911147^(30*i)) from generate_series(1,12) i;  
 i  |                ?column?                   
----+-----------------------------------------  
  1 |        3.831186849557287699111983662036  
  2 |       14.677992676220695409205171148171  
  3 |       56.234132519034908039495103977664  
  4 |      215.443469003188372175929356652017  
  5 |      825.404185268018425679628885772418  
  6 |     3162.277660168379331998893544434512  
  7 |    12115.276586285884463586029333237398  
  8 |    46415.888336127788924100763509229562  
  9 |   177827.941003892280122542119519419752  
 10 |   681292.069057961285497988179630667890  
 11 |  2610157.215682536785339580652993869030  
 12 | 10000000.000000000000000000000011341833  
(12 rows)

显然这个数字是非常不科学的，不可能前半年每个月都低于1万，而从下半年开始就一下子增长那么多，所以问题出在哪里呢？

增长是与大环境有关的，x = 1.045789903003930609038766911147 这个值太大，年初到年底，增长倍数需要是一个合理的区间，例如年初到年底增长10倍，那么第一个月的值就应该是100万。重新计算X：

1000000*(x^11) = 10000000  
使用科学计算器即可：  
x^11 = 10  
x = 10 开11方根 = 1.2328467394420661390534007897309

求每个月的目标：

第一个月为100万。

postgres=# select i+1, 100000*(1.5199110829529337171040338922572^(i)) from generate_series(1,11) i;  
 ?column? |                ?column?                   
----------+-----------------------------------------  
        2 | 1232846.7394420661390534007897309000000  
        3 | 1519911.0829529337171040338922571000000  
        4 | 1873817.4228603840477603332870082000000  
        5 | 2310129.7000831597589838307886841000000  
        6 | 2848035.8684358016549075949054963000000  
        7 | 3511191.7342151313213486458970854000000  
        8 | 4328761.2810830583474021368863977000000  
        9 | 5336699.2312063096581536941949412000000  
       10 | 6579332.2465756799227076122255576000000  
       11 | 8111308.3078968709132111001153555000000  
       12 | 9999999.9999999999999999999999957000000  
(11 rows)

严重漏洞：以上解决的是每个周期的增长问题，并非总的计数。

以上算法，最后一个月是1000万，总数实际上是所有月份的累加，已经远超1000万。

而实际上的KPI测算是说一年总共要完成多少，而不是一年后的最后一个月要达到多少。

所以算法要调整，如下函数即可用于测算。

postgres=# create or replace function comp_x (  
  v_start float8,    -- 起始月、或起始测算周期的目标营收  
  v_x float8,     -- 每个月、或每个测算周期相比前一个周期的增长率  
  v_terms int    -- 总共多少个周期，如果是一年，每个周期是一个月，则取值12  
) returns float8 as $$  
declare  
  res float8 := 0;  
begin  
    -- 每个月的结果累加  
    -- 例如，要完成全年1000万目标，第一个月目标为a，每个月的增长率为x，公式如下  
    -- a + ax + ax^2 + ax^3 + ... + ax^11 = 1000万  
  for i in 1..v_terms loop  
    res := res + v_start*(v_x^(i-1));  
    raise notice '%: %', i, v_start*(v_x^(i-1));   
  end loop;  
  return res;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION

年底要完成1000万目标，每个月应该完成多少？

1、首先要计算首月（第一个测算周期）的目标

如果保持每个月增长同等比例，那么肯定是越到后面的月份，每个月的营收会越大。因此第一个月一定要低于平均值，所以应该低于83万。

postgres=# select 1000/12;  
 ?column?   
----------  
       83  
(1 row)

2、假设我们第一个月完成70万，等比数列的X差不多等于1.032，最后总的完成率就是1000万左右

postgres=# select * from comp_x(700000, 1.032, 12);  
NOTICE:  1: 700000  
NOTICE:  2: 722400  
NOTICE:  3: 745516.8  
NOTICE:  4: 769373.3376  
NOTICE:  5: 793993.2844032  
NOTICE:  6: 819401.069504103  
NOTICE:  7: 845621.903728234  
NOTICE:  8: 872681.804647537  
NOTICE:  9: 900607.622396259  
NOTICE:  10: 929427.066312939  
NOTICE:  11: 959168.732434953  
NOTICE:  12: 989862.131872871  
      comp_x        
------------------  
 10048053.7529001  
(1 row)

微调第二个参数，结果向1000万靠拢即可。

年底要完成1000万目标，每天应该完成多少？

1、首先要计算第一天（第一个测算周期）的目标

postgres=# select 10000000/365;  
 ?column?   
----------  
    27397  
(1 row)

2、假设我们第一天完成1万，等比数列的X差不多等于1.00165，最后总的完成率就是1000万左右

postgres=# select * from comp_x(10000, 1.0048525, 365);  
NOTICE:  1:     10000  
NOTICE:  2:     10048.525  
NOTICE:  3:     10097.2854675625  
NOTICE:  4:     10146.2825452938  
NOTICE:  5:     10195.5173813449  
NOTICE:  6:     10244.9911294379  
NOTICE:  7:     10294.7049488935  
NOTICE:  8:     10344.660004658  
NOTICE:  9:     10394.8574673306  
NOTICE:  10:     10445.2985131908  
NOTICE:  11:     10495.984324226  
NOTICE:  12:     10546.9160881593  
NOTICE:  13:     10598.0949984771  
NOTICE:  14:     10649.5222544572  
NOTICE:  15:     10701.199061197  
NOTICE:  16:     10753.1266296415  
NOTICE:  17:     10805.3061766118  
NOTICE:  18:     10857.7389248338  
NOTICE:  19:     10910.4261029666  
NOTICE:  20:     10963.3689456312  
NOTICE:  21:     11016.5686934399  
NOTICE:  22:     11070.0265930248  
NOTICE:  23:     11123.7438970674  
NOTICE:  24:     11177.721864328  
NOTICE:  25:     11231.9617596746  
NOTICE:  26:     11286.4648541134  
NOTICE:  27:     11341.232424818  
NOTICE:  28:     11396.2657551594  
NOTICE:  29:     11451.5661347364  
NOTICE:  30:     11507.1348594052  
NOTICE:  31:     11562.9732313104  
NOTICE:  32:     11619.0825589154  
NOTICE:  33:     11675.4641570325  
NOTICE:  34:     11732.1193468545  
NOTICE:  35:     11789.0494559851  
NOTICE:  36:     11846.2558184703  
NOTICE:  37:     11903.7397748294  
NOTICE:  38:     11961.5026720868  
NOTICE:  39:     12019.5458638031  
NOTICE:  40:     12077.8707101072  
NOTICE:  41:     12136.478577728  
NOTICE:  42:     12195.3708400264  
NOTICE:  43:     12254.5488770276  
NOTICE:  44:     12314.0140754534  
NOTICE:  45:     12373.7678287545  
NOTICE:  46:     12433.8115371435  
NOTICE:  47:     12494.1466076275  
NOTICE:  48:     12554.774454041  
NOTICE:  49:     12615.6964970793  
NOTICE:  50:     12676.9141643314  
NOTICE:  51:     12738.4288903138  
NOTICE:  52:     12800.242116504  
NOTICE:  53:     12862.3552913744  
NOTICE:  54:     12924.7698704257  
NOTICE:  55:     12987.487316222  
NOTICE:  56:     13050.509098424  
NOTICE:  57:     13113.8366938241  
..........................................
NOTICE:  352:     54690.6880014031  
NOTICE:  353:     54956.0745649299  
NOTICE:  354:     55222.7489167562  
NOTICE:  355:     55490.7173058748  
NOTICE:  356:     55759.9860116015  
NOTICE:  357:     56030.5613437228  
NOTICE:  358:     56302.4496426432  
NOTICE:  359:     56575.6572795341  
NOTICE:  360:     56850.1906564831  
NOTICE:  361:     57126.0562066436  
NOTICE:  362:     57403.2603943864  
NOTICE:  363:     57681.8097154501  
NOTICE:  364:     57961.7106970944  
NOTICE:  365:     58242.969898252  
     comp_x        
-----------------  
 10000122.392516  
(1 row)

微调第二个参数，结果向1000万靠拢即可。

小结

1、如果设定的目标为最后一个测算周期，当期的目标，使用等比数列，计算方法如下：

求X:

初始周期值*(x^总期数) = 最后一个周期的当期目标  
使用科学计算器即可得到X：  
x^总期数 = 最后一个周期的当期目标/初始周期值

求每个周期当期的目标（例如12个月）：

postgres=# select i, 初始周期值*(x^(i)) from generate_series(1,12) i;

2、如果设定的目标为整个测算周期的总和，那么使用函数，设定初始周期值，输入计算增长比例，得到每个周期的目标值

函数如下

postgres=# create or replace function comp_x (  
  v_start float8,    -- 起始月、或起始测算周期的目标营收  
  v_x float8,     -- 每个月、或每个测算周期相比前一个周期的增长率  
  v_terms int    -- 总共多少个周期，如果是一年，每个周期是一个月，则取值12  
) returns float8 as $$  
declare  
  res float8 := 0;  
begin  
    -- 每个月的结果累加  
    -- 例如，要完成全年1000万目标，第一个月目标为a，每个月的增长率为x，公式如下  
    -- a + ax + ax^2 + ax^3 + ... + ax^11 = 1000万  
  for i in 1..v_terms loop  
    res := res + v_start*(v_x^(i-1));  
    raise notice '%: %', i, v_start*(v_x^(i-1));   
  end loop;  
  return res;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION

参考

https://www.postgresql.org/docs/11/functions-math.html

原文：http://mysql.taobao.org/monthly/2019/05/09/