SQL Server 2000数据库：计算停机时长总和及占比技术需求

嘿，针对你在SQL Server 2000里的需求，我来一步步拆解出可行的实现方案。先提前说明下：我会假设一个常见的事件表结构，要是你的表字段名或逻辑有差异，对应调整就行。

前提假设（可根据实际修改） #

• 事件表名为 EventLog
• 核心字段：EventID（事件标识，取值1200/1201，假设1200是停机开始、1201是停机结束）、EventTime（事件发生时间）、DeviceID（可选，如果你需要按设备分组计算）
• 逻辑规则：每一条1201事件都对应一条最近的、未被其他事件打断的1201事件

第一步：计算总停机时长 TimeFull #

SQL Server 2000不支持LEAD()/LAG()这类高版本窗口函数，所以我们用自连接+子查询的方式来精准配对每一组启停事件，再计算时间差总和：

SELECT 
    -- 如果需要按设备分组，就保留下面这行
    -- DeviceID,
    -- 先计算总分钟数，方便后续占比计算
    SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) AS TotalMinutes,
    -- 把总分钟数转成HH:MM:SS格式的TimeFull
    CONVERT(VARCHAR, SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) / 60) + ':' +
    RIGHT('0' + CONVERT(VARCHAR, SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) % 60), 2) + ':00' AS TimeFull
FROM 
    EventLog start_event
INNER JOIN 
    EventLog end_event 
        ON start_event.EventID = 1200 
        AND end_event.EventID = 1201
        AND end_event.EventTime > start_event.EventTime
        -- 如果按设备分组，加上这句：AND start_event.DeviceID = end_event.DeviceID
        -- 关键：确保每对启停事件之间没有其他1200/1201事件，避免错误配对
        AND NOT EXISTS (
            SELECT 1 
            FROM EventLog mid_event
            WHERE mid_event.EventID IN (1200, 1201)
            AND mid_event.EventTime BETWEEN start_event.EventTime AND end_event.EventTime
            AND mid_event.EventID != start_event.EventID
            -- 按设备分组时需加上：AND mid_event.DeviceID = start_event.DeviceID
        )

第二步：计算停机占比 Downtime% #

基于上面的总时长，和10小时（即600分钟）计算占比，这里提供两种常用展示方式：

方式1：百分比数值（业务分析常用） #

SELECT 
    -- 分组字段按需保留
    -- DeviceID,
    TimeFull,
    -- 计算占比并保留两位小数
    ROUND((TotalMinutes / 600.0) * 100, 2) AS [Downtime%]
FROM (
    -- 嵌入第一步的查询作为子查询
    SELECT 
        -- DeviceID,
        SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) AS TotalMinutes,
        CONVERT(VARCHAR, SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) / 60) + ':' +
        RIGHT('0' + CONVERT(VARCHAR, SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) % 60), 2) + ':00' AS TimeFull
    FROM 
        EventLog start_event
    INNER JOIN 
        EventLog end_event 
            ON start_event.EventID = 1200 
            AND end_event.EventID = 1201
            AND end_event.EventTime > start_event.EventTime
            -- AND start_event.DeviceID = end_event.DeviceID
            AND NOT EXISTS (
                SELECT 1 
                FROM EventLog mid_event
                WHERE mid_event.EventID IN (1200, 1201)
                AND mid_event.EventTime BETWEEN start_event.EventTime AND end_event.EventTime
                AND mid_event.EventID != start_event.EventID
                -- AND mid_event.DeviceID = start_event.DeviceID
            )
) AS DowntimeSummary

方式2：时间比例字符串（和你示例格式一致） #

注意SQL Server 2000不支持CONCAT()，所以用+拼接字符串：

SELECT 
    -- 分组字段按需保留
    -- DeviceID,
    TimeFull,
    TimeFull + ' ÷ 10:00:00 = ' + CONVERT(VARCHAR, ROUND((TotalMinutes / 600.0) * 100, 2)) + '%' AS [Downtime%]
FROM (
    -- 同样嵌入第一步的子查询
    SELECT 
        -- DeviceID,
        SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) AS TotalMinutes,
        CONVERT(VARCHAR, SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) / 60) + ':' +
        RIGHT('0' + CONVERT(VARCHAR, SUM(DATEDIFF(MINUTE, start_event.EventTime, end_event.EventTime)) % 60), 2) + ':00' AS TimeFull
    FROM 
        EventLog start_event
    INNER JOIN 
        EventLog end_event 
            ON start_event.EventID = 1200 
            AND end_event.EventID = 1201
            AND end_event.EventTime > start_event.EventTime
            -- AND start_event.DeviceID = end_event.DeviceID
            AND NOT EXISTS (
                SELECT 1 
                FROM EventLog mid_event
                WHERE mid_event.EventID IN (1200, 1201)
                AND mid_event.EventTime BETWEEN start_event.EventTime AND end_event.EventTime
                AND mid_event.EventID != start_event.EventID
                -- AND mid_event.DeviceID = start_event.DeviceID
            )
) AS DowntimeSummary

额外注意事项 #

1. 精度调整：如果需要秒级精度，把所有DATEDIFF(MINUTE, ...)改成DATEDIFF(SECOND, ...)，再调整时分秒的转换逻辑即可。
2. 不完整配对处理：如果存在1200无对应1201、或1201无对应1200的情况，可用LEFT JOIN/RIGHT JOIN替代INNER JOIN，并额外处理NULL值。
3. 性能优化：如果数据量较大，建议给EventID、EventTime（以及DeviceID如果分组的话）创建索引，提升查询速度。

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