SQL Server中随时间计算支持工单处理时长中位数的技术需求

我懂你这种困扰——异常值一出来，平均时长直接就失去参考意义了，中位数才是能反映大多数工单处理效率的靠谱指标。在SQL Server里实现按年月分组的中位数计算，分两种场景给你具体方案：

方案1：用SQL Server 2012+内置函数（推荐） #

从SQL Server 2012开始，官方提供了PERCENTILE_CONT和PERCENTILE_DISC两个函数，专门用来计算百分位数（中位数就是50%分位数），写法简洁还高效。

先看完整的SQL示例：

-- 第一步：计算每个已关闭工单的处理时长，并按年月分组
WITH TicketMetrics AS (
    SELECT
        -- 把日期转成当月第一天，方便按年月聚合
        DATEFROMPARTS(YEAR(CreateDate), MONTH(CreateDate), 1) AS TicketMonth,
        -- 这里用小时作为时长单位，你可以换成DAY/MINUTE等符合业务需求的单位
        DATEDIFF(HOUR, CreateDate, CloseDate) AS ProcessingHours
    FROM SupportTickets
    WHERE CloseDate IS NOT NULL -- 只统计已完成关闭的工单
)
-- 第二步：按年月计算中位数
SELECT DISTINCT
    TicketMonth,
    -- 连续型中位数：如果是偶数个值，会在中间两个值之间插值
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ProcessingHours) OVER (PARTITION BY TicketMonth) AS Median_Continuous,
    -- 离散型中位数：直接取数据集中实际存在的中间值
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY ProcessingHours) OVER (PARTITION BY TicketMonth) AS Median_Discrete
FROM TicketMetrics;

两种函数的区别： #

• PERCENTILE_CONT(0.5)：适合需要“理论中位数”的场景，比如当工单数量是偶数时，会计算中间两个时长的平均值。
• PERCENTILE_DISC(0.5)：适合需要“实际存在的工单时长”的场景，会直接选取排序后中间位置的那个时长值。

方案2：兼容SQL Server 2008及更早版本 #

如果你的SQL Server版本比较老，没法用上面的新函数，可以用传统的窗口函数方法来实现：

WITH TicketMetrics AS (
    SELECT
        DATEFROMPARTS(YEAR(CreateDate), MONTH(CreateDate), 1) AS TicketMonth,
        DATEDIFF(HOUR, CreateDate, CloseDate) AS ProcessingHours,
        -- 给每个年月组内的时长排序，生成行号
        ROW_NUMBER() OVER (PARTITION BY DATEFROMPARTS(YEAR(CreateDate), MONTH(CreateDate), 1) ORDER BY DATEDIFF(HOUR, CreateDate, CloseDate)) AS RowNum,
        -- 统计每个年月组的工单总数
        COUNT(*) OVER (PARTITION BY DATEFROMPARTS(YEAR(CreateDate), MONTH(CreateDate), 1)) AS TotalTickets
    FROM SupportTickets
    WHERE CloseDate IS NOT NULL
)
SELECT
    TicketMonth,
    -- 奇数个值取中间行，偶数个值取中间两行的平均值
    AVG(CAST(ProcessingHours AS DECIMAL(10,2))) AS MedianProcessingHours
FROM TicketMetrics
-- 筛选出中间位置的行：(总数+1)/2 和 (总数+2)/2 覆盖奇偶两种情况
WHERE RowNum IN ((TotalTickets + 1)/2, (TotalTickets + 2)/2)
GROUP BY TicketMonth;

注意事项： #

• 一定要过滤掉CloseDate IS NULL的工单，避免计算未完成工单的无效时长。
• 时长单位要统一：比如统一用小时、天或者分钟，不要混合计算。
• 如果是SQL Server 2008及更早版本，DATEFROMPARTS不可用，可以用CAST(YEAR(CreateDate) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(CreateDate) AS VARCHAR(2)),2) + '-01'生成年月标识，记得转成日期类型。

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