Postgresql Table Same Data last adjacent occurance and first In One row

Question

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

I want result to be as follows

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

How can I get this output?.

Radim Bača · Accepted Answer · 2017-12-06 08:16:09Z

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 demo

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.

Result

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

Similar questions

SQL query to get min, max rows

Collectives™ on Stack Overflow

Postgresql Table Same Data last adjacent occurance and first In One row

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related