正如我的信息所述,我继承了当地飞镖联盟的统计学家的工作. (哇哦一季20美元)
我很乐意实施ELO评级系统.
当前数据库表有超过100,000个条目.
有3种不同的游戏.单打,双打和团队.
数据库的发起者输入的游戏如下：
Index Player1_num Player2_num Opp_Player1_num Odd_Player2_num H_team V_team Season Week W L Game_type
单打游戏输入两次.
values(1,20,null,30,200,300,11,2,1,301_singles)
和
values(2,301_singles)
这样做是为了方便个人查看.
双打游戏将具有诸如的价值
(3,21,31,501_doubles) (4,501_doubles) (5,501_doubles) (6,501_doubles)
同样,它是为了更容易查询查询跨季节和不同合作伙伴的赢利和损失而构建的.
团队游戏是：
(7,team_game)
所以我在表格中添加了一个match_num列,以帮助将游戏归为1个数字.
但是,我不确定如何最好地分配数字,可以理解的是,我不想手动完成所有90k条目.
注意：早期的季节是手工修改的,并非所有的双重匹配都有4个条目(有些只有1个).有些匹配也可能出现故障(而不是索引：1,2可能是1,9).
我不知道你是否需要更多信息,我不确定如何发布更大的表格示例.
Current code: <body> <?PHP include "inc.PHP"; // Select Max Match Number $sqlMatch="select max(match_num) from stats_results"; $match =MysqL_query($sqlMatch); $m= $match ? MysqL_result($match,0):MysqL_error(); $matchno= $m+1; // First Match number $game=array("'301_singles'","'cricket_singles'","'501_singles'","'301_doubles'","'cricket_doubles'"); // selects max season $sqlSeason="select max(season_index) from stats_season"; $season =MysqL_query($sqlSeason); $smax= $season ? MysqL_result($season,0):MysqL_error(); # $smax=2; // for each season up to max season for($s=1;$s<=$smax;$s++) { // Selects max week within the current season $sqlWeek="select max(week) from stats_results where season=$s "; $Week =MysqL_query($sqlWeek); $wmax= $Week ? MysqL_result($Week,0):MysqL_error(); # $wmax=2; // for each week within current season up to the max week for ($w=1;$w<=$wmax;$w++) { // each singles game foreach($game as $g) { ########################################################################################################### $result = MysqL_query("SELECT * FROM stats_results where season=$s and week=$w and game_code=$g ;") or die(MysqL_error()); // Put them in array for($i = 0; $rows[$i] = MysqL_fetch_assoc($result); $i++) ; // Delete last empty one array_pop($rows); //****************************************************** $matches=array(); foreach($rows as $record) { // Get a unique match code for this match $matchid= getMatchID($record); // Have we seen this ID before? If yes add this record index to the existing array // otherwise create an array with just this value if (!isset($matches[$matchid])) $matches[$matchid]= array($record['index_results']); // No else $matches[$matchid][]= $record['index_results']; // Yes,add this Index } // Now $matches is an array of arrays of Index values,grouped by "match" so... /* Sort $matches by key (see getMatchID for why!) */ ksort($matches); // Update the table foreach($matches as $match) { // Create sql instruction to set the match_num for all the Index values in each entry $sql= "UPDATE stats_results SET match_num = $matchno WHERE index_results IN (".implode(",",$match).")"; echo "<br>"; echo $sql; /* Execute the sql using your chosen method! */ // Move the match count on $matchno++; } // End our loops } } } function getMatchID($gamerecord) { $index= "{$gamerecord['season']}-{$gamerecord['week']}-{$gamerecord['game_code']}-"; $players= array( $gamerecord['player1_num'],empty($gamerecord['player2_num'])?0:$gamerecord['player2_num'],$gamerecord['opp_player1_num'],empty($gamerecord['opp_player2_num'])?0:$gamerecord['opp_player2_num'] ); // Sort the players to get them in a consistent order sort($players); // Add the sorted players to the index $index.= implode('-',$players); return $index; } ?> </body>
(我把它放到一个答案中,所以我可以让它更好地布局 – 但它并不是真正的“解决方案”！)
原文链接：https://www.f2er.com/php/136653.html我想我要做的是尝试构建一个按游戏分组的索引值数组 – 然后您可以使用它来创建sql来更新表.
因此,如果$rows是所有记录的数组,我们将执行类似以下伪代码的操作：
$matches= array();
foreach($rows as $record)
{
// Get a unique match code for this match
$matchid= getMatchID($record);
// Have we seen this ID before? If yes add this record index to the existing array
// otherwise create an array with just this value
if (!isset($matches[$matchid])) $matches[$matchid]= array($record['Index']); // No
else $matches[$matchid][]= $record['Index']; // Yes,add this Index
}
// Now $matches is an array of arrays of Index values,grouped by "match" so...
/* Sort $matches by key (see getMatchID for why!) */
ksort($matches);
// Update the table
$matchno= 1; // First Match number
foreach($matches as $match)
{
// Create sql instruction to set the match_num for all the Index values in each entry
$sql= "UPDATE games SET match_num = $matchno WHERE Index IN (".implode(",$match).")";
/* Execute the sql using your chosen method! */
// Move the match count on
$matchno++;
}
所以离开的是getMatchID函数 – 如果我们根据每个匹配的季节,星期以及它的参与者的排序列表(并使用季节和周首先)给每个匹配一个临时ID,这对于每个游戏应该是唯一的,我们可以排序通过此指数稍后以正确的顺序获得游戏.再次粗略的伪代码,如下：
function getMatchID($gamerecord)
{
$index= "{$gamerecord['Season']}-{$gamerecord['Week']}-{$gamerecord['H_Team']}-{$gamerecord['V_Team']}-";
$players= array(
empty($gamerecord['Player1_Num'])?0:$gamerecord['Player1_Num'],empty($gamerecord['Player2_Num'])?0:$gamerecord['Player2_Num'],empty($gamerecord['Opp_Player1_Num'])?0:$gamerecord['Opp_Player1_Num'],empty($gamerecord['Opp_Player2_Num'])?0:$gamerecord['Opp_Player2_Num']
);
// Sort the players to get them in a consistent order
sort($players);
// Add the sorted players to the index
$index.= implode('-',$players);
return $index;
}
因此,无论我们看到的是哪一场比赛记录,所有好的$index都会以11-2-200-300-0-0-20-30的比例回归到你的例子中的第一场单打比赛.
这有什么意义/帮助吗？
