Splitting a group up into multiple groups using a score....
-
Splitting a group up into multiple groups using a score for how many times an item should appear in a sub group
This is related to betting, and whilst I do alright by myself I want to remove human bias from my main income which is making money from football accumulators.
I have a table which holds ALL game outcomes I wish to use, which I have devised using a tally score system, that uses their last 6 results, current positions in their league, players injured, European games upcoming - resting players- and more. This fills a table which contains all number of game outcomes such as...
Home Team WIN
Away Team WIN
DRAW
Home or Away Team WIN (double chance)
Home Team WIN or Draw (double chance)
Away Team WIN or Draw (double chance)
In this following example are outcomes for 2 games only to keep it simple, however imagine that there would be at least 10 matches in the table (which could be any no of outcomes depending on their current status in the league and performance) - as you can see Man UTD v Leicester is closer in Match Score and odds as it's the 2nd and 3rd teams in the Premiership playing each other. This is why the outcomes are for Man UTD to win, Leicester to WIN, a double chance for Man UTD or Leicester to win or a DRAW.
The other game is top position in Premiership, Man City v Fulham (3rd from bottom), so a wider odds difference and more pro Man City outcomes, Man City win, Man City or Draw double chance, and a Fulham win (as there is always an upset in any accumulator).
It's these upsets I want to spread about as I have noticed lately that my bias for a team such as AJAX top of their league hardly lost at all can affect me by placing it in more accas to WIN their game than I should and in the end it loses - once in a blue moon - but there is always an upset somewhere....
The idea is to spread these outcomes equally out into smaller groups which are decided by Total Stake e.g £10 / individual Acca stake £0.50 == 20 Accumulators/Groups.
I have thought about doing it procedurally and using a random no to decide which of the game outcomes goes in each acca but is there a better SET based approach that could split X game outcomes into a defined no of accumulators (or groups) using the MatchScore value to decide how many times that outcome should be used in each Acca.
For example Man City to WIN or Man City to WIN or DRAW would be spread around a lot more than Fulham to WIN. However Fulham to win WOULD be used at least a couple of times at least (always an upset....)
As for the other game the Match Scores are pretty equal so the outcomes would be spread pretty evenly. The reason the Away Team Score for Leicester is 50 and Home Team Score for Man UTD is 82 is that Leicester have lost 2 games out of their last 6 whereas Man UTD haven't lost a game, only drew once. I don't think the Home/Away team scores are relevant but I put them in just in case they may be of use.
So with the outcomes already decided is there a set based way of using the MatchScore to place each outcome into a smaller group (accumulator bet), so that all outcomes are used even the ones with low MatchScores like Fulham to win.
It can only take one acca to win to pay off the stakes off all the other accas and make profit, I just want to ensure human bias is removed and that all outcomes are spread equally into smaller groups (accas).
Hope that makes sense (football fans - or Soccer if you are American)...
Thanks for your help in advance.
-
How about providing some data in the form of CREATE TABLE / INSERT statements, so that people here have something to work with?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
You want the schema for the table I printed off, and the stored proc code that tally's up the score, unfortunately it doesn't all fit in here or uploadable as it's too big.
I can give you the schema for the table and the params that are passed into the proc that creates those match outcomes though.
USE [Brainiac]
GO
/****** Object: Table [dbo].[FOOTBALL_MATCHES] Script Date: 11/04/2021 19:39:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FOOTBALL_MATCHES](
[MatchID] [int] IDENTITY(1,1) NOT NULL,
[GameID] [int] NOT NULL,
[Match] [nvarchar](200) NOT NULL,
[MatchType] [nvarchar](200) NOT NULL,
[Odds] [money] NOT NULL,
[HomeTeamScore] [int] NOT NULL,
[AwayTeamScore] [int] NOT NULL,
[MatchScore] [int] NOT NULL,
[Stamp] [datetime] NOT NULL,
CONSTRAINT [PK_FOOTBALL_MATCHES] PRIMARY KEY CLUSTERED
(
[MatchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FOOTBALL_MATCHES] ADD CONSTRAINT [DF_FOOTBALL_MATCHES_GameID] DEFAULT ((0)) FOR [GameID]
GO
ALTER TABLE [dbo].[FOOTBALL_MATCHES] ADD CONSTRAINT [DF_FOOTBALL_MATCHES_MatchType] DEFAULT ('') FOR [MatchType]
GO
ALTER TABLE [dbo].[FOOTBALL_MATCHES] ADD CONSTRAINT [DF_FOOTBALL_MATCHES_HomeTeamScore] DEFAULT ((0)) FOR [HomeTeamScore]
GO
ALTER TABLE [dbo].[FOOTBALL_MATCHES] ADD CONSTRAINT [DF_FOOTBALL_MATCHES_AwayTeamScore] DEFAULT ((0)) FOR [AwayTeamScore]
GO
ALTER TABLE [dbo].[FOOTBALL_MATCHES] ADD CONSTRAINT [DF_FOOTBALL_MATCHES_MatchScore] DEFAULT ((0)) FOR [MatchScore]
GO
ALTER TABLE [dbo].[FOOTBALL_MATCHES] ADD CONSTRAINT [DF_FOOTBALL_MATCHES_Stamp] DEFAULT (getdate()) FOR [Stamp]
GOAnd the params that call the stored proc that fills that table, with an example of a game which I have to do a lot of homework first for is..
And now the proc being called to add a game and all the variables used to create the table I showed you a screen shot of.
@TeamA_Home nvarchar(100),-- Name of Home Team, once crowds are allowed back to watch football the bias for home teams will be stronger
@TeamA_Last6Results varchar(6), -- WLWWWD - the last 6 game results for the home team
@TeamA_PosInLeague int, -- 1 for 1st, their position in the league
@TeamA_FormScore int, -- personal judgment for how they have been playing, strong team out, injuries etc, 1 weak - 10 strong if they have been playing to form like Real Madrid, Ajax or Man City a way to "tweak" the score
@TeamB_Away nvarchar(100),-- Name of Away team.
@TeamB_Last6Results varchar(6), -- WWDLWD the Away teams last 6 result W=WON, D=DRAW, L=LOST
@TeamB_PosInLeague int, -- 1 for 1st, 20th for near the bottom of the league, their pos in the league before the match
@TeamB_FormScore int, -- personal judgment for how they have been playing, strong team out, injuries 1 weak - 10 strong
@TeamAWin MONEY, -- price for Team A winning on Betfair
@TeamAWinORDraw MONEY, -- price for Team A Winning or drawing on Betfair
@TeamAorTeamB MONEY, -- double chance each could win on Betfair
@TeamBWinORDraw MONEY, -- price for Team B Winning or drawing on Betfair
@TeamBWin MONEY, -- price for Team B winning on Betfair
@Draw MONEY, -- price for a straight draw between the two teams on Betfair
@LeagueForm INT, -- 1 to 10, 10 league is performing as it should, 1 Man City is top, Sheff UTD is bottom, usual Premiership scenario, if Leicester were top that year they stunned everyone by being promoted and winning the league then it would be 1 as new team to the league doesn't usually win in 1st season
@SameLeague BIT = 1, -- if both teams in same league, as might have EUROPEAN matches where games from different leagues are playing each other set to 0
@TeamALeagueStatus INT = 5, -- if different leagues then we can adjust this to say Premiership is far supior than the Portugese or French league at the moment by giving it a higher no.
@TeamBLeagueStatus INT = 5,-- from 1 to 10 to state 1 = poor league, 10 good league, 5 defaults as standard or dont know.
@TestMODE BIT = 1 -- test mode deletes all from table inserts this one match then shows it at end
--- call the proc with a match between Man City and Fulham
EXEC dbo.[usp_sql_insert_football_acca_teams]'Man City','WLWWWL', 1, 9,
'Fulham', 'LWLLLLL', 18, 3,
1.50,1.20,1.25,3.80,4.20,3.50,7
,1,5,5,1 -- can ignore last 3 (For same league games), last param is test mode that on/off that doesn't fill the table just outputs debug etc.Does that help you out?
You can put as many game outcomes in as you want.
I jut need to be able to split that table of game outcomes into smaller groups/accumulators ensuring that ALL outcomes are used, but the outcomes with the highest match score would appear more often in the groups/accas than those with smaller values.
Thanks for replying.....
-
This is a good start, but there's more that I suggest you do.
- Create INSERT statements for some sample data to populate your Football_Matches table.
- Provide two or three alternative result sets, showing how the data might look after it has been grouped. Base these result sets on the sample data which you have provided.
- Confirm what the parameters would be for the semi-random grouping exercise. Presumably something like 'desired number of groups' and 'desired number of games in each group'
As far as I can see, the proc which you use to populate the table is background information, and not relevant to this part of the problem. Would you agree?
PS I'm a Leeds fan. I assume you didn't have Leeds down to beat Man City in many of your accas?!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Yes the info for the proc is just background data after I have done hours of work, checking leagues, last game results, sores, prices on Betfair for each outcome and using the "variable" options to move the score one way or another.
In Man City's case I would have probably added a minus score because they are playing in the European Cup this week v Dortmund. I didn't see the game and would have expected Leeds to have lost, but then that's why I want to take Human Bias out of the equation, as most people would have expected Man City to have won but I want to spread an "upset" result across the accas/groups as well so that there is more chance of getting winning accas.
By they way did Man City rest players because of this weeks European Championship game? As if I had seen the team sheet before the game then I would also be tweaking the variables to adjust the Team Scores which make up the MatchScores to account for missing key players, which would have provide more double chances, and pro Leeds outcomes.
I haven't had much time to do this, 2 hours it's taken and I have to go to the doctors but hopefully this is enough.
I have put 6 games into the FOOTBALL_MATCHES table with their scores and prices, using real prices from tonights games Everton v Brighton, Southampton v WBA etc.
However in reality there would be far more than 6 games in the table, more like 12-18, as the odds would be too low to make a decent return.
However as there are only 6 matches, I have put the Stake to 50p, and in the winning example it returns £8+
/****** Object: Table [dbo].[FOOTBALL_ACCAS] Script Date: 12/04/2021 13:01:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FOOTBALL_ACCAS](
[AccaID] [int] IDENTITY(1,1) NOT NULL,
[AccaNo] [int] NOT NULL,
[MatchID] [int] NOT NULL,
[Result] [char](1) NOT NULL,
[AccaReturn] [money] NOT NULL,
[Locked] [bit] NOT NULL,
CONSTRAINT [PK_FOOTBALL_ACCAS] PRIMARY KEY CLUSTERED
(
[AccaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FOOTBALL_ACCAS] ADD CONSTRAINT [DF_FOOTBALL_ACCAS_AccaNo] DEFAULT ((1)) FOR [AccaNo]
GO
ALTER TABLE [dbo].[FOOTBALL_ACCAS] ADD CONSTRAINT [DF_FOOTBALL_ACCAS_MatchID] DEFAULT ((0)) FOR [MatchID]
GO
ALTER TABLE [dbo].[FOOTBALL_ACCAS] ADD CONSTRAINT [DF_FOOTBALL_ACCAS_Result] DEFAULT ('L') FOR [Result]
GO
ALTER TABLE [dbo].[FOOTBALL_ACCAS] ADD CONSTRAINT [DF_FOOTBALL_ACCAS_Return] DEFAULT ((0)) FOR [AccaReturn]
GO
ALTER TABLE [dbo].[FOOTBALL_ACCAS] ADD CONSTRAINT [DF_FOOTBALL_ACCAS_Fixed] DEFAULT ((0)) FOR [Locked]
GO
/* create the 6 matches and 21 match outcomes to be spread across mini groups */
INSERT INTO FOOTBALL_MATCHES
(GameID,Match,MatchType,Odds,HomeTeamScore,AwayTeamScore,MatchScore)
VALUES
(1,'Man City v Leeds','Man City Win', 1.40,92,76,810),
(1,'Man City v Leeds','Man City Win or Draw', 1.20,92,76,830),
(1,'Man City v Leeds','Man City Win or Leeds', 1.18,92,76,825),
(1,'Man City v Leeds','Leeds Win', 2.20,92,76,700),
(2,'Man UTD v Leicester','DRAW', 2.00,82,50,614),
(2,'Man UTD v Leicester','Man UTD WIN', 2.50,82,50,640),
(2,'Man UTD v Leicester','Man UTD WIN OR Leicester', 2.00,82,50,645),
(2,'Man UTD v Leicester','Leicester WIN', 2.25,82,50,643),
(3,'Brighton v Everton','Brighton WIN',2.30,60,70,600),
(3,'Brighton v Everton','Brighton WIN or Everton',1.30,60,70,630),
(3,'Brighton v Everton','DRAW',3.30,60,70,620),
(3,'Brighton v Everton','Everton WIN',3.30,60,70,620),
(4,'West Brom v Southampton','West Brom WIN', 3.50,35,42,540),
(4,'West Brom v Southampton','Southampton WIN', 2.20,35,42,570),
(4,'West Brom v Southampton','West Brom or Southampton', 1.30,35,42,590),
(5,'Liverpool v Aston Villa','Liverpool WIN', 1.40,83,68,770),
(5,'Liverpool v Aston Villa','Liverpool WIN or Draw', 1.20,83,68,790),
(5,'Liverpool v Aston Villa','Aston Villa', 1.20,83,68,750),
(6,'Chelesa v Spurs','Chelsea WIN',1.45,75,72,680),
(6,'Chelesa v Spurs','Spurs WIN',1.40,75,72,670),
(6,'Chelesa v Spurs','DRAW',1.30,75,72,660)
/* insert results into accas table */
INSERT INTO FOOTBALL_ACCAS
(AccaNo,MatchID,Result,AccaReturn,Locked)
VALUES
(1,1,'L',0,1),
(1,5,'L',0,1),
(1,9,'L',0,1),
(1,13,'W',0,1),
(1,16,'W',0,1),
(1,19,'L',0,1),
(2,3,'W',8.17,1),
(2,6,'W',8.17,1),
(2,11,'W',8.17,1),
(2,13,'W',8.17,1),
(2,17,'W',8.17,1),
(2,20,'W',8.17,1)
-- calculate profit of winning acca 2, by using a 50p stake across 6 winning outcomes = £8.17
SELECT 0.50 * 1.18 * 2.50 * 3.30 * 1.20 * 1.40Ideally you want to make as much money from as little layout as possible, a min stake on Betfair is 10p, so £2 can make 20 accas.
If you can get the odds ALSO right for each acca which I don't know if you could do in a set based solution as you would want to ensure that the game outcomes you add into each group/acca would always add up to a profit higher than the TOTAL STAKE e.g £6 result > £2 total stake. £4 - £6 is about the average return on an acca from a 10p stake with 8-16 games in it. The max no of games you can have in an acca is 25 by the way.
So ideally you wouldn't stop filling up a sub group/acca until the prices (odds x odds x odds etc) = a ££ return more than the TOTAL STAKE you have used for all possible accas. So if you are using £2 for 20 accas, each acca would stop around double/treble the £2 amount, £4-£6 etc. So the acca groups would keep getting filled up until they would return a profit overral.
Hope this helps, sorry I have to rush to GPs now. Horrible Vaccine side effects = FLU for a week, blood clots in legs. Not nice.
Thanks for help
-
Where are all the SET Based Geniuses, Jeff Moden, I remember was one who said everything could be done in a set based solution.....
I don't know if this really can be done in a set based solution, especially as you need to
-Keep count of how many outcomes go into an acca and ensure the math / potential profit from the odds adds up to above the Total Stake parameter.
-Ensure you don't place the same Match Outcome into an acca multiple times.
-Ensure that all Match Outcomes are used, including the low MatchScore (upset result), outcomes even if they are only used a couple of times.
-Keep an eye on the total number of groups/accas you create so that they don't breach the maximum no of groups/accas e.g £2 = 20 x 10p accas. At this price each acca MUST return over £2 at least, double really to ensure profit to cover your max stake.
I know procedural code in SQL is not supposed to be done but sometimes I cannot think of a simpe SET based solution that can accomplish something that if written in C# or JavaScript would be a loop (from 1 to max accas), and inside another loop to fill each acca, ensuring all the points above are met.
Anyone got the skills to help Jeff, maybe?
-
It's not a good approach imo. It's not necessary or polite to call anyone out. For my part I'm not familiar with the terms and I'm generally uninterested in gambling. Maybe this query could be helpful. It calculates all the possible combinations of outcomes for the 6 games.
with xply_cte as (
select row_number() over (order by (select null)) combo_rn,
g1.MatchType result1, g2.MatchType result2, g3.MatchType result3,
g4.MatchType result4, g5.MatchType result5, g6.MatchType result6
from (select MatchType from dbo.FOOTBALL_MATCHES where GameID=1) g1 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=2) g2 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=3) g3 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=4) g4 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=5) g5 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=6) g6)
select x.combo_rn, unpvt.*
from xply_cte x
cross apply (values (1, x.result1),
(2, x.result2),
(3, x.result3),
(4, x.result4),
(5, x.result5),
(6, x.result6)) unpvt(GameID, result);Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
I was just trying find out if Jeff Moden was still on here, as I have not been on this site for a while and I do remember him always talking about SET BASED option over anything procedural. I wasn't "calling anyone out" so to speak, just that I knew he was the SET based solution specialist when I was working last and used to come on here all the time. I don't know if you can Privately Message people on here anymore, plus I have forgotten the correct spelling of his username.
The problem with this solution, apart from it doesn't use the FOOTBALL_ACCA table schema to hold results, or uses the odds to ensure that a profit is made, is that the result set contains multiple outcomes for the same match in the same acca which is obviously impossible. You cannot have 2 outcomes from the same match in the same acca.
E.G from the test data when running it, in ACCA/Group 2 you have Man UTD to WIN twice and in other accas Leicester to WIN twice. You can have Man UTD to WIN once or any MAN UTD v Leicester outcome in an acca, but only one outcome from the FOOTBALL_MATCHES table could appear in an acca. However it constantly repeats 2 outcomes from the same game Man UTD v Leicester game all throughout the result set e.g Man UTD to win in the same acca twice, or Leicester to WIN twice.
I am not sure why that is happening apart from the fact you are using a Cross Join and there is no way to prevent multiple occurrences of a Match Outcome in the SQL.
In fact as I scroll down Man UTD or Leicester to WIN appears in multiple accas, sometimes even 3 times like in Acca 1705 (where I am assuming you are using combo_rn rather than the provided schema of the Acca table to denote which Acca/Group it is), so in acca 1705 you have...
1706 1 Man City Win or Leeds (same game)
1706 2 Man UTD WIN (same game)
1706 3 Man UTD WIN (same game so 3 outcomes from 1 match in the same acca)
1706 4 West Brom or Southampton
1706 5 Aston Villa
1706 6 DRAW
Obviously this is impossible to have. So it's putting Game Outcomes that cannot be combined together in the same group multiple time due to the CROSS JOIN.
Also a key point is the Total Stake used, and then the Stake per acca. I used a stake of 50p per acca, which was just to inflate the potential return due to the lack of games in the FOOTBAL_MATCHES table. The potential £ return, to a total stake of £10 / £0.50 = 20 accas, just as I said in the example £2/£0.10 = 20 accas, yet you have output 1728 accas which means a total stake of £864 not £10 at 50p per acca.
As the stake defines how many groups/accas there are it needs to stop at that no. Plus each acca need to have a potential winning £ result above the total stake.
I am not sure that a set based approach can handle all those points I mentioned in the previous post because you cannot just CROSS JOIN all the outcomes as only 1 outcome per match can appear in an acca.
However thanks for the code, I will see if I can try and tweak it somehow but I am thinking that ensuring the total ££ return is above the total stake, and that only 1 game outcome per match appears in an acca, and that there are only so many accas defined by the Total Stake / Acca £ amount that the code should stop at, all means that a set based approach might not be workable.
I will try and play with it to see if I can maybe use UDF's to calculate the £ results and a TOP(X) which would be worked out by Total Stake/Acca stake e.g TOP(20) for 20 accas.
Then once you know you only have 20 accas there is the job of ensuring each match outcome appears across all accas, the higher the MatchScore the more often they appear - but still somehow ensuring low MatchScore outcomes still are outputted.
Thanks for attempting the task though it much appreciated and parts of it may be useful to have.
-
My apologies there was a mistake in the code. You're correct there shouldn't be duplicates as you described. In the 'xply_cte' CTE the 4th column contained an incorrect table alias. It's been edited and ought to be good now afaik.
Instead of (correct)
g3.MatchType result3
Prior to editing it was (incorrect)
g2.MatchType result3
Sorry again
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Heh... I normally avoid helping people with anything having to do with gambling. You seem to indicate that you're making money at it. What's my cut going to be if I make your stuff work?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
LOL Depends how much you invest Jeff...... 🙂
-
Hi Ten,
Not really sure what you mean, as column 3 in your example I saw is g3.MatchType result3 e.g
DECLARE @TotalStake MONEY,
@AccaStake MONEY,
@NoOfAccas INT
SELECT @TotaLStake = 10.00, @AccaStake = 0.50
SELECT @NoOfAccas = @TotalStake / @AccaStake
--show how many accas there
SELECT @NoOfAccas
;with xply_cte as (
select top(@NoOfAccas) row_number() over (order by (select null)) combo_rn,
g1.MatchType result1, g2.MatchType result2, g3.MatchType result3,
g4.MatchType result4, g5.MatchType result5, g6.MatchType result6
from (select MatchType from dbo.FOOTBALL_MATCHES where GameID=1) g1 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=2) g2 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=3) g3 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=4) g4 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=5) g5 cross join
(select MatchType from dbo.FOOTBALL_MATCHES where GameID=6) g6)
select x.combo_rn, unpvt.*
from xply_cte x
cross apply (values (1, x.result1),
(2, x.result2),
(3, x.result3),
(4, x.result4),
(5, x.result5),
(6, x.result6)) unpvt(GameID, result);Adding the TOP() fixes the problem of stopping at the total no of groups allowed e.g Total Stake [money] £10 / Acca Stake [money] £0.50 == No of Accas to fill (int)2 0.
However this does nothing to ensure each Acca's potential return amount which in the table schema I supplied is ALWAYS above the Total Stake of £20 (by adding up the stake x each games in the accas odds for the outcome chosen
It also doesn't ensure that the lower MatchScore outcomes like Aston Villa to WIN DO appear in these top r0 at least a couple of times.
As we have to stop at 40 groups, some sort of weighting needs to ensure that the top MatchScores appear MORE than those lower ones but the lower ones still need to appear. It's like a CROSS JOIN that somehow ensures Top Match Outcomes appear mostly but EVERY outcomes is somehow used, despite the TOP(20), stopping the CROSS JOIN of all possible outputs.
Maybe having multiple CTES might work, the main one with a TOP < Total Accas that will try to CROSS JOIN the most highest Match Scores it can, then a TOP(3-5) that only looks at the lower MatchScores for it's game outcomes.
Thanks for your help though.
-
Thank you for your qualified thanks, I think. You've been on this site for a long time so ok maybe something like this is helpful.
declare
@totalstake int=1000,
@accastake int=50;
declare
@noofaccas int=@totalstake/@accastake;
with xply_cte as (
select row_number() over (order by (select null)) combo_rn,
g1.MatchType result1, g1.Odds odds1, g1.MatchScore match_score1,
g2.MatchType result2, g2.Odds odds2, g2.MatchScore match_score2,
g3.MatchType result3, g3.Odds odds3, g3.MatchScore match_score3,
g4.MatchType result4, g4.Odds odds4, g4.MatchScore match_score4,
g5.MatchType result5, g5.Odds odds5, g5.MatchScore match_score5,
g6.MatchType result6, g6.Odds odds6, g6.MatchScore match_score6
from (select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=1) g1 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=2) g2 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=3) g3 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=4) g4 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=5) g5 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=6) g6)
select top(@noofaccas) x.combo_rn,
sum(unpvt.odds) sum_odds, sum(unpvt.match_score) sum_match_score
from xply_cte x
cross apply (values (1, x.result1, x.odds1, x.match_score1),
(2, x.result2, x.odds2, x.match_score2),
(3, x.result3, x.odds3, x.match_score3),
(4, x.result4, x.odds4, x.match_score4),
(5, x.result5, x.odds5, x.match_score5),
(6, x.result6, x.odds6, x.match_score6))
unpvt(GameID, result, odds, match_score)
group by x.combo_rn
order by sum(unpvt.odds) desc;combo_rnsum_oddssum_match_score
5814.353950
6214.353950
25014.303940
25414.303940
44214.203930
44614.203930
63414.153970
63814.153970
121014.153930
121414.153930
140214.103920
140614.103920
82614.103960
83014.103960
6414.103953
6014.103953
25614.053943
25214.053943
101814.003950
102214.003950Fwiw, it's a good idea to avoid the MONEY data type imo. INT is appropriate here imo
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
So you think MONEY is a bad column for storing MONETARY values and that an INT which stores PENNIES (CENTS in US), is better. Is there a reason for that?
Also, yes I was thanking you, I have been on this site a while but the last 5 years since I left my last job due to disabilities has been a pain in the .... and I make money from betting.
I just wanted to have some procs that can help me as I do OK on my own, but taking the human element out, as that poster Phil Parkin said. Man City were short priced favs to win that game v Leeds, but they had a more important game mid week for the EUROPEAN CHAMPIONSHIP, so I can imagine they rested some players, maybe even sending out their under 23 side, I don't know as I didn't see the game but human reasoning would have seen the price and probably added Man City to WIN into a lot of accas (as they are top of table), without there being at least a few Man City or Leeds, or Leeds to WIN, match outcomes spread about.
I've been away so much I didn't even realise you can do C# / JS type variable declarations now e.g:
declare @totalstake int=1000,
@accastake int=50;
declare@noofaccas int=@totalstake/@accastake;That is good to know. I never new TSQL had progressed so much,
What I want to know is how can I put these results into a TEMP table and JOIN it to FOOTBALL_MATCHES to output the actual Game Outcome .e.g MAN CITY TO WIN or MAN CITY OR LEEDS TO WIN.
I've played about with the SQL a bit but cannot find a way to use this new output to get the MatchType out so I can join onto that table to fill out the FOOTBALL_ACCA table and see the BETS I am placing in each acca. I am sure it is easy but this in new TSQL for me since I've stopped working for "the man", and started Betfair TRADING (GOD I Keep falling asleep at this part and waking up to see a long list of one letter LOL)
But if you could show me how do a join from a temp table to the FOOTBALL_MATCHES table so I an see which Match Outcome occur in each acca.
And I really do appreciate your help by the way! Thanks.
Something like this so I can go off and loop through the table to place each bet in turn with my Betting BOT.
declare @totalstake int=1000,
@accastake int=50;
declare@noofaccas int=@totalstake/@accastake;
DECLARE @TEMP TABLE(combo_rn INT,AccaOdds MONEY, MATCHSCORE int)
;with xply_cte as (
select row_number() over (order by (select null)) combo_rn,
g1.MatchType result1, g1.Odds odds1, g1.MatchScore match_score1,
g2.MatchType result2, g2.Odds odds2, g2.MatchScore match_score2,
g3.MatchType result3, g3.Odds odds3, g3.MatchScore match_score3,
g4.MatchType result4, g4.Odds odds4, g4.MatchScore match_score4,
g5.MatchType result5, g5.Odds odds5, g5.MatchScore match_score5,
g6.MatchType result6, g6.Odds odds6, g6.MatchScore match_score6
from (select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=1) g1 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=2) g2 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=3) g3 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=4) g4 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=5) g5 cross join
(select MatchType, Odds, MatchScore
from dbo.FOOTBALL_MATCHES where GameID=6) g6)
INSERT INTO @TEMP(combo_rn, AccaOdds, MatchScore)
select top(@noofaccas) x.combo_rn,
sum(unpvt.odds) sum_odds, sum(unpvt.match_score) sum_match_score
from xply_cte x
cross apply (values (1,x.result1, x.odds1, x.match_score1),
(2, x.result2, x.odds2, x.match_score2),
(3, x.result3, x.odds3, x.match_score3),
(4, x.result4, x.odds4, x.match_score4),
(5, x.result5, x.odds5, x.match_score5),
(6, x.result6, x.odds6, x.match_score6))
unpvt(GameID, result, odds, match_score)
group by x.combo_rn
order by sum(unpvt.odds) desc;
-- want the accasa and games in r
SELECT *
FROM@TEMP as t
JOINFOOTBALL_MATCHES as f
ONt.combo_rn = ???LOL, I think I've missed all the football that was on tonight now! I swear I came out here to write this response just before 8PM and the football games were all at 8PM so I wanted to watch them.
Thanks for all your help by the way.
- Rob Reid-246754 wrote:
So you think MONEY is a bad column for storing MONETARY values and that an INT which stores PENNIES (CENTS in US), is better. Is there a reason for that?
MONEY is a nondeterministic data type so it's subject to rounding errors when used used in calculations. Google it. Pence or pennies (or potatoes) are the easiest way. In large ecommerce API's like Stripe money is expressed as integers. For what I do to convert it (to DECIMAL or another deterministic data type) would be extra work for no benefit.
Rob Reid-246754 wrote:Also, yes I was thanking you, I have been on this site a while but the last 5 years since I left my last job due to disabilities has been a pain in the .... and I make money from betting.
I just wanted to have some procs that can help me as I do OK on my own, but taking the human element out, as that poster Phil Parkin said. Man City were short priced favs to win that game v Leeds, but they had a more important game mid week for the EUROPEAN CHAMPIONSHIP, so I can imagine they rested some players, maybe even sending out their under 23 side, I don't know as I didn't see the game but human reasoning would have seen the price and probably added Man City to WIN into a lot of accas (as they are top of table), without there being at least a few Man City or Leeds, or Leeds to WIN, match outcomes spread about.
I've been away so much I didn't even realise you can do C# / JS type variable declarations now e.g:
declare @totalstake int=1000,
@accastake int=50;
declare@noofaccas int=@totalstake/@accastake;That is good to know. I never new TSQL had progressed so much,
What I want to know is how can I put these results into a TEMP table and JOIN it to FOOTBALL_MATCHES to output the actual Game Outcome .e.g MAN CITY TO WIN or MAN CITY OR LEEDS TO WIN.
I've played about with the SQL a bit but cannot find a way to use this new output to get the MatchType out so I can join onto that table to fill out the FOOTBALL_ACCA table and see the BETS I am placing in each acca. I am sure it is easy but this in new TSQL for me since I've stopped working for "the man", and started Betfair TRADING (GOD I Keep falling asleep at this part and waking up to see a long list of one letter LOL)
But if you could show me how do a join from a temp table to the FOOTBALL_MATCHES table so I an see which Match Outcome occur in each acca.
Something like this so I can go off and loop through the table to place each bet in turn with my Betting BOT.
LOL, I think I've missed all the football that was on tonight now! I swear I came out here to write this response just before 8PM and the football games were all at 8PM so I wanted to watch them.
You've got a lot going on here. The original title of this thread is "splitting a group into..." which is a narrow question. Has it been answered yet? If you're making money then why not spend a little and get a professional solution? Tho it's not so easy to just spend money and always get a good result. Cutting Jeff in with a thick slice would be a very fortunate move! Good luck with your betting bot project. If you create a project thread on SSC and add to it incrementally (like you've been doing here which has worked so far) it would be more interesting. You seem to have had some ASP.NET experience so why not get a Betfair API Key and do it the right way? It seems likely it would be easier that way to do what you're trying to do. For now I'm just trying to get you unstuck with the minimum code possible. Here's some more new-ish SQL. It adds a string aggregation into the SELECT list of the CTE and then splits it apart in the accessor. It works with the sample data.
declare
@totalstake int=1000,
@accastake int=50;
declare
@noofaccas int=@totalstake/@accastake;
with
xply_cte as (
select row_number() over (order by (select null)) combo_rn,
g1.MatchType result1, g1.Odds odds1, g1.MatchScore match_score1,
g2.MatchType result2, g2.Odds odds2, g2.MatchScore match_score2,
g3.MatchType result3, g3.Odds odds3, g3.MatchScore match_score3,
g4.MatchType result4, g4.Odds odds4, g4.MatchScore match_score4,
g5.MatchType result5, g5.Odds odds5, g5.MatchScore match_score5,
g6.MatchType result6, g6.Odds odds6, g6.MatchScore match_score6
from (select MatchType, Odds, MatchScore
from #football_matches where GameID=1) g1 cross join
(select MatchType, Odds, MatchScore
from #football_matches where GameID=2) g2 cross join
(select MatchType, Odds, MatchScore
from #football_matches where GameID=3) g3 cross join
(select MatchType, Odds, MatchScore
from #football_matches where GameID=4) g4 cross join
(select MatchType, Odds, MatchScore
from #football_matches where GameID=5) g5 cross join
(select MatchType, Odds, MatchScore
from #football_matches where GameID=6) g6),
top_cte as (
select top(@noofaccas) with ties x.combo_rn,
sum(unpvt.odds) sum_odds, sum(unpvt.match_score) sum_match_score,
string_agg(concat_ws('|', unpvt.GameID, unpvt.result), ',') results
from xply_cte x
cross apply (values (1, x.result1, x.odds1, x.match_score1),
(2, x.result2, x.odds2, x.match_score2),
(3, x.result3, x.odds3, x.match_score3),
(4, x.result4, x.odds4, x.match_score4),
(5, x.result5, x.odds5, x.match_score5),
(6, x.result6, x.odds6, x.match_score6))
unpvt(GameID, result, odds, match_score)
group by x.combo_rn
order by sum(unpvt.odds) desc)
select t.combo_rn, t.sum_odds, t.sum_match_score,
left(sc.[value], cx.ploc-1) GameID,
substring(sc.[value], cx.ploc+1, len(sc.[value])) MatchType
from top_cte t
cross apply string_split(t.results, ',') sc
cross apply (values (charindex('|', sc.[value]))) cx(ploc)
order by combo_rn, left(sc.[value], cx.ploc-1);combo_rnsum_oddssum_match_scoreGameIDMatchType
5814.3539501Leeds Win
5814.3539502Man UTD WIN
5814.3539503DRAW
5814.3539504West Brom WIN
5814.3539505Liverpool WIN
5814.3539506Chelsea WIN
6014.1039531Leeds Win
6014.1039532Leicester WIN
6014.1039533DRAW
...Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply