用于评估某事物“开启”的总持续时间的 MySQL 查询

Question

我在表中有一系列带时间戳的开/关数据，代表开/关状态，或者状态“开始”的点，

00:00:00    0
04:00:00    1
08:00:00    0
09:00:00    1
15:00:00    0
20:00:00    1
23:59:59    0

我需要计算（例如）24 小时内开状态的总持续时间。

在此简化示例中，总持续时间 = 1 为 (04:00:00->08:00:00、09:00:00->15:00:00、20:00:00->23:59:59 即 13:59:59 大约 14h

我无法确定这是否可以单独在 SQL 中完成，或者我使用的底层框架 (django) 是否需要根据返回的数据来完成此操作。如果可能的话，我显然更愿意让数据库承担繁重的工作，因为我们可能还需要在单独的统计数据包中使用 SQL。

我不清楚我是否可以对选择中的上一个或下一个元素进行操作，我是一个自信的 SQL 用户，但不知道从哪里开始这个或通用方法，有什么想法吗？

我真的很喜欢在一个查询中做到这一点，或者用其他一些聪明的方法来计算我所缺少的！

Answer 1

MySQL 中没有 row_number()，但您可以执行双连接来搜索上一行：

select 
    sum(case when cur.state = 0 then 0
        else subtime(cur.timeCol, prev.timeCol)
        end) as TotalOnTime
from YourTable cur
join YourTable prev
    on prev.timeCol < cur.timeCol
left join YourTable inbetween
    on prev.timeCol < inbetween.timeCol
    and inbetween.timeCol < cur.timeCol
where inbetween.timeCol is null;

在 MySQL 中，您还可以使用变量，在这种情况下可能更有效：

set @total := '00:00:00';
set @lasttime := '00:00:00';

select 
    @total := addtime(@total, case 
        when state = 0 then 0
        when @lasttime is null then 0
        else subtime(timeCol, @lasttime)
        end)
,   @lasttime := timeCol
from YourTable
order by timeCol;

select 'Result = ', @total;

代码为创建并填充测试表：

DROP TABLE IF EXISTS YourTable;
CREATE TABLE YourTable (
   timeCol time,
   state bit
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into YourTable values ('00:00:00',    0);
insert into YourTable values ('04:00:00',    1);
insert into YourTable values ('08:00:00',    0);
insert into YourTable values ('09:00:00',    1);
insert into YourTable values ('15:00:00',    0);
insert into YourTable values ('20:00:00',    1);
insert into YourTable values ('23:59:59',    0);