Menu
I have been kindly given this algorithm to help me create a fixture list in SQL, but applying it as SQL code, I have no idea how to do. Is there a way somebody can guide me on how to apply it with code?
Below is my tables schema and below that is the algorithm:
League:
[LeagueID] TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[LeagueName] VARCHAR(30) UNIQUE
Team:
[TeamID] TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[TeamAbbreviation] CHAR(3) UNIQUE,
[TeamName] VARCHAR(50) UNIQUE,
[LeagueID] TINYINT CONSTRAINT FK_Team_League FOREIGN KEY REFERENCES League(LeagueID)
Fixture:
[FixtureID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[WeekNumber] INT NOT NULL,
[FixtureDate] DATE NULL,
[HomeTeamID] TINYINT NULL,
[AwayTeamID] TINYINT NULL,
[LeagueID] TINYINT CONSTRAINT FK_Fixture_League FOREIGN KEY REFERENCES League(LeagueID)
ALGORITHM:
Lets us translate the algorithm, called round robin scheduling, in terms of an ordered list l of N teams (which correspond to N-1 polygon vertices + the polygon center):
l defines fixtures by playing the first team from the list against the last, the second against the first before last, etc.
That is, for 0 ≤ x < N, you play team l[x] vs team l[N-1-x].
To generate the next set of fixtures, you rotate the N-1 first elements of the list.
That is l = l[1] + l[2] + ... + l[N-2] + l[0] + l[N-1]
Once you've done the full set of N-1 rotations, do it again but swapping home and away teams: play team l[N-1-x] vs team l[x] instead of the opposite.
If you start with the numerically ordered list 0..N-1 then at round i the list is:l = [(i + 0) % (N-1)] + [(i + 1) % (N-1)] + ... + [(i + N-2) % (N-1)] + [N-1]
That is, fixtures are at round i:
i vs N-10 < x < (N-1) / 2, (x + i) % (N-1) vs (N-1 - x + i) % (N-1)
Now there is a trick, as this only works for even numbers. Otherwise the last team always plays (against team i at round i) whereas naturally each round has one team that can't play. That would mean team 4 plays one more game than the other teams.
To solve this, we add a dummy team, so for 5 teams we have N = 6, and at round i:
i vs 5 (the dummy team)(i + 1) % 4 vs (4 + i) % 4
(i + 2) % 4 vs (3 + i) % 4
Now that you know this, you can generate a function that will give you fixtures based on round number. It should output the following:
round 0: 0 rests, 1 vs 4, 2 vs 3
round 1: 1 rests, 2 vs 0, 3 vs 4
round 2: 2 rests, 3 vs 1, 4 vs 0
round 3: 3 rests, 4 vs 2, 0 vs 1
round 4: 4 rests, 0 vs 3, 1 vs 2
Note that instead of i in the formulas x + i and N-1 - x + i you can use any multiple m * i (so x + m * i and N-1 - x + m * i) as long as m and N-1 and relatively prime. Here N - 1 = 5 is prime, so you can use any m you want.
UPDATE:
As required below is thee test data for first the League table and second the teams table (matching the table schema columns in order)
League:
1, 'English Premiership'
2, 'English Division 1'
Teams:
1, 'BCN', 'FC Barcelona', 1
2, 'MAD', 'Real Madrid', 1
3, 'ATH', 'Athletico Madrid', 1
4, 'ESP', 'Espanyol', 1
5, 'MAN', 'Manchester United', 2
6, 'BOL', 'Bolton', 2
7, 'CHE', 'Chelsea', 2
8, 'ARS', 'Arsenal', 2
Teams play each other home and away and can only play against teams who are in the same league (hence the different LeagueIDs)
The matches should be like this for each round:
League 1:
Round 1- 01/05/2016:
1v4, 2v3
Round 2- 08/05/2016:
1v2, 3v4
Round 3- 15/05/2016:
3v1, 4v2
Round 4- 22/05/2016:
4v1, 3v2
Round 5- 29/05/2016:
2v1, 4v3
Round 6- 05/06/2016:
1v3, 2v4
League 2:
Round 1- 01/05/2016:
5v8, 6v7
Round 2- 08/05/2016:
5v6, 7v8
Round 3- 15/05/2016:
3v1, 4v2
Round 4- 22/05/2016:
8v5, 7v6
Round 5- 29/05/2016:
6v5, 8v7
Round 6- 05/06/2016:
5v7, 6v8
This should all be inserted in the 'Fixture' table.
934
Another Oracle solution.
Setup:
CREATE TABLE League (
LeagueID INT PRIMARY KEY,
LeagueName VARCHAR(30) UNIQUE
);
CREATE TABLE Team (
TeamID INT PRIMARY KEY,
TeamAbbreviation CHAR(3) UNIQUE,
TeamName VARCHAR(50) UNIQUE,
LeagueID INT CONSTRAINT FK_Team_League REFERENCES League(LeagueID)
);
CREATE TABLE Fixture (
FixtureID INT PRIMARY KEY,
WeekNumber INT NOT NULL,
FixtureDate DATE NULL,
HomeTeamID INT NULL,
AwayTeamID INT NULL,
LeagueID INT CONSTRAINT FK_Fixture_League REFERENCES League(LeagueID)
);
INSERT INTO League VALUES ( 1, 'League 1' );
INSERT INTO League VALUES ( 2, 'League 2' );
INSERT INTO Team VALUES ( 1, 'AAA', 'Team A', 1 );
INSERT INTO Team VALUES ( 2, 'BBB', 'Team B', 1 );
INSERT INTO Team VALUES ( 3, 'CCC', 'Team C', 1 );
INSERT INTO Team VALUES ( 4, 'DDD', 'Team D', 1 );
INSERT INTO Team VALUES ( 5, 'EEE', 'Team E', 2 );
INSERT INTO Team VALUES ( 6, 'FFF', 'Team F', 2 );
INSERT INTO Team VALUES ( 7, 'GGG', 'Team G', 2 );
INSERT INTO Team VALUES ( 8, 'HHH', 'Team H', 2 );
INSERT INTO Team VALUES ( 9, 'III', 'Team I', 2 );
Insert - Fixtures:
INSERT INTO Fixture
WITH league_teams ( id, leagueid, idx, is_fake, num_teams, num_fake ) AS (
-- Generate a unique-per-league index for each team that is between 0
-- and the (number of teams - 1) and calculate the number of teams
-- and if this is an odd number then generate a fake team as well.
SELECT TeamID,
LeagueID,
ROW_NUMBER() OVER ( PARTITION BY LeagueID ORDER BY TeamID ) - 1,
0,
COUNT(1) OVER ( PARTITION BY LeagueID ),
MOD( COUNT(1) OVER ( PARTITION BY LeagueID ), 2 )
FROM Team
UNION ALL
SELECT NULL,
LeagueID,
COUNT(1),
1,
COUNT(1),
1
FROM Team
GROUP BY LeagueID
HAVING MOD( COUNT(1), 2 ) > 0
),
cte ( home_idx, away_idx, week_number, leagueID, num_teams, num_fake ) AS (
-- Start by calculating the round 1 games
SELECT idx,
num_teams + num_fake - 1 - idx,
1,
LeagueID,
num_teams,
num_fake
FROM league_teams
WHERE 2 * idx < num_teams
UNION ALL
-- Then generate the successive rounds with the two cases when the
-- away team has the maximum index or otherwise.
SELECT CASE away_idx
WHEN num_teams + num_fake - 1
THEN home_idx + 1
ELSE MOD( home_idx + 1, num_teams + num_fake -1 )
END,
CASE away_idx
WHEN num_teams + num_fake - 1
THEN away_idx
ELSE MOD( away_idx + 1, num_teams + num_fake - 1 )
END,
week_number + 1,
LeagueID,
num_teams,
num_fake
FROM cte
WHERE week_number < num_teams + num_fake - 1
)
-- Finally join the cte results back to the League_Teams table to convert
-- the indexes used in calculation back to the actual team ids.
SELECT rn,
week_number,
NULL,
h.id,
a.id,
c.leagueid
FROM (
-- This step isn't necessary but it keeps the results in a nice order.
SELECT ROWNUM AS rn,
t.*
FROM (
-- Duplicate the results swapping home and away.
SELECT week_number,
home_idx,
away_idx,
LeagueId
FROM cte
UNION ALL
SELECT week_number + num_teams + num_fake - 1,
away_idx,
home_idx,
LeagueId
FROM cte
) t
) c
INNER JOIN League_Teams h
ON ( c.home_idx = h.idx AND c.leagueId = h.leagueID )
INNER JOIN League_Teams a
ON ( c.away_idx = a.idx AND c.leagueId = a.leagueID )
ORDER BY rn;
Output:
SELECT * FROM fixture;
FIXTUREID WEEKNUMBER FIXTUREDATE HOMETEAMID AWAYTEAMID LEAGUEID
---------- ---------- ------------------- ---------- ---------- ----------
1 1 1 4 1
2 1 2 3 1
3 1 5 2
4 1 6 9 2
5 1 7 8 2
6 2 2 4 1
7 2 3 1 1
8 2 6 2
9 2 7 5 2
10 2 8 9 2
11 3 3 4 1
12 3 1 2 1
13 3 7 2
14 3 8 6 2
15 3 9 5 2
16 4 8 2
17 4 9 7 2
18 4 5 6 2
19 5 9 2
20 5 5 8 2
21 5 6 7 2
22 4 4 1 1
23 4 3 2 1
24 6 5 2
25 6 9 6 2
26 6 8 7 2
27 5 4 2 1
28 5 1 3 1
29 7 6 2
30 7 5 7 2
31 7 9 8 2
32 6 4 3 1
33 6 2 1 1
34 8 7 2
35 8 6 8 2
36 8 5 9 2
37 9 8 2
38 9 7 9 2
39 9 6 5 2
40 10 9 2
41 10 8 5 2
42 10 7 6 2
(Note: FixtureDate is NULL since it is unclear how you want this generated but you should be able to take the week number and use this as the offset from the start of the season to generate dates)
154
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
