Formation of tournament tables, stored procedures SQL
The other day I read a post about the automated formation of football championships and decided to share my solution to this problem, which I used for a small game. The implementation of the draw is not a standard approach, using MS SQL Server stored procedures.
As a result, I got the database structure and stored procedures that allow you to create a table of games between the teams (to draw) and process the results of the championship. All scripts can be downloaded from the repository on github .
The main stored procedure is the procedure for forming championship games between teams. When forming, I adhered to the basic rules of the tournament:
Let's consider step by step the algorithm for forming the table of games. I will try to describe the logic in detail, not boring and with a demonstration of the schemes. As an example, let's take the championship in which 4 teams participate, although the algorithm can work with any even number of teams. Conventionally, let's denote our teams as 1, 2, 3, and 4, which in my implementation are their direct IDs.
The first thing that needed to be done was to calculate the number of championship tournaments and form pairs of teams for games. The number of tournaments depends on the number of teams, since one team with another can play in one round only once and it should not play with itself. This is calculated by the formula:
')
After that we form a list of pairs of teams for all games of the championship. For this we can assume that we have 2 lists with identical numbers of all the teams of the championship.
Each team of the first list must play with each team of the second list once, excluding the team with the same number (itself). As a result, we get a list of pairs of championship teams of the next type.
After we received a list of pairs of teams, we need to distribute them around the tour without forgetting about the condition: one team can play only once per round and two teams can play only twice with each other for the championship at home and guest stadiums.
The number of games that occur in one round are equal to the total number of championship teams. For the formation of the tour, the very first pair of teams is taken from the previously formed list, the numbers of the teams are written into the game of the tour and into a temporary array, and are removed from the list of pairs of teams. The same logic will be used to form the remaining games of the tour, only now pairs of teams that have numbers in the temporary array will be skipped, so two identical teams will not be able to play in one round, but none of the teams will remain idle. Upon completion, the temporary array is cleared and this algorithm will be repeated until all tours are filled.
At the end of the algorithm, the list of all pairs of championship teams will be empty, the games will be formed and all the tours are filled with games - everything follows the rules of the championship.
The main algorithm I wanted to share is described, I did not go into the details of the syntax of the stored SQL procedures, since this is another topic and another post. All scripts with an example implementation can be found in the github repository. At the moment, the scripts are working and tested on MS SQL Server, if this is interesting for you, I will rewrite it under the DBMS that you are using. The project also contains stored procedures for calculating the best goalkeeper, scorer, "The Way of the Champion" and several others to interact with the championship data.
For the scripts to work correctly, you must first create the FootballTournament database. For the operation of the stored counting procedures, better than the scorer, goalkeeper and other auxiliary stored procedures, the first two must be performed: the formation of tournaments and the generation of random data of game results.
Run the scripts from the example in the following order:
I thank everyone who was interested to read to the end.
Link to the project repository on github
As a result, I got the database structure and stored procedures that allow you to create a table of games between the teams (to draw) and process the results of the championship. All scripts can be downloaded from the repository on github .
Championship game table
The main stored procedure is the procedure for forming championship games between teams. When forming, I adhered to the basic rules of the tournament:
- The number of teams participating in the tournament must be even;
- Each team must play on the other 2 times - in their own stadium and in the stadium of rivals;
- In the same round, the same team can play only once;
- For winning the match, the team gets 2 points, for a draw, 1 point, and for losing, respectively - 0.
Let's consider step by step the algorithm for forming the table of games. I will try to describe the logic in detail, not boring and with a demonstration of the schemes. As an example, let's take the championship in which 4 teams participate, although the algorithm can work with any even number of teams. Conventionally, let's denote our teams as 1, 2, 3, and 4, which in my implementation are their direct IDs.
The first thing that needed to be done was to calculate the number of championship tournaments and form pairs of teams for games. The number of tournaments depends on the number of teams, since one team with another can play in one round only once and it should not play with itself. This is calculated by the formula:
')
@TournamentsCnt = (@TeamsCnt * (@TeamsCnt - 1) * 2) / @TeamsCnt After that we form a list of pairs of teams for all games of the championship. For this we can assume that we have 2 lists with identical numbers of all the teams of the championship.
Each team of the first list must play with each team of the second list once, excluding the team with the same number (itself). As a result, we get a list of pairs of championship teams of the next type.
After we received a list of pairs of teams, we need to distribute them around the tour without forgetting about the condition: one team can play only once per round and two teams can play only twice with each other for the championship at home and guest stadiums.
The number of games that occur in one round are equal to the total number of championship teams. For the formation of the tour, the very first pair of teams is taken from the previously formed list, the numbers of the teams are written into the game of the tour and into a temporary array, and are removed from the list of pairs of teams. The same logic will be used to form the remaining games of the tour, only now pairs of teams that have numbers in the temporary array will be skipped, so two identical teams will not be able to play in one round, but none of the teams will remain idle. Upon completion, the temporary array is cleared and this algorithm will be repeated until all tours are filled.
At the end of the algorithm, the list of all pairs of championship teams will be empty, the games will be formed and all the tours are filled with games - everything follows the rules of the championship.
The main algorithm I wanted to share is described, I did not go into the details of the syntax of the stored SQL procedures, since this is another topic and another post. All scripts with an example implementation can be found in the github repository. At the moment, the scripts are working and tested on MS SQL Server, if this is interesting for you, I will rewrite it under the DBMS that you are using. The project also contains stored procedures for calculating the best goalkeeper, scorer, "The Way of the Champion" and several others to interact with the championship data.
For the scripts to work correctly, you must first create the FootballTournament database. For the operation of the stored counting procedures, better than the scorer, goalkeeper and other auxiliary stored procedures, the first two must be performed: the formation of tournaments and the generation of random data of game results.
Run the scripts from the example in the following order:
- Formation of database schema ' schema.sql '.
- Tables are filled with players, teams, coaches and their ' fillTestData.sql ' stadiums.
- Creating stored procedures ' storedProcedurs.sql '.
- Call stored procedures ' execute.sql '.
I thank everyone who was interested to read to the end.
Link to the project repository on github
Source: https://habr.com/ru/post/167937/
All Articles