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

由 发布于 postgresqlsql
 收藏

我正在为一个大学项目工作，这取决于我：

我有一张这样的桌子：

enter image description here

我想获得执行器处于状态的最大持续时间。例如，cool0持续18分钟。

结果表应如下所示：

NAME         COOL0   
State        False
Duration     18
评论
  • Otis
    Otis 回复

    这是一个悬而未决的问题。您的数据有点难以理解，但我认为：

    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)

    For the maximum per actuator, use 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;