MySQL计算单元非100%状态持续时长的技术求助

我明白你现在的困境——你想统计设备未处于100%状态的累计时长，但当前的SQL逻辑方向有误：它在计算100%状态的总时长，而非你需要的非100%时段总和。下面我会给出两种可行的实现方案，分别适配不同版本的MySQL：

方案一：使用窗口函数（MySQL 8.0+ 推荐） #

这种方法更直观，直接定位连续的非100%区间并计算时长：

WITH ranked_data AS (
    SELECT 
        UNITNAME,
        DATE,
        Percentage,
        -- 标记连续非100%的分组：每次遇到100%则分组ID递增
        SUM(CASE WHEN Percentage = 100 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY UNITNAME ORDER BY DATE) AS non_100_group
    FROM Replication_History
),
non_100_intervals AS (
    SELECT 
        UNITNAME,
        non_100_group,
        -- 非100区间的结束时间：优先取后续第一个100%的时间，无则取最后一条记录时间
        COALESCE(
            MIN(CASE WHEN Percentage = 100 THEN DATE END) OVER (PARTITION BY UNITNAME ORDER BY non_100_group ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),
            MAX(DATE) OVER (PARTITION BY UNITNAME)
        ) AS interval_end,
        MIN(DATE) AS interval_start
    FROM ranked_data
    WHERE Percentage != 100
    GROUP BY UNITNAME, non_100_group
)
SELECT 
    UNITNAME,
    SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(interval_end, interval_start)))) AS RPO
FROM non_100_intervals
GROUP BY UNITNAME;

逻辑说明： #

1. ranked_data：通过累加100%的出现次数，把连续的非100%记录归为同一个分组。比如第一个100%之后的非100%都属于non_100_group=1，下一个100%之后的非100%属于non_100_group=2。
2. non_100_intervals：筛选出非100%的记录，按分组聚合得到每个区间的开始/结束时间。
3. 最后计算每个区间的时长差，求和后转换为可读的时间格式。

方案二：变量实现（MySQL 5.x 兼容） #

如果你的MySQL版本不支持窗口函数，可以用用户自定义变量来追踪状态切换：

SELECT 
    UNITNAME,
    SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS RPO
FROM (
    SELECT 
        UNITNAME,
        TIMEDIFF(interval_end, interval_start) AS duration
    FROM (
        SELECT 
            tp.*,
            -- 从非100%切换到100%时，记录区间结束时间
            @interval_end := IF(Percentage = 100 AND @prev_percent != 100, @prev_date, @interval_end),
            -- 从100%切换到非100%时，记录区间开始时间
            @interval_start := IF(Percentage != 100 AND @prev_percent = 100, DATE, @interval_start),
            -- 更新变量状态
            @prev_percent := Percentage,
            @prev_date := DATE
        FROM Replication_History tp
        CROSS JOIN (SELECT @prev_percent := NULL, @prev_date := NULL, @interval_start := NULL, @interval_end := NULL) var_init
        ORDER BY UNITNAME, DATE
    ) sq
    -- 过滤出有效区间（同时有开始和结束时间）
    WHERE interval_end IS NOT NULL AND interval_start IS NOT NULL
    GROUP BY UNITNAME, interval_start
) ts
GROUP BY UNITNAME;

逻辑说明： #

• 遍历每条记录时，用变量追踪上一条记录的状态和时间。
• 当状态从100%切换到非100%时，记录区间开始时间；当从非100%切回100%时，记录区间结束时间。
• 最后聚合所有有效区间的时长并求和。

验证你的测试数据 #

用你提供的测试数据运行上述SQL，都会得到预期结果：

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