mysql在一个表上进行多个分组
I have one table job_result
我有一个表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.
每个节点都有许多jobId。
Each jobId has many subResults (usually less than 10).
每个jobId都有许多subResults(通常少于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.
我想要一个查询,在每个节点上给我一个“运行状况检查”,看看一个节点是否有X个连续失败的作业。失败的作业是至少有一个非零结果的jobId。
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.
这给了我节点A上最后5个作业的结果,其中0表示一个好工作。
+------+---------+------+
| 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.
我的问题是:如何扩展此查询以返回每个节点一行?我已经尝试了几天与相关的子查询和连接,但我总是失败,因为内部选择无法看到选择哪个节点。当它到位时,我将努力将LIMIT 5更改为动态值。
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!
1 个解决方案
#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:
在MySQL中,在大型表上,获得最后5个的最佳方法是使用变量来枚举结果。其余的只是聚合:
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;
更多相关文章
- 尝试使用PHP和MySQL获取节点的路径
- 如何从一个节点生成exe文件。js应用?
- 前端笔记之JavaScript(十)深入JavaScript节点&DOM&事件
- js的html元素的父节点,子节点
- 不使用chokidar更新所需的节点/快速模块?
- 当尝试安装节点时,会得到一个“DLL”错误。js在Windows 7上
- contains和compareDocumentPosition 方法来确定是否HTML节点间的
- 如何访问远程节点。浏览器中的js应用程序,而不是本地主机
- js之DOM操作(访问父节点parentNode)