表中每组底部 N 行的列的平均值

发布于 2024-10-21 18:45:36 字数 454 浏览 5 评论 0原文

我想获得最后 5 行的列的平均值（如果玩家的行数少于 5 行（游戏），那么它应该不返回任何内容..）

如果我可以使用子查询，那么它会很简单，但是因为这不受支持，所以我不知道如何处理它。

下面的 sql 是我到目前为止所拥有的，如果玩家玩了 5 场以上的游戏，这将得到每个玩家所有游戏的平均值。

CREATE VIEW last_5_avg
AS
   SELECT player, avg(score) FROM game_stats
 JOIN games
ON games.id = games_stats.id GROUP BY player HAVING COUNT(games.id) > 4
ORDER BY games.id DESC;

看起来解决这个问题的唯一方法是为每个玩家的最后 5 个 game_stats 创建一个视图，然后使用第二个视图来计算平均值。

原文

I want to get the average of a column for the last 5 rows (if there are less than 5 rows (games) for a player then it should return nothing..)

If I could use a subquery then it would be straight forward, but since this isn't supported Im not sure how to approach it.

The sql below is what I have so far, this will get me the average for all games for each player if the player has played more than 5 games.

CREATE VIEW last_5_avg
AS
   SELECT player, avg(score) FROM game_stats
 JOIN games
ON games.id = games_stats.id GROUP BY player HAVING COUNT(games.id) > 4
ORDER BY games.id DESC;

Looks like the only way around this problem is to create a view for the last 5 game_stats of each player and then use a second view to do the averages.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泅人 2024-10-28 18:45:36

select player, avg(score) 
from game_stats
inner join games g0 on g0.id = games_stats.id 
where g0.id in (
    select g1.id
    from games g1
    where g1.id = g0.id
    order by g1.id desc
    limit 5
    )
group by player 
having count(g0.id) > 4
order by g0.id desc;

select player, avg(score) 
from game_stats
inner join games g0 on g0.id = games_stats.id 
where g0.id in (
    select g1.id
    from games g1
    where g1.id = g0.id
    order by g1.id desc
    limit 5
    )
group by player 
having count(g0.id) > 4
order by g0.id desc;

回复收藏 0 原文