代码之家  ›  专栏  ›  技术社区  ›  maček

如何用MySQL生成这两个报表?

  •  1
  • maček  · 技术社区  · 14 年前

    我的架构

    我有下列表格

    table             notes/example values
    ------------------------------------------------
    users (
      id
      email           # "foo@example.com"
    )                 
    
    games (           
      id              
      name            # "Space Invaders", "Asteroids", "Centipede"
    )                 
    
    players (         
      id              
      name            # "uber dude"
      user_id         # player belongs to user
      game_id         # player belongs to game
    )                 
    
    scores (          
      id              
      player_id       # belongs to one player
      value           # 50
      created_at      # "2010-09-10",   "2010-08-05"
      month           # "2010-09",      "2010-08"
    )
    

    我需要创建两个报告。

    1) 顶尖选手

    最近表现最好的玩家(每个玩家的所有分数相加) 4个月。显示每个月的前10名。

        2010-07         2010-08           2010-09    2010-10
     1  plyA 5,000 pts  plyB  9,400 pts   ...        ...
        Centipede       Solitaire
    
     2  plyB 3,600 pts  plyC  8,200 pts   ...        ...
        Asteroids       Centipede       
    
     3  plyC 2,900 pts  plyA  7,000 pts   ...        ...
        Centipede       Centipede
    
     4  ...             ...               ...        ...
     5  ...             ...               ...        ...
     6  ...             ...               ...        ...
     7  ...             ...               ...        ...
     8  ...             ...               ...        ...
     9  ...             ...               ...        ...
    10  ...             ...               ...        ...
    

    2) 顶级用户:

    表现最好的用户(每个用户的每个玩家的所有分数之和)

        2010-07           2010-08             2010-09    2010-10
     1  userA 50,000 pts  userB 51,400 pts    ...        ...
     2  userB 40,500 pts  userA 39,300 pts    ...        ...
     3  userC 40,200 pts  userC 37,000 pts    ...        ...
     4  ...               ...                 ...        ...
     5  ...               ...                 ...        ...
     6  ...               ...                 ...        ...
     7  ...               ...                 ...        ...
     8  ...               ...                 ...        ...
     9  ...               ...                 ...        ...
    10  ...               ...                 ...        ...
    

    MySQL视图助手

    为了便于加入,我有一个存储视图来帮助查询报表的月份。它总是会在最近的4个月内返回。

    report_months (
      month
    )
    
    SELECT * FROM report_months;
    
    2010-07
    2010-08
    2010-09
    2010-10
    

    问题

    例如,在报告1中,我可以很容易地得到总数。

    select
      p.name        as player_name,
      g.name        as game_name,
      s.month       as month,
      sum(s.score)  as sum_score
    
    from players  as p
    
    join games    as g
      on g.id = p.game_id
    
    join scores   as s
      on s.player_id = p.id
    
    join report_months as rm  -- handy view helper
      on rm.month = s.month
    
    group by
      p.name, g.name
    
    order by
      sum(s.score) desc
    
    -- I can't do this :(
    -- limit 0, 40
    

    然而,我不能简单地获取前40名的结果,并将它们分散到4个月,因为这并不能保证每个月我有10个结果。

    问题

    如何修改查询以确保每月获得10个?

    1 回复  |  直到 14 年前
        1
  •  2
  •   Bill Karwin    14 年前

    我不会像您所展示的那样尝试进行按月制表的SQL查询。

    取而代之的是,将每月排名前10位的玩家查询为行,而不是列:

    Month    Rank  Player  TotalScore  Game
    2010-07     1    plyA   5,000 pts  Centipede
    2010-07     2    plyB   3,600 pts  Asteroids
    2010-07     3    plyC   2,900 pts  Centipede
    ...
    2010-08     1    plyB   9,400 pts  Solitaire
    2010-08     2    plyC   8,200 pts  Centipede
    2010-08     3    plyA   7,000 pts  Centipede
    ...
    

    这就变成了 greatest-n-per-group n 是10。

    CREATE VIEW PlayerScoresByMonth AS
      SELECT month, player_id, SUM(value) AS score
      FROM scores
      GROUP BY month, player_id;
    
    SELECT s1.month, COUNT(s2.month)+1 AS Rank, s1.player_id, s1.score AS TotalScore
    FROM PlayerScoresByMonth s1
    LEFT OUTER JOIN PlayerScoresByMonth s2 ON s1.month = s2.month 
      AND (s1.score < s2.score OR s1.score = s2.score AND s1.player_id < s2.player_id)
    GROUP BY s1.month, s1.player_id
    HAVING COUNT(*) < 10
    ORDER BY s1.month, Rank;
    

    (这是未经测试的,但应该可以让您开始)