1

Topic: Grouping of combinations

Good afternoon, All!
I saw a search engine of bookmaker forks.
Faced such interesting task which it is possible means SQL and not to solve.
There is a table of coefficients from various bookmaker offices.
Looks approximately so:
Market type (for example - 12 - i.e. a possible outcome of a match - only a victory of one of players (for example - tennis). Or 1X2 - the drawn game here is still possible. For example - football)
Coefficient type (on a victory, or a drawn game)
ID the contender
ID event (match)
Let's assume that in this table coefficients on the same football match of 3 offices are written down:
Office | the Contender | coefficient Type | market Type | Coefficient
1 1 | the Contender 1 | the Victory | 1X2 | 1,5
2 1 | the Contender 1 | the Drawn game | 1X2 | 3
3 1 | the Contender 2 | the Victory | 1X2 | 5
4 1 | the Contender 2 | the Drawn game | 1X2 | 3
5 2 | the Contender 1 | the Victory | 1X2 | 1,6
6 2 | the Contender 1 | the Drawn game | 1X2 | 2,9
7 2 | the Contender 2 | the Victory | 1X2 | 7
8 2 | the Contender 2 | the Drawn game | 1X2 |2,9
9 2 | the Contender 1 | the Victory | 1X2 | 1,7
10 2 | the Contender 1 | the Drawn game | 1X2 | 2,8
11 2 | the Contender 2 | the Victory | 1X2 |7,1
12 2 | the Contender 2 | the Drawn game | 1X2 |2,8
(The drawn game for each office is stored 2 times - once for one contender, the second time for another)
And here a question: whether it is possible as that means SQL to show all possible "" combinations?
I.e. as a result of SQL request I would like to see here such table:
1 Combination 1 | 1 | the Contender 1 | the Victory | 1,5
2 Combination 1 | 1 | the Contender 1 | the Drawn game | 3
3 Combination 1 | 2 | the Contender 2 | the Victory | 7
4 Combination 2 | 2 | the Contender 1 | the Victory | 1,6
5 Combination 2 | 2 | the Contender 1 | the Drawn game | 2,9
6 Combination 2 | 1 | the Contender 2 | the Victory | 5
7 Combination 3 | 1 | the Contender 1 | the Victory | 1,5
8 Combination 3 | 2 | the Contender 1 | the Drawn game | 2,9
9 Combination 3 | 1 | the Contender 2 | the Victory | 5
I.e. "Combination" is a combination of coefficients of various offices on all possible outcomes of a match.
I could group such result across the field "combination", and apply various grouped functions to coefficients.
At me all boots in the program, all combinations there are made and all operation by means of arrays becomes. I want to simplify a program construction - all can be made SQL - request?

2

Re: Grouping of combinations

Yes as though I do not see a problem... It is necessary to understand only a principle on which of these initial this result is received.

3

Re: Grouping of combinations

Akina wrote:

Yes as though I do not see a problem... It is necessary to understand only a principle on which of these initial this result is received.

Principle idle time: We can deliver on a victory of the contender 1 and on a drawn game in office BK1, and on a victory of the contender 2 in office BK2.
Thereby we superimpose all possible outcomes of a match. It we will be, for example, "a combination 1".
Also it is not dependent on a match outcome at certain ratios of coefficients and the totals of rates we receive small - but profit.
It also is "a bookmaker fork".

4

Re: Grouping of combinations

At saw that in the first table of the first post - an error: from 9-12 records concern to 3, instead of to 2

5

Re: Grouping of combinations

forklive wrote:

the Principle idle time: We can deliver on a victory of the contender 1 and on a drawn game in office BK1, and on a victory of the contender 2 in office BK2.
Thereby we superimpose all possible outcomes of a match. It we will be, for example, "a combination 1".

While sounds it is obscure. What sense to make combinations? No, I would understand, if ALL were made possible (4 for 12 at 2 offices, 8 at three, etc., and accordingly 9, 27... - for 1X2) but why from all possible those are necessary to you shown three and the remaining are not necessary? What principle of selection?
Well and the main thing. The Purpose what? I understand, there would be a purpose "to make a combination so that for each outcome to receive greatest/least of offered coefficients". Or in the advanced variant - to receive the maximum expected scoring (truth, here it is necessary to set a certain probability of each of outcomes)...

6

Re: Grouping of combinations

Akina wrote:

While sounds it is obscure. What sense to make combinations? No, I would understand, if ALL were made possible (4 for 12 at 2 offices, 8 at three, etc., and accordingly 9, 27... - for 1X2) but why from all possible those are necessary to you shown three and the remaining are not necessary? What principle of selection?

I also need to make all possible combinations for each event. And then to group across the field "combination", and to receive for each combination fork percent.

Akina wrote:

Well and the main thing. The Purpose what? I understand, there would be a purpose "to make a combination so that for each outcome to receive greatest/least of offered coefficients". Or in the advanced variant - to receive the maximum expected scoring (truth, here it is necessary to set a certain probability of each of outcomes)...

Sense to find all profitable forks. When I will receive the above described result, by means of SUM and by means of division/addition/multiplication I will receive percent of profitableness for each combination.
For example - one of combinations it turned out such:
1 - The Contender 1 - a victory: 3,8 (the total of the rate 28)
1 - The Contender 1 - a drawn game: 4,5 (the total of the rate 23)
2 - The Contender 2 - a victory: 2,2 (the total of the rate 48)
Percent of such fork = 6 %
For example if we deliver the totals specified in brackets on this event - that we receive 6 USD profits, irrespective of result of a match.
I.e. all combinations which cover all possible outcomes of a match of one event are necessary.
It turns out that where that to request will be kf1.eventId = kf2.eventId
----------------------------------------------------------------------------------------------
While thoughts such: what that of the general-purpose request for all types of the markets and all types of forks to make it does not turn out.
Most likely, it is necessary to use CROSS JOIN
But if for market P1P2 type it is necessary to connect 2 times the table of coefficients kf for 1X2 - it is necessary to connect the table of coefficients already 3 times. And for multiinitial forks - (to guess the exact account) - repeatedly. I.e. for each amount of outcomes the request is necessary.
Well and I tested cross join - certainly works not quickly.

7

Re: Grouping of combinations

Well so it generally is trivial. Especially if to change storage (for example, in representation), replacing the contender on ordered pair, and accordingly entering a variant "Defeat". I.e. not
Office, the Contender, coefficient Type, market Type, Coefficient
1, the Contender 1, the Victory, 1X2, 1.5
1, the Contender 1, the Drawn game, 1X2, 3
1, the Contender 2, the Victory, 1X2, 5
1, the Contender 2, the Drawn game, 1X2, 3
And
Office, Contenders, coefficient Type, market Type, Coefficient
1, the Contender 1 - the Contender 2, the Victory, 1X2, 1.5
1, the Contender 1 - the Contender 2, the Drawn game, 1X2, 3
1, the Contender 1 - the Contender 2, Defeat, 1X2, 5
The normal Cartesian product of several copies of the table if all variants in one record are necessary... Or we tell a choice on a generated mask if it is necessary each variant in the record, as in an example.

8

Re: Grouping of combinations

Akina wrote:

or we tell a choice on a generated mask if it is necessary each variant in the record, as in an example.

Here did not understand: what the choice on a generated mask "means"?
Also it turns out - if outcomes 2 - that  2 tables, if 3 outcomes - 3, etc.
I.e. it does not turn out scalabilities.
I after all correctly understood all?

9

Re: Grouping of combinations

forklive wrote:

that the choice on a generated mask "means"?

In request data source the synthetic table of type is added

SELECT ' the Victory ' value
UNION ALL SELECT ' the Drawn game'
UNION ALL SELECT ' Defeat'

Which is multiplied on all  and allows  one combination of Kontora-Sopernik1-Sopernik2 on three records with different variants of an outcome.

forklive wrote:

it turns out - if outcomes 2 - that  2 tables, if 3 outcomes - 3, etc.
I.e. it does not turn out scalabilities.

Well why? Tables always will be three. Simply in everyone tables pre-selection as an outcome becomes, for 12 in  for drawn games will be empty, and in . Fields of output dial-up NULL to eliminate which it is possible.

10

Re: Grouping of combinations

Akina, thanks for answers and for the help!
Went to understand)