开发存储过程：计算存在sys_run_hrs重置的系统运行总时长

需求概述 #

需要创建一个SQL Server存储过程，实现以下功能：

• 接收三个参数：查询起始日期@start_date、结束日期@end_date、分号分隔的位置ID列表@locations
• 从包含location_id、status_date、sys_run_hrs的历史表中，计算指定位置在日期范围内的有效系统运行总时长
• 核心难点：处理sys_run_hrs字段的重置情况（即数值突然变小，代表设备重启或计数重置），仅统计每个递增区间的时长增量

示例数据 #

调用示例 #

exec sys_run_hrs @start_date='2024-01-01', @end_date='2024-01-04', @locations='2;7';

期望输出 #

解决方案：完整存储过程代码 #

CREATE PROCEDURE sys_run_hrs
    @start_date DATE,
    @end_date DATE,
    @locations NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 步骤1：拆分分号分隔的location参数为临时表
    CREATE TABLE #TempLocations (location_id INT);
    DECLARE @splitChar CHAR(1) = ';', @start INT = 1, @end INT;

    WHILE CHARINDEX(@splitChar, @locations, @start) > 0
    BEGIN
        SET @end = CHARINDEX(@splitChar, @locations, @start);
        INSERT INTO #TempLocations (location_id)
        VALUES (CAST(SUBSTRING(@locations, @start, @end - @start) AS INT));
        SET @start = @end + 1;
    END
    -- 插入最后一个location
    INSERT INTO #TempLocations (location_id)
    VALUES (CAST(SUBSTRING(@locations, @start, LEN(@locations) - @start + 1) AS INT));

    -- 步骤2：处理重置区间，计算有效运行时长
    WITH RankedData AS (
        SELECT 
            hb.location_id,
            hb.status_date,
            hb.sys_run_hrs,
            -- 标记重置区间：当前sys_run_hrs小于前一行时，区间编号+1
            SUM(CASE WHEN hb.sys_run_hrs < LAG(hb.sys_run_hrs) OVER (PARTITION BY hb.location_id ORDER BY hb.status_date) THEN 1 ELSE 0 END) 
                OVER (PARTITION BY hb.location_id ORDER BY hb.status_date) AS interval_id
        FROM history_table hb
        WHERE 
            hb.status_date BETWEEN @start_date AND @end_date
            AND hb.location_id IN (SELECT location_id FROM #TempLocations)
    ),
    IntervalTotals AS (
        SELECT 
            location_id,
            interval_id,
            -- 每个区间的时长增量=区间最后值-区间初始值
            MAX(sys_run_hrs) - MIN(sys_run_hrs) AS interval_hours
        FROM RankedData
        GROUP BY location_id, interval_id
    )
    -- 汇总每个location的总时长
    SELECT 
        location_id,
        SUM(interval_hours) AS total_hours
    FROM IntervalTotals
    GROUP BY location_id
    ORDER BY location_id;

    -- 清理临时表
    DROP TABLE #TempLocations;
END

关键逻辑说明 #

1. 参数拆分：通过循环将分号分隔的@locations字符串拆分为临时表，方便后续IN查询
2. 重置区间标记：使用LAG()窗口函数获取前一行的sys_run_hrs，当当前值小于前一行时，判定为重置，通过SUM()累加生成唯一的区间ID
3. 区间时长计算：按location_id和interval_id分组，取每个区间的最大和最小sys_run_hrs差值，即为该区间的有效运行时长
4. 总时长汇总：对每个location的所有区间时长求和，得到最终结果

原代码问题分析 #

你提供的原有代码直接取每个location的首尾sys_run_hrs值相减，没有处理重置情况：

• 对于location 2，原代码会计算3-2=1，但实际有效时长是(3-2)+(3-0)=4
• 这种方式仅适用于sys_run_hrs持续递增的场景，无法覆盖重置后的新递增区间

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