SQL Server 2019存储过程：获取通话时长总和达指定区间的前X条记录

问题分析 #

你需要实现的是：输入目标分钟数（如20），找出前X条记录，使得这些记录的call_length总和落在[目标值, 目标值+1]分钟区间内（四舍五入到最近分钟）。现有代码存在两个核心问题：

1. 窗口函数错误使用PARTITION BY ReviewID，导致每个ReviewID的累计和仅为自身的通话时长，而非全局累计。
2. Call_Length的转换逻辑错误：用REPLACE将冒号替换为小数点会误判秒数（例如0:30会被转为0.30分钟，实际应为0.5分钟）。

修正后的代码 #

DECLARE @Call_Mins INT = 20;
DECLARE @TargetMin FLOAT = @Call_Mins;
DECLARE @TargetMax FLOAT = @Call_Mins + 1;

WITH CallMinutes AS (
    -- 将时分格式的Call_Length转换为精确的分钟数
    SELECT 
        reviewid,
        DATEDIFF(SECOND, '00:00:00', '00:' + Call_Length) / 60.0 AS call_length_mins
    FROM LegacyReviews
),
CumulativeCalls AS (
    -- 计算按ReviewID排序的累计通话时长总和，并标记行号
    SELECT 
        reviewid,
        call_length_mins,
        SUM(call_length_mins) OVER (ORDER BY reviewid ASC) AS cumulative_sum,
        ROW_NUMBER() OVER (ORDER BY reviewid ASC) AS row_num
    FROM CallMinutes
),
TargetRowRange AS (
    -- 定位累计和首次进入目标区间的最后一条记录行号
    SELECT MAX(row_num) AS target_row
    FROM CumulativeCalls
    WHERE cumulative_sum <= @TargetMax
    AND cumulative_sum >= @TargetMin
)
-- 取出前target_row条记录，它们的累计总和恰好落在目标区间内
SELECT 
    c.reviewid,
    c.call_length_mins,
    ROUND(c.cumulative_sum, 2) AS rounded_cumulative_sum -- 可选：保留两位小数便于查看
FROM CumulativeCalls c
CROSS JOIN TargetRowRange t
WHERE c.row_num <= t.target_row;

代码说明 #

1. CallMinutes CTE：使用DATEDIFF计算通话时长的精确分钟数，避免字符串替换带来的计算错误。
2. CumulativeCalls CTE：通过无分区的窗口函数SUM(...) OVER (ORDER BY reviewid ASC)计算全局累计时长，同时标记行号用于定位前X条记录。
3. TargetRowRange CTE：找到累计和落在目标区间内的最后一条记录的行号，以此确定需要取出的前X条记录范围。
4. 最终查询：取出所有行号小于等于目标行号的记录，这些记录的累计总和恰好满足[20,21]分钟的要求。

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