通过shell脚本分析足彩(r2笔记74天)

sqlplus -s n1/n1 <<EOF
set serveroutput on
set linesize  200
set pages 50
set feedback on
col w format a10
col t format  a10
col l format a10
col rw format a10
col rt format a10
col rl  format a10
col diff_sum format 9.99 
prompt --- win
prompt -- big  win
select zu,ke,r,w||'('||(w-$1)||')' w,t||'('||(t-$2)||')'  t,l||'('||(l-$3)||')' l,rw||'('||(rw-$4)||')' rw,rt||'('||(rt-$5)||')'  rt,rl||'('||(rl-$6)||')'  rl,abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6) diff_sum from  data where w<=$1+0.2 and w>=$1-0.2  and l<=$3+1 and l>=$3-0.2 and   rw<=$4+0.2 and rw>=$4-0.2 and  rl<=$6+1 and rl>=$6-0.2  and  r<=-1  and zu>ke+1 ;
prompt -- little win
select  zu,ke,r,w||'('||(w-$1)||')' w,t||'('||(t-$2)||')' t,l||'('||(l-$3)||')'  l,rw||'('||(rw-$4)||')' rw,rt||'('||(rt-$5)||')' rt,rl||'('||(rl-$6)||')'  rl,
abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6) diff_sum  from data where w<=$1+0.2 and w>=$1-0.2  and l<=$3+1 and l>=$3-0.2  and  rw<=$4+0.2 and rw>=$4-0.2 and  rl<=$6+1 and rl>=$6-0.2  and  r<=-1  and zu=ke+1;
prompt .
prompt ---tie
select  zu,ke,r,w||'('||(w-$1)||')' w,t||'('||(t-$2)||')' t,l||'('||(l-$3)||')'  l,rw||'('||(rw-$4)||')' rw,rt||'('||(rt-$5)||')' rt,rl||'('||(rl-$6)||')' rl  ,abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6) diff_sum from  data where w<=$1+0.2 and w>=$1-0.2  and  l<=$3+1 and l>=$3-0.2 and   rw<=$4+0.2 and rw>=$4-0.2  and rl<=$6+1 and rl>=$6-0.2  and r<=-1  and zu=ke;
prompt .
prompt --- big lose
select  zu,ke,r,w||'('||(w-$1)||')' w,t||'('||(t-$2)||')' t,l||'('||(l-$3)||')'  l,rw||'('||(rw-$4)||')' rw,rt||'('||(rt-$5)||')' rt,rl||'('||(rl-$6)||')' rl   ,abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6) diff_sum from  data where w<=$1+0.2 and w>=$1-0.2  and l<=$3+1 and l>=$3-0.2 and   rw<=$4+0.2 and rw>=$4-0.2  and rl<=$6+1 and rl>=$6-0.2  and r<=-1  and ke>zu+1;
prompt --- little lose
select zu,ke,r,w||'('||(w-$1)||')' w,t||'('||(t-$2)||')'  t,l||'('||(l-$3)||')' l,rw||'('||(rw-$4)||')' rw,rt||'('||(rt-$5)||')'  rt,rl||'('||(rl-$6)||')' rl  ,abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6) diff_sum from  data where w<=$1+0.2 and w>=$1-0.2  and l<=$3+1 and l>=$3-0.2 and   rw<=$4+0.2 and rw>=$4-0.2 and rl<=$6+1 and rl>=$6-0.2  and r<=-1  and ke=zu+1;
set feedback off
declare
big_win number;
little_win number;
tie  number;
big_lose number;
little_lose number;
total_cnt  number;
begin
select count(*) cnt into big_win from data where  w<=$1+0.2 and w>=$1-0.2  and l<=$3+1 and l>=$3-0.2 and   rw<=$4+0.2 and rw>=$4-0.2  and rl<=$6+1 and rl>=$6-0.2  and  r<=-1  and zu>ke+1 and  (abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6))<=1;
select  count(*) cnt into little_win  from data where w<=$1+0.2 and w>=$1-0.2  and  l<=$3+1 and l>=$3-0.2 and  rw<=$4+0.2 and rw>=$4-0.2   and
 rl<=$6+1 and rl>=$6-0.2  and r<=-1  and zu=ke+1 and  (abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6))<=1;
select  count(*) cnt into tie from data where w<=$1+0.2 and w>=$1-0.2 and  l<=$3+1 and l>=$3-0.2 and  rw<=$4+0.2 and rw>=$4-0.2   and
 rl<=$6+1 and rl>=$6-0.2  and r<=-1 and zu=ke and  (abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6))<=1;
select  count(*) cnt into big_lose from data where w<=$1+0.2 and w>=$1-0.2  and   l<=$3+1 and l>=$3-0.2 and  rw<=$4+0.2 and rw>=$4-0.2   and
 rl<=$6+1 and rl>=$6-0.2  and r<=-1 and ke>zu+1 and  (abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6))<=1;
select count(*) cnt into little_lose from data where w<=$1+0.2 and  w>=$1-0.2 and  l<=$3+1 and l>=$3-0.2 and  rw<=$4+0.2 and  rw>=$4-0.2  and
 rl<=$6+1 and rl>=$6-0.2  and r<=-1 and ke=zu+1  and (abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6))<=1;
select count(*) cnt into total_cnt from data where w<=$1+0.2 and  w>=$1-0.2 and  l<=$3+1 and l>=$3-0.2 and  rw<=$4+0.2 and  rw>=$4-0.2  and rl<=$6+1 and rl>=$6-0.2  and r<=-1  and  (abs(w-$1)+abs(t-$2)+abs(l-$3)+abs(rw-$4)+abs(rt-$5)+abs(rl-$6))<=1;
dbms_output.put_line('big  win     :'||big_win||' of total:'||total_cnt||'  percentage:'||round(big_win/total_cnt,2)*100||'%'||'  ratio:'||to_number($1+$4));
dbms_output.put_line('little win   :'||little_win||' of total:'||total_cnt||'  percentage:'||round(little_win/total_cnt,2)*100||'%'||'  ratio:'||to_number($1+$5));
dbms_output.put_line('tie         :'||tie||' of  total:'||total_cnt||' percentage:'||round(tie/total_cnt,2)*100||'%'||'  ratio:'||to_number($2+$6));
dbms_output.put_line('big lose win:'||big_lose||'  of total:'||total_cnt||' percentage:'||round(big_lose/total_cnt,2)*100||'%'||'  ratio:'||to_number($3+$6));
dbms_output.put_line('little lose  :'||little_lose||' of total:'||total_cnt||'  percentage:'||round(little_lose/total_cnt,2)*100||'%'||'  ratio:'||to_number($3+$6));
end;
/
EOF


来简单的做两个测试。如果一场比赛赔率如下，那么有50%的可能性为胜，17%的概率为平，33%的概率为负。：）
1.91  3.5 3.2 3.85 3.65 1.68
.
big win     :8 of total:30 percentage:27%  ratio:5.76
little win  :7 of total:30 percentage:23%  ratio:5.56
tie         :5 of total:30 percentage:17% ratio:5.18
big lose  win:3 of total:30 percentage:10% ratio:4.88
little lose :7 of total:30  percentage:23% ratio:4.88
--END

再来一场比赛
2.35 3.45 2.45 5.1 4.1  1.45
.
big win     :3 of total:19 percentage:16% ratio:7.45
little win   :3 of total:19 percentage:16% ratio:6.45
tie         :5 of total:19  percentage:26% ratio:4.9
big lose win:2 of total:19 percentage:11%  ratio:3.9
little lose :6 of total:19 percentage:32% ratio:3.9
--END