如何合并SQL查询并简化指定周期任务平均完成时长计算

问题背景 #

需要计算指定周期（示例：2022-07-01 至 2022-07-23）内任务的平均完成时长，数据表包含Received_date和Completed_date两个时间戳列，数据样例如下：

现有两个可正常运行的查询，分别计算秒级平均时长和格式化后的时长，需合并为一个查询并简化实现。

合并后的单查询实现 #

通过子查询直接计算平均秒数，再在同一查询中完成时长格式化，无需单独定义变量：

SELECT 
    CAST(DATEPART(MONTH, DATEADD(SECOND, avg_seconds, 0)) - 1 AS VARCHAR(2)) + ' month(s) ' +
    CAST(DATEPART(DAY, DATEADD(SECOND, avg_seconds, 0)) - 1 AS VARCHAR(2)) + ' day(s) ' +
    CAST(DATEPART(HOUR, DATEADD(SECOND, avg_seconds, 0)) AS VARCHAR(2)) + ' hour(s) ' +
    CAST(DATEPART(MINUTE, DATEADD(SECOND, avg_seconds, 0)) AS VARCHAR(2)) + ' minute(s) ' +
    CAST(DATEPART(SECOND, DATEADD(SECOND, avg_seconds, 0)) AS VARCHAR(2)) + ' second(s)' 
    AS '指定周期内任务完成平均时长',
    avg_seconds AS '请求完成平均时长（秒）'
FROM (
    SELECT AVG(ABS(DATEDIFF(SECOND, Completed_date, Received_date))) AS avg_seconds
    FROM Mytable
    WHERE Completed_date >= '2022-07-01 00:00' AND Completed_date <= '2022-07-23 23:59'
) AS avg_calc

输出示例 #

更简便的格式化方式（SQL Server） #

利用FORMAT函数替代手动字符串拼接，代码更简洁易维护：

SELECT 
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        FORMAT(DATEADD(SECOND, avg_seconds, 0), 'M month(s) d day(s) H hour(s) m minute(s) s second(s)'),
        ' 1 month(s)', ' 1 month(s)'),
        ' 1 day(s)', ' 1 day(s)'),
        ' 1 hour(s)', ' 1 hour(s)'),
        ' 1 minute(s)', ' 1 minute(s)'),
        ' 1 second(s)', ' 1 second(s)') AS '指定周期内任务完成平均时长',
    avg_seconds AS '请求完成平均时长（秒）'
FROM (
    SELECT AVG(ABS(DATEDIFF(SECOND, Completed_date, Received_date))) AS avg_seconds
    FROM Mytable
    WHERE Completed_date >= '2022-07-01 00:00' AND Completed_date <= '2022-07-23 23:59'
) AS avg_calc

说明 #

• 子查询负责计算指定周期内的平均完成秒数，外层查询直接将秒数转换为格式化的时长字符串，一次查询满足两个需求。
• FORMAT函数可快速生成标准化时长格式，若需调整输出样式（如去掉前导零），仅需修改格式字符串即可。

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