PgSQL · 应用案例 · PostgreSQL KPI 预测例子

自变量，第N周的数据

因变量，第N+1周的数据

src = array[第N周数据，第N+1周数据，...，第N+x周数据];

  select   
  regr_slope(y,x),   -- 斜率  
  regr_intercept(y,x)   -- 截距  
  from (
    select src[xx] x, src[xx+1] y from generate_series(1, array_length(src, 1)-1) xx  
  ) as t;

预测第N+x+1周数据 = 第N+x周数据 * 斜率 + 截距

时间 | 财年目标 | 完成营收 | 完成比例  
---|---|---|---  
20190422 | 100亿 | xxx | 3.9%   
20190415 | 100亿 | xxx | 2.7%   
20190408 | 100亿 | xxx | 1.6%   
20190401 | 100亿 | xxx | 0.49%

d1 date := '20190630';   
  d2 date := '20190930';   
  d3 date := '20191231';   
  d4 date := '20200331';

do language plpgsql $$  
declare  
  -- 录入连续4周的完成比率，一定要按顺序  
  src float8[] := array[0.49, 1.6, 2.7, 3.9];

  -- 连续四周的最后一周的时间点  
  d0 date := '20190422';

  -- 四个Q的时间节点  
  d1 date := '20190630';   
  d2 date := '20190930';   
  d3 date := '20191231';   
  d4 date := '20200331';

  -- 四个Q离连续四周的最后一周的时间点的间隔周数  
  q1 int := round((d1-d0)/7, 0);   
  q2 int := round((d2-d0)/7, 0);   
  q3 int := round((d3-d0)/7, 0);   
  q4 int := round((d4-d0)/7, 0);

  -- 斜率  
  slope float8;   
  -- 截距  
  intercept float8;

  -- 每一次预测的下一个预测数，因变量数组  
  prev float8[];

  -- 因变量数组的下标，从2开始动态计算  
  i int := 2;

  -- 包含源数据、所有预测数据的大数组，作为每一次预测的源  
  tmp float8[];

begin  
  -- 第一次预测，计算斜率、截距  
  select regr_slope(y,x), regr_intercept(y,x) into slope,intercept from (  
    select src[xx] x, src[xx+1] y from generate_series(1, array_length(src, 1)-1) xx  
  ) as t;

  -- raise notice '%,%', slope, intercept;

  -- 第一个预测到的因变量  
  prev[1] := round((src[array_length(src,1)]*slope + intercept)::numeric, 2);  
  -- raise notice '%,%', prev, src;

loop  
  -- 将预测到的因变量数组追加到原始数组，生成tmp  
  tmp := array_cat(src, prev);  
  -- raise notice '%', tmp;  
  -- 使用tmp，计算截距、斜率  
  select regr_slope(y,x),regr_intercept(y,x) into slope,intercept from (  
    select tmp[xx] x, tmp[xx+1] y from generate_series(1, array_length(tmp, 1)-1) xx  
  ) as t;

  -- 那截距、斜率计算因变量  
  prev[i] := round(((prev[i-1])*slope + intercept)::numeric, 2);  
  -- raise notice '%,%', prev, src;

  -- 遇到关键节点，抛出对应预测数据  
  case i   
    when q1 then raise notice 'q1: %', prev[i];  
    when q2 then raise notice 'q2: %', prev[i];  
    when q3 then raise notice 'q3: %', prev[i];  
    when q4 then raise notice 'q4: %', prev[i];  
  else  
    null;  
  end case;

  -- 到达Q4最后一天的周数，退出循环  
  exit when i=q4;

  -- 周数累加  
  i := i+1;  
end loop;

end;  
$$;

NOTICE:  q1: 16.93  
NOTICE:  q2: 49.17  
NOTICE:  q3: 100.18  
NOTICE:  q4: 185.56  
DO