On Tue, Oct 22, 2013 at 8:16 AM, Rémi Cura <remi.c...@gmail.com> wrote: > Hey, > when using a for you implicitly use a cursor (I think), > so this is the same, use FOR if you like it more. > It should be *very* fast to write ! > > As I wrote, relational algebra can handle it, but it is not practically > feasible : > > If you just execute 3 times the query I wrote, you will have your answer. > It is 3 times because the biggest sequence is A A A A. > That's the problem, your number of execution depends on the max size of > sequence. > > The problems boils down to this : the answer for one row depends on the > answer of the previous row, the row before , etc. > > You could succeed with ordering by id in a windows function, and in this > window function order by new_id and putting null to the end, but such nested > windows functions calls are not allowed. > > Nevertheless if you find something purely relational please keep me posted !
CREATE TYPE count_same_t AS ( item TEXT, count_item INT ); CREATE OR REPLACE FUNCTION count_same_internal(state count_same_t, item TEXT) RETURNS count_same_t AS $$ BEGIN state.count_item := CASE WHEN item = state.item THEN state.count_item + 1 ELSE 1 END; state.item := item; RETURN state; END; $$ LANGUAGE PLPGSQL; CREATE FUNCTION count_same_count(state count_same_t) RETURNS INT AS $$ BEGIN RETURN state.count_item; END; $$ LANGUAGE PLPGSQL; CREATE AGGREGATE count_same(TEXT) ( SFUNC=count_same_internal, STYPE=count_same_t, FINALFUNC=count_same_count, INITCOND='(,)' ); WITH testdata as (select s, chr((floor(random() * 3))::int + 65) as v from generate_series(1,50) s) SELECT s, v, count_same(v) OVER(order by s) from testdata; s | v | count_same ----+---+------------ 1 | A | 1 2 | B | 1 3 | A | 1 4 | A | 2 5 | C | 1 6 | A | 1 7 | C | 1 8 | C | 2 9 | C | 3 10 | C | 4 /snip merlin :-D -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general