Window function?

From: Robert Stanford <rstanford(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Window function?
Date: 2022-06-04 08:18:30
Message-ID: CAC1FqCEFtphM0EVZY01TA5TkSn8v8YdQRpXgasfmVYPtK-EsRw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have time series data from multiple inputs with start and
end timestamps.

Can anyone suggest an elegant way to coalesce consecutive rows so only the
first start time and last end time for each group of events (by input) is
returned.

Sample from and to below where the rows for Input number 4 could be
massaged.
(Timestamps selected with timestamp(0) for convenience)

From this:
Input Start End
5 2022-06-04 09:09:00 2022-06-04 09:09:29
4 2022-06-04 09:08:50 2022-06-04 09:09:00
4 2022-06-04 09:08:10 2022-06-04 09:08:50
4 2022-06-04 09:07:47 2022-06-04 09:08:10
17 2022-06-04 09:06:47 2022-06-04 09:07:47
4 2022-06-04 09:06:37 2022-06-04 09:06:47
4 2022-06-04 09:06:29 2022-06-04 09:06:37
4 2022-06-04 09:06:17 2022-06-04 09:06:29
4 2022-06-04 09:05:53 2022-06-04 09:06:17
16 2022-06-04 09:04:33 2022-06-04 09:05:53

To this:
Input Start End
5 2022-06-04 09:09:00 2022-06-04 09:09:29
4 2022-06-04 09:07:47 2022-06-04 09:09:00
17 2022-06-04 09:06:47 2022-06-04 09:07:47
4 2022-06-04 09:05:53 2022-06-04 09:06:47
16 2022-06-04 09:04:33 2022-06-04 09:05:53

Thanks in advance to anyone who can help!
Robert

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tim Kelly 2022-06-04 11:32:18 Re: unoptimized nested loops
Previous Message Tom Lane 2022-06-04 05:37:43 Re: '{"x": 42, "y": null}'::jsonb != '{"x": 42}'::jsonb ... Really?