PgSQL · 应用案例 · PostgreSQL KPI分解，目标设定之 - 等比数列
Author: digoal
背景
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