June 2, 2011 at 5:47 am
I am trying to come up with a set based solution to the following problem.
I have a horse racing BOT that automatically selects and places bets on Betfair.
The horse selection side of things is all worked out apart from one problem I need to try and resolve which is to evenly spread the types of bet across my "multiples" selections.
Single bets are fine but my aim is to use the ticking over of singles to fund multiples (as that is where the money is)
At the moment my system ranks, orders and sets up the bets fine but I need to find a way of evenly spreading out my selections across a number of bets (if possible) without going down the CROSS JOIN route that would just give me thousands of bets I cannot fund.
So using the following parameters an example would be:
Parameters:
Max No of bets to return e.g the number of total bets I am allowed to place e.g 5
Min No of selections per bet e.g 2 (a double)
Max No of selections per bet e.g 4 (an accumulator)
My system would then take the 5 horses it thinks has the most chance of succeeding (or any sub-selection I want to use) e.g
Horse NameRankingRaceTime
Majestic Dream15514:00
Night Lily15014:30
Quazy de Joie15015:00
Teenage Kicks14515:30
Piment D'Estruval13016:00
and return me a "mixture" of bets that combine my horses together in various selections without lumping the same horse(s) in each bet and mixing the selections up as much as possible.
Bet 1 - 4 way accumulator of the top 4 ranked
Majestic Dream15514:00
Night Lily15014:30
Quazy de Joie15015:00
Teenage Kicks14515:30
Bet 2 - A double
Majestic Dream15514:00
Night Lily15014:30
Bet 3 A double
Quazy de Joie15015:00
Piment D'Estruval13016:00
Bet 4 A double
Majestic Dream15514:00
Teenage Kicks14515:30
Bet 5 A Triple
Night Lily15014:30
Quazy de Joie15015:00
Piment D'Estruval13016:00
Obviously the ordering is important in that the same 3 horses can only ever appear in one order - the order that they race in. So any randomising needs to account for that.
I might run this procedure a number of times with a different selection of horses but my main aim is to prevent the same horse (e.g Majestic Dream with the top ranking) from appearing in all the bets and to get a mixture of Doubles (2 selections), Triples, Quads and so on with as much even spread of the selections as is possible with the parameter for max no of bets to return.
This can be broken down into steps if it makes things easier.
I have a solution but it's long winded, not set based and is prone to lumping up the best ranked horses together whereas I really need an even spread.
I am thinking about some kind of select TOP X from cross join with a randomiser (order by newid) and then a cleanup at the end to remove dupes e.g the same horses in different time orders.
Any help would be much appreciated.
June 2, 2011 at 7:24 am
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2011 at 7:44 am
Here's a skeleton that should get you started. You should be able to see how the rules work in it and customize them as you see fit.
/*
Horse Name Ranking RaceTime
Majestic Dream 155 14:00
Night Lily 150 14:30
Quazy de Joie 150 15:00
Teenage Kicks 145 15:30
Piment D'Estruval 130 16:00
*/
IF OBJECT_ID(N'tempdb..#Horses_Races') IS NOT NULL
DROP TABLE #Horses_Races ;
CREATE TABLE #Horses_Races
(ID INT IDENTITY
PRIMARY KEY,
[Name] VARCHAR(25),
Ranking INT,
RaceTime TIME) ;
INSERT INTO #Horses_Races
(Name, Ranking, RaceTime)
VALUES ('Majestic Dream', 155, '14:00'),
('Night Lilly', 150, '14:30'),
('Quazy de Joie', 150, '15:00'),
('Teenage Kicks', 145, '15:30'),
('Piment D''Estruval', 130, '16:00'),
('Additional', 125, '16:30') ;
SELECT HR0.*,
CASE WHEN ID IN (SELECT TOP 4
ID
FROM #Horses_Races AS Rankings
ORDER BY Ranking DESC) THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS Top4,
CASE WHEN ID IN (Double1ID1, Double1ID2) THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS Double1,
CASE WHEN ID IN (Double2ID1, Double2ID2) THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS Double2
FROM #Horses_Races AS HR0
CROSS APPLY (SELECT TOP 1
HR1.ID AS Double1ID1,
HR2.ID AS Double1ID2
FROM #Horses_Races AS HR1
CROSS JOIN #Horses_Races AS HR2
WHERE HR1.RaceTime <= HR2.RaceTime
AND HR1.ID != HR2.ID
ORDER BY NEWID()) AS Double1
CROSS APPLY (SELECT TOP 1
HR1.ID AS Double2ID1,
HR2.ID AS Double2ID2
FROM #Horses_Races AS HR1
CROSS JOIN #Horses_Races AS HR2
WHERE HR1.RaceTime <= HR2.RaceTime
AND HR1.ID != HR2.ID
AND HR1.ID != Double1ID1
AND HR2.ID != Double1ID1
AND HR1.ID != Double1ID2
AND HR2.ID != Double1ID2
ORDER BY NEWID()) AS Double2 ;
P.S.: I routinely see horse racing as a homework assignment for database work. I don't usually help out with homework, but this one was an interesting enough problem to intrigue me. If it were a real database, I'd expect normalization, like a table of horses, a table of races, including locations (not just times), and a join table to indicate which horses were in which. Also, if actual money were involved, you'd need the rankings to be based on a more complex algorithm than just a fixed number in a table, because of factors like jockey weigh-in, how recently the horse has raced (most horses these days don't have the stamina to race more than a couple of times per year or whatever), and so on. Of course, if real money were involved, horse-racing would be out of the question. There are better ways to gamble.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 2, 2011 at 7:51 am
Thanks G!
I like it.
Don't worry there are lots of tables full of stats including jockey, trainer, course, draw bias and other form stats that are involved in calculating the ranking score.
Thanks for your help
June 2, 2011 at 7:55 am
Glad I could help.
Let me know if any of the Cross Apply stuff doesn't make sense.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply