我有一个表,Jobs,其中每一行代表一个由计算机程序执行的维护任务.它有这样的设计：
CREATE TABLE Jobs (
JobId bigint PRIMARY KEY,...
Status int NOT NULL,OriginalJobId bigint NULL
)
创建/启动作业时,其行将添加到表中,其状态为0.作业完成后,其状态将更新为1,当作业失败时,其状态将更新为2.当作业失败时,job-manager将通过复制失败作业的详细信息并将Status重置为0并在OriginalJobId中使用原始(失败的)JobId进行跟踪来将新行插入Jobs表中来重试该作业.如果此重新尝试失败,则应再次尝试最多3次,每次后续重试都将在OriginalJobId列中保留原始JobId.
我的问题是尝试制定一个查询来获取当前失败的作业集并获得重试次数.
这是表格中的示例数据：
JobId | Status | OriginalJobId
1,1,NULL -- Successful initial job
2,NULL -- Pending initial job
3,2,NULL -- Failed initial job
4,3 -- Successful retry of Job 3
5,NULL -- Failed initial job
6,5 -- Failed retry 1 of Job 5
7,5 -- Failed retry 2 of Job 5 -- should be tried again for 1 more time
8,NULL -- Failed initial job
9,8 -- Failed retry 1 of Job 8
10,8 -- Failed retry 2 of Job 8
11,8 -- Failed retry 3 of Job 8 -- don't try again
12,NULL -- Failed initial job
我的查询需要返回：
JobId | RetryCount
5,2
12,0
请注意如何不包括Job 3,因为它的上次重试成功(状态1).类似地,排除作业8,因为重试次数超过了限制3.包括作业5,因为它仍然失败并且仅重试了2次,并且包括作业12并且还没有重试.
我在想解决方案是这样的：
SELECT
J1.JobId
FROM
Jobs AS J1
LEFT OUTER JOIN Jobs AS J2 ON J1.JobId = J2.OriginalJobId
WHERE
J1.Status = 2
…但我想不出如何获取RetryCount数据.
这是我为这个问题创建的sqlFiddle,其中一个解决方案如下：
http://sqlfiddle.com/#!6/8765f
更新
这是一个更新的sqlFiddle,它比较了目前为止提供的5个解决方案(我添加了一个额外的HAVING子句来删除重试次数超过3次的作业)
http://sqlfiddle.com/#!6/8765f/23
性能方面,我认为GarethD的答案是最好的,因为它具有最简单的执行计划,并且倾向于以最快的时间在sqlFiddle中完成.
我的生产表有大约14,000,000行,所以显然结果会有所不同.我会在生产中尝试每一个,看看哪个是最快的,然后相应地选择答案.
谢谢大家的帮助！
解决方法
SELECT J1.JobId,Retries = COUNT(J2.JobId)
FROM Jobs AS J1
INNER JOIN Jobs AS J2
ON J1.JobId = J2.OriginalJobId
WHERE J1.Status = 2
GROUP BY J1.JobId
HAVING COUNT(CASE WHEN J2.Status = 1 THEN 1 END) = 0;
我已将其更改为INNER连接,以便仅包含已重试的作业,但这可以切换回LEFT联接以包括尚未重试的失败作业.我还添加了一个HAVING子句来排除重试时没有失败的任何作业.
编辑
如上所述,使用INNER JOIN意味着您只返回已重试的作业,以获取您需要使用LEFT JOIN的所有失败作业,这将意味着重试作为失败的作业返回,因此我添加了一个额外的谓词J1.OriginalJobId为NULL以确保仅返回原始作业：
SELECT J1.JobId,Retries = COUNT(J2.JobId)
FROM Jobs AS J1
LEFT JOIN Jobs AS J2
ON J1.JobId = J2.OriginalJobId
WHERE J1.Status = 2
AND J1.OriginalJobId IS NULL
GROUP BY J1.JobId
HAVING COUNT(CASE WHEN J2.Status = 1 THEN 1 END) = 0;
