计算Stalking表Agent对应Assigned表指定时段Stalking任务占比的SQL问题

需求与表结构 #

统计Stalking表中每个Agent的记录数，与Assigned表中该Agent在Stalking表stalkDate最小到最大时段内task为'Stalking'的记录数的占比。

• Assigned表：包含Agent、task、taskDate字段，记录各Agent的任务及执行时间
• Stalking表：包含Agent、stalkDate字段，stalkDate范围固定在2020-01-31至2022-12-31

现存问题与解决方案 #

修正后的SQL语句 #

WITH StalkingAgentMetrics AS (
    SELECT
        Agent,
        COUNT(*) AS total_stalk_records,
        MIN(stalkDate) AS earliest_stalk_date,
        MAX(stalkDate) AS latest_stalk_date
    FROM Stalking
    GROUP BY Agent
)
SELECT
    sam.Agent,
    CASE
        -- 针对mrEnglish的除零特殊处理：无符合条件的Assigned记录时设为100%
        WHEN sam.Agent = 'mrEnglish' AND COALESCE(asm.stalking_task_count, 0) = 0 THEN 100.00
        -- 其他Agent无Assigned记录时设为0%
        WHEN COALESCE(asm.stalking_task_count, 0) = 0 THEN 0.00
        -- 正常计算占比：(符合条件的任务数 / Stalking总记录数) * 100，保留两位小数
        ELSE ROUND((asm.stalking_task_count * 100.0) / sam.total_stalk_records, 2)
    END AS prcnt
FROM StalkingAgentMetrics sam
LEFT JOIN (
    SELECT
        Agent,
        COUNT(*) AS stalking_task_count
    FROM Assigned
    WHERE task = 'Stalking'
      AND taskDate BETWEEN (SELECT MIN(stalkDate) FROM Stalking WHERE Agent = Assigned.Agent)
                      AND (SELECT MAX(stalkDate) FROM Stalking WHERE Agent = Assigned.Agent)
    GROUP BY Agent
) asm ON sam.Agent = asm.Agent
ORDER BY sam.Agent;

问题逐一解决说明 #

1. 所有prcnt为100.00的问题
   原SQL的核心问题通常是未正确过滤时间范围或分子分母逻辑颠倒：

   • 修正后通过CTE先计算每个Agent的Stalking时间区间，再在Assigned子查询中严格匹配该区间的taskDate，确保只统计对应时段内的'Stalking'任务；
   • 明确占比计算逻辑：用Assigned符合条件的记录数除以Stalking总记录数，而非颠倒计算。

2. mrEnglish的除零处理
   通过CASE分支单独判断mrEnglish，当该Agent没有符合条件的Assigned记录（stalking_task_count为0或NULL）时，直接赋值100.00，同时用COALESCE避免NULL引发的除零错误。

3. Assigned无记录的mrPowers赋值0.00
   使用LEFT JOIN保留Stalking表中所有Agent，当Assigned无对应记录时，stalking_task_count为NULL，通过COALESCE转为0，再在CASE分支中为非mrEnglish的这类Agent赋值0.00。

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