Re: Count of records in a row

On Tue, Oct 22, 2013 at 8:41 AM, Rémi Cura <remi(dot)cura(at)gmail(dot)com> wrote:
> héhé,
> nice snipping Merlin !
>
> I guess you are almost there, output is still wrong (should be) (
>> 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
> )
>
> I don't understand enough to make the modifications =)

oh right -- whoops.

CREATE TYPE count_same_t AS
(
item TEXT,
item_group INT
);

CREATE OR REPLACE FUNCTION count_same_internal(state count_same_t,
item TEXT) RETURNS count_same_t AS
$$
BEGIN
state.item_group := CASE WHEN item = state.item THEN
state.item_group ELSE state.item_group + 1 END;
state.item := item;
RETURN state;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION count_same_count(state count_same_t) RETURNS INT AS
$$
BEGIN
RETURN state.item_group;
END;
$$ LANGUAGE PLPGSQL;

CREATE AGGREGATE count_same(TEXT)
(
SFUNC=count_same_internal,
STYPE=count_same_t,
FINALFUNC=count_same_count,
INITCOND='(,0)'
);

WITH testdata as (select s, chr((floor(random() * 3))::int + 65) as v
from generate_series(1,50) s)
select v, count(*) from (SELECT s, v, count_same(v) OVER(order by s)
grp from testdata) q
GROUP BY v, grp order by grp;

v | count
---+-------
A | 1
B | 1
A | 1
B | 2
C | 1
A | 1
C | 2
A | 1
C | 2
A | 3
B | 3
A | 1
B | 1

/snip

aside: learn the technique. custom aggregates may seem awkward and
weird at first, but they can be used to solve all sorts of wonderful
problems.

merlin