如何实施一个系统来确定是否已达到里程碑

Question

我有一个名为“统计”的表，

player_id team_id match_date  goal assist`  
        1       8  2010-01-01    1      1
        1       8  2010-01-01    2      0
        1       9  2010-01-01    0      5
  ...

我想知道球员何时达到里程碑（例如 100 个进球、100 个助攻、500 个进球...）
我还想知道团队何时达到里程碑。
我想知道哪个球员或球队第一、第二、第三达到 100 个进球...

我想使用带有表格的触发器来累计总数。
表player_accumulator（和team_accumulator）表将是

player_id total_goals total_assists
        1           3             6


team_id   total_goals total_assists
      8             3             1
      9             0             5 

每次在统计表中插入一行时，触发器将插入/更新player_accumulator和team_accumulator表。
此触发器还可以验证玩家或团队是否已达到包含数字的里程碑表中的里程碑

milestone
      100
      500
     1000
      ...

表player_milestone将包含玩家达到的里程碑：

player_id  stat    milestone          date
        1  goal          100    2013-04-02
        1  assist        100    2012-11-19

---

There is a better way to implements a "milestone" ?
There is an easiest way without triggers ?

我正在使用 PostgreSQL

Answer 1

我只计算得分球员以及得分球队的所有进球和助攻。

像这样在客户端（以伪代码）：

function insert_stat(player_id, team_id, match_date, goals, assists)
{
  if (goals>0) {
    player_goals_before = query('select count(goal) from stats where player_id=?',player_id);
    team_goals_before = query('select count(goal) from stats where team_id=?',team_id);
  }
  if (assists>0) {
    player_assists_before = query('select count(assist) from stats where player_id=?',player_id);
    team_assists_before = query('select count(assist) from stats where team_id=?',team_id);
  }
  query("insert into stats (player_id, team_id, match_date, goal, assist)\n"
    +"values (?, ?, ?, ?, ?)", player_id, team_id, match_date, goal, assist);

  if (goals>0) {
    if ( has_milestone(player_goals_before+goals) and !has_milestone(player_goals_before) ) {
      alert("player " + player_id + " reached milestone!")
    }
    if ( has_milestone(team_goals_before+goals) and !has_milestone(team_goals_before) ) {
      alert("team " + team_id + " reached milestone!")
    }
  }
  // etc
}

不要维护里程碑表，因为这会使数据库非规范化。我认为这是一个不成熟的优化。只有当上述确实不够快时（例如，当每个player_id或team_id的统计数据超过几千行时），您才可以考虑维护里程碑表。