Re: Count of records in a row - Mailing list pgsql-general

From Rémi Cura
Subject Re: Count of records in a row
Date
Msg-id CAJvUf_tg+zKXFnXpRXohAA9CsTaV6+vbQr51YPR8nbP2GWdNtg@mail.gmail.com
Whole thread Raw
In response to Re: Count of records in a row  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-general
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 <mmoncure@gmail.com>
On Tue, Oct 22, 2013 at 10:01 AM, Elliot <yields.falsehood@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

pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Count of records in a row
Next
From: Shaun Thomas
Date:
Subject: Re: Backup Question