如何计算serverstatus数据库表的总停机时长？自连接尝试未果

我来帮你搞定这个问题！你的原SQL之所以出错，是因为它把所有UP状态的记录和之前所有DOWN的记录做了配对，这样会重复计算停机时间——比如每条UP都会和之前所有的DOWN关联，导致结果完全不对。

正确的思路应该是把每一段连续的停机时间（从DOWN到下一个UP）单独计算，然后求和；如果到给定时间点服务器还处于DOWN状态，那还要把从最后一次DOWN到给定时间的时长也算进去。

具体实现方案 #

我们可以用窗口函数LEAD()来关联每条DOWN记录对应的下一条UP记录，再结合给定时间点来确定每个停机时段的结束时间，最后求和所有停机时长。

假设给定时间点是'16:00'，对应的SQL如下（以MySQL为例，其他数据库逻辑类似，时间函数可能略有差异）：

WITH status_ordered AS (
    -- 给每条记录按时间排序，获取下一条记录的时间
    SELECT 
        Time, 
        Status,
        LEAD(Time) OVER (ORDER BY Time) AS next_status_time
    FROM serverstatus
),
downtime_intervals AS (
    -- 筛选出所有停机开始的记录，并确定停机结束时间
    SELECT 
        Time AS downtime_start,
        CASE
            -- 如果下一条记录不存在（最后一条是DOWN），或者下一条时间晚于给定时间，就用给定时间作为结束
            WHEN next_status_time IS NULL OR next_status_time > '16:00' THEN '16:00'
            -- 否则用下一条UP的时间作为停机结束时间
            ELSE next_status_time
        END AS downtime_end
    FROM status_ordered
    WHERE Status = 'DOWN'
)
-- 计算所有停机时段的总时长（这里以分钟为单位，可根据需求调整）
SELECT 
    SUM(TIMESTAMPDIFF(MINUTE, downtime_start, downtime_end)) AS total_downtime_minutes,
    -- 转换成小时+分钟的格式
    CONCAT(
        FLOOR(SUM(TIMESTAMPDIFF(MINUTE, downtime_start, downtime_end)) / 60),
        '小时',
        SUM(TIMESTAMPDIFF(MINUTE, downtime_start, downtime_end)) % 60,
        '分钟'
    ) AS total_downtime_hhmm
FROM downtime_intervals;

逻辑拆解 #

1. status_ordered CTE：通过LEAD()窗口函数，为每条记录获取它的下一条记录的时间。这样每个DOWN记录就能直接关联到后续的第一个UP记录时间。
2. downtime_intervals CTE：筛选出所有DOWN状态的记录，然后判断停机结束时间：
   • 如果当前DOWN是最后一条记录，或者下一条记录的时间晚于给定时间，就用给定时间作为停机结束点；
   • 否则用下一条UP的时间作为停机结束点。
3. 最终求和：用TIMESTAMPDIFF()计算每个停机时段的分钟数，求和后可以转换成更友好的小时+分钟格式。

适配不同数据库 #

如果你的数据库不是MySQL，只需要调整时间计算函数即可：

• PostgreSQL可以用EXTRACT(EPOCH FROM (downtime_end - downtime_start)) / 60计算分钟数；
• SQL Server可以用DATEDIFF(MINUTE, downtime_start, downtime_end)。

测试你的示例数据 #

用你提供的示例数据运行这个SQL，给定时间16:00时，计算结果就是90分钟（1小时30分钟），和你预期的一致。

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