digoal
2015-03-05
PostgreSQL , 线性回归 , 数据预测 , 股价预测
请先参考如下文章，了解线性回归的原理，以及PostgreSQL有哪些函数可以支撑线性回归分析。
《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》
《用PostgreSQL了解一些统计学术语以及计算方法和表示方法 - 1》
《在PostgreSQL中用线性回归分析(linear regression) - 实现数据预测》
以贵州茅台的历史数据为例, 校验预测误差如下 :
未来1天的最准
未来第二天的预测开始误差开始放大, 但是放大还不明显..
未来第三天,第四天的就有比较大的误差率.
将数据导入源表
插入顺序从旧到新排列. 不要搞反了, 例如
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);
数据转换为
1, 13.49
2, 13.09
,......
创建样本表
create table tmp (
id int,
x numeric, -- 自变量
y numeric -- 因变量
);
自变量和因变量的选择很有讲究.
例如你可以选择昨天的收盘价和今天的开盘价作为自变量和因变量.
当然也可以做多元的分析, 例如昨天的收盘价, 交易量作为自变量, 今天的开盘价作为因变量.
等等......
昨天预测今天的样本数据
truncate tmp;
insert into tmp
select id,
lag(x,1) over(order by id),
x from orig;
生成最近2天预测未来1天的样本数据
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;
生成最近3天预测未来2天的样本数据
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;
生成最近4天预测未来3天的样本数据
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;
生成最近5天预测未来4天的样本数据
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;
生成预测数据的函数如下
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 as
(
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$;
生成最近100天的历史产生的预测数据和校验数据.
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);
预测未来4天的数据的方法.
未来第1天 p1
未来第2天 p2-p1
未来第3天 p3-p2
未来第4天 p4-p3
验证方法 :
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;
验证数据 :
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)
希望可以有更多的行业数据拿来验证一下这种预测方法的可行性.
例如餐饮, 零售行业的销售数据, 商场的人流量数据, 车流量数据, 火车站, 汽车站的人流量数据等等.
农副业的生产和销售数据.
《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》
《用PostgreSQL了解一些统计学术语以及计算方法和表示方法 - 1》
《在PostgreSQL中用线性回归分析(linear regression) - 实现数据预测》
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
