MySQL 限制、组和 AVG 查询

这里有一个谜题给你：

我将集群计算的统计数据保存在名为“jobs”的 MySQL 表中。每个作业行都有一个执行作业的主机（不是唯一的）、以秒为单位的作业执行时间以及作为 PK 的唯一整数，因此我可以简单地通过订购 PK 来订购已完成的作业。

截至目前，使用平均值和分组依据，我可以找到每个主机在所有已完成作业中的平均执行时间（以秒为单位）。我想要的是每个主机最后五个作业的平均时间，而不是平均每个主机的所有执行时间。

有各种各样的操作和分组示例，还有很多限制操作的示例，但是有没有办法将这两者结合到一个相当简单的 MySQL 查询中？

编辑：如果我不清楚，我想要主机 1 的平均 5 次执行时间，以及主机 2 的平均 5 次执行时间，等等。

我最初的反应是使用 LIMIT 将平均值限制为 5 个结果，这让我建议：

select a.host, avg(a.execution_time) from (select id, execution_time, host from jobs order by id desc limit 5) a group by a.host;

但很明显，这将平均值限制为最近 5 个作业，而不是每个主机最近的 5 个作业。

在不使用某种存储过程的情况下，使用 LIMIT 来限制平均值似乎很困难。这导致我考虑使用 mysql 变量为每个作业分配每个主机的完成顺序或位置。

这是未经测试的，但它所阐述的理论应该是一个很好的起点：

首先，我们应该根据每个作业的宿主为其分配一个位置：

select
  host, 
  execution_time,
  @current_pos := if (@current_host = host, @current_pos, 0) + 1 as position,
  @current_host := host
from
  (select @current_host := null, @current_pos := 0) set_pos,
  jobs
order by
  host,
  id desc;

建立位置后，只需选择聚合函数，将结果限制为前 5 个位置：

select
  jt.host,
  avg(jt.execution_time)
from
  (
  select
    host, 
    execution_time,
    @current_pos := if (@current_host = host, @current_pos, 0) + 1 as position,
    @current_host := host
  from
    (select @current_host := null, @current_pos := 0) set_pos,
    jobs
  order by
    host,
    id desc
  ) jt
where
  jt.position <= 5
group
  by host;

请告诉我这是否适合您，或者是否还有其他我没有考虑到的方面。这是一个有趣的问题。