Pivot with row_number() in PostgreSQL

user_3707019 0
Viewed 45

I have a table schema in PostgreSQL which is used to monitor some device. Based on some condition, device enters into monitor phase and if it remains in that condition for next consecutive day, then device moves into action phase. It will remain in action phase untill the condition improves.

CREATE TABLE public.monitoring_engine
(
    cell_id character varying(1024) COLLATE pg_catalog."default",
    monitoring_started_at timestamp without time zone,
    gap character varying(1024) COLLATE pg_catalog."default",
    status character varying(1024) COLLATE pg_catalog."default"
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.monitoring_engine
    OWNER to postgres;

Insert Statement

INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_A', '2020-09-15', NULL, 'Monitor');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_A', '2020-09-16', '1 day', 'Action');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_A', '2020-09-17', '1 day', 'Action');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_A', '2020-09-18', '1 day', 'Action');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_A', '2020-09-20', '2 days', 'Monitor');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-15', NULL, 'Monitor');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-17', '2 days', 'Monitor');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-18', '1 day', 'Action');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-20', '2 days', 'Monitor');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-21', '1 day', 'Action');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-23', '2 days', 'Monitor');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-24', '1 day', 'Action');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-25', '1 day', 'Action');
INSERT INTO public.monitoring_engine(cell_id, monitoring_started_at, gap, status) VALUES ('Cell_B', '2020-09-26', '1 day', 'Action');

After executing the above queries, the following data will be available in the table.

cell_id         monitoring_started_at       gap     status
Cell_A          9/15/2020 0:00          NULL        Monitor
Cell_A          9/16/2020 0:00          1 day       Action
Cell_A          9/17/2020 0:00          1 day       Action
Cell_A          9/18/2020 0:00          1 day       Action
Cell_A          9/20/2020 0:00          2 days      Monitor
Cell_B          9/15/2020 0:00          NULL        Monitor
Cell_B          9/17/2020 0:00          2 days      Monitor
Cell_B          9/18/2020 0:00          1 day       Action
Cell_B          9/20/2020 0:00          2 days      Monitor
Cell_B          9/21/2020 0:00          1 day       Action
Cell_B          9/23/2020 0:00          2 days      Monitor
Cell_B          9/24/2020 0:00          1 day       Action
Cell_B          9/25/2020 0:00          1 day       Action
Cell_B          9/26/2020 0:00          1 day       Action

Required Output

cell_id     monitor_date        first_action_date   last_action_date
Cell_A      9/15/2020 0:00      9/16/2020 0:00      9/18/2020 0:00
Cell_A      9/20/2020 0:00      null                null
Cell_B      9/15/2020 0:00      null                null
Cell_B      9/17/2020 0:00      9/18/2020 0:00      9/18/2020 0:00
Cell_B      9/20/2020 0:00      9/21/2020 0:00      9/21/2020 0:00
Cell_B      9/23/2020 0:00      9/24/2020 0:00      9/26/2020 0:00

The required output will depend upon the consecutive dates. If there is any break in continuity of dates, then device enters monitor phase.

It needs to be done in PostgreSQL.

gaps-and-islands pivot window-functions postgresql sql
1 Answers

This is a gaps-and-islands problem. I think that the simplest approach is to compute the date of the latest "Monitor" status, and then use it to group the rows.

select 
    cell_id, 
    monitor_date, 
    min(monitoring_started_at) filter(where status = 'Action') first_action_date,
    max(monitoring_started_at) filter(where status = 'Action') last_action_date
from (
    select me.*,
        max(monitoring_started_at) 
            filter(where status = 'Monitor') 
            over(partition by cell_id order by monitoring_started_at) 
            as monitor_date
    from monitoring_engine me
) t
group by cell_id, monitor_date
order by cell_id, monitor_date

Demo on DB Fiddle:

cell_id | monitor_date        | first_action_date   | last_action_date   
:------ | :------------------ | :------------------ | :------------------
Cell_A  | 2020-09-15 00:00:00 | 2020-09-16 00:00:00 | 2020-09-18 00:00:00
Cell_A  | 2020-09-20 00:00:00 | null                | null               
Cell_B  | 2020-09-15 00:00:00 | null                | null               
Cell_B  | 2020-09-17 00:00:00 | 2020-09-18 00:00:00 | 2020-09-18 00:00:00
Cell_B  | 2020-09-20 00:00:00 | 2020-09-21 00:00:00 | 2020-09-21 00:00:00
Cell_B  | 2020-09-23 00:00:00 | 2020-09-24 00:00:00 | 2020-09-26 00:00:00
user_106767160
Related