我的书签
添加书签
移除书签背景
预测营收目标，预测KPI风险。使用PostgreSQL二维线性回归，更高级一点可以使用MADLIB多维线性回归。
预测方法：
例如有最近连续4周的营收数据，预测第一季度，第二季度，第三季度，第四季度的营收。
例如设定了财年的营收目标，有最近连续四周的营收完成比率，预测第一季度，第二季度，第三季度，第四季度的营收完成率。
公式：
1. 自变量，第N周的数据
3. 因变量，第N+1周的数据
5. src = array[第N周数据，第N+1周数据，...，第N+x周数据];
7. select
8. regr_slope(y,x), -- 斜率
9. regr_intercept(y,x) -- 截距
10. from (
11. select src[xx] x, src[xx+1] y from generate_series(1, array_length(src, 1)-1) xx
12. ) as t;
14. 预测第N+x+1周数据 = 第N+x周数据 * 斜率 + 截距
例子
假设设定今年要完成100亿的营收目标，有如下连续4周的营收完成情况
1. 时间 | 财年目标 | 完成营收 | 完成比例
2. ---|---|---|---
3. 20190422 | 100亿 | xxx | 3.9%
4. 20190415 | 100亿 | xxx | 2.7%
5. 20190408 | 100亿 | xxx | 1.6%
6. 20190401 | 100亿 | xxx | 0.49%
财年的Q1，Q2，Q3，Q4时间点如下
1. d1 date := '20190630';
2. d2 date := '20190930';
3. d3 date := '20191231';
4. d4 date := '20200331';
预测财年每个Q的完成比例。
1. do language plpgsql $$
2. declare
3. -- 录入连续4周的完成比率，一定要按顺序
4. src float8[] := array[0.49, 1.6, 2.7, 3.9];
6. -- 连续四周的最后一周的时间点
7. d0 date := '20190422';
9. -- 四个Q的时间节点
10. d1 date := '20190630';
11. d2 date := '20190930';
12. d3 date := '20191231';
13. d4 date := '20200331';
15. -- 四个Q离连续四周的最后一周的时间点的间隔周数
16. q1 int := round((d1-d0)/7, 0);
17. q2 int := round((d2-d0)/7, 0);
18. q3 int := round((d3-d0)/7, 0);
19. q4 int := round((d4-d0)/7, 0);
21. -- 斜率
22. slope float8;
23. -- 截距
24. intercept float8;
26. -- 每一次预测的下一个预测数，因变量数组
27. prev float8[];
29. -- 因变量数组的下标，从2开始动态计算
30. i int := 2;
32. -- 包含源数据、所有预测数据的大数组，作为每一次预测的源
33. tmp float8[];
35. begin
36. -- 第一次预测，计算斜率、截距
37. select regr_slope(y,x), regr_intercept(y,x) into slope,intercept from (
38. select src[xx] x, src[xx+1] y from generate_series(1, array_length(src, 1)-1) xx
39. ) as t;
41. -- raise notice '%,%', slope, intercept;
43. -- 第一个预测到的因变量
44. prev[1] := round((src[array_length(src,1)]*slope + intercept)::numeric, 2);
45. -- raise notice '%,%', prev, src;
47. loop
48. -- 将预测到的因变量数组追加到原始数组，生成tmp
49. tmp := array_cat(src, prev);
50. -- raise notice '%', tmp;
51. -- 使用tmp，计算截距、斜率
52. select regr_slope(y,x),regr_intercept(y,x) into slope,intercept from (
53. select tmp[xx] x, tmp[xx+1] y from generate_series(1, array_length(tmp, 1)-1) xx
54. ) as t;
56. -- 那截距、斜率计算因变量
57. prev[i] := round(((prev[i-1])*slope + intercept)::numeric, 2);
58. -- raise notice '%,%', prev, src;
60. -- 遇到关键节点，抛出对应预测数据
61. case i
62. when q1 then raise notice 'q1: %', prev[i];
63. when q2 then raise notice 'q2: %', prev[i];
64. when q3 then raise notice 'q3: %', prev[i];
65. when q4 then raise notice 'q4: %', prev[i];
66. else
67. null;
68. end case;
70. -- 到达Q4最后一天的周数，退出循环
71. exit when i=q4;
73. -- 周数累加
74. i := i+1;
75. end loop;
77. end;
78. $$;
结果
1. NOTICE: q1: 16.93
2. NOTICE: q2: 49.17
3. NOTICE: q3: 100.18
4. NOTICE: q4: 185.56
5. DO
预测数据还不错。
预测数据说明
此预测方法为线性预测，在加速上升期的产品，实际曲线是斜率越来越大的，所以预测期越远的预测数值可能会越低于实际数值。
处于放缓上升速度的上升期的产品，实际曲线的斜率是越来越小的，所以预测期越远的预测数值可能会远大于实际数值。
参考
《在PostgreSQL中用线性回归分析(linear regression) - 实现数据预测》
《在PostgreSQL中用线性回归分析linear regression做预测 - 例子2, 预测未来数日某股收盘价》
