多次关联同表的任务统计SQL查询优化咨询

你的原始查询思路没问题，但多次子查询关联同一个TaskStatusHistories表（还要每次都关联其他表），会让数据库反复扫描相同的数据，性能开销很大。我来帮你重构这个查询，用更高效的方式实现相同的逻辑。

优化后的查询语句 #

SELECT
    AvergaeTimeToAccept = AVG(DATEDIFF(s, AllocatedTime, AcceptedTime)),
    AvergaeTimeToStart = AVG(DATEDIFF(s, AcceptedTime, StartedTime)),
    AvergaeTimeToComplete = AVG(DATEDIFF(s, StartedTime, CompletedTime))
FROM (
    SELECT
        -- 统一获取人员GUID：Allocated状态用TaskAllocationHistories的，其他用TaskResponses的
        COALESCE(th.PersonGUID, r.PersonGUID) AS PersonGUID,
        h.TaskID1,
        -- 用条件聚合提取每个状态的最早时间
        AllocatedTime = MIN(CASE WHEN t.Status = 'Allocated' THEN h.TimeSet END),
        AcceptedTime = MIN(CASE WHEN t.Status = 'Accepted' THEN h.TimeSet END),
        StartedTime = MIN(CASE WHEN t.Status = 'Started' THEN h.TimeSet END),
        CompletedTime = MIN(CASE WHEN t.Status = 'Completed' THEN h.TimeSet END)
    FROM TaskStatusHistories h
    JOIN TaskStatuses t ON h.StatusID = t.ID
    -- 左连接：只有Allocated状态需要关联TaskAllocationHistories
    LEFT JOIN TaskAllocationHistories th 
        ON h.TaskStatusHistoryID = th.TaskStatusHistoryID 
        AND t.Status = 'Allocated'
    -- 左连接：其他状态关联TaskResponses
    LEFT JOIN TaskResponses r 
        ON h.TaskID1 = r.TaskID 
        AND r.ResponseID = h.StatusID 
        AND t.Status IN ('Accepted', 'Started', 'Completed')
    -- 只过滤我们需要的状态，减少处理的数据量
    WHERE t.Status IN ('Allocated', 'Accepted', 'Started', 'Completed')
    -- 按任务+人员分组，确保每个组对应同一个人处理的同一个任务
    GROUP BY h.TaskID1, COALESCE(th.PersonGUID, r.PersonGUID)
    -- 过滤掉缺少任一关键状态的记录，和原查询逻辑保持一致
    HAVING 
        AllocatedTime IS NOT NULL
        AND AcceptedTime IS NOT NULL
        AND StartedTime IS NOT NULL
        AND CompletedTime IS NOT NULL
) AS times

优化关键点解析 #

1. 减少表扫描次数：原查询需要4次扫描TaskStatusHistories及其关联表，优化后只需要1次完整扫描，直接降低了IO和CPU开销。
2. 条件聚合替代多次关联：用CASE WHEN配合MIN()函数，在同一个分组里直接提取各状态的最早时间，避免了多次子查询和复杂的关联操作。
3. 统一人员标识：通过COALESCE合并两个表的人员GUID，确保分组的正确性——毕竟Allocated状态的人员信息存在TaskAllocationHistories，其他状态在TaskResponses里。
4. 保留原始业务逻辑：HAVING子句过滤掉状态不完整的任务，和你原来只统计完整流转流程的逻辑完全一致。

额外性能建议 #

• 给TaskStatusHistories建立联合索引：(TaskID1, StatusID, TimeSet)，这个索引能直接加速分组和聚合操作，让数据库快速定位每个任务+状态的最早时间。
• 检查TaskStatuses的Status字段是否有索引，如果经常用状态过滤数据，这个索引能帮数据库快速筛选出需要的记录。
• 确保TaskAllocationHistories的TaskStatusHistoryID和PersonGUID，以及TaskResponses的TaskID、ResponseID、PersonGUID都有合适的索引，提升关联效率。

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