如何按AssignedTo统计工单响应、完成时间区间的计数与占比？

你已经有了不错的开头！要补充占比和完成时间的统计，核心思路是先算出每个用户的总工单数，然后用各区间的计数除以总工单数得到占比；完成时间的统计逻辑和响应时间完全一致，直接复用模式即可。

针对示例数据集的完整SQL（双区间版本） #

先结合你给出的示例数据，写出能输出期望结果的SQL：

WITH TicketMetrics AS (
    SELECT 
        AssignedTo,
        -- 计算响应时间（示例中已为分钟单位，直接做差）
        (HandleTime - CreatedTime) AS PickupTime,
        -- 计算完成时间（示例中已为分钟单位）
        (FinishTime - CreatedTime) AS CompletedTime
    FROM TicketTable
)
SELECT 
    AssignedTo,
    -- 响应时间区间计数
    SUM(CASE WHEN PickupTime <= 1 THEN 1 ELSE 0 END) AS Pickup_range1_count,
    SUM(CASE WHEN PickupTime > 1 THEN 1 ELSE 0 END) AS Pickup_range2_count,
    -- 响应时间区间占比：区间计数/总工单数，保留两位小数
    ROUND(SUM(CASE WHEN PickupTime <= 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS Pickup_range1_percentage,
    ROUND(SUM(CASE WHEN PickupTime > 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS Pickup_range2_percentage,
    -- 完成时间区间计数
    SUM(CASE WHEN CompletedTime <= 1 THEN 1 ELSE 0 END) AS Complete_range1_count,
    SUM(CASE WHEN CompletedTime > 1 THEN 1 ELSE 0 END) AS Complete_range2_count,
    -- 完成时间区间占比
    ROUND(SUM(CASE WHEN CompletedTime <= 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS Complete_range1_percentage,
    ROUND(SUM(CASE WHEN CompletedTime > 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS Complete_range2_percentage,
    -- 总工单数
    COUNT(*) AS Total_Tickets
FROM TicketMetrics
GROUP BY AssignedTo
ORDER BY AssignedTo;

关键逻辑说明 #

• 用WITH子句（CTE）把时间差的计算抽离出来，让主查询更清晰，你原来的子查询写法也完全可行，CTE只是提升了可读性。
• COUNT(*)用来获取每个用户的总工单数，这是计算占比的核心分母。
• ROUND()函数用来格式化占比的小数位数，你可以根据需求调整保留的位数（比如改成1位或者不保留）。
• 每个区间的计数通过SUM(CASE...)实现，占比就是对应区间的计数除以总工单数。

扩展到你最初要求的三区间版本 #

如果要适配你最初提到的<1分钟、1-2分钟、2-5分钟三个区间，只需要调整CASE条件即可（注意原表时间戳是毫秒级，所以要除以60000转成分钟）：

WITH TicketMetrics AS (
    SELECT 
        AssignedTo,
        -- 原表为毫秒级时间戳，转成分钟
        (HandleTime - CreatedTime)/60000 AS PickupTime,
        (FinishTime - CreatedTime)/60000 AS CompletedTime
    FROM TicketTable
)
SELECT 
    AssignedTo,
    -- 响应时间区间计数
    SUM(CASE WHEN PickupTime < 1 THEN 1 ELSE 0 END) AS Pickup_less1_count,
    SUM(CASE WHEN PickupTime >= 1 AND PickupTime < 2 THEN 1 ELSE 0 END) AS Pickup_1to2_count,
    SUM(CASE WHEN PickupTime >= 2 AND PickupTime < 5 THEN 1 ELSE 0 END) AS Pickup_2to5_count,
    -- 响应时间区间占比
    ROUND(SUM(CASE WHEN PickupTime < 1 THEN 1 ELSE 0 END)/COUNT(*),2) AS Pickup_less1_percent,
    ROUND(SUM(CASE WHEN PickupTime >=1 AND PickupTime <2 THEN 1 ELSE 0 END)/COUNT(*),2) AS Pickup_1to2_percent,
    ROUND(SUM(CASE WHEN PickupTime >=2 AND PickupTime <5 THEN 1 ELSE 0 END)/COUNT(*),2) AS Pickup_2to5_percent,
    -- 完成时间区间计数
    SUM(CASE WHEN CompletedTime <1 THEN 1 ELSE 0 END) AS Complete_less1_count,
    SUM(CASE WHEN CompletedTime >=1 AND CompletedTime <2 THEN 1 ELSE 0 END) AS Complete_1to2_count,
    SUM(CASE WHEN CompletedTime >=2 AND CompletedTime <5 THEN 1 ELSE 0 END) AS Complete_2to5_count,
    -- 完成时间区间占比
    ROUND(SUM(CASE WHEN CompletedTime <1 THEN 1 ELSE 0 END)/COUNT(*),2) AS Complete_less1_percent,
    ROUND(SUM(CASE WHEN CompletedTime >=1 AND CompletedTime <2 THEN 1 ELSE 0 END)/COUNT(*),2) AS Complete_1to2_percent,
    ROUND(SUM(CASE WHEN CompletedTime >=2 AND CompletedTime <5 THEN 1 ELSE 0 END)/COUNT(*),2) AS Complete_2to5_percent,
    -- 总工单数
    COUNT(*) AS Total_Tickets
FROM TicketMetrics
GROUP BY AssignedTo
ORDER BY AssignedTo;

额外注意事项 #

• 如果你的时间戳是秒级而非毫秒级，记得把/60000改成/60。
• 若担心出现总工单数为0的场景（比如加了过滤条件后），可以用NULLIF(COUNT(*),0)作为分母避免报错：SUM(...) / NULLIF(COUNT(*),0)，此时总单数为0时占比会显示NULL而非报错。
• 不同数据库的ROUND函数语法基本一致，若用PostgreSQL也可以用CAST(...) AS DECIMAL(5,2)来格式化占比。

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