Mysql在单个查询中将平均结果与当前结果进行比较

Question

我想知道是否可以运行一个查询来告诉我以下内容:

Get the names of any applications that have (currently executing) jobs exceeding the average historical runtime

表 1:工作历史

• 工作开始时间
• 工作结束时间
• 工作状态(成功、失败、正在执行等)
• 触发作业的应用程序 ID

表 2:应用

• 身份证
• 姓名

这似乎是一个简单的问题，但创建 mysql 查询已被证明是一个相当复杂(尽管很有趣)的挑战......

查询需要抓取satus = executing的jobs，使用application.id查看对于具有匹配的 application.id 和 status = success 的其他作业，平均 (end_time - start_time)，然后将平均时间与(current_time - start_time) 当前正在执行的作业。最后，它必须使用未通过此测试的任何作业的应用程序 ID 从应用程序表中获取 application.name。这甚至可以在单个查询中完成吗？

为了这个问题，我们假设 current_time 作为参数传入。

我尝试进行三重嵌套查询，但出现以下错误，我不知道为什么。我花了几个小时试图让这个工作，但我已经在我的头上了:

错误 1054 (42S22):“having 子句”中的未知列“jh.start_time”

这是我的尝试:

SELECT name FROM application
WHERE application.id IN (
    SELECT application_id 
    FROM job_history AS jh
    WHERE application_id IN (
        SELECT application_id 
        FROM job_history
        WHERE status='EXECUTION' )
    AND jh.status='SUCCESS'
    HAVING (avg(jh.end_time - jh.start_time)) < (current_time - jh.start_time)
    );

编辑:按照建议，这里是一些示例数据。

表一

+--------+------------+------------+----------+----------------+
| job_id |   status   | start_time | end_time | application_id |
+--------+------------+------------+----------+----------------+
| job1   | successful |        100 |      200 | app1           |
| job2   | failed     |        150 |      350 | app2           |
| job3   | successful |        200 |      400 | app1           |
| job4   | execution  |        500 |        0 | app1           |
| job5   | successful |        600 |      800 | app3           |
+--------+------------+------------+----------+----------------+

表2

+------+------------------+
|  id  |       name       |
+------+------------------+
| app1 | Team Green's app |
| app2 | Team Blue's app  |
| app3 | Team Red's app   |
+------+------------------+

我想获取job4，使用application_id 找到job1 和job3。然后取 job1 和 job3 的平均运行时间，并将其与 job4 的当前运行时间进行比较。如果当前运行时间大于平均运行时间，那么我要报告应用名称:Team Green's app。

Answer 1

如果我没有正确理解你的问题，这里有一个使用 join 的选项。从这里可以很容易地根据您的需要调整 where 标准:

select distinct a.name
from applications a 
    join jobhistory jh on a.id = jh.applicationid
    join (
      select applicationid, avg(endtime - starttime) avgtime
      from jobhistory
      where status = 'success'
      group by applicationid
    ) t on a.id = t.applicationid
where jh.status = 'execution' and 
  @current_time - jh.starttime > t.avgtime