線形回帰

PostgreSQLのpsqlで書いてみた

CREATE FUNCTION FncRA(wPair text, wLeng int) RETURNS numeric as '
  DECLARE

 rs record;
 wSQL text;

 result numeric;

 wNUM numeric;
 wAVG numeric;


 wS1  numeric;
 wS2  numeric;
 wRA  numeric;

  BEGIN

 wSQL:=''SELECT row_number() over() as rn, bid FROM manepa_rate where pair='''''' || wPair || '''''' order by timeline desc limit '' || FncRA.wLeng || '';'';
 
 wNUM:=(wLeng/2) + 0.5 + ( mod(wLeng,2)::numeric / 2);
 select into wAVG avg(bid) from ( select bid FROM manepa_rate where pair='''' || FncRA.wPair || '''' order by timeline desc limit FncRA.wLeng ) as w;

 wS1:=0;
 wS2:=0;
 
 for rs in EXECUTE wSQL loop
  
  wS1:=wS1+((rs.bid - wAVG)^2)::numeric;
  wS2:=wS2+((rs.rn - wNUM)^2)::numeric;

 end loop;
 
 wRA:=wAVG - (wNUM * (wS1 / wS2)::numeric );

 RETURN wRA;

  END;

' LANGUAGE 'plpgsql';