如何在postgresql中找到一个值保持相同状态的最大时差?

mgdq6dx1  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(389)

我为一个大学项目工作,突然想到:
我有一张这样的table:

我想得到执行器处于状态的最长持续时间。例如,cool0持续了18分钟。
结果表应如下所示:

NAME         COOL0   
State        False
Duration     18
fjnneemd

fjnneemd1#

这是一个缺口和孤岛问题。你的数据有点难以理解,但我认为:

select actuator, state, min(actuator_time), max(actuator_time)
from (select t.*,
             row_number() over (partition by actuator order by actuator_time) as seqnum,
             row_number() over (partition by actuator, state order by actuator_time) as seqnum_s
      from t
     ) t
group by actuator, (seqnum- seqnum_s)

对于每个致动器的最大值,使用 distinct on :

select distinct on (actuator) actuator, state, min(actuator_time), max(actuator_time)
from (select t.*,
             row_number() over (partition by actuator order by actuator_time) as seqnum,
             row_number() over (partition by actuator, state order by actuator_time) as seqnum_s
      from t
     ) t
group by actuator, (seqnum- seqnum_s)
order by actuator, max(actuator_time) - min(actuator_time) desc;

相关问题