Re: Window function?

[Thiemo Kellner <thiemo@xxxxxxxxxxxxxxxxxxxx>]

Hi Robert

Interesting problem. I need to think about it.

You need to figure out when Input changes. You can achieve this by using lead or lag (depending of the sort direction over start) https://www.postgresql.org/docs/current/functions-window.html .

Hope this nudges you to a solution.

Kind regards

Thiemo

Am 04.06.22 um 10:18 schrieb Robert Stanford:

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