Re: Window function?

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