Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PostgreSQL: Performing Linear Regression Analysis to Predict Future Data (Part 2)

PostgreSQL: Performing Linear Regression Analysis to Predict Future Data (Part 2)

In this article, the author discusses a practical example to demonstrate future data prediction using linear regression analysis in PostgreSQL.

By Digoal

Background

This blog helps you familiarize with the principle of linear regression and PostgreSQL functions available to support linear regression analysis.

Practical Example

The following figure shows data prediction errors for Kweichow Moutai's stock price using its historical data.

For the next day’s data, the prediction is most accurate.

For the third day’s data, the prediction error increases slightly.

For the data of the fourth and fifth day, the prediction error is significant.

1

First, import data to a source table.

Note: Import the data in chronological order. Do not reverse the order.

06/02/2010 13.49 13.49 12.52 13.03 38670320 1571709568.000  
06/03/2010 13.09 13.26 12.69 12.75 27873689 1135419264.000  
06/04/2010 12.63 12.99 12.56 12.77 19305447 775373248.000  
06/07/2010 12.52 13.13 12.43 13.03 24762597 997748928.000  
06/08/2010 12.99 13.02 12.56 12.82 18987054 762023168.000  
06/09/2010 12.87 13.90 12.87 13.36 38510441 1623107328.000  
06/10/2010 13.37 13.51 13.26 13.39 19669987 823111744.000  
06/11/2010 13.46 13.58 13.27 13.37 18622806 783614336.000  
06/17/2010 13.48 13.99 13.29 13.31 25604558 1095663744.000  
06/18/2010 13.13 13.23 12.37 12.57 24897719 996842496.000  
....  
  
create table orig (id int, x numeric);

Data conversion:

1, 13.49  
2, 13.09  
,.....

Then, create a sample table.

create table tmp (  
id int,   
x numeric, --自变量
y numeric --因变量  
);

You can set independent variables and dependent variables in various ways.

For example, you can choose yesterday's closing price as the independent variable and today's opening price as the dependent variable.

You can also perform multivariate analysis. For example, take yesterday's closing price and the trading volume as independent variables and today's opening price as the dependent variable.

Generate yesterday’s sample data to predict today’s data.

truncate tmp;  
insert into tmp   
  select id,   
  lag(x,1) over(order by id)   
  x from orig;

Generate the sample data of the last two days to predict the next day’s data.

create table tmp1 (like tmp);  
insert into tmp1   
select id,  
x+  
lag(x,1) over(order by id)  
lead(x,1) over(order by id)  
from orig;

Generate the sample data of the last three days to predict the data of the next two days.

create table tmp2 (like tmp);  
insert into tmp2   
select id,  
x+  
lag(x,1) over(order by id)+  
lag(x,2) over(order by id)  
lead(x,1) over(order by id)+  
lead(x,2) over(order by id)  
from orig;

Generate the sample data of the last four days to predict the data of the next three days.

create table tmp3 (like tmp);  
insert into tmp3   
select id,  
x+  
lag(x,1) over(order by id)+  
lag(x,2) over(order by id)+  
lag(x,3) over(order by id)  
lead(x,1) over(order by id)+  
lead(x,2) over(order by id)+  
lead(x,3) over(order by id)  
from orig;

Generate the sample data of the last five days to predict the data of the next four days.

create table tmp4 (like tmp);  
insert into tmp4   
select id,  
x+  
lag(x,1) over(order by id)+  
lag(x,2) over(order by id)+  
lag(x,3) over(order by id)+  
lag(x,4) over(order by id)  
lead(x,1) over(order by id)+  
lead(x,2) over(order by id)+  
lead(x,3) over(order by id)+  
lead(x,4) over(order by id)  
from orig;

Use the following function to generate predicted data.

CREATE OR REPLACE FUNCTION public.check_predict(  
IN v_tbl name,       --  样本表名  
IN OUT ov integer,   --  校验哪条记录, 倒数第?个值的预测值, 不停迭代, 最后计算所有的实际值和预测值的corr, 选择最佳相关?  
OUT v_id int,        --  真实值唯一标识  
OUT r_chkv numeric,  --  真实值, 用于校验  
OUT p_yv numeric,    --  预测值,因变量  
OUT r_xv numeric,    --  自变量,用于预测因变量  
OUT dev numeric,     --  误差  
OUT v_slope numeric, --  斜率  
OUT v_inter numeric, --  截距  
OUT v_r2  numeric,   --  相关性  
OUT sampcnt int      --  获得最大相关度的样本数  
)  
 RETURNS record  
 LANGUAGE plpgsql  
AS $function$  
declare   
 r2_1 numeric := 0; -- 相关性  
  r2_2 numeric := 0; -- 最大相关性  
  inter_1 numeric;  --  截距  
  slope_1 numeric;  --  斜率  
  inter_2 numeric;  --  最大相关性截距  
  slope_2 numeric;  --  最大相关性斜率  
  v_lmt int := 90;  --  使用的最大样本集, 影响预测准确度  
  v_min int := 5;   --  使用的最小样本数, 影响预测准确度  
begin  
  --自变量 tbl.x  
  --  因变量 tbl.y  
  
  --  筛选最大相关度的样本数, 并记录下储斜率, 截距.  
  for I in 0..v_lmt   
  loop  
    execute $_$with t1   
      (   
       select row_number() over(order by id desc) as rn ,*   
       from   
       (select id,x,y from $_$||v_tbl||$_$ where x+y is not null order by id desc offset $1 limit $2) t  
      )  
        select regr_intercept(t1.y,t1.x), regr_slope(t1.y,t1.x), regr_r2(t1.y,t1.x)   
        from t1  
        where t1.rn<=$3 $_$  
    into inter_1,slope_1,r2_1  
    using ov, v_lmt+v_min, I +v_min;  
  
    if r2_1>r2_2 then   
      inter_2 := inter_1;  
      slope_2 := slope_1;  
      r2_2 := r2_1;  
      sampcnt := I +v_min;  
    end if;  
  end loop;  
  
  -- 下一个自变量ID, 用于预测因变量
  execute $_$select id+1 from $_$||v_tbl||$_$ where x+y is not null order by id desc offset $1 limit 1$_$  
  into v_id using ov;  
  
  --预测值,自变量,真实值, 如果真实值为空, 说明该条记录没有未来的真实记录, 就是要预测的将来值.  
  execute $_$select round($_$||slope_2||'*x+'||inter_2||$_$,4), x, y from $_$||v_tbl||$_$ where id=$1 $_$   
  into p_yv,r_xv,r_chkv  
  using v_id;  
  
  dev := abs(1-round(p_yv/r_chkv,4));  
  v_slope := round(slope_2,5);  
  v_inter := round(inter_2,5);  
  v_r2 := round(r2_2,5);  
  
  return;  
end;  
$function$

Generate the predicted data and verification data of the last 100 days.

create table p1 as select (check_predict('tmp1', I )).* from generate_series(0,100) t( I);  
create table p2 as select (check_predict('tmp2', I )).* from generate_series(0,100) t( I);  
create table p3 as select (check_predict('tmp3', I )).* from generate_series(0,100) t( I);  
create table p4 as select (check_predict('tmp4', I )).* from generate_series(0,100) t( I);

Method for predicting data of the next four days:

The next day p1

The 2nd day p2-p1

The 3rd day p3-p2

The 4th day p4-p3

Verification method:

select   
v_id  
r_chkv  
p_yv  
abs(1-round(p_yv/r_chkv,4)) as mis1  
r_chkv2  
p_yv2  
abs(1-round(p_yv2/r_chkv2,4)) as mis2  
r_chkv3  
p_yv3  
abs(1-round(p_yv3/r_chkv3,4)) as mis3  
r_chkv4  
p_yv4  
abs(1-round(p_yv4/r_chkv4,4)) as mis4  
from  
(  
select   
  p1.v_id  
  p1.r_chkv  
  p1.p_yv  
  lag(p1.r_chkv,1) over(order by p1.v_id desc) as r_chkv2  
  p2.p_yv-p1.p_yv as p_yv2  
  lag(p1.r_chkv,2) over(order by p1.v_id desc) as r_chkv3  
  p3.p_yv-p2.p_yv as p_yv3  
  lag(p1.r_chkv,3) over(order by p1.v_id desc) as r_chkv4  
  p4.p_yv-p3.p_yv as p_yv4  
  from   
  p1,p2,p3,p4   
  where p1.v_id=p2.v_id and p1.v_id=p3.v_id and p1.v_id=p4.v_id  
) t;

Verified data:

 v_id | r_chkv | p_yv | miss1 | r_chkv2 | p_yv2 | mis2 | r_chkv3 | p_yv3 | miss3 | r_chkv4 | p_yv4 | miss4    
+------------------------------------------------+--------------------------------------------------------------------+--------+  
 1050 | 157.81 | 159.5745 | 0.0112 | | 160.7402 | | | 162.2997 | | | 151.4013 |         
 1049 | 159.02 | 160.5464 | 0.0096 | 157.81 | 162.1956 | 0.0278 | | 162.3978 | | | 148.0711 |         
 1048 | 159.87 | 161.9598 | 0.0131 | 159.02 | 162.4292 | 0.0214 | 157.81 | 162.0693 | 0.0270 | | 147.9578 |         
 1047 | 161.00 | 162.6313 | 0.0101 | 159.87 | 162.1514 | 0.0143 | 159.02 | 161.7551 | 0.0172 | 157.81 | 158.5270 | 0.0045  
 1046 | 162.74 | 162.0216 | 0.0044 | 161.00 | 161.6165 | 0.0038 | 159.87 | 158.5411 | 0.0083 | 159.02 | 158.4651 | 0.0035  
 1045 | 162.33 | 161.3257 | 0.0062 | 162.74 | 149.7262 | 0.0800 | 161.00 | 167.0954 | 0.0379 | 159.87 | 161.2083 | 0.0084  
 1044 | 161.50 | 158.8824 | 0.0162 | 162.33 | 157.8832 | 0.0274 | 162.74 | 160.6849 | 0.0126 | 161.00 | 163.8815 | 0.0179  
 1043 | 160.92 | 156.8056 | 0.0256 | 161.50 | 159.7366 | 0.0109 | 162.33 | 159.6163 | 0.0167 | 162.74 | 159.0992 | 0.0224  
 1042 | 156.49 | 158.8599 | 0.0151 | 160.92 | 159.4193 | 0.0093 | 161.50 | 158.9038 | 0.0161 | 162.33 | 159.4251 | 0.0179  
 1041 | 156.70 | 160.1439 | 0.0220 | 156.49 | 159.0677 | 0.0165 | 160.92 | 159.2684 | 0.0103 | 161.50 | 160.9401 | 0.0035  
 1040 | 160.71 | 158.8216 | 0.0118 | 156.70 | 158.9957 | 0.0147 | 156.49 | 160.6000 | 0.0263 | 160.92 | 161.9692 | 0.0065  
 1039 | 159.25 | 158.4342 | 0.0051 | 160.71 | 160.1400 | 0.0035 | 156.70 | 161.7433 | 0.0322 | 156.49 | 161.8723 | 0.0344  
 1038 | 158.02 | 159.6522 | 0.0103 | 159.25 | 161.4267 | 0.0137 | 160.71 | 149.8541 | 0.0675 | 156.70 | 175.7699 | 0.1217  
 1037 | 158.47 | 161.3605 | 0.0182 | 158.02 | 161.7616 | 0.0237 | 159.25 | 163.7032 | 0.0280 | 160.71 | 164.0469 | 0.0208  
 1036 | 160.50 | 161.8688 | 0.0085 | 158.47 | 163.5362 | 0.0320 | 158.02 | 163.9749 | 0.0377 | 159.25 | 167.3141 | 0.0506  
 1035 | 161.90 | 162.8418 | 0.0058 | 160.50 | 163.8163 | 0.0207 | 158.47 | 167.1359 | 0.0547 | 158.02 | 153.6915 | 0.0274  
 1034 | 161.49 | 163.9804 | 0.0154 | 161.90 | 166.8222 | 0.0304 | 160.50 | 166.4108 | 0.0368 | 158.47 | 151.0944 | 0.0465  
 1033 | 163.87 | 165.8788 | 0.0123 | 161.49 | 166.3372 | 0.0300 | 161.90 | 165.9846 | 0.0252 | 160.50 | 164.6662 | 0.0260  
 1032 | 163.70 | 166.9794 | 0.0200 | 163.87 | 166.0894 | 0.0135 | 161.49 | 164.6576 | 0.0196 | 161.90 | 161.7623 | 0.0009  
 1031 | 167.65 | 165.4608 | 0.0131 | 163.70 | 164.2976 | 0.0037 | 163.87 | 161.5290 | 0.0143 | 161.49 | 160.9499 | 0.0033  
 1030 | 165.70 | 163.8841 | 0.0110 | 167.65 | 161.2073 | 0.0384 | 163.70 | 160.5254 | 0.0194 | 163.87 | 158.8926 | 0.0304  
 1029 | 164.70 | 160.9278 | 0.0229 | 165.70 | 159.7672 | 0.0358 | 167.65 | 158.2872 | 0.0558 | 163.70 | 158.4474 | 0.0321  
 1028 | 162.58 | 158.7257 | 0.0237 | 164.70 | 157.5536 | 0.0434 | 165.70 | 157.6546 | 0.0486 | 167.65 | 173.5841 | 0.0354  
 1027 | 158.81 | 157.3449 | 0.0092 | 162.58 | 157.0147 | 0.0342 | 164.70 | 158.5247 | 0.0375 | 165.70 | 171.3281 | 0.0340  
 1026 | 158.20 | 156.2507 | 0.0123 | 158.81 | 157.7433 | 0.0067 | 162.58 | 158.9512 | 0.0223 | 164.70 | 160.1243 | 0.0278  
 1025 | 156.00 | 157.0983 | 0.0070 | 158.20 | 158.4975 | 0.0019 | 158.81 | 159.6796 | 0.0055 | 162.58 | 145.3973 | 0.1057  
 1024 | 156.00 | 158.3550 | 0.0151 | 156.00 | 159.4457 | 0.0221 | 158.20 | 160.4796 | 0.0144 | 158.81 | 148.8340 | 0.0628  
 1023 | 157.72 | 159.1382 | 0.0090 | 156.00 | 160.2608 | 0.0273 | 156.00 | 162.2360 | 0.0400 | 158.20 | 146.8649 | 0.0717  
 1022 | 158.50 | 159.9296 | 0.0090 | 157.72 | 161.8630 | 0.0263 | 156.00 | 160.3253 | 0.0277 | 156.00 | 161.2206 | 0.0335  
 1021 | 159.27 | 161.3250 | 0.0129 | 158.50 | 160.2559 | 0.0111 | 157.72 | 161.1143 | 0.0215 | 156.00 | 163.1739 | 0.0460  
 1020 | 160.00 | 160.7364 | 0.0046 | 159.27 | 160.7603 | 0.0094 | 158.50 | 162.8499 | 0.0274 | 157.72 | 163.6353 | 0.0375  
 1019 | 162.00 | 159.5627 | 0.0150 | 160.00 | 161.9158 | 0.0120 | 159.27 | 163.1346 | 0.0243 | 158.50 | 163.7176 | 0.0329  
 1018 | 158.74 | 161.2400 | 0.0157 | 162.00 | 162.5665 | 0.0035 | 160.00 | 163.4097 | 0.0213 | 159.27 | 164.0869 | 0.0302  
 1017 | 159.75 | 162.5560 | 0.0176 | 158.74 | 163.0135 | 0.0269 | 162.00 | 163.6795 | 0.0104 | 160.00 | 164.2167 | 0.0264  
 1016 | 162.00 | 162.5926 | 0.0037 | 159.75 | 163.1884 | 0.0215 | 158.74 | 163.7584 | 0.0316 | 162.00 | 164.8763 | 0.0178  
 1015 | 162.20 | 162.5296 | 0.0020 | 162.00 | 163.0662 | 0.0066 | 159.75 | 164.4497 | 0.0294 | 158.74 | 164.8095 | 0.0382  
 1014 | 162.02 | 162.4474 | 0.0026 | 162.20 | 163.5357 | 0.0082 | 162.00 | 164.2277 | 0.0138 | 159.75 | 166.0446 | 0.0394  
 1013 | 162.04 | 162.6981 | 0.0041 | 162.02 | 163.3388 | 0.0081 | 162.20 | 162.3852 | 0.0011 | 162.00 | 170.6363 | 0.0533  
 1012 | 161.81 | 162.8286 | 0.0063 | 162.04 | 164.2343 | 0.0135 | 162.02 | 160.1472 | 0.0116 | 162.20 | 174.4763 | 0.0757  
 1011 | 162.48 | 163.1765 | 0.0043 | 161.81 | 165.7026 | 0.0241 | 162.04 | 157.7701 | 0.0264 | 162.02 | 176.5230 | 0.0895  
 1010 | 161.96 | 164.7836 | 0.0174 | 162.48 | 165.9001 | 0.0210 | 161.81 | 166.6854 | 0.0301 | 162.04 | 165.4803 | 0.0212  
 1009 | 163.10 | 165.4185 | 0.0142 | 161.96 | 165.6852 | 0.0230 | 162.48 | 152.0170 | 0.0644 | 161.81 | 176.6369 | 0.0916  
 1008 | 164.80 | 164.4899 | 0.0019 | 163.10 | 163.5277 | 0.0026 | 161.96 | 161.2467 | 0.0044 | 162.48 | 166.5030 | 0.0248  
 1007 | 164.00 | 162.4525 | 0.0094 | 164.80 | 161.3928 | 0.0207 | 163.10 | 168.8513 | 0.0353 | 161.96 | 164.6355 | 0.0165  
 1006 | 163.00 | 159.8954 | 0.0190 | 164.00 | 162.0882 | 0.0117 | 164.80 | 172.2702 | 0.0453 | 163.10 | 164.4402 | 0.0082  
 1005 | 160.17 | 160.1776 | 0.0000 | 163.00 | 164.6717 | 0.0103 | 164.00 | 165.7490 | 0.0107 | 164.80 | 155.6993 | 0.0552  
 1004 | 158.29 | 163.8475 | 0.0351 | 160.17 | 166.8448 | 0.0417 | 163.00 | 168.3659 | 0.0329 | 164.00 | 143.8282 | 0.1230  
 1003 | 160.74 | 166.3904 | 0.0352 | 158.29 | 163.5572 | 0.0333 | 160.17 | 175.3659 | 0.0949 | 163.00 | 172.4586 | 0.0580  
 1002 | 164.30 | 166.5811 | 0.0139 | 160.74 | 164.5718 | 0.0238 | 158.29 | 166.3753 | 0.0511 | 160.17 | 182.3603 | 0.1385  
 1001 | 164.23 | 166.7825 | 0.0155 | 164.30 | 164.0588 | 0.0015 | 160.74 | 167.1305 | 0.0398 | 158.29 | 165.5798 | 0.0461  
 1000 | 164.00 | 166.1178 | 0.0129 | 164.23 | 165.1107 | 0.0054 | 164.30 | 166.9978 | 0.0164 | 160.74 | 164.5353 | 0.0236  
  999 | 163.76 | 163.8419 | 0.0005 | 164.00 | 167.2181 | 0.0196 | 164.23 | 162.5489 | 0.0102 | 164.30 | 165.9863 | 0.0103  
  998 | 162.00 | 163.2600 | 0.0078 | 163.76 | 159.6280 | 0.0252 | 164.00 | 166.3823 | 0.0145 | 164.23 | 167.9779 | 0.0228  
  997 | 159.60 | 159.9979 | 0.0025 | 162.00 | 159.8471 | 0.0133 | 163.76 | 167.4848 | 0.0227 | 164.00 | 172.3491 | 0.0509  
  996 | 160.39 | 152.9068 | 0.0467 | 159.60 | 163.1250 | 0.0221 | 162.00 | 161.9731 | 0.0002 | 163.76 | 172.0482 | 0.0506  
  995 | 153.59 | 152.9868 | 0.0039 | 160.39 | 149.1597 | 0.0700 | 159.60 | 161.8337 | 0.0140 | 162.00 | 148.5907 | 0.0828  
  994 | 154.09 | 150.5045 | 0.0233 | 153.59 | 144.0137 | 0.0623 | 160.39 | 142.0351 | 0.1144 | 159.60 | 158.6477 | 0.0060  
  993 | 153.85 | 144.8516 | 0.0585 | 154.09 | 142.1239 | 0.0777 | 153.59 | 130.1523 | 0.1526 | 160.39 | 143.8730 | 0.1030  
  992 | 149.11 | 141.3053 | 0.0523 | 153.85 | 140.7890 | 0.0849 | 154.09 | 169.4041 | 0.0994 | 153.59 | 122.2508 | 0.2040  
  991 | 141.87 | 140.9553 | 0.0064 | 149.11 | 141.0677 | 0.0539 | 153.85 | 151.3856 | 0.0160 | 154.09 | 138.8321 | 0.0990  
  990 | 141.47 | 141.1875 | 0.0020 | 141.87 | 142.3318 | 0.0033 | 149.11 | 144.5009 | 0.0309 | 153.85 | 144.1629 | 0.0630  
  989 | 141.12 | 142.5450 | 0.0101 | 141.47 | 141.9629 | 0.0035 | 141.87 | 141.8605 | 0.0001 | 149.11 | 140.3056 | 0.0590  
  988 | 142.00 | 142.9206 | 0.0065 | 141.12 | 142.4254 | 0.0093 | 141.47 | 141.1854 | 0.0020 | 141.87 | 141.4228 | 0.0032  
  987 | 144.10 | 142.5783 | 0.0106 | 142.00 | 145.0599 | 0.0215 | 141.12 | 137.6055 | 0.0249 | 141.47 | 140.2459 | 0.0087  
  986 | 142.80 | 142.3051 | 0.0035 | 144.10 | 141.4349 | 0.0185 | 142.00 | 141.3055 | 0.0049 | 141.12 | 139.3921 | 0.0122  
  985 | 143.40 | 142.0016 | 0.0098 | 142.80 | 141.2233 | 0.0110 | 144.10 | 140.3575 | 0.0260 | 142.00 | 139.5268 | 0.0174  
  984 | 142.21 | 141.8180 | 0.0028 | 143.40 | 140.7662 | 0.0184 | 142.80 | 140.4219 | 0.0167 | 144.10 | 146.7834 | 0.0186  
  983 | 142.03 | 142.0947 | 0.0005 | 142.21 | 140.1053 | 0.0148 | 143.40 | 140.8399 | 0.0179 | 142.80 | 141.7092 | 0.0076  
  982 | 142.59 | 140.8774 | 0.0120 | 142.03 | 141.0675 | 0.0068 | 142.21 | 142.7018 | 0.0035 | 143.40 | 137.5108 | 0.0411  
  981 | 141.18 | 141.3493 | 0.0012 | 142.59 | 142.9580 | 0.0026 | 142.03 | 138.6157 | 0.0240 | 142.21 | 135.7893 | 0.0451  
  980 | 141.42 | 143.1335 | 0.0121 | 141.18 | 139.4681 | 0.0121 | 142.59 | 136.6854 | 0.0414 | 142.03 | 135.3345 | 0.0471  
  979 | 142.23 | 141.9348 | 0.0021 | 141.42 | 136.7925 | 0.0327 | 141.18 | 135.8861 | 0.0375 | 142.59 | 135.7501 | 0.0480  
  978 | 145.33 | 136.8286 | 0.0585 | 142.23 | 135.5206 | 0.0472 | 141.42 | 135.6964 | 0.0405 | 141.18 | 135.9612 | 0.0370  
  977 | 138.41 | 135.1052 | 0.0239 | 145.33 | 135.1948 | 0.0697 | 142.23 | 135.5496 | 0.0470 | 141.42 | 136.0220 | 0.0382  
  976 | 135.57 | 135.1128 | 0.0034 | 138.41 | 135.2206 | 0.0230 | 145.33 | 135.5166 | 0.0675 | 142.23 | 134.9895 | 0.0509  
  975 | 134.84 | 135.5939 | 0.0056 | 135.57 | 135.6036 | 0.0002 | 138.41 | 134.6630 | 0.0271 | 145.33 | 134.4826 | 0.0746  
  974 | 135.62 | 135.9134 | 0.0022 | 134.84 | 134.9537 | 0.0008 | 135.57 | 134.4806 | 0.0080 | 138.41 | 135.1341 | 0.0237  
  973 | 135.84 | 136.2169 | 0.0028 | 135.62 | 134.0258 | 0.0118 | 134.84 | 135.2289 | 0.0029 | 135.57 | 135.5020 | 0.0005  
  972 | 136.30 | 135.5374 | 0.0056 | 135.84 | 134.5033 | 0.0098 | 135.62 | 135.6042 | 0.0001 | 134.84 | 134.6588 | 0.0013  
  971 | 135.02 | 136.0409 | 0.0076 | 136.30 | 134.9620 | 0.0098 | 135.84 | 134.7869 | 0.0078 | 135.62 | 136.5069 | 0.0065  
  970 | 134.83 | 136.8923 | 0.0153 | 135.02 | 136.8753 | 0.0137 | 136.30 | 136.7761 | 0.0035 | 135.84 | 138.5646 | 0.0201  
  969 | 136.09 | 136.5405 | 0.0033 | 134.83 | 136.2487 | 0.0105 | 135.02 | 130.9540 | 0.0301 | 136.30 | 143.5801 | 0.0534  
  968 | 136.71 | 135.4534 | 0.0092 | 136.09 | 137.1882 | 0.0081 | 134.83 | 134.5044 | 0.0024 | 135.02 | 138.9483 | 0.0291  
  967 | 135.34 | 136.0370 | 0.0051 | 136.71 | 135.3722 | 0.0098 | 136.09 | 136.8509 | 0.0056 | 134.83 | 137.4119 | 0.0191  
  966 | 134.48 | 135.5328 | 0.0078 | 135.34 | 136.1922 | 0.0063 | 136.71 | 136.7664 | 0.0004 | 136.09 | 138.0672 | 0.0145  
  965 | 136.64 | 134.7746 | 0.0137 | 134.48 | 135.9564 | 0.0110 | 135.34 | 137.3660 | 0.0150 | 136.71 | 137.6435 | 0.0068  
  964 | 133.41 | 135.5599 | 0.0161 | 136.64 | 136.7964 | 0.0011 | 134.48 | 137.1349 | 0.0197 | 135.34 | 137.0746 | 0.0128  
  963 | 135.11 | 136.0755 | 0.0071 | 133.41 | 136.6319 | 0.0242 | 136.64 | 136.5669 | 0.0005 | 134.48 | 137.0378 | 0.0190  
  962 | 135.11 | 136.3818 | 0.0094 | 135.11 | 136.1917 | 0.0080 | 133.41 | 136.5466 | 0.0235 | 136.64 | 138.1209 | 0.0108  
  961 | 136.25 | 135.7434 | 0.0037 | 135.11 | 136.0128 | 0.0067 | 135.11 | 137.5760 | 0.0183 | 133.41 | 126.0336 | 0.0553  
  960 | 135.78 | 135.3767 | 0.0030 | 136.25 | 136.9301 | 0.0050 | 135.11 | 134.5556 | 0.0041 | 135.11 | 128.1953 | 0.0512  
  959 | 135.01 | 136.2514 | 0.0092 | 135.78 | 134.3601 | 0.0105 | 136.25 | 127.2257 | 0.0662 | 135.11 | 141.2927 | 0.0458  
  958 | 135.11 | 134.9542 | 0.0012 | 135.01 | 134.4525 | 0.0041 | 135.78 | 132.8486 | 0.0216 | 136.25 | 139.1490 | 0.0213  
  957 | 136.84 | 133.1440 | 0.0270 | 135.11 | 130.0163 | 0.0377 | 135.01 | 132.2245 | 0.0206 | 135.78 | 145.3409 | 0.0704  
  956 | 132.39 | 130.5360 | 0.0140 | 136.84 | 131.4004 | 0.0398 | 135.11 | 134.8190 | 0.0022 | 135.01 | 137.9683 | 0.0219  
  955 | 133.21 | 129.2345 | 0.0298 | 132.39 | 133.5173 | 0.0085 | 136.84 | 137.1225 | 0.0021 | 135.11 | 137.3024 | 0.0162  
  954 | 127.02 | 132.9641 | 0.0468 | 133.21 | 118.8850 | 0.1075 | 132.39 | 154.8653 | 0.1698 | 136.84 | 140.8124 | 0.0290  
  953 | 130.66 | 136.5636 | 0.0452 | 127.02 | 137.1733 | 0.0799 | 133.21 | 102.4452 | 0.2309 | 132.39 | 118.3449 | 0.1061  
(98 rows)

You can use more industry data to verify the feasibility of this prediction technique.

It could be the sales data of the catering and retail industries, the traffic data of people and vehicles of shopping malls, the traffic data of people of railway stations and bus stations, and production and sales data of the agricultural and sideline industry.

PostgreSQL Database Linear Regression Data Prediction Stock Price Prediction
0 0 0
Share on

Read previous post:

PostgreSQL: Performing Linear Regression Analysis to Predict Future Data (Part 1)

Read next post:

PostgreSQL: High Availability and Disaster Recovery Solutions for the Financial Industry

digoal

277 posts | 24 followers

You may also like

Comments

digoal

277 posts | 24 followers

Related Products

  • PolarDB for PostgreSQL

    Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.

    Learn More

  • Database Overview

    ApsaraDB: Faster, Stronger, More Secure

    Learn More

  • Epidemic Prediction Solution

    This technology can be used to predict the spread of COVID-19 and help decision makers evaluate the impact of various prevention and control measures on the development of the epidemic.

    Learn More

  • Tair

    Tair is a Redis-compatible in-memory database service that provides a variety of data structures and enterprise-level capabilities.

    Learn More

More Posts by digoal

See All