MySQL获取间隙之间的持续时间

Question

我正在尝试计算单位未达到 100% 的持续时间。

|ID| DATE               | UNITNAME | PERCENTAGE|  
------------------------------------------------
|1 |2018-01-20 00:00:00 | UNIT1    |100        |
|2 |2018-01-20 00:05:00 | UNIT1    |0          |
|3 |2018-01-20 00:10:00 | UNIT1    |100        |
|4 |2018-01-20 00:15:00 | UNIT1    |99         | 
|5 |2018-01-20 00:20:00 | UNIT1    |50         |
|6 |2018-01-20 00:25:00 | UNIT1    |100        |

所以我希望输出类似于:

| UNITNAME | RPO         |
| UNIT1    | 00:15:00    | 

单位在 00:05:00 报告为 0%，然后在下一个时间间隔 00:10:00 报告为 100%。所以我假设该装置在 5 分钟内没有 RPO。这种情况在 00:15:00 到 00:25:00 再次发生，因此假设这在整个期间都没有发生。

我环顾四周并设法在 SQL Fiddle 上创建了后续内容使用以下；

select UNITNAME, SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) as 'RPO'
from(
      SELECT UNITNAME, TIMEDIFF(MAX(DATE), MIN(DATE)) AS duration, MIN(DATE) AS 
startime, MAX(DATE) AS endtime
FROM (
    SELECT 
tp.*
, @val_change := IF(@prev_val != Percentage, @val_change+1 , @val_change) AS vc
, @prev_val := Percentage
FROM
Replication_History tp 
, (SELECT @prev_val := NULL, @val_change := 0) var_init_subquery
ORDER BY UNITNAME, DATE
 ) sq
 WHERE Percentage =100 
 GROUP BY UNITNAME, vc
 )ts
 group by UNITNAME

这种方法有效，但不能正确管理 100% 之间的差距。

有没有人做过类似的事情？

Answer 1

它只是这些值之间差异的总和吗...

SELECT a.unitname
     , MIN(a.date) my_start
     , a.min_date my_end 
  FROM 
     ( SELECT x.*
            , MIN(y.date) min_date 
         FROM replication_history x 
         LEFT 
         JOIN replication_history y 
           ON y.unitname = x.unitname 
          AND y.date > x.date 
          AND y.percentage = 100 
        WHERE x.percentage < 100 
        GROUP 
           BY x.id
     ) a 
 GROUP 
    BY unitname
     , my_end;
+----------+---------------------+---------------------+
| unitname | my_start            | my_end              |
+----------+---------------------+---------------------+
| UNIT1    | 2018-01-20 10:15:00 | 2018-01-20 10:25:00 |
| UNIT2    | 2018-01-20 10:05:00 | 2018-01-20 10:10:00 |
| UNIT2    | 2018-01-20 10:15:00 | 2018-01-20 10:25:00 |
+----------+---------------------+---------------------+