gistfile1.sql

WITH t1 as
(
SELECT distinct
  hk_odds.racenumber as thisracenumber, 
  hk_odds.horsenumber as thishorsenumber,
    (select winodds from hk_odds o2 where o2.racenumber =  hk_odds.racenumber and o2.horsenumber = hk_odds.horsenumber order by updatedate asc LIMIT 1) as openingprice,
   (select updatedate from hk_odds o2 where o2.racenumber =  hk_odds.racenumber and o2.horsenumber = hk_odds.horsenumber order by updatedate desc LIMIT 1) as currenttime,
  (select winodds from hk_odds o2 where o2.racenumber =  hk_odds.racenumber and o2.horsenumber = hk_odds.horsenumber order by updatedate desc LIMIT 1) as currentodds,
  (select winodds from hk_odds o2 where o2.racenumber =  hk_odds.racenumber and o2.horsenumber = hk_odds.horsenumber order by updatedate desc LIMIT 1 OFFSET 1) as L1odds,
  (select winodds from hk_odds o2 where o2.racenumber =  hk_odds.racenumber and o2.horsenumber = hk_odds.horsenumber order by updatedate desc LIMIT 1 OFFSET 2) as L2odds,
 (select winodds from hk_odds o2 where o2.racenumber =  hk_odds.racenumber and o2.horsenumber = hk_odds.horsenumber order by updatedate desc LIMIT 1 OFFSET 3) as L3odds,
  MAX(hk_odds.winodds) OVER (PARTITION BY racenumber, horsenumber) as maxwinodds, 
  MIN(hk_odds.winodds) OVER (PARTITION BY racenumber, horsenumber) as minwinodds, 
  round(AVG(hk_odds.winodds) OVER (PARTITION BY racenumber, horsenumber),2) as avgwinodds,
  hk_odds."isWinFav" 
FROM 
  public.hk_odds
ORDER BY
  hk_odds.racenumber ASC, 
  avgwinodds ASC
), T2 as (
select 
currenttime,
thisracenumber,
thishorsenumber,
openingprice,
round( ((openingprice-currentodds)/openingprice)*-100,2) as pcdiffNowOP,
currentodds,
L1odds,
round( ((L1odds-currentodds)/L1odds)*-100,2) as pcdiffNowL1,
L2odds,
round( ((L2odds-L1odds)/L2odds)*-100,2) as pcdiffL1L2,
L3odds,
round( ((L3odds-L2odds)/L3odds)*-100,2) as pcdiffL2L3,
MAX(currentodds) OVER (PARTITION BY thisracenumber) as currentwinoddsracemax,
MIN(currentodds) OVER (PARTITION BY thisracenumber) as currentwinoddsracemin,
RANK() OVER (PARTITION BY thisracenumber ORDER BY currentodds asc) as currentwinoddsrank,
maxwinodds,
minwinodds,
avgwinodds,
case  
WHEN currentodds < 5 then 1 
WHEN 5 <= currentodds and currentodds < 10 then 2
WHEN 10 <= currentodds and currentodds < 20 then 3
WHEN 20 <= currentodds and currentodds < 30 then 4
WHEN currentodds >= 30 and currentodds < 50 then 5 
WHEN currentodds >= 50 and currentodds < 100 then 6
ELSE 7
END as bettingline 
from t1
where thisracenumber =10
order by currentwinoddsrank ASC
)
insert into HKOddsStats(racedate, racenumber, horsenumber,updatedate,opwin,pcdiffnowop,currentodds,bettingline, pcdiffnowL1,l1odds,opracemax, opracemin,currentwinoddsrank,maxwinodds,minwinodds,avgwinodds)
select thisracenumber,thishorsenumber,currenttime,openingprice,pcdiffNowOP,currentodds,bettingline,pcdiffNowL1, L1odds,maxwinodds,minwinodds,currentwinoddsrank,maxwinodds,minwinodds,avgwinodds
from t2