我正在为一个大学项目工作,这取决于我:
我有一张这样的桌子:
我想获得执行器处于状态的最大持续时间。例如,cool0持续18分钟。
结果表应如下所示:
NAME COOL0
State False
Duration 18
我正在为一个大学项目工作,这取决于我:
我有一张这样的桌子:
我想获得执行器处于状态的最大持续时间。例如,cool0持续18分钟。
结果表应如下所示:
NAME COOL0
State False
Duration 18
这是一个悬而未决的问题。您的数据有点难以理解,但我认为:
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;