mysql multiple grouping on one table

Question

I have one table job_result

CREATE TABLE job_result  (
  node varchar(20) DEFAULT NULL,
  jobId int(10) DEFAULT NULL,
  subResult int(1) DEFAULT NULL
) 

The table contains many nodes.

Each node has many jobId.

Each jobId has many subResults (usually less than 10).

Some example data provided

insert into job_result values ('A', 14, 0);
insert into job_result values ('A', 15, 0);
insert into job_result values ('A', 16, 1);
insert into job_result values ('A', 17, 0);
insert into job_result values ('A', 18, 1);
insert into job_result values ('A', 19, 1);
insert into job_result values ('A', 20, 0);


insert into job_result values ('B', 1, 0);
insert into job_result values ('B', 2, 0);
insert into job_result values ('B', 3, 1);
insert into job_result values ('B', 4, 1);
insert into job_result values ('B', 5, 1);
insert into job_result values ('B', 6, 1);
insert into job_result values ('B', 7, 1);


insert into job_result values ('C', 10, 0);
insert into job_result values ('C', 11, 0);
insert into job_result values ('C', 12, 0);
insert into job_result values ('C', 13, 0);
insert into job_result values ('C', 14, 0);
insert into job_result values ('C', 15, 0);
insert into job_result values ('C', 16, 0);

I want a query to give me a "health check" on each node to see if a node has had X consecutively failed jobs. A failed job is a jobId that have at least one non-zero result.

SELECT node, jobId, SUM(subResult) res
FROM job_result WHERE node = 'A' 
GROUP BY jobId 
ORDER BY jobId desc 
LIMIT 5

This gives me the last 5 job's result on node A, where 0 indicates a good job.

+------+---------+------+
| node | jobId   | res  |
+------+---------+------+
| A    |   20    |   0  |
| A    |   19    |   1  |
| A    |   18    |   1  |
| A    |   17    |   0  |
| A    |   16    |   1  |

Then I need to sum and group the results into one row per node and I got this far:

SELECT node, SUM(I.res) res
FROM
(SELECT node, sum(subResult) res
FROM job_result WHERE node = 'A' 
GROUP BY jobId 
ORDER BY jobId desc 
LIMIT 5) I

+------+------+
| node | res  |
+------+------+
| A    |  3   |

My question is: How can I expand this query to return one row per node? I have tried for several days with correlated sub queries and joins, but I always fail due to the inner select cannot see which node to select on. And when that is in place, I will work on changing LIMIT 5 to a dynamic value.

Desired result would be like

+------+------+
| node | res  |
+------+------+
| A    |  3   |
| B    |  0   |
| C    |  1   |
| E    |  0   |

One row for each node, with the right column showing number of fails

Please advise!

Answer 1

In MySQL, on a large table, the best approach to getting the last 5 is to use variables to enumerate the results. The rest is just aggregation:

SELECT node, SUM(subr > 0) as numfails
FROM (SELECT node, jobid, subr,
             (@rn := if(@n = node, @rn + 1,
                        if(@n := node, 1, 1)
                       )
             ) as rn
      FROM (SELECT node, jobid,
                   SUM(subresult) as subr
            FROM job_result jr
            GROUP BY node, jobid
           ) jr CROSS JOIN
           (SELECT @n := '', @rn := 0) init
      ORDER BY node, jobid desc
     ) nr
WHERE rn <= 5
GROUP BY node;