# Community

Blog Events Webinars Tutorials Forum
×
Community Blog PostgreSQL: How to Predict Stock Price Using Univariate Linear Regression

# PostgreSQL: How to Predict Stock Price Using Univariate Linear Regression

In this article, we will show you how to use PostgreSQL to predict stock price by employing univariate linear regression.

By digoal

## Background

This article gives a simple example of univariate linear regression using PostgreSQL. The data used here is the closing price of the stock.

If you want to perform multivariate analysis, you need to add the dimensions to calculate the expected value manually. For example, you can perform regression calculations on the market index, turnover, lowest, highest price, and price range.

Examples are as follows:

Enter the test in the actual order — for example, the test time and the ID range from small to large. Here, the end goal is to use the previous value to predict the next value.

The first record is the oldest value, and the last record is the latest value.

``create table test(id int, cnt numeric);  ``

The following is Alibaba's closing price data netizens provided.

Format: date, close, volume, open, high, low

``````vi ali.txt
2015/3/3,81.58,39173430,82.95,83.25,80.03
2015/3/2,84,11175870,85,85.02,83.75
2015/2/27,85.12,8362081,85.95,86.56,85
2015/2/26,85.37,8758019,86.92,87.16,85.2
2015/2/25,86.19,13592080,84.38,86.83,84.36
2015/2/24,84.69,15817560,85.53,85.53,83.88
2015/2/23,85.47,9352529,86.51,86.68,85.25
2015/2/20,86.64,7805009,87.25,87.29,86.38
2015/2/19,86.89,7568275,86.81,87.87,86.71
2015/2/18,86.74,7415961,87.1,87.43,86.5
2015/2/17,86.85,15133130,88.78,88.99,86.7
2015/2/13,89.05,14587390,88.2,89.3,87.65
2015/2/12,87.1,15143910,85.6,88.3,85.55
2015/2/11,86,12281340,87.58,87.7,85.82
2015/2/10,87.26,12026260,87.01,87.47,86.52
2015/2/9,86,12088070,85.83,86.75,85.47
2015/2/6,85.68,17597490,87.11,87.4,85.42
2015/2/5,87,28867570,89.58,89.84,86.1
2015/2/4,90,14599400,90.99,91.88,89.48
2015/2/3,90.61,13475970,91.65,91.65,89.9
2015/2/2,90.13,18845140,91.13,91.66,88.61
2015/1/30,89.08,36747370,89.6,92,88.11
2015/1/29,89.81,76430640,90.53,90.74,87.36
2015/1/28,98.45,42012700,100.3,101.49,97.79
2015/1/27,102.94,15591560,102.89,103.57,100.58
2015/1/26,103.99,10656340,104.4,105.13,103.33
2015/1/23,103.11,9860504,104.02,105.2,103.02
2015/1/22,104,11400180,104.6,104.92,103.1
2015/1/21,103.29,15141470,100.75,103.86,100.32
2015/1/20,100.04,12049470,98.3,100.21,97.59
2015/1/16,96.89,13313120,96.09,97.8,95.52
2015/1/15,96.31,18182200,99.67,100.14,96.02
2015/1/14,99.58,17785990,99.28,100.18,98.06
2015/1/13,100.77,11275790,102.59,102.85,100.01
2015/1/12,101.62,7946740,103.2,103.36,101.21
2015/1/9,103.02,10193410,105.24,105.3,102.89
2015/1/8,105.03,12904640,102.95,105.3399,102.68
2015/1/7,102.13,11000010,104.59,104.74,102.03
2015/1/6,103.32,15662710,101.25,103.85,100.11
2015/1/5,101,18313750,102.76,103.02,99.9
2015/1/2,103.6,12286400,104.24,104.72,102.52
2014/12/31,103.94,10254350,106.46,106.47,103.69
2014/12/30,105.75,10185890,105.64,106.71,105.13
2014/12/29,105.98,8059191,105.95,107.66,105.64
2014/12/26,105.95,6523956,105.99,106.94,105.5
2014/12/24,105.95,5870358,105.68,107.21,105.6
2014/12/23,105.52,19077270,108.3,108.47,103.88
2014/12/22,108.77,12985130,110.63,110.98,108.53
2014/12/19,110.65,14785340,109.93,110.65,108.04
2014/12/18,109.25,22749890,110.58,111.2,108.26
2014/12/17,109.02,17242940,107.11,109.19,106.28
2014/12/16,105.77,21655360,103.75,107.68,103.7
2014/12/15,104.7,16476750,106.39,107.77,103.7
2014/12/12,105.11,14504050,104.7,107.45,104.179
2014/12/11,104.97,15658440,104.44,106.84,104.29
2014/12/10,103.88,18398440,107.09,107.38,103.51
2014/12/9,107.48,24805130,102.27,107.95,101.2
2014/12/8,105.07,19166550,105.97,107.4,104.2101
2014/12/5,107.9,12125090,109.6,110.35,107.76
2014/12/4,109.17,10790400,110.1,110.5,108.91
2014/12/3,110.64,16164860,110.4,111.68,108.798  ``````

Import the data into the database:

``````create table tmp(c1 text,c2 numeric,c3 numeric,c4 numeric,c5 numeric,c6 numeric);
copy tmp from '/home/postgres/ali.txt' with (format csv);  ``````

Change the order:

``insert into test select row_number() over(order by ctid desc), c2 from tmp;  ``

Checksum function:

``````CREATE OR REPLACE FUNCTION public.check_predict(
IN ov integer,   --  校验哪条记录
OUT rv numeric,  --  真实值
OUT pv numeric,  --  预测值
OUT dev numeric, --  误差
OUT zv numeric,  --  自变量
OUT v_slope numeric, --  斜率
OUT v_inter numeric, --  截距
OUT v_r2  numeric  --  相关性
)
RETURNS record
LANGUAGE plpgsql
AS \$function\$
declare
r2_1 numeric := 0; -- 相关性
r2_2 numeric := 0; -- 最大相关性
var int;           --  样本起点
inter_1 numeric;  --  截距
slope_1 numeric;  --  斜率
inter_2 numeric;  --  最大相关性截距
slope_2 numeric;  --  最大相关性斜率
realv numeric;    --  真实数据
predicv numeric;  --  预测数据
offset_var int := ov;   -- 倒数第? 个值的预测值, 不停迭代, 最后计算所有的实际值和预测值的corr, 选择最佳相关?
v_cnt int8 := 0;
v_lmt int := 90;  --  使用的最大样本集, 影响预测准确度
v_min int := 5;   --  使用的最小样本数, 影响预测准确度
zcnt numeric := 0;    --  自变量
begin
select count(*) into v_cnt from test;

--  自变量 t1
--  因变量 t2

--  筛选最大相关度的样本数, 并记录下储斜率, 截距.
for i in 0..v_lmt
loop
with t1 as (select row_number() over(order by id desc) as rn,* from (select id,lag(cnt,1) over(order by id) as zbl_cnt,cnt from test order by id desc offset offset_var limit v_lmt+v_min) t
where t.zbl_cnt is not null
)
select regr_intercept(t1.cnt,t1.zbl_cnt), regr_slope(t1.cnt,t1.zbl_cnt), regr_r2(t1.cnt,t1.zbl_cnt)
into inter_1,slope_1,r2_1
from t1
where t1.rn<=i+v_min;

if r2_1>r2_2 then
inter_2 := inter_1;
slope_2 := slope_1;
r2_2 := r2_1;
var := i+v_min;
end if;
end loop;

raise notice 'offset:%, 最大相关度样本数:%, 截距%, 斜率%, 相关性%', offset_var, var, round(inter_2,4), round(slope_2,4), round(r2_2,4);
select slope_2*cnt+inter_2, cnt into predicv,zcnt from test order by id desc offset offset_var limit 1;

--  offset_var=0的自变量, 表示最近一个值, 所以真实值还不存在, 那么realv=NULL
if  offset_var=0 then
realv := NULL;
else
select cnt into realv from test order by id desc offset offset_var-1 limit 1;
end if;

raise notice '自变量%, 真实数据%, 预测数据%, 本次预测偏差%%%', zcnt, realv, round(predicv,2), abs(1-round(predicv/realv,4))*100;

rv := realv;
pv := round(predicv,2);
dev := abs(1-round(predicv/realv,4));
zv  := zcnt;
v_slope := slope_2;
v_inter := inter_2;
v_r2 := r2_2;

return;
end;
\$function\$;  ``````

If the prediction result deviates a lot, look at the deviation data displayed in the dev column.

If your data contains data on today's closing price, the first line is the forecast closing price for tomorrow.

``````postgres=# select (check_predict(i)).* from generate_series(0,50) t(i);
rv   |   pv   |  dev   |   zv   |      v_slope       |      v_inter       |        v_r2
--------+--------+--------+--------+--------------------+--------------------+--------------------
|  81.50 |        |  81.58 |  0.930196213911424 |   5.61421408650552 |  0.461071168376043
81.58 |  84.90 | 0.0407 |     84 |  0.560745221248429 |   37.8015394128117 |   0.23571694348864
84 |  85.84 | 0.0219 |  85.12 |  0.416036492272315 |    50.428211921701 |  0.160707728559778
85.12 |  86.06 | 0.0110 |  85.37 |   0.31332110091768 |   59.3087633551948 | 0.0916611831863153
85.37 |  86.40 | 0.0121 |  86.19 |  0.223065327998904 |   67.1752208792373 | 0.0892441401437571
86.19 |  85.51 | 0.0078 |  84.69 |  0.509756185614852 |    42.343562005006 |  0.338718526733452
84.69 |  85.97 | 0.0151 |  85.47 |   0.60492702580558 |   34.2628366224153 |  0.408610576552954
85.47 |  86.86 | 0.0163 |  86.64 |  0.641095276611868 |   31.3167837973076 |  0.422562923551744
86.64 |  87.06 | 0.0049 |  86.89 |    0.6396258966744 |   31.4865883689919 |  0.442022365274712
86.89 |  87.31 | 0.0048 |  86.74 |  0.314176461801723 |   60.0558838893752 |  0.345972028763493
86.74 |  87.07 | 0.0038 |  86.85 |  0.556953610508981 |   38.6967242480077 |  0.709038352162874
86.85 |  88.54 | 0.0194 |  89.05 |   0.71646536311035 |   24.7355217235248 |  0.806474667992637
89.05 |  86.82 | 0.0250 |   87.1 |  0.828893551212771 |   14.6233989516049 |  0.843205122436764
87.1 |  85.63 | 0.0169 |     86 |  0.876681721784925 |   10.2362647858593 |  0.868757340937373
86 |  86.79 | 0.0092 |  87.26 |  0.907607969680494 |   7.59324024469571 |  0.872354617595825
87.26 |  85.35 | 0.0219 |     86 |  0.963046324742614 |   2.52920153372814 |  0.854052902081886
86 |  85.04 | 0.0111 |  85.68 |  0.984857752501153 |  0.658717160003272 |  0.818153667442131
85.68 |  86.65 | 0.0113 |     87 |  0.968673034113725 |    2.3751701572406 |  0.782575372168168
87 |  90.00 | 0.0345 |     90 |  0.910991684444145 |   8.01456054235934 |  0.742602997511869
90 |  90.69 | 0.0076 |  90.61 |  0.897821640533335 |   9.33440206466203 |  0.713122343798605
90.61 |  90.13 | 0.0053 |  90.13 |  0.909958318693295 |   8.11836657246885 |  0.676080976394243
90.13 |  88.54 | 0.0177 |  89.08 |  0.959429536023691 |   3.07240205281187 |  0.608007093767562
89.08 |  89.76 | 0.0076 |  89.81 |   0.91029848144619 |   8.00500306348446 |  0.404654669040077
89.81 |  99.27 | 0.1054 |  98.45 |  0.657207082561843 |   34.5727385355619 |  0.393214748683796
98.45 | 102.53 | 0.0415 | 102.94 |  0.704409588700779 |   30.0196224514047 |  0.502462865561183
102.94 | 103.45 | 0.0049 | 103.99 |  0.703297557503838 |   30.3100075437914 |   0.46367875870034
103.99 | 102.47 | 0.0147 | 103.11 |   0.62837962126522 |   37.6738507164757 |  0.403487822887036
103.11 | 103.10 | 0.0001 |    104 |  0.643348564991283 |   36.1926878205066 |  0.412471976813411
104 | 102.47 | 0.0147 | 103.29 |  0.609715827989882 |   39.4938840550046 |  0.434204693264911
103.29 | 100.19 | 0.0300 | 100.04 |  0.714689142171646 |   28.6965783703781 |  0.579882468389968
100.04 |  96.87 | 0.0317 |  96.89 |  0.891706172449747 |   10.4761802869589 |  0.673676693300631
96.89 |  95.65 | 0.0128 |  96.31 |  0.996480964507578 | -0.324693104249417 |  0.614510491219348
96.31 |  99.98 | 0.0381 |  99.58 |  0.805484441868333 |   19.7717089277751 |  0.512838450280928
99.58 | 101.72 | 0.0215 | 100.77 |  0.580782945386486 |   43.1942330487114 |  0.411626746536197
100.77 | 102.31 | 0.0153 | 101.62 |   0.60960923807431 |   40.3630398853694 |  0.534574960836081
101.62 | 103.28 | 0.0163 | 103.02 |  0.715901485259652 |   29.5241464649391 |  0.578753862361246
103.02 | 104.93 | 0.0185 | 105.03 |  0.755513345311285 |   25.5752266330287 |  0.637919333414959
105.03 | 102.17 | 0.0272 | 102.13 |   0.85397742981656 |   14.9562551932185 |  0.706505426646503
102.13 | 103.39 | 0.0123 | 103.32 |  0.816331012523094 |   19.0456461040167 |  0.678069331200632
103.32 |  97.81 | 0.0533 |    101 |   1.59944623063686 |  -63.7322919498157 |  0.754678672545085
101 | 103.53 | 0.0251 |  103.6 |  0.863779780883968 |   14.0441886225418 |  0.661109144730258
103.6 | 103.94 | 0.0033 | 103.94 |  0.838387460079692 |   16.7970520107185 |  0.577059699997509
103.94 | 105.92 | 0.0191 | 105.75 | -0.133229605047718 |   120.010159783732 |  0.804055392782976
105.75 | 105.93 | 0.0017 | 105.98 | -0.145112549418295 |   121.311379359051 |  0.969009451994014
105.98 | 105.95 | 0.0003 | 105.95 |                  0 |   105.949999994131 |                  1
105.95 | 105.89 | 0.0005 | 105.95 |  -0.13230769230761 |   119.911107692316 |   0.99999999999768
105.95 |  99.90 | 0.0571 | 105.52 |   1.72872340425854 |  -82.5132446808904 |   1.00000000000077
105.52 | 111.29 | 0.0547 | 108.77 |  -1.34285714286032 |   257.357142857695 |   1.00000000000725
108.77 | 119.17 | 0.0956 | 110.65 |   6.08695652160159 |  -554.349999972815 |   1.00000000000081
110.65 | 109.27 | 0.0125 | 109.25 | 0.0707692307696992 |    101.53473846147 |  0.999999999967684
109.25 | 118.89 | 0.0883 | 109.02 |   3.03738317759831 |  -212.244018694157 |   1.00000000001163
(51 rows)  ``````

The prediction results for the stock price of Guizhou Moutai: The prediction deviation is still relatively ideal. (Maybe external factors greatly influence some stocks, while others are relatively small because this article does not introduce the multivariate analysis.)

``````postgres=# select (check_predict(i)).* from generate_series(0,50) t(i);
rv   |   pv   |  dev   |   zv   |      v_slope      |      v_inter      |       v_r2
--------+--------+--------+--------+-------------------+-------------------+-------------------
| 155.68 |        | 154.75 | 0.749327368763041 |  39.7193487454018 | 0.443299946747894
154.75 | 156.33 | 0.0102 | 155.00 | 0.658983381690956 |  54.1888692212598 |  0.31605703462515
155.00 | 158.61 | 0.0233 | 157.81 | 0.505691375378367 |  78.8096738403756 | 0.240429553874766
157.81 | 159.35 | 0.0097 | 159.02 | 0.471362121189687 |  84.3914502631931 |  0.23632158869318
159.02 | 159.89 | 0.0055 | 159.87 | 0.502631474677655 |  79.5350129389031 | 0.262084379639135
159.87 | 160.46 | 0.0037 | 161.00 | 0.486478989379233 |  82.1377272523488 |  0.29236983925612
161.00 | 161.73 | 0.0045 | 162.74 | 0.592859008065614 |  65.2441140525351 |  0.38051931752417
162.74 | 161.22 | 0.0094 | 162.33 | 0.522125410976863 |  76.4612098979163 | 0.450724600316921
162.33 | 161.07 | 0.0078 | 161.50 | 0.670999115633002 |  52.7013374696206 | 0.535249445391989
161.50 | 160.77 | 0.0045 | 160.92 |  0.72782591656929 |  43.6501486130006 | 0.574758659704234
160.92 | 156.66 | 0.0264 | 156.49 | 0.885275616598876 |  18.1277330183775 | 0.655113491671201
156.49 | 157.82 | 0.0085 | 156.70 | 0.757494701869893 |    39.12388483696 | 0.495319263616962
156.70 | 161.18 | 0.0286 | 160.71 | 0.725555251667536 |  44.5773888438119 | 0.584398418586498
160.71 | 160.13 | 0.0036 | 159.25 | 0.718340613774721 |  45.7320036637828 | 0.626975960572309
159.25 | 158.92 | 0.0021 | 158.02 | 0.776715149169011 |  36.1819821709587 | 0.676670415905896
158.02 | 159.06 | 0.0066 | 158.47 | 0.812642712296025 |  30.2825991865648 | 0.679920408166994
158.47 | 160.77 | 0.0145 | 160.50 | 0.824536957368669 |  28.4277502764865 | 0.708045226118134
160.50 | 161.95 | 0.0090 | 161.90 | 0.843561194042496 |  25.3766280000652 | 0.722573245190375
161.90 | 161.51 | 0.0024 | 161.49 | 0.847987413256958 |  24.5663395825434 | 0.721633674185219
161.49 | 163.60 | 0.0131 | 163.87 | 0.870581601947346 |  20.9406294921026 | 0.725206713695765
163.87 | 163.51 | 0.0022 | 163.70 | 0.833749346623708 |  27.0240967784583 | 0.670383837164832
163.70 | 168.62 | 0.0301 | 167.65 |  1.05254359115864 | -7.83507005049674 | 0.735574587910647
167.65 | 165.25 | 0.0143 | 165.70 | 0.883481284376093 |  18.8595907684336 | 0.566222610315986
165.70 | 163.61 | 0.0126 | 164.70 | 0.760665237060595 |  38.3314388491422 | 0.437037290387292
164.70 | 161.46 | 0.0197 | 162.58 |  0.63926420779486 |  57.5312362089702 | 0.386820980329109
162.58 | 158.80 | 0.0232 | 158.81 | 0.717636038072266 |  44.8353807921515 | 0.569066830110592
158.81 | 158.34 | 0.0030 | 158.20 | 0.761022139545313 |  37.9453143278904 | 0.597076723252649
158.20 | 156.01 | 0.0138 | 156.00 | 0.890859569629593 |  17.0349264413836 | 0.660262576219616
156.00 | 155.91 | 0.0006 | 156.00 | 0.928603251444626 |   11.051262047026 | 0.569697453056979
156.00 | 158.28 | 0.0146 | 157.72 | 0.723658951735034 |  44.1435343246399 |  0.44078135697188
157.72 | 159.30 | 0.0100 | 158.50 | 0.593435970103012 |  65.2440679649604 | 0.394699579585899
158.50 | 159.91 | 0.0089 | 159.27 | 0.632807705774723 |  59.1184436985211 | 0.387382904311161
159.27 | 160.63 | 0.0085 | 160.00 | 0.604419927649386 |  63.9233718732132 | 0.330175855885006
160.00 | 162.13 | 0.0133 | 162.00 | 0.479607769753778 |  84.4368575468047 | 0.257868311166715
162.00 | 159.99 | 0.0124 | 158.74 | 0.624745284091551 |  60.8132213491609 | 0.426844197393141
158.74 | 160.57 | 0.0115 | 159.75 |   0.6106793906001 |  63.0161876923878 | 0.337283942825567
159.75 | 161.95 | 0.0138 | 162.00 | 0.453875422780405 |  88.4214116897916 | 0.235385451808691
162.00 | 162.13 | 0.0008 | 162.20 | 0.514218487026421 |  78.7230165782795 | 0.291003288858034
162.20 | 162.09 | 0.0007 | 162.02 | 0.556324729871488 |  71.9532675373276 | 0.326799171637252
162.02 | 162.16 | 0.0009 | 162.04 | 0.579222185293716 |  68.3028288619664 | 0.344557168377077
162.04 | 162.16 | 0.0007 | 161.81 | 0.556599549002475 |  72.0969606318282 | 0.307924224254188
161.81 | 162.66 | 0.0052 | 162.48 | 0.500693143269678 |   81.305630489949 | 0.298678253677398
162.48 | 162.22 | 0.0016 | 161.96 | 0.568957052943135 |  70.0759551450734 | 0.346462148633283
161.96 | 162.89 | 0.0057 | 163.10 |  0.39281014679101 |  98.8215502236369 | 0.340803249529129
163.10 | 164.15 | 0.0065 | 164.80 | 0.640630057919025 |  58.5763291969098 | 0.536313525971144
164.80 | 163.63 | 0.0071 | 164.00 |   0.7153108267714 |  46.3161409479079 | 0.611327694763799
164.00 | 162.73 | 0.0078 | 163.00 | 0.671618352789207 |  53.2545511273858 | 0.679718050633678
163.00 | 160.66 | 0.0144 | 160.17 | 0.646714009825995 |  57.0713176290233 | 0.804063835532055
160.17 | 159.11 | 0.0066 | 158.29 | 0.769358075679891 |  37.3298385738639 | 0.848976826898375
158.29 | 161.29 | 0.0190 | 160.74 | 0.845694258048787 |  25.3560723887127 | 0.887140705169265
160.74 | 165.13 | 0.0273 | 164.30 | 0.928055617122675 |  12.6470087942632 | 0.910309053795291
(51 rows)  ``````
0 0 0
Share on

# digoal

255 posts | 20 followers

# digoal

255 posts | 20 followers

# Related Products

• ## Database Overview

Fully managed and less trouble database services

• ## AnalyticDB for PostgreSQL

An online MPP warehousing service based on the Greenplum Database open source program

• ## CloudQuotation

A quotation service that establishes stable, high-quality connections to exchanges all around the world at ultra-low latency.

• ## Digital Marketing Solution

Transform your business into a customer-centric brand while keeping marketing campaigns cost effective.