Postgresql表相同数据最后一次相邻发生和第一行 [英] Postgresql Table Same Data last adjacent occurance and first In One row

问题描述

我有一个程序，每分钟PING检查一次网络中计算机的状态。
每次它将向数据库中插入新行，如下所示（我正在使用PostgreSQL）

I have a program that checks status of Computers in a network by PING each minutes. Each time it will insert a new row to DB as follows (I'm using postgresql)

id_status   status   checking_time(timestamp)   id_device(int)

1           OK       '2017-01-01 00:00:00'      1
2           OK       '2017-01-01 00:00:00'      2
3           OK       '2017-01-01 00:00:00'      3

4           Failed   '2017-01-01 00:01:00'      1
5           OK       '2017-01-01 00:01:00'      2
6           OK       '2017-01-01 00:01:00'      3

7           Failed   '2017-01-01 00:02:00'      1
8           OK       '2017-01-01 00:02:00'      2
9           OK       '2017-01-01 00:02:00'      3

10          Failed   '2017-01-01 00:03:00'      1
11          OK       '2017-01-01 00:03:00'      2
12          OK       '2017-01-01 00:03:00'      3

13          OK       '2017-01-01 00:04:00'      1
14          OK       '2017-01-01 00:04:00'      2
15          OK       '2017-01-01 00:04:00'      3

我希望结果如下

status   from_time(timestamp)    to_time(timestamp)      id_device(int)

OK       '2017-01-01 00:00:00'   '2017-01-01 00:01:00'   1
Failed   '2017-01-01 00:01:00'   '2017-01-01 00:04:00'   1
OK       '2017-01-01 00:04:00'   NOW                     1

OK       '2017-01-01 00:00:00'   NOW                     2
OK       '2017-01-01 00:00:00'   NOW                     3

如何获取此输出？

推荐答案

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

It is the gaps and islands problem. It can be solved as follows:

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_device
from
(
    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
) t
order by  t.id_device, t.from_time

dbffile演示

dbffile demo

最关键的是嵌套的子查询，其中我使用两个 row_number 函数来隔离连续的occu在设备上具有相同状态。一旦获得 grn 的值，其余的操作就很容易。

The crucial is the most nested subquery where I use two row_number functions in order to isolate consecutive occurrence of the same status on a device. Once you have the grn value then the rest is easy.

结果

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

类似的问题

SQL查询以获取最小，最大行

这篇关于Postgresql表相同数据最后一次相邻发生和第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！