用于评估聚合持续时间的 MySQL 查询是 "on"

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 小时内(比如说)ON 状态的总持续时间。

在这个简化的例子中，total duration = 1 是(04:00:00->08:00:00, 09:00:00->15:00:00, 20:00:00->23:59:59即 13:59:59 大约 14 小时

我无法确定这是否可以单独在 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);