Hmm exactly what I was thinking !
Thank you a lot, I spend many hours thinking about this and this solution
is very nice.
Cheers,
Rémi-C
2013/10/22 Merlin Moncure <mmonc...@gmail.com>
> On Tue, Oct 22, 2013 at 10:01 AM, Elliot <yields.falseh...@gmail.com>
> wrote:
> > On 2013-10-21 20:38, Robert James wrote:
> >>
> >> I have a table of event_id, event_time. Many times, several events
> >> happen in a row. I'd like a query which replaces all of those events
> >> with a single record, showing the count.
> >>
> >> Eg: Take A,A,A,B,C,A,D,A,A,D,D,B,C,C and return: A,3; B,1; C,1; A,1;
> >> D,1; A,2; D,2; B,1; C,2
> >>
> >> How can I do that?
> >>
> >>
> >
> > It looks like you already found a solution, but here's one with a CTE. I
> > cobbled this together from an older query I had for doing something
> similar,
> > for which I unfortunately lost the original source of this approach.
> Also,
> > this implies that there is something that gives an ordering to these rows
> > (in this case, the field "i").
> >
> > create temp table data (i int, val char);
> >
> > insert into data (val, i)
> > values
> > ('A',1),
> > ('A',2),
> > ('A',3),
> > ('B',4),
> > ('C',5),
> > ('A',6),
> > ('D',7),
> > ('A',8),
> > ('A',9),
> > ('D',10),
> > ('D',11),
> > ('B',12),
> > ('C',13),
> > ('C',14)
> > ;
> >
> > with x
> > as
> > (
> > select i,
> > row_number() over () as xxx,
> > val,
> > row_number() over (partition by val order by i asc)
> > - row_number() over () as d
> > from data
> > order by i
> > )
> > select val,
> > count(*)
> > from x
> > group by d,
> > val
> > order by min(i)
>
> wow, that's really clever.
>
> merlin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
