Trainer and jockey rolling win percentages

Question

 

Hi all, I have a sql related question that I would really appreciate some help with. Example:

 

SELECT age

, weight_pounds AS 'lbs'

, days_since_ran AS 'd_l_ran'

, position_in_betting AS 'pos_in_bet'

, going

, trainer win % over the last 12 month

, jockey win % over the last 12 month

FROM historic_races

, historic_runners

WHERE race_type = 'Flat'

AND meeting_date >= '2012-01-01'

AND meeting_date <= '2012-04-01'

ORDER BY meeting_date ASC

 

I'm trying to create a select statement that will give me, among other things, a win percentage for the trainer and jockey.

Basically for a given horse in a given race I'd like to be able to see as a percentage how many wins that horses trainer and jockey have had in the X months prior to that event. Like in the example code above. (and a way of dealing with small sample sizes eg 1 win out of 1 race is 100% however that's very misleading, would be really really good)

I'm not sure if this forum is the place to ask but any help with this would be awesomel, I've been messing around with nested select statements all weekend and I'm no closer to a solution.

Thanks in advance.

 

Answer 1

I assume you will be manually inputting both the trainer and the jockey?

It's not clear whether you want the Trainer and Jockey as a combination or if you want Trainer separate from Jockey? 

Without knowing exactly what you're after, I hope this will be of some help, it will give you the win percentage of all trainers between the dates you gave above where they've had more than 100 runners in the same period:

 

SELECT 

    trainer_name,

    sum(win) AS num_wins,

    sum(run) AS num_runs,

    100 / sum(run) * sum(win) AS win_percent

FROM

    (SELECT 

        trainer_name,

            CASE

                WHEN finish_position = 1 THEN 1

                ELSE 0

            END AS win,

            1 run

    FROM

        historic_runners hru

    JOIN historic_races hra ON (hru.race_id = hra.race_id)

    WHERE

        meeting_date >= '2012-01-01'

            AND meeting_date <= '2012-04-01') x

GROUP BY trainer_name

HAVING num_runs > 100

ORDER BY win_percent DESC

Answer 2

Producing a 'live' performance measure for jockeys and trainers  has been a real headache for me.

 

I tried various approaches but they involved fiddly programming and tended to produce output (whether rolling win percentages or something else) that were unhelpfully volatile.

 

I've ended up simply producing a calendar year 'look-up' table of jockey and trainer performance statistics. This seems to work as well as anything else and is quite simple to program. The core code I used the populate the 'jockey_lookup' table is:

 

insert into `smartform`.`jockey_lookup`

select

`run`.`jockey_id` AS `jockey_id`,

year(`rac`.`meeting_date`) AS `reference_year`,

count(`run`.`runner_id`) AS `count_of_runners`,

sum((`run`.`distance_beaten` is null) * (`run`.`finish_position` = 1)) AS `count_of_winners`

from

(`smartform`.`historic_runners` `run`

join `smartform`.`historic_races` `rac`) 

where

`run`.`race_id` = `rac`.`race_id`

and `run`.`jockey_id` is not null

group by `run`.`jockey_id`,year(`rac`.`meeting_date`)

having ((`count_of_winners` >= 5) or (`count_of_runners` >= 50));

 

update `smartform`.`jockey_lookup`

set `reference_year` = `reference_year` + 1;

When analysing a race I can then look up the reference jockey win percentage (calculated as count_of_winners / count_of_runners) using the jockey_id and reference_year as join variables.

I've used the same approach for trainers, and produce separate look-ups for flat and national hunt.

Hope this helps