I'm new to SQL and I'm trying to make a table which stores the schedule of a football league. The table contains 4 columns (matchID, home_team, away_team, match_date). I have 8 different teams and each team should play against each of the other teams exactly twice. The tournament will last for 14 weeks(4 games per week). I searched and found out that the double round robin algorithm does the same thing but I'm not sure how to write it in PL/SQL and generate a table. Please help me.
Asked
Active
Viewed 1,325 times
0
Kaushik Nayak
- 30,772
- 5
- 32
- 45
Ayush Dixit
- 3
- 5
-
2It looks like there is a lot missing from this question. For example are there rules about how often a team is expected to play in a particular timeframe, are home and away games allowed to be sequential, what is the actual timeframe for all games to be played in (and lots of others I'm sure) – P.Salmon Oct 08 '17 at 10:01
-
Refer https://stackoverflow.com/questions/13372232/sql-all-possible-round-robin-combinations-between-two-tables – Kaushik Nayak Oct 08 '17 at 11:35
3 Answers
0
You can generate records for your Table using this query. Note here that I have not enforced any constraints which is up to you and you should. You can write simple CREATE TABLE ( syntax with all datatype and constraints and use this query to insert records.
match_groups generates the match combinations
All_matches repeats the matches by swapping home team and away team.
CREATE TABLE MATCH_SCHEDULE AS WITH TEAMS (name) AS
(SELECT 'TEAM1'
FROM DUAL
UNION ALL SELECT 'TEAM2'
FROM DUAL
UNION ALL SELECT 'TEAM3'
FROM DUAL
UNION ALL SELECT 'TEAM4'
FROM DUAL
UNION ALL SELECT 'TEAM5'
FROM DUAL
UNION ALL SELECT 'TEAM6'
FROM DUAL
UNION ALL SELECT 'TEAM7'
FROM DUAL
UNION ALL SELECT 'TEAM8'
FROM DUAL),
match_groups AS
(SELECT t1.name home_team,
t2.name away_team
FROM
(SELECT rownum RNUM,
NAME
FROM TEAMS) t1
JOIN
(SELECT rownum RNUM,
NAME
FROM TEAMS) t2 ON t1.RNUM < t2.RNUM),
All_matches AS
(SELECT home_team,
away_team
FROM match_groups
UNION ALL SELECT away_team home_team,
home_team away_team
FROM match_groups)
SELECT ROWNUM matchID,
home_team,
away_team,
SYSDATE + ROWNUM match_date
FROM All_matches;
This would create a table with following data.
TABLE MATCH_SCHEDULE
MATCHID HOME_TEAM AWAY_TEAM MATCH_DATE
1 TEAM1 TEAM2 09-OCT-17
2 TEAM1 TEAM3 10-OCT-17
3 TEAM1 TEAM4 11-OCT-17
4 TEAM1 TEAM5 12-OCT-17
5 TEAM1 TEAM6 13-OCT-17
6 TEAM1 TEAM7 14-OCT-17
...
...
54 TEAM7 TEAM6 01-DEC-17
55 TEAM8 TEAM6 02-DEC-17
56 TEAM8 TEAM7 03-DEC-17
Note that MATCHID has just numbers 1,2,3.... You can also make use of an Oracle sequence if you like or update it accordingly. Now, since you need to have 4 matches per week, you need to construct an adhoc update script based on your need with proper dates and run it.
Kaushik Nayak
- 30,772
- 5
- 32
- 45
-
Thanks for this. The table was successfully created. But I have one problem, according to the round robin algorithm, a team cant play its second match until all other teams have played their first match. That's why i wanted 4 matches per week so that all 8 teams would be done with their first match. Have a look at this http://tournamentscheduler.net/schedule/NDg2NDg0MzUzNg. – Ayush Dixit Oct 08 '17 at 15:13
-
If it is a one time activity , you could generate the update scripts from excel for updating it to correct dates. For eg: `update table SET matchdate =
where home_team = TEAM3 AND away_team = TEAM1 ` ,`update table SET matchdate = – Kaushik Nayak Oct 08 '17 at 16:42where home_team = TEAM5 AND away_team = TEAM7 ` .. and so on. -
Yes that's easy but a time consuming way. Is there a way to create a procedure? And i guess it'd be easier if the match_date for all 4 games in a week are kept same so that all 4 matches are held on the same day. And hence every team will play a match after exactly one week. – Ayush Dixit Oct 08 '17 at 18:30
0
Oracle 11g R2 Schema Setup:
CREATE TABLE matches ( matchID, home_team, away_team, match_date ) AS
WITH rounds ( round, home, away, num_players ) AS (
SELECT 1,
LEVEL,
num_players + 1 - LEVEL,
num_players
FROM ( SELECT 8 AS num_players FROM DUAL )
CONNECT BY LEVEL <= num_players / 2
UNION ALL
SELECT round + 1,
CASE home
WHEN 1 THEN 1
WHEN 2 THEN num_players
ELSE home - 1
END,
CASE away
WHEN 2 THEN num_players
ELSE away - 1
END,
num_players
FROM rounds
WHERE round < num_players - 1
)
SELECT ROWNUM,
t.*
FROM (
SELECT home,
away,
DATE '2017-01-01' + ( round - 1 ) * 7 AS match_date
FROM rounds
UNION ALL
SELECT away,
home,
DATE '2017-01-01' + ( round + num_players - 2 ) * 7
FROM rounds
) t;
Query 1:
SELECT * FROM matches
| MATCHID | HOME_TEAM | AWAY_TEAM | MATCH_DATE |
|---------|-----------|-----------|----------------------|
| 1 | 1 | 8 | 2017-01-01T00:00:00Z |
| 2 | 2 | 7 | 2017-01-01T00:00:00Z |
| 3 | 3 | 6 | 2017-01-01T00:00:00Z |
| 4 | 4 | 5 | 2017-01-01T00:00:00Z |
| 5 | 1 | 7 | 2017-01-08T00:00:00Z |
| 6 | 8 | 6 | 2017-01-08T00:00:00Z |
| 7 | 2 | 5 | 2017-01-08T00:00:00Z |
| 8 | 3 | 4 | 2017-01-08T00:00:00Z |
| 9 | 1 | 6 | 2017-01-15T00:00:00Z |
| 10 | 7 | 5 | 2017-01-15T00:00:00Z |
| 11 | 8 | 4 | 2017-01-15T00:00:00Z |
| 12 | 2 | 3 | 2017-01-15T00:00:00Z |
| 13 | 1 | 5 | 2017-01-22T00:00:00Z |
| 14 | 6 | 4 | 2017-01-22T00:00:00Z |
| 15 | 7 | 3 | 2017-01-22T00:00:00Z |
| 16 | 8 | 2 | 2017-01-22T00:00:00Z |
| 17 | 1 | 4 | 2017-01-29T00:00:00Z |
| 18 | 5 | 3 | 2017-01-29T00:00:00Z |
| 19 | 6 | 2 | 2017-01-29T00:00:00Z |
| 20 | 7 | 8 | 2017-01-29T00:00:00Z |
| 21 | 1 | 3 | 2017-02-05T00:00:00Z |
| 22 | 4 | 2 | 2017-02-05T00:00:00Z |
| 23 | 5 | 8 | 2017-02-05T00:00:00Z |
| 24 | 6 | 7 | 2017-02-05T00:00:00Z |
| 25 | 1 | 2 | 2017-02-12T00:00:00Z |
| 26 | 3 | 8 | 2017-02-12T00:00:00Z |
| 27 | 4 | 7 | 2017-02-12T00:00:00Z |
| 28 | 5 | 6 | 2017-02-12T00:00:00Z |
| 29 | 8 | 1 | 2017-02-19T00:00:00Z |
| 30 | 7 | 2 | 2017-02-19T00:00:00Z |
| 31 | 6 | 3 | 2017-02-19T00:00:00Z |
| 32 | 5 | 4 | 2017-02-19T00:00:00Z |
| 33 | 7 | 1 | 2017-02-26T00:00:00Z |
| 34 | 6 | 8 | 2017-02-26T00:00:00Z |
| 35 | 5 | 2 | 2017-02-26T00:00:00Z |
| 36 | 4 | 3 | 2017-02-26T00:00:00Z |
| 37 | 6 | 1 | 2017-03-05T00:00:00Z |
| 38 | 5 | 7 | 2017-03-05T00:00:00Z |
| 39 | 4 | 8 | 2017-03-05T00:00:00Z |
| 40 | 3 | 2 | 2017-03-05T00:00:00Z |
| 41 | 5 | 1 | 2017-03-12T00:00:00Z |
| 42 | 4 | 6 | 2017-03-12T00:00:00Z |
| 43 | 3 | 7 | 2017-03-12T00:00:00Z |
| 44 | 2 | 8 | 2017-03-12T00:00:00Z |
| 45 | 4 | 1 | 2017-03-19T00:00:00Z |
| 46 | 3 | 5 | 2017-03-19T00:00:00Z |
| 47 | 2 | 6 | 2017-03-19T00:00:00Z |
| 48 | 8 | 7 | 2017-03-19T00:00:00Z |
| 49 | 3 | 1 | 2017-03-26T00:00:00Z |
| 50 | 2 | 4 | 2017-03-26T00:00:00Z |
| 51 | 8 | 5 | 2017-03-26T00:00:00Z |
| 52 | 7 | 6 | 2017-03-26T00:00:00Z |
| 53 | 2 | 1 | 2017-04-02T00:00:00Z |
| 54 | 8 | 3 | 2017-04-02T00:00:00Z |
| 55 | 7 | 4 | 2017-04-02T00:00:00Z |
| 56 | 6 | 5 | 2017-04-02T00:00:00Z |
MT0
- 143,790
- 11
- 59
- 117
0
Try this procedure. The logic is based on round robin
Note that I used: week instead of date. Scheduling the matches for a specific date requires other information on how many per day, can you have a team playing more than once per week, etc.
The input to the procedure is a comma separated team list. If you input odd number, a "DUMMY" is created for round robin to work. This does not insert into a table but prompt you the schedule. You can replace the DBMS_OUTPUT statements with insert statements
create or replace
procedure test_schedule(p_teams in varchar2)
is
type t_teams is table of varchar2(15);
cursor c_teams is
select trim(regexp_substr(p_teams,'[^,]+', 1, level)) team
from dual
connect by regexp_substr(p_teams, '[^,]+', 1, level) is not null;
v_teams t_teams := t_teams();
v_team_shift varchar2(60);
v_weeks number;
v_count number:=0;
v_date date;
begin
for r_teams in c_teams loop
v_count := v_count + 1;
v_teams.extend();
v_teams(v_count) := r_teams.team;
end loop;
if mod(v_count, 2) != 0 then
v_count := v_count + 1;
v_teams.extend();
v_teams(v_count) := 'DUMMAY';
end if;
for i in 1..v_count loop
dbms_output.put_line(v_teams(i));
end loop;
v_weeks := v_count - 1;
dbms_output.put_line('Weeks: '|| v_weeks||' Count: '||v_count);
for week in 1..v_weeks loop
for i in 1..v_count/2 loop
dbms_output.put_line(week||': '||v_teams(i)||' X '||v_teams(v_count-i+1));
dbms_output.put_line(week+v_weeks||': '||v_teams(v_count-i+1)||' X '||v_teams(i));
end loop;
-- shift teams
v_team_shift := v_teams(v_count);
for i in 1..v_count-2 loop
v_teams(v_count-i+1) := v_teams(v_count-i);
end loop;
v_teams(2) := v_team_shift;
end loop;
end;
/
To test, I used:
begin test_schedule('TEAM A, TEAM B, TEAM C, TEAM D, TEAM E'); end;
/
The output:
Week Home Away
1: TEAM A X DUMMAY
6: DUMMAY X TEAM A
1: TEAM B X TEAM E
6: TEAM E X TEAM B
1: TEAM C X TEAM D
6: TEAM D X TEAM C
2: TEAM A X TEAM E
7: TEAM E X TEAM A
2: DUMMAY X TEAM D
7: TEAM D X DUMMAY
2: TEAM B X TEAM C
7: TEAM C X TEAM B
3: TEAM A X TEAM D
8: TEAM D X TEAM A
3: TEAM E X TEAM C
8: TEAM C X TEAM E
3: DUMMAY X TEAM B
8: TEAM B X DUMMAY
4: TEAM A X TEAM C
9: TEAM C X TEAM A
4: TEAM D X TEAM B
9: TEAM B X TEAM D
4: TEAM E X DUMMAY
9: DUMMAY X TEAM E
5: TEAM A X TEAM B
10: TEAM B X TEAM A
5: TEAM C X DUMMAY
10: DUMMAY X TEAM C
5: TEAM D X TEAM E
10: TEAM E X TEAM D
BA.
- 924
- 7
- 10