背景

预测营收目标，预测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 多元线性回归 - 2 股票预测》

《在PostgreSQL中用线性回归分析(linear regression) - 实现数据预测》

《PostgreSQL 线性回归 - 股价预测 1》

《在PostgreSQL中用线性回归分析linear regression做预测 - 例子2, 预测未来数日某股收盘价》