MySQL中SELECT多聚合查询：按Job_Type统计平均总工时

数据表结构 #

我有一张结构如下的数据表（Start_Time与End_Time为时间戳秒数）：

需求 #

需要编写一条SELECT语句，完成以下两步计算：

1. 计算每个Job_ID的总工作时长（End_Time - Start_Time的求和值）
2. 按Job_Type分组，计算该类型下所有Job_ID总工时的平均值

预期输出：

现有方案问题 #

实际场景中Job_Type不止两种，且处于JDBC环境，无法使用创建用户变量等操作。目前通过硬编码每个任务类型结合子查询的方式实现，但该方案不具备动态性与可扩展性，现有代码如下：

SELECT
'A' AS `Job_Type`,
ROUND(
    (
        SELECT SUM(End_time - Start_time) FROM MyTable
        WHERE Job_Type = 'A'
    )
    /
    (
        SELECT COUNT(Job_ID) FROM MyTable
        WHERE Job_Type = 'A'
    )
) AS `Avg_Total_Time`

UNION

SELECT
'B' AS `Job_Type`,
ROUND(
    (
        SELECT SUM(End_time - Start_time) FROM MyTable
        WHERE Job_Type = 'B'
    )
    /
    (
        SELECT COUNT(Job_ID) FROM MyTable
        WHERE Job_Type = 'B'
    )
) AS `Avg_Total_Time`

优化方案 #

可以通过嵌套子查询或**CTE（公共表表达式）**实现动态计算，无需硬编码Job_Type：

方法1：嵌套子查询 #

先计算每个Job_ID的总工时，再按Job_Type分组求平均：

SELECT
    Job_Type,
    ROUND(AVG(job_total_time)) AS Avg_Total_Time
FROM (
    SELECT
        Job_ID,
        Job_Type,
        SUM(End_Time - Start_Time) AS job_total_time
    FROM MyTable
    GROUP BY Job_ID, Job_Type
) AS job_totals
GROUP BY Job_Type;

方法2：CTE（支持CTE的数据库如MySQL 8+、PostgreSQL等） #

如果数据库支持CTE，代码可读性更强：

WITH job_totals AS (
    SELECT
        Job_ID,
        Job_Type,
        SUM(End_Time - Start_Time) AS job_total_time
    FROM MyTable
    GROUP BY Job_ID, Job_Type
)
SELECT
    Job_Type,
    ROUND(AVG(job_total_time)) AS Avg_Total_Time
FROM job_totals
GROUP BY Job_Type;

方案说明 #

1. 内层查询（或CTE）先按Job_ID和Job_Type分组，计算每个任务ID的总工时job_total_time
2. 外层查询按Job_Type分组，对每个类型下的job_total_time取平均值并四舍五入，得到最终结果
3. 该方案会自动适配所有Job_Type，无需手动添加UNION和硬编码类型，具备完全的动态性和可扩展性

内容的提问来源于stack exchange，提问作者Josh