SQL Server 2017中如何计算各状态平均耗时并输出天小时格式

实现方案 #

以下是基于常见数据库的查询实现，你可以根据自己使用的数据库类型选择对应写法：

单条记录对应一个状态的场景（耗时为记录创建到结束的时长） #

MySQL 写法

SELECT 
    status,
    CONCAT(
        FLOOR(AVG(TIMESTAMPDIFF(HOUR, created_at, IFNULL(end_time, NOW())))/24), '天',
        MOD(FLOOR(AVG(TIMESTAMPDIFF(HOUR, created_at, IFNULL(end_time, NOW())))), 24), '小时'
    ) AS avg_cost_time
FROM 你的表名
-- 可补充自定义筛选条件
GROUP BY status;

PostgreSQL 写法

SELECT 
    status,
    CONCAT(
        FLOOR(EXTRACT(EPOCH FROM AVG(COALESCE(end_time, NOW()) - created_at)) / 86400)::INT, '天',
        FLOOR((EXTRACT(EPOCH FROM AVG(COALESCE(end_time, NOW()) - created_at)) % 86400) / 3600)::INT, '小时'
    ) AS avg_cost_time
FROM 你的表名
GROUP BY status;

状态流转耗时计算场景（计算同一业务单从上一个状态流转到当前状态的平均耗时，MySQL8.0+支持） #

WITH status_with_prev AS (
    SELECT 
        *,
        LAG(created_at) OVER (PARTITION BY 业务ID字段 ORDER BY created_at) AS prev_status_time
    FROM 你的表名
)
SELECT 
    status,
    CONCAT(
        FLOOR(AVG(TIMESTAMPDIFF(HOUR, prev_status_time, created_at))/24), '天',
        MOD(FLOOR(AVG(TIMESTAMPDIFF(HOUR, prev_status_time, created_at))), 24), '小时'
    ) AS avg_cost_time
FROM status_with_prev
WHERE prev_status_time IS NOT NULL
GROUP BY status;

逻辑说明 #

• 先计算单条记录对应状态的耗时，统一转换为小时单位保证精度
• 按状态分组后用AVG()计算该组所有记录的平均耗时
• 对平均小时数做整除和取余运算，拆分出天数和剩余小时数
• 最后用字符串拼接函数组合成你需要的「X天Y小时」输出格式

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