MySQL Limit, Group, and AVG Query -

here's puzzler you:

i'm keeping stats of cluster computing stuff in mysql table named 'jobs'. each job row has host job executed on (not unique), job execution time in seconds, , unique integer pk can order completed jobs ordering pk.

as of right now, using average , group by, can find average execution time in seconds each host on of jobs completed. instead of averaging execution times per host, want average time of last 5 jobs per host.

there's sorts of examples operations , group by, , lots of examples operations limit, there way of combining 2 in straightforward mysql query?

edit: in event i'm not clear it, want average 5 execution times host 1, , average 5 execution times host 2, etc.

my initial reaction use limit restrict average 5 results, led me suggest:

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

but clear limits average recent 5 jobs, , not recent 5 jobs per host.

it seems difficult use limit restrict average, without using kind of stored procedure. led me consider assigning each job per-host completion order, or position, using mysql variable.

this untested, theory illustrates should starting point:

first, should assign each job position based on host:

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

after establishing position, select aggregate function, restricting results top 5 positions:

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

please let me know if works you, or if there more aspects have not considered. intriguing problem.