如何编写特定的数学算法 [英] How to code a certain maths algorithm
问题描述
有人给我这个算法来帮助我在 SQL 中创建一个夹具列表，但是将它应用为 SQL 代码，我不知道该怎么做.有人可以指导我如何用代码应用它吗?
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:
联赛:
[LeagueID] TINYINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[LeagueName] VARCHAR(30) UNIQUE
团队:
[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)
固定装置:
[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)
算法:
让我们将称为循环调度的算法翻译为N个团队的有序列表l(对应于N-1个多边形顶点+多边形中心):
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通过与列表中的第一支球队对阵最后一支球队，第二支球队对阵最后一支球队，等等来定义赛程.
也就是说，对于0 ≤ x <;N，你玩的是团队l[x]和团队l[N-1-x].
ldefines fixtures by playing the first team from the list against the last, the second against the first before last, etc.
That is, for0 ≤ x < N, you play teaml[x]vs teaml[N-1-x].
要生成下一组装置，请旋转列表的 N-1 个第一个元素.
即 l = l[1] + l[2] + ... + l[N-2] + l[0] + l[N-1]
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]
一旦你完成了全套 N-1 轮换，再做一次，但交换主客队:玩团队 l[N-1-x] vs team l[x] 而不是相反.
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.
如果你从数字顺序列表 0..N-1 开始，那么在第 i 轮的列表是:l = [(i + 0) % (N-1)] + [(i + 1) % (N-1)] + ... + [(i + N-2) % (N-1)] + [N-1]
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]
也就是说，赛程在 i 回合:
That is, fixtures are at round i:
ivsN-1- 对于
0 <×＜(N-1)/2,(x + i) % (N-1)vs(N-1 - x + i) % (N-1)
现在有一个技巧，因为这只适用于偶数.否则最后一支球队总是在比赛(在 i 回合对阵 i>) 而自然每一轮都有一个不能比赛的球队.这意味着第 4 队比其他球队多打一场比赛.
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.
为了解决这个问题，我们添加了一个虚拟团队，所以对于 5 个团队，我们有 N = 6，并且在 i 回合:
To solve this, we add a dummy team, so for 5 teams we have N = 6, and at round i:
ivs5(虚拟团队)(i + 1) % 4vs(4 + i) % 4(i + 2) % 4vs(3 + i) % 4
ivs5(the dummy team)(i + 1) % 4vs(4 + i) % 4(i + 2) % 4vs(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:
第 0 轮:0 次休息，1 对 4，2 对 3
第 1 轮:休息 1 次，2 对 0，3 对 4
第 2 轮:2 次休息，3 对 1，4 对 0
第 3 轮:3 次休息，4 对 2，0 对 1
第 4 轮:4 次休息，0 对 3，1 对 2
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
<小时>
请注意，在公式 x + i 和 N-1 - x + i 中，您可以使用任意多个 代替 (所以 i>m * ix + m * i 和 N-1 - x + m * i)只要 m> 和 N-1 和 相对质数.这里 N - 1 = 5 是质数，所以你可以使用任何你想要的 m.
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.
更新:
根据下面的要求，首先是联赛表的测试数据，其次是团队表的测试数据(按顺序匹配表模式列)
As required below is thee test data for first the League table and second the teams table (matching the table schema columns in order)
联赛:
1, 'English Premiership'
2, 'English Division 1'
团队:
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
球队在主场和客场互相比赛，并且只能与同一联赛中的球队比赛(因此联赛ID不同)
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
- 联赛编号为LeagueID"
- 轮数是WeekNumber"
- 日期是固定日期"
- 主队号码是HomeTeamID"
- 客队号码是AwayTeamID"
这应该全部插入到Fixture"表中.
This should all be inserted in the 'Fixture' table.
推荐答案
另一种 Oracle 解决方案.
Another Oracle solution.
设置:
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 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;
输出:
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
(注意:FixtureDate 是 NULL 因为不清楚您希望如何生成它，但您应该能够获取周数并将其用作从季节开始偏移以生成日期)
(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)
这篇关于如何编写特定的数学算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
