From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Stanford <rstanford(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Window function? |
Date: | 2022-06-04 16:25:55 |
Message-ID: | CAKFQuwZWdZzPT2brDH_k+dqC2DxPwgMDc0ZXzXBZ09g_WMQ-Ow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Jun 4, 2022 at 7:50 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Stanford <rstanford(at)gmail(dot)com> writes:
> > 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.
>
> Window functions don't change the number of query result rows, so that
> idea is a dead end. The standard way to solve this is GROUP BY:
>
> SELECT Input, MIN(Start) AS Start, MAX(End) AS End
> FROM ...
> GROUP BY Input
> ORDER BY Input -- optional
>
Assuming that input group 4 really is supposed to show up twice in the
output then the final solution is going to have to be a combination of this
and window functions. You need the later in order to be able to say "input
4, appearance 1" and "input 4, appearance 2" - which lead/lag tends to help
with. Then the group by becomes {input, appearance}.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-06-04 17:05:29 | Re: Call pstrdup() of palloc.h will change source string, please help! |
Previous Message | Jeff Janes | 2022-06-04 16:00:12 | Re: Why password authentication failed for user "postgres"? |