On Tue, Oct 22, 2013 at 8:41 AM, Rémi Cura <remi.cura@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
