Oracle SQL：按坐席统计服务解决耗时（天/时/分）的正确实现

问题说明 #

需要统计每个坐席的三类信息：

• 坐席名称resolved_by
• 解决的服务数量services_resolved
• 所有已解决服务的上报时间creation_date到解决时间last_resolved_date的总耗时，需以天、小时、分钟的规范格式展示

但原查询返回的结果不符合时间进制逻辑（比如出现14 Days 327 Hours 19598 Minutes），核心问题是原代码直接将总天数转换为小时、分钟，未做进制拆分，导致小时数超过24、分钟数超过60。

错误原因分析 #

原代码的计算逻辑存在本质问题：

• 直接对总天数求和后乘24得到的是所有耗时的总小时数（包含了天数对应的小时），并非剩余小时
• 同理，总分钟数是把总天数直接转成分钟，没有扣除天和小时对应的部分，自然会出现远超60的数值

正确逻辑应该是先计算所有服务的总耗时分钟数，再基于总分钟数拆分出天、剩余小时、剩余分钟。

正确实现方案 #

以下是修正后的SQL代码（适配Oracle数据库，原查询中ORA_SVC_RESOLVED标识可佐证）：

SELECT
    resolved_by,
    COUNT(resolved_by) AS services_resolved,
    -- 按时间进制拆分总分钟数为天、小时、分钟
    FLOOR(total_minutes / 1440) || ' Days ' ||
    FLOOR(MOD(total_minutes, 1440) / 60) || ' Hours ' ||
    MOD(total_minutes, 60) || ' Minutes' AS total_effort
FROM (
    -- 先计算单条服务耗时分钟数，再按坐席求和得到总分钟数
    SELECT
        resolved_by,
        SUM(
            ROUND(
                (CAST(last_resolved_date AS DATE) - CAST(creation_date AS DATE)) * 24 * 60,
                0
            )
        ) AS total_minutes
    FROM svc_service_requests
    WHERE status_type_cd = 'ORA_SVC_RESOLVED'
    GROUP BY resolved_by
) t
ORDER BY resolved_by;

代码细节说明 #

1. 子查询层：

   • 显式将日期字段转换为DATE类型，替代原代码中+0的隐式转换，避免类型异常
   • 单条服务耗时计算：(结束日期 - 开始日期)得到天数，乘24*60转为分钟后取整
   • 按坐席分组求和，得到每个坐席的总耗时分钟数

2. 外层查询层：

   • FLOOR(total_minutes / 1440)：总分钟数除以1440（一天的分钟数）取整，得到完整天数
   • FLOOR(MOD(total_minutes, 1440) / 60)：总分钟数取余1440得到剩余分钟，再除以60取整，得到剩余小时
   • MOD(total_minutes, 60)：总分钟数取余60，得到最终剩余分钟
   • 拼接成符合时间逻辑的规范格式

修改后即可得到正常结果，比如总耗时会展示为14 Days 11 Hours 18 Minutes，而非原查询的异常数值。

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