MySQL:单列中每个 id 的平均值

Question

对于图表，我被要求使用 MYSQL 语句构建数据。它需要以下输出(3 列):

AVG_points |玩家ID |日期

在数据库中，我没有每轮 ID 的平均得分，只有每个玩家每轮得分。

使用 avg(point) 计算当前平均分很容易，但我需要每一轮的平均分，以便可以将其绘制在图表中。

我尝试编写一条 SQL 语句来给出每轮的平均值，但它没有以可用于绘制图表的格式显示。我阅读了 Pivoting，但这不是在这种情况下有效的方法，我认为我的 sql 太简单了，而且对于出现的每一个新回合，我需要编程更多行，这意味着手动编辑每个表更新以使图表正常工作。 .

这是我尝试过的:

SELECT   t1.playerid as player
    ,date_format(t1.CreatedTime, '%Y%m%d%H%i') as date

/* calculate average points per round */                

                ,(select avg(points) from pokermax_scores t2 where tournamentid <= (select distinct(tournamentid) from pokermax_scores order by tournamentid desc limit 0,1) and t2.playerid = t1.playerid) as avg_current
                ,(select avg(points) from pokermax_scores t2 where tournamentid <= (select distinct(tournamentid) from pokermax_scores order by tournamentid desc limit 1,1) and t2.playerid = t1.playerid) as 1_avg_last
                ,(select avg(points) from pokermax_scores t2 where tournamentid <= (select distinct(tournamentid) from pokermax_scores order by tournamentid desc limit 2,1) and t2.playerid = t1.playerid) as 2_avg_last
                ,(select avg(points) from pokermax_scores t2 where tournamentid <= (select distinct(tournamentid) from pokermax_scores order by tournamentid desc limit 3,1) and t2.playerid = t1.playerid) as 3_avg_last
                ,(select avg(points) from pokermax_scores t2 where tournamentid <= (select distinct(tournamentid) from pokermax_scores order by tournamentid desc limit 4,1) and t2.playerid = t1.playerid) as 4_avg_last
                ,(select avg(points) from pokermax_scores t2 where tournamentid <= (select distinct(tournamentid) from pokermax_scores order by tournamentid desc limit 5,1) and t2.playerid = t1.playerid) as 5_avg_last

FROM pokermax_scores as t1, pokermax_players as t3
GROUP BY player

给出以下输出:< SEE SQLFIDDLE LINK >

但我需要这种格式的数据，以便 PHP 可以正确循环它:

http://i57.tinypic.com/10wists.png

这里有没有 SQL 专家知道如何编辑我的语句以使其如上图所示？

感谢您阅读所有这些:)

这里是 SQLFIDDLE:http://sqlfiddle.com/#!9/a956f/2

Answer 1

目前尚不清楚您的数据到底是什么样的。以下似乎是一个很好的起点，因为它会以您想要的格式生成输出:

SELECT date(ps.CreatedTime) as date,
       ps.playerid as player,
       avg(ps.score)
FROM pokermax_scores ps
GROUP BY date(ps.CreatedTime), ps.playerid;

编辑:

评论有帮助。数据中没有任何称为“圆形”的内容。

我猜它是tournamentid。如果查询是其他内容，则很容易修改该查询。我认为你需要两个级别的聚合:

SELECT date, player, avg(score)
FROM (SELECT date(ps.CreatedTime) as date,
             ps.playerid as player, tournamentid,
             SUM(ps.points) as score
      FROM pokermax_scores ps
      GROUP BY date(ps.CreatedTime), ps.playerid, tournamentid
     ) dpt
GROUP BY date, player;

Here它在 SQL Fiddle 中。