PgSQL · 应用案例 · PostgreSQL KPI 预测例子__背景预测营收目标，预测KPI风险。使用PostgreSQL二维线性回归，更高级一点可以使用MADLIB多维线性回归。预测方法：例如有最近连续4周的营收数据，预测第一季度，第二季度，第三季度，第四季度的营收。例如设定了财年的营收目标，有最近连续四周的营收完成比率，预测第一季度，第二季度，第三季度，第四季度的营收完成率。_

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

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

原作者：阿里云PG数据库内核组　　创作时间：2020-09-09 20:19:03+08　　

wangliyun 发布于2020-09-10 08:00:00　　

评论: 1　　

顶: 425　

踩: 481　

### **背景**

#### 预测营收目标，预测KPI风险。使用PostgreSQL二维线性回归，更高级一点可以使用MADLIB多维线性回归。
#### 预测方法：
#### 例如有最近连续4周的营收数据，预测第一季度，第二季度，第三季度，第四季度的营收。
#### 例如设定了财年的营收目标，有最近连续四周的营收完成比率，预测第一季度，第二季度，第三季度，第四季度的营收完成率。
#### 公式：
```
自变量，第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周数据 * 斜率 + 截距
```

### **例子**

#### 假设设定今年要完成100亿的营收目标，有如下连续4周的营收完成情况
```
时间 | 财年目标 | 完成营收 | 完成比例  
---|---|---|---  
20190422 | 100亿 | xxx | 3.9%   
20190415 | 100亿 | xxx | 2.7%   
20190408 | 100亿 | xxx | 1.6%   
20190401 | 100亿 | xxx | 0.49%
```
#### 财年的Q1，Q2，Q3，Q4时间点如下
```
d1 date := '20190630';   
  d2 date := '20190930';   
  d3 date := '20191231';   
  d4 date := '20200331';
```
#### 预测财年每个Q的完成比例。
```
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
```
#### 预测数据还不错。

### **预测数据说明**

#### 此预测方法为线性预测，在加速上升期的产品，实际曲线是斜率越来越大的，所以预测期越远的预测数值可能会越低于实际数值。
#### 处于放缓上升速度的上升期的产品，实际曲线的斜率是越来越小的，所以预测期越远的预测数值可能会远大于实际数值。

### **参考**

#### 《PostgreSQL 多元线性回归 - 2 股票预测》
#### https://github.com/digoal/blog/blob/master/201512/20151214_01.md
#### 《在PostgreSQL中用线性回归分析(linear regression) - 实现数据预测》
#### https://github.com/digoal/blog/blob/master/201503/20150303_01.md
#### 《PostgreSQL 线性回归 - 股价预测 1》
#### https://github.com/digoal/blog/blob/master/201503/20150304_01.md
#### 《在PostgreSQL中用线性回归分析linear regression做预测 - 例子2, 预测未来数日某股收盘价》
#### https://github.com/digoal/blog/blob/master/201503/20150305_01.md

![CENTER_PostgreSQL_Community]( /images/news/2020/20200909_2030_最新底部文章.png)

评论:1　　

顶: 425　

踩: 481　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-24 10:45:21+08

护士长年度工作总结:https://www.nanss.com/gongzuo/19273.html 公司办公室工作总结:https://www.nanss.com/gongzuo/20191.html 丹顶鹤的故乡:https://www.nanss.com/shenghuo/18530.html 材料员岗位职责:https://www.nanss.com/gongzuo/20485.html 铁道游击队读后感:https://www.nanss.com/xuexi/19656.html 行政专员的工作内容:https://www.nanss.com/gongzuo/20714.html 镜子风水:https://www.nanss.com/jiaju/19249.html 橱柜高度:https://www.nanss.com/jiaju/20432.html 7的乘法口诀教学反思:https://www.nanss.com/xuexi/20610.html 营业收入:https://www.nanss.com/gongzuo/19011.html 容积率计算公式:https://www.nanss.com/shenghuo/20386.html 汽车维修论文:https://www.nanss.com/gongzuo/18587.html 办护照需要什么手续:https://www.nanss.com/shenghuo/18499.html 晚会开幕词:https://www.nanss.com/xuexi/20336.html 事业单位考试科目有哪些:https://www.nanss.com/xuexi/19825.html 供暖时间:https://www.nanss.com/shenghuo/20394.html 个人发展计划:https://www.nanss.com/gongzuo/19147.html 感谢领导的话语:https://www.nanss.com/gongzuo/19413.html 羽毛球比赛方案:https://www.nanss.com/xuexi/20519.html 优秀家长事迹材料:https://www.nanss.com/xuexi/19278.html 现实表现证明:https://www.nanss.com/gongzuo/20301.html 导师推荐信:https://www.nanss.com/xuexi/20342.html 木地板规格:https://www.nanss.com/shenghuo/20429.html 工厂标语:https://www.nanss.com/wenan/18671.html 卯时是几点到几点:https://www.nanss.com/jiaju/20561.html 立春是什么意思:https://www.nanss.com/shenghuo/20352.html 护理个人简历:https://www.nanss.com/gongzuo/18627.html 基层党建工作总结:https://www.nanss.com/gongzuo/20066.html 合作建房协议:https://www.nanss.com/shenghuo/18755.html 体育教学总结:https://www.nanss.com/gongzuo/18763.html