MySQL Limit, Group and AVG Query

Question

Here is a puzzle for you:

I save cluster computing statistics in a MySQL table named "jobs". Each line of the task has a host performing the task (not unique), the time in seconds and a unique integer as a PC, so I can order completed tasks by simply ordering PK.

As of now, using the average value and the group, I can find the average execution time in seconds for each node for all completed tasks. Instead of averaging all the execution time per host, I want the average time of the last five jobs per host.

There are all kinds of examples for operations and grouping, as well as many examples for operations with limit, but is there a way to combine these two in a fairly simple MySQL query?

EDIT: in case I don't understand, I want an average of five runtimes for host 1 and average runtime for host 2, etc.

Answer 1

My initial reaction was to use LIMIT to limit the average to 5 results, which led me to the conclusion:

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;

But it’s clear that this limits the average for the last 5 jobs, not the most recent 5 jobs per host.

It seems difficult to use LIMIT to limit the average without using any stored procedure. This led me to consider assigning each job an order or host for each host using the mysql variable.

This is untested, but the theory he illustrates should be a good starting point:

-, :

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;

, , , , . .

Answer 2

, 1, 2 ..

Oh... :

SELECT x.host, AVG(x.execution_time)
  FROM (SELECT j.pk,
               j.host,
               j.execution_time,
               CASE 
                 WHEN @host != j.host THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @host := j.host
          FROM JOBS j
          JOIN (SELECT @rownum := 0; @host := '') r
      ORDER BY j.host, j.execution_time DESC) x
 WHERE x.rank <= 5
GROUP BY x.host

MySQL //, , ROW_NUMBER() OVER (PARTITION BY host ORDER BY execution_time DESC).

:

      SELECT AVG(j.execution_time) AS avg_last_five_jobs
        FROM JOBS j
        JOIN (SELECT t.pk
                    FROM JOBS t
             ORDER BY t.pk DESC
                    LIMIT 5) x ON x.pk = j.pk