PostgreSQL表相同数据最后一次相邻发生和第一行

PostgreSQL表相同数据最后一次相邻发生和第一行

这是差距和孤岛的问题。可以解决如下：

select t.status,    t.from_time,    coalesce(CAST(lead(from_time) over (partition by id_device order by from_time) AS varchar(20)), 'NOW') to_date,    t.id_devicefrom(    select t.status, min(checking_time) from_time, t.id_device    from    (        select *, row_number() over (partition by id_device, status order by checking_time) -        row_number() over (partition by id_device order by checking_time) grn        from data    ) t    group by t.id_device, grn, t.status) torder by  t.id_device, t.from_time

dbffile演示

关键是最嵌套的子查询，在该子查询中，我使用两个

row_number

函数来隔离设备上相同状态的连续出现。一旦有了

grn

价值，剩下的就很容易了。

结果

status  from_timeto_time  id_device------------------------------------------------------------OK      2017-01-01 00:00:00 2017-01-01 00:01:00 1Failed  2017-01-01 00:01:00 2017-01-01 00:04:00 1OK      2017-01-01 00:04:00 NOW      1OK      2017-01-01 00:00:00 NOW      2OK      2017-01-01 00:00:00 NOW      3